Skip to Content
avatar image
Former Member

duplicate in data

hello

i am facing a problem in this query i want to make SQL query to retrieve all price lists and each price list in a separate column

and below my query

SELECT T0."ItemCode", T0."ItemName", T0."FirmCode", T5."FirmName", T1."WhsCode", T1."OnHand", T2."WhsName", T0."InvntryUom", T0."VatGourpSa", T0."AvgPrice" "UnitCost", (T0."AvgPrice" * T1."OnHand") "Accumulative Cost", MAX(Case WHEN T4."ListNum" = 1 THEN T4."ListName" else null end) "Wholesales", MAX(Case WHEN T4."ListNum" = 1 THEN T3."Price" else null end) "Wholesales Price", MAX(Case WHEN T4."ListNum" = 2 THEN T4."ListName" else null end) "Retail", MAX(Case WHEN T4."ListNum" = 2 THEN T3."Price" else null end) "Retail Price", MAX(Case WHEN T4."ListNum" = 3 THEN T4."ListName" else null end) "Food Service", MAX(Case WHEN T4."ListNum" = 3 THEN T3."Price" else null end) "Food Service Price", MAX(Case WHEN T4."ListNum" = 4 THEN T4."ListName" else null end) "Key Accounts", MAX(Case WHEN T4."ListNum" = 4 THEN T3."Price" else null end) "Key Accounts Price", MAX(Case WHEN T4."ListNum" = 5 THEN T4."ListName" else null end) "Cash Van", MAX(Case WHEN T4."ListNum" = 5 THEN T3."Price" else null end) "Cash Van Price", MAX(Case WHEN T4."ListNum" = 6 THEN T4."ListName" else null end) "Coop’s", MAX(Case WHEN T4."ListNum" = 6 THEN T3."Price" else null end) "Coop’s Price", MAX(Case WHEN T4."ListNum" = 7 THEN T4."ListName" else null end) "Key Accounts - Deli", MAX(Case WHEN T4."ListNum" = 7 THEN T3."Price" else null end) "Key Accounts - Deli Price", MAX(Case WHEN T4."ListNum" = 8 THEN T4."ListName" else null end) "Employees", MAX(Case WHEN T4."ListNum" = 8 THEN T3."Price" else null end) "Employees Price", MAX(Case WHEN T4."ListNum" = 9 THEN T4."ListName" else null end) "RITCO Aqaba", MAX(Case WHEN T4."ListNum" = 9 THEN T3."Price" else null end) "RITCO Aqaba Price", MAX(Case WHEN T4."ListNum" = 10 THEN T4."ListName" else null end) "Foreign Vendors", MAX(Case WHEN T4."ListNum" = 10 THEN T3."Price" else null end) "Foreign Vendors Price", MAX(Case WHEN T4."ListNum" = 11 THEN T4."ListName" else null end) "Local Vendors", MAX(Case WHEN T4."ListNum" = 11 THEN T3."Price" else null end) "Local Vendors Price", MAX(Case WHEN T4."ListNum" = 12 THEN T4."ListName" else null end) "Bonded", MAX(Case WHEN T4."ListNum" = 12 THEN T3."Price" else null end) "Bonded Price", MAX(Case WHEN T4."ListNum" = 13 THEN T4."ListName" else null end) "Others", MAX(Case WHEN T4."ListNum" = 13 THEN T3."Price" else null end) "Others Price" FROM OITM T0 LEFT OUTER JOIN OITW T1 ON T0."ItemCode" = T1."ItemCode" LEFT OUTER JOIN OWHS T2 ON T1."WhsCode" = T2."WhsCode" LEFT OUTER JOIN ITM1 T3 ON T0."ItemCode" = T3."ItemCode" LEFT OUTER JOIN OPLN T4 ON T3."PriceList" = T4."ListNum" LEFT OUTER JOIN OMRC T5 ON T0."FirmCode" = T5."FirmCode" WHERE T3."ItemCode" ='CE00020' GROUP BY T2."WhsName", T0."ItemCode", T0."ItemName", T0."FirmCode", T5."FirmName", T1."WhsCode", T1."OnHand", T2."WhsName", T0."InvntryUom", T0."VatGourpSa", T0."AvgPrice" , (T0."AvgPrice" * T1."OnHand"), T4."ListNum", T4."ListName", T3."Price"

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers