Formated Search

[former_member325743]

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.

[former_member185682 · Accepted solution]

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

[former_member325743 · Accepted solution]

It worked! pretty cool. Thanks a lot Diego! I´ll close this thread. I´m giving you the best answer on one of your answers above because the page it doesn´t allow me to select the last answer as the right one.

[former_member185682 · Accepted solution]

Hi Anderson,

Great.

I will convert my comment to answer.

Don't forget to click on accept button below my answer.

Kind Regards,

Diego Lother

[former_member325743]

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!

[former_member325743]

Some ideas to do that query usable for all windows of Marketing Documents?

[kothandaraman_nagarajan]

Try this,

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

[kothandaraman_nagarajan]

Hi,

Try this,

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

Regards,

Nagarajan

[former_member325743]

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!

[former_member185682]

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

[former_member325743]

Hi Diego, I tried your querie but it said this

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '*'. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement 'Consultas' (OUQR) (s) could not be prepared.

Then I modified the querie and I have something like this:

SELECT((SELECT $[$39.81.NUMBER])*(SELECT $[$39.11.NUMBER]))

No error appeared but the result on the field was '0' . Maybe If I show you the fields would help to have a better understanding.

The black ones are default fields and the red one is an UDF. I want to multiply the red one and the left black one and put the result on the black right one (the one with the label 'Total (ML)' )

[former_member185682]

Hi Anderson,

I thought that your two fields were numeric, but how one of those is alpha, you need convert this field.

The your query should look like this:

SELECT CAST($[$39.81.0] AS NUMERIC(18,2)) * $[$38.11.NUMBER]

Hope it helps,

Kind Regards,

Diego Lother

[former_member325743]

Hi Diego, If I try your query this error appeared

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '$39.81.0'.
2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.

I change the querie like this

SELECT ((Select CAST($[$39.81.0] AS NUMERIC(18,2))) * (Select $[$38.11.NUMBER]))

It also has an error, this one

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '$39.81.0'.
2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ')'.
3). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI

But I assigned to the field anyways and said this when I execute it

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type nvarchar to numeric.
 '' (SEVT)

So I am not sure what´s going on or where is the error.

[former_member185682]

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