Query in Inventory Transfer

[Former Member]

Hi,

I have created a line UDF(U_UnitPrice) in Inventory Transfer.

This UDF draws the value according to rules:

- If the IT is copied from ITR, it draws the value of U_UnitPrice of ITR.

- If the IT is standalone, it draws the value from a price list.

The query I used is:

case when (ISNULL($[$38.43.0], -1) > -1)

        then select $[$38.U_UnitPrice.0]            

else

       select T0.[Price] FROM ITM1 T0

       where T0.[ItemCode] = $[WTR1.itemcode] and T0.[PriceList] = 24

end

The value cannot be drawn.The error message is "Incorrect syntax near the keyword 'case'.

Please help!

[Former Member]

Does any expert have idea about the query?

[julie_jamieson2]

The grid reference in Inventory transfer is 23 not 38 (which is item based marketing documents). Try replacing 38 with 23 in your query

[Former Member]

Hi,

Try below one of the query with select clause

select case when (ISNULL($[$38.43.0], -1) > -1)

        then select $[$38.U_UnitPrice.0]           

else

       select T0.[Price] FROM ITM1 T0

       where T0.[ItemCode] = $[WTR1.itemcode] and T0.[PriceList] = 24

end

OR

select case when (ISNULL($[$38.43.0], -1) > -1)

        then select $[$38.U_UnitPrice.0]           

else

       select T0.[Price] FROM ITM1 T0

End

       where T0.[ItemCode] = $[WTR1.itemcode] and T0.[PriceList] = 24

Thanks,

Harshal

[Former Member]

I tried. Both queries return error.

"Incorrect syntax near the keyword 'select'."

"Incorrect syntax near the keyword 'WHERE'

[Former Member]

Hi,

try again with below one's.

select case when (ISNULL($[$38.43.0], -1) > -1)

        then $[$38.U_UnitPrice.0]          

else T0.[Price] FROM ITM1 T0

       where T0.[ItemCode] = $[WTR1.itemcode] and T0.[PriceList] = 24

end

OR

Select case when (ISNULL($[$38.43.0], -1) > -1)

        then $[$38.U_UnitPrice.0]          

else T0.[Price] FROM ITM1 T0

End

       where T0.[ItemCode] = $[WTR1.itemcode] and T0.[PriceList] = 24

Hope one of the above works now.

Thanks,

Harshal

[Former Member]

Thanks for your speedy prompt.

"Incorrect syntax near the keyword 'FROM'

"Statement 'Special Prices' (OSPP) (s) could not be prepared.

[frank_wang6]

select case when (ISNULL($[$38.43.0], -1) > -1)

        then $[$38.U_UnitPrice.0]           

else

       (select T0.[Price] FROM ITM1 T0

       where T0.[ItemCode] = $[WTR1.itemcode] and T0.[PriceList] = 24)

end

[Former Member]

Oh, I think its near. Just some data type problem.

"Error converting data type nvarchar to numeric. 'Special Prices' (OSPP)"

[frank_wang6]

select case when (ISNULL($[$38.43.Number], -1) > -1)

        then $[$38.U_UnitPrice.Number]          

else

       (select T0.[Price] FROM ITM1 T0

       where T0.[ItemCode] = $[WTR1.itemcode] and T0.[PriceList] = 24)

end

.0 returns a string, you should use .Number

[Former Member]

Thanks, there are no syntax errors for the above query.

However, it fails to draw the value and return "0.00" in the UDF (U_UnitPrice) for both ITR copy to and standalone case.

Am I selecting the wrong field?

---------------------------

This UDF draws the value according to rules:

- If the IT is copied from ITR, it draws the value of U_UnitPrice of ITR.

- If the IT is standalone, it draws the value from a price list.