cancel
Showing results for 
Search instead for 
Did you mean: 

Select Query for FMS

Former Member
0 Kudos

See Attached - I need a query that selects an Item Alias from a UDT.

In this query the SiteID may be blank in some cases in the UDT.

So there would be a BP Code, Item Code, and SiteID(again in some cases the SiteID may be blank in the UDT and then I only want the BP Code and Item Code.

BP Code     Item Code      Site ID      ALIAS

AA               123                    1               Fred (Fred would be selected)

BB               234                    2               Dan  (Dan would be selected)

CC               345                                    John  (John would be selected even though it is blank)

My Query:

Accepted Solutions (1)

Accepted Solutions (1)

frank_wang6
Active Contributor
0 Kudos

AND ISNULL(T0.[U_SiteID], 99999999) = $[ORDR.ShipToCode]

just replace your query last part with this one.

Basically u use ISNULL to change U_SiteID to a non existing value such as 9999999 in your case, and then compare with ShipToCode.

Former Member
0 Kudos

Hi Frank - thanks for the awesomely quick response!!

The query is not working as I want I have attached the UDT Table - Sales Order and Query -

I want the FMS query to select Item Alias even if the SiteID is blank or populated:

Former Member
0 Kudos

In other words if SiteID is blank then the query simply uses the first two criteria

BP Code and Item Code to select the ALIAS

frank_wang6
Active Contributor
0 Kudos

SELECT TOP 1 T0.[U_ItemAlias] FROM ….. (copy the stuff here except the last part)

AND ISNULL(T0.U_SiteID), $[ORDR.ShipToCode]) = $[ORDR.ShipToCode]

Former Member
0 Kudos

Hi Frank - getting an error now:

The isnull function requires 2 arguement

frank_wang6
Active Contributor
0 Kudos

AND ISNULL(T0.U_SiteID, $[ORDR.ShipToCode]) = $[ORDR.ShipToCode]


Sorry, there is an extra ), just remove it. it should be fine.

Former Member
0 Kudos

Hi Frank - thanks so much for the help - unfortunately the query does not select any ALIAS at all...

frank_wang6
Active Contributor
0 Kudos

Post ur full query here. Dont use screen shot, it is hard for us to type.

Former Member
0 Kudos

SELECT TOP 1 T0.[U_ItemAlias] FROM [@UDC_ITEM_ALIAS] T0

WHERE T0.[U_BPCode]=$[ORDR.CardCode]

AND T0.[U_ItemCode]=$[RDR1.ItemCode]

AND ISNULL (T0.U_SiteID, $[ORDR.ShipToCode]) = $[ORDR.ShipToCode]

frank_wang6
Active Contributor
0 Kudos

Please check if ur siteid is NULL or just empty string. The query should be correct for NULL value, if u have EMPTY string, then it can be modified.

Former Member
0 Kudos

Hi Frank - it is an empty string -

Thank You for all this help!!

frank_wang6
Active Contributor
0 Kudos

SELECT TOP 1 T0.[U_ItemAlias] FROM [@UDC_ITEM_ALIAS] T0

WHERE T0.[U_BPCode]=$[ORDR.CardCode]

AND T0.[U_ItemCode]=$[RDR1.ItemCode]

AND (CASE WHEN T0.U_SiteID = '' THEN $[ORDR.ShipToCode] ELSE T0.U_SiteID END) = $[ORDR.ShipToCode]

Former Member
0 Kudos

Frank - works PERFECTLY - thanks SO MUCH for all your knowledge and help!!!

Answers (0)