Skip to Content
avatar image
Former Member

Formated Search

Hi everyone, I´m doing a formated search to multiply 2 fields and assign it to another one. This is my code

SELECT((SELECT $[$39.81.2])*(SELECT $[$39.11.2]))

And it "works" fine but when I assign it to the field, this error appears

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand data type nvarchar is invalid for multiply operator.
2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.

Then I tried with this structure

SELECT ((SELECT cast($[$39.81.2]) as numeric) *(SELECT cast($[$39.11.2]) as numeric))

But this error appears when I try to execute it

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '$39.81.2'.
2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '$39.11.2'.
3). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '

And this is when I execute it, can´t even introduce it to the field with SHIFT+F2. So, someone could help me with the syntaxis or maybe tell me another way to do this.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Feb 09, 2017 at 04:54 PM

    Hi Anderson,

    This should work:

    If you use a document with service type

    SELECT $[$39.5.NUMBER] * CAST($[$39.U_p_unitario.STRING] AS NUMERIC(18, 2))

    Or this for item type

    SELECT $[$38.14.NUMBER] * CAST($[$38.U_p_unitario.STRING] AS NUMERIC(18, 2))

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 02, 2017 at 07:34 PM

    Hi Anderson,

    Try this:

    SELECT $[$39.81.NUMBER] * $[$38.11.NUMBER]

    When you want to convert field from form in numeric format use the syntax "NUMBER" as your last parameter and is not necessary to use select always you want call some variable.

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Anderson,

      I made a mistake in my last query. As you provided in your question, the correct is $[$39.11.NUMBER], but i wrote 38.

      Now, I believe there is something wrong in the fields that you provided.

      For example for "Precio por unidad" the correct variable is $[$39.5.NUMBER], for your udf, the correct variable should be $[$39.U_p_unitario.STRING].

      Then, if I'm not wrong, try this:

      SELECT $[$39.5.NUMBER] * CAST($[$39.U_p_unitario.STRING] AS NUMERIC(18, 2))

      If the variables you provided on your question are correct, just try run again the last query, after replace 38 by 39.

      Kind Regards,

      Diego Lother

  • Feb 02, 2017 at 10:41 PM

    Hi,

    Try this,

    SELECT ((select $[$39.81.2]) * $[$39.11.2])

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      It didn´t work because of the nvharchar data

      1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand data type nvarchar is invalid for multiply operator.
      2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.

      Thanks anyway!

  • Feb 02, 2017 at 11:07 PM

    Try this,

    SELECT ((select $[$39.81.0]) * $[$39.11.number])

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 03, 2017 at 01:16 AM

    It´s working with this querie

    select (t0.pricebefdi * t0.U_p_unitario) from qut1 t0 inner join oqut t1 on t1.docentry = t0.docentry where  t1.docnum = (select $[OQUT.DOCNUM])

    The only problem is that it would only works with the OQUT table or "Sales Offer" window (I think that´s the english name, in spanish is "Oferta de venta") and I have to do one querie per window, no problem but it would be unpractical. That 2 fields are in all the 'Marketing Documents' so that wouldn´t be a problem either. So, if someone could make it work universally I would be so thankful.

    Best regards!

    Add comment
    10|10000 characters needed characters exceeded