November 12, 2012 at 3:35 pm
Hi, I will try to explain myself as clear as I can:
I am having difficulty trying to list query results under a new column name. That is, list/display/return the value of column "VALUE" under a new column named "Line_no" based on the combination of two set values of two different columns: "ELEMENT" and "SEGMENT" and just to be 100% sure, a possible third column, "QUALIFIER".
Example1:
If ELEMENT='18' AND SEGMENT='SLN" (AND QUALIFIER='PL'), return the value of column named "VALUE" (in this case will be = '60') under a NEW COLUMN named "Line_no"
Example2:
If ELEMENT='05' AND SEGMENT='PID" (AND QUALIFIER='91'), return the value of column named "VALUE" (in this case will be = '28W') under a NEW COLUMN named "Size"
Example3:
If ELEMENT='22' AND SEGMENT='SLN" (AND QUALIFIER='DV'), return the value of column named "VALUE" (in this case will be = '305') under a NEW COLUMN named "Dept"
Line_seq,Line_no,Size, Dept
6,60,28W,305
There are three tables involved in this query (actually there are more):
1-zzedatawhse - This is where most of the data of the query will come from.
2-zzoordrh - This is the table of the order header
3-zzoordrd - This is the table of the order detail (where all the lines of the order are listed)
The link of the tables are:
zzoordrh.PKEY = zzordrd.fkey.
zzedatawhse.ORD_NUM = zzoordrd.ORD_NUM
zzedatawhse.line_deq = zzoordrd.line_seq
zzedatawhse.DOC_NUM = zzoordrh.edi_doc_num
The big pictures here is that the order header and details are link to each other by the PKEY on the header to the fkey on the details. Based on the DOC_NUM and ORD_NUM of the header and the line_seq of the corresponding order detail, you will match the EDI information to the zzedatawhse table by line_seq.
In this case, there are 8 lines in the order packed in 8 cartons. One line per carton. But there are cases where each line of the order may be packed in several cartons.
I am hoping to make the query based on BILL_NUM
I have included all four tables. "Carton query sample.txt" is just a what I have done so far. It includes other tables that
are not listed here but all I am just trying to show is what I have done so far.
I hope I explained myself correctly and clearly. If you are familiar to label software "bartender", I am trying to create a
query to print shipping labels, and the three fields that I am looking for are the examples 1,2, and 3 listed above.
Thank you in advance for your help.
November 12, 2012 at 4:21 pm
select
case ELEMENT
when '18' then
case when SEGMENT='SLN' AND QUALIFIER='PL'
then Value
end
end as Line_no,
case ELEMENT
when '05' then
case when SEGMENT='PID' AND QUALIFIER='91'
then Value
end
end as Size
when '22' then
case when SEGMENT='SLN' AND QUALIFIER='DV'
then Value
end
end as Dept
November 21, 2012 at 2:04 pm
Hi lnardozi.
Thank you for the response. I have been trying to insert the whole CASE statement inside my existing elaborated query but I keep having errors. Perhaps I am not inserting the CASE in the proper place.
So far, this is what I have:
SELECT DISTINCT zzoordrh.BILL_NUM
,zzoctnph.DIVISION
,zzoordrh.CARTON AS NoCartons
,zzoordrh.ORD_NUM
,zzoordrh.PO_NUM
,zzoordrh.STORE
,zzoctnph.carton_num AS UCC128
,SUM(zzoctnpd.total_qty) AS CRTNQty
,zzoctnph.CARTON_WGT
,zzoctnph.CARTON_SEQ
,zzxdistr.center_name AS DC
,zzxdistr.address1 AS DCaddress1
,zzxdistr.address2 AS DCaddress2
,zzxdistr.city AS DCcity
,zzxdistr.state AS DCstate
,zzxdistr.zipcode AS DCzip
,zzxshipr.scac_code
,zzxstorr.store_name AS STname
,zzxstorr.address1 AS STaddress1
,zzxstorr.address2 AS STaddress2
,zzxstorr.city AS STcity
,zzxstorr.state AS STstate
,zzxstorr.zipcode AS STzip
,zzxcartr.carton_desc
,zzedatawhse.value
,zzedatawhse.QUALIFIER
,zzedatawhse.SEGMENT
FROMZzoordrh INNER JOIN
zzxshipr ON zzoordrh.SHIPPER = zzxshipr.shipper INNER JOIN
zzoordsp ON zzoordrh.PICK_NUM = zzoordsp.pick_num AND zzoordrh.ORD_NUM = zzoordsp.ORD_NUM INNER JOIN
zzoctnph ON zzoordsp.pkey = zzoctnph.FKEY INNER JOIN
zzxcartr ON zzoctnph.CARTON_CODE = zzxcartr.carton_code INNER JOIN
zzoctnpd ON zzoctnpd.fKEY = zzoctnph.pkey INNER JOIN
zzxstorr ON zzoctnph.customer = zzxstorr.customer AND zzoordrh.store = zzxstorr.store INNER JOIN
zzxdistr ON zzxstorr.customer = zzxdistr.customer AND Zzoordrh.store = zzxdistr.center_code
WHERE(select
case ELEMENT
when '18' then
case when SEGMENT='SLN' AND QUALIFIER='PL'
then Value
end
end as Line_no,
case ELEMENT
when '05' then
case when SEGMENT='PID' AND QUALIFIER='91'
then Value
end
end as Size,
case ELEMENT
when '22' then
case when SEGMENT='SLN' AND QUALIFIER='DV'
then Value
end
end as Dept
FROMzzoordrh INNER JOIN
zzedatawhse ON zzedatawhse.DOC_NUM = zzoordrh.edi_doc_num INNER JOIN
zzoordrd ON zzoordrh.PKEY = zzoordrd.fkey
WHERE zzoordrd.line_seq = zzedatawhse.LINE_SEQ AND zzedatawhse.ELEMENT = '18') AND zzoordrh.BILL_NUM = '113198'
GROUP BYzzoordrh.BILL_NUM,zzoctnph.DIVISION,zzoordrh.CARTON,zzoordrh.ORD_NUM,zzoordrh.PO_NUM,zzoordrh.STORE,zzoctnph.carton_num,
zzoctnph.CARTON_WGT,zzoctnph.CARTON_SEQ,zzxdistr.center_name,zzxdistr.address1,zzxdistr.address2,zzxdistr.city,zzxdistr.state,
zzxdistr.zipcode,zzxshipr.scac_code,zzxstorr.store_name,zzxstorr.address1,zzxstorr.address2,zzxstorr.city,zzxstorr.state,
zzxstorr.zipcode,zzxcartr.carton_desc,zzedatawhse.value,zzedatawhse.QUALIFIER,zzedatawhse.SEGMENT
ORDER BYzzoordrh.STORE
Hope this make sense.
Thank you
November 21, 2012 at 2:56 pm
The WHERE statement is invalid... you can't resolve the query to a boolean...
not really clear on why you want this in the where statement... you can put it in the from statement and join it to what you currently have...
Are the 3 new values suppose to be a filter for the rest of the query?
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
November 21, 2012 at 3:14 pm
Thank you for your response!!!
So I pasted the entire SELECT CASE in the FROM statement and I try to INNER JOIN to my first table "zzoordrh" but I am getting: "Incorrect syntax near the keyword 'INNER'"
FROM(select
case ELEMENT
when '18' then
case when SEGMENT='SLN' AND QUALIFIER='PL'
then Value
end
end as Line_no,
case ELEMENT
when '05' then
case when SEGMENT='PID' AND QUALIFIER='91'
then Value
end
end as Size,
case ELEMENT
when '22' then
case when SEGMENT='SLN' AND QUALIFIER='DV'
then Value
end
end as Dept
FROMzzoordrh INNER JOIN
zzedatawhse ON zzedatawhse.DOC_NUM = zzoordrh.edi_doc_num INNER JOIN
zzoordrd ON zzoordrh.PKEY = zzoordrd.fkey
WHERE zzoordrd.line_seq = zzedatawhse.LINE_SEQ AND zzedatawhse.ELEMENT = '18') INNER JOIN
Zzoordrh ON zzedatawhse.DOC_NUM = zzoordrh.edi_doc_num INNER JOIN
zzxshipr ON zzoordrh.SHIPPER = zzxshipr.shipper INNER JOIN
zzoordsp ON zzoordrh.PICK_NUM = zzoordsp.pick_num AND zzoordrh.ORD_NUM = zzoordsp.ORD_NUM INNER JOIN
zzoctnph ON zzoordsp.pkey = zzoctnph.FKEY INNER JOIN
zzxcartr ON zzoctnph.CARTON_CODE = zzxcartr.carton_code INNER JOIN
zzoctnpd ON zzoctnpd.fKEY = zzoctnph.pkey INNER JOIN
zzxstorr ON zzoctnph.customer = zzxstorr.customer AND zzoordrh.store = zzxstorr.store INNER JOIN
zzxdistr ON zzxstorr.customer = zzxdistr.customer AND Zzoordrh.store = zzxdistr.center_code
WHEREzzoordrh.BILL_NUM = '113198'
GROUP BYzzoordrh.BILL_NUM,zzoctnph.DIVISION,zzoordrh.CARTON,zzoordrh.ORD_NUM,zzoordrh.PO_NUM,zzoordrh.STORE,zzoctnph.carton_num,
zzoctnph.CARTON_WGT,zzoctnph.CARTON_SEQ,zzxdistr.center_name,zzxdistr.address1,zzxdistr.address2,zzxdistr.city,zzxdistr.state,
zzxdistr.zipcode,zzxshipr.scac_code,zzxstorr.store_name,zzxstorr.address1,zzxstorr.address2,zzxstorr.city,zzxstorr.state,
zzxstorr.zipcode,zzxcartr.carton_desc,zzedatawhse.value,zzedatawhse.QUALIFIER,zzedatawhse.SEGMENT
ORDER BYzzoordrh.STORE
As for the new tree values; yes, they are to be filtered. I need to bring these values to match the order line: line number, size, and department.
Does that make sense?
Thank you for your help!
November 21, 2012 at 9:01 pm
Arr... now the light is shining. You want
SELECT DISTINCT zzoordrh.BILL_NUM
,zzoctnph.DIVISION
,zzoordrh.CARTON AS NoCartons
,zzoordrh.ORD_NUM
,zzoordrh.PO_NUM
,zzoordrh.STORE
,zzoctnph.carton_num AS UCC128
,SUM(zzoctnpd.total_qty) AS CRTNQty
,zzoctnph.CARTON_WGT
,zzoctnph.CARTON_SEQ
,zzxdistr.center_name AS DC
,zzxdistr.address1 AS DCaddress1
,zzxdistr.address2 AS DCaddress2
,zzxdistr.city AS DCcity
,zzxdistr.state AS DCstate
,zzxdistr.zipcode AS DCzip
,zzxshipr.scac_code
,zzxstorr.store_name AS STname
,zzxstorr.address1 AS STaddress1
,zzxstorr.address2 AS STaddress2
,zzxstorr.city AS STcity
,zzxstorr.state AS STstate
,zzxstorr.zipcode AS STzip
,zzxcartr.carton_desc
,zzedatawhse.value
,zzedatawhse.QUALIFIER
,zzedatawhse.SEGMENT
,Line_no=case ELEMENT when '18' then
case when SEGMENT='SLN' AND QUALIFIER='PL'
then Value
end
end
,Size=case ELEMENT when '05' then
case when SEGMENT='PID' AND QUALIFIER='91'
then Value
end
end
,Dept=case ELEMENT when '22' then
case when SEGMENT='SLN' AND QUALIFIER='DV'
then Value
end
end
FROMZzoordrh INNER JOIN
zzxshipr ON zzoordrh.SHIPPER = zzxshipr.shipper INNER JOIN
zzoordsp ON zzoordrh.PICK_NUM = zzoordsp.pick_num AND zzoordrh.ORD_NUM = zzoordsp.ORD_NUM INNER JOIN
zzoctnph ON zzoordsp.pkey = zzoctnph.FKEY INNER JOIN
zzxcartr ON zzoctnph.CARTON_CODE = zzxcartr.carton_code INNER JOIN
zzoctnpd ON zzoctnpd.fKEY = zzoctnph.pkey INNER JOIN
zzxstorr ON zzoctnph.customer = zzxstorr.customer AND zzoordrh.store = zzxstorr.store INNER JOIN
zzxdistr ON zzxstorr.customer = zzxdistr.customer AND Zzoordrh.store = zzxdistr.center_codeFROMzzoordrh
Then you can include Line_No, Size and Dept in your where clause.
November 22, 2012 at 6:57 am
CELKO (11/22/2012)
...“No matter how far you have gone down the wrong road, turn around.” -- Turkish Proverb.
It is so true!
But, why don't you follow it yourself?
😉
November 22, 2012 at 7:10 am
This is a total mess that should be thrown out
Ah, and if we were the boss of all creation how differently things might be designed! Unfortunately we work in the real world where we get data from other places, where tables are populated by processes we have no control over, where we must query remote servers, and where we work with other people who might not be quite so brilliant as we. We needs must be able to 'stoop to their level' on occasion. After all, if Reliable can't answer this question for his boss how is he going to convince his boss he knows a better way to design it?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply