Error on Queries

[Former Member]

Hello, does someone knows why this happens when I write my query:

SELECT

CASE

WHEN $[ORDR.SlpCode]='6' THEN

CASE WHEN $[ORDR.U_CustomerType] = 'Mercant' THEN

CASE

I save it, then wen I open it after a wile the query turn on this:

SELECT

CASE

WHEN N'6'='6' THEN

CASE WHEN N'Automotive' = 'Mercant' THEN

CASE

I am using this querie in a FMS field.

Thank you!

[former_member218051 · Accepted solution]

Hi Silvio,

$[$TableName.columnName.DataType] is the syntax used to capture on screen information entered by the user.

So, keeping the form open, the query captures the information and it turns into the values.

So make sure to keep the form close before framing and saving the query.

Thanks

Malhaar

[Former Member]

Hi,

When you open this FMS query, make sure the Sales Order form is CLOSED.

Thanks,

Gordon

[jitin_chawla]

Hi,

Is this your complete query? Please post the complete query and the requirement.

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hi, this is the full query:

SELECT

CASE

WHEN $[ORDR.SlpCode]='6' THEN /Außendienst 1/

CASE WHEN $[ORDR.U_CustomerType] = 'Mercant' THEN

CASE

WHEN $[RDR1.DiscPrcnt] > convert(numeric(19,6),0) and $[RDR1.DiscPrcnt] <= convert(numeric(19,6),5) THEN (Select U_5 From where U_GroupCode=(Select ItmsGrpCod from OITM Where OITM.ItemCode=$[RDR1.ItemCode]))

WHEN $[RDR1.DiscPrcnt] > convert(numeric(19,6),5) and $[RDR1.DiscPrcnt] <= convert(numeric(19,6),10) THEN (Select U_10 From where U_GroupCode=(Select ItmsGrpCod from OITM Where OITM.ItemCode=$[RDR1.ItemCode]))

WHEN $[RDR1.DiscPrcnt] > convert(numeric(19,6),10) and $[RDR1.DiscPrcnt] <= convert(numeric(19,6),15) THEN (Select U_15 From where U_GroupCode=(Select ItmsGrpCod from OITM Where OITM.ItemCode=$[RDR1.ItemCode]))

WHEN $[RDR1.DiscPrcnt] > convert(numeric(19,6),15) and $[RDR1.DiscPrcnt] <= convert(numeric(19,6),20) THEN (Select U_20 From where U_GroupCode=(Select ItmsGrpCod from OITM Where OITM.ItemCode=$[RDR1.ItemCode]))

WHEN $[RDR1.DiscPrcnt] > convert(numeric(19,6),20) and $[RDR1.DiscPrcnt] <= convert(numeric(19,6),25) THEN (Select U_25 From where U_GroupCode=(Select ItmsGrpCod from OITM Where OITM.ItemCode=$[RDR1.ItemCode]))

ELSE 0

END

WHEN $[ORDR.U_CustomerType] = 'Automotive' THEN

CASE

WHEN $[RDR1.DiscPrcnt] = convert(numeric(19,6),0) THEN (Select U_0 From where U_GroupCode=(Select ItmsGrpCod from OITM Where OITM.ItemCode=$[RDR1.ItemCode]))

WHEN $[RDR1.DiscPrcnt] > convert(numeric(19,6),0) and $[RDR1.DiscPrcnt] <= convert(numeric(19,6),3) THEN (Select U_3 From where U_GroupCode=(Select ItmsGrpCod from OITM Where OITM.ItemCode=$[RDR1.ItemCode]))

WHEN $[RDR1.DiscPrcnt] > convert(numeric(19,6),3) and $[RDR1.DiscPrcnt] <= convert(numeric(19,6),5) THEN (Select U_5 From where U_GroupCode=(Select ItmsGrpCod from OITM Where OITM.ItemCode=$[RDR1.ItemCode]))

ELSE 0

END

END

END

FROM RDR1

WHERE (RDR1.ItemCode = $[RDR1.ItemCode])