Skip to Content
0

duplicate in data

Apr 27 at 09:45 PM

62

avatar image
Former Member

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"

10 |10000 characters needed characters left characters exceeded

Hi Ramez, what exactly is wrong with this query? Without having your database schema we cannot really know what you are doing or trying to do.

0
* Please Login or Register to Answer, Follow or Comment.

0 Answers