April 27, 2009 at 2:33 pm
I always want to pull the records on the left if there is records on the right I want to pull them where TMS_EXT_REF_ID and SEQ_NBR Match(where A.TMS_EXT_REF_ID=D.SEQ_NBR) but if there is know match I still want to pull the records on the left.What this amounts to is this is a Truck Load information
for our drivers it has all the customer information , numbers of stops( A.TMS_EXT_REF_ID=D.SEQ_NBR) and now I want to add the PS_HM_LOAD_STAT_DT to the query this table has four fields business_unit, load_id,seq_nbr and load_notes. the results I want is to get the load information no mater what but if there is a note to go with with a stop (A.TMS_EXT_REF_ID=D.SEQ_NBR) then I want to display it. I am sending and excel along with some Data.
the Code:
SELECT DISTINCT A.BUSINESS_UNIT
,A.LOAD_ID
,A.TMS_EXT_REF_ID
,A.SHIP_TO_CUST_ID
,A.ADDRESS_SEQ_NUM
,A.CUST_NAME
,C.CITY
,C.STATE
,C.POSTAL
,C.PHONE
,A.LINE_FIELD_C1_A
,A.LINE_FIELD_C10_B
,A.LINE_FIELD_C30_A
,D.TEXT254 AS LOAD_NOTE
,D.SEQ_NBR
FROM
PS_CUST_ADDRESS C,
PS_IN_DEMAND A
LEFT JOIN PS_HM_LOAD_STAT_DT D ON A.LOAD_ID=D.LOAD_ID
WHERE A.LOAD_ID <> ' '
AND A.IN_FULFILL_STATE NOT IN ('10', '90')
AND C.SETID = 'CORP1'
AND C.CUST_ID=A.SHIP_TO_CUST_ID
AND C.ADDRESS_SEQ_NUM = A.ADDRESS_SEQ_NUM
---- What needs to be added -----
AND D.SEQ_NBR=A.TMS_EXT_REF_ID
April 27, 2009 at 2:47 pm
If I understand you correctly, which I'm not sure I do, I think this query will work:
SELECT DISTINCT
A.BUSINESS_UNIT,
A.LOAD_ID,
A.TMS_EXT_REF_ID,
A.SHIP_TO_CUST_ID,
A.ADDRESS_SEQ_NUM,
A.CUST_NAME,
C.CITY,
C.STATE,
C.POSTAL,
C.PHONE,
A.LINE_FIELD_C1_A,
A.LINE_FIELD_C10_B,
A.LINE_FIELD_C30_A,
D.TEXT254 AS LOAD_NOTE,
D.SEQ_NBR
FROM
PS_CUST_ADDRESS C JOIN
PS_IN_DEMAND A ON
C.CUST_ID = A.SHIP_TO_CUST_ID AND
C.ADDRESS_SEQ_NUM = A.ADDRESS_SEQ_NUM LEFT JOIN
PS_HM_LOAD_STAT_DT D ON
A.LOAD_ID = D.LOAD_ID AND
D.SEQ_NBR = A.TMS_EXT_REF_ID
WHERE
A.LOAD_ID ' ' AND
A.IN_FULFILL_STATE NOT IN ('10', '90') AND
C.SETID = 'CORP1'
Notice I used ANSI standard join syntax for all the joins as it is easier to read, especially when you use ANSI for the LEFT JOIN and not for the INNER.
In order to avoid confusion with your requirements you should read the first link in my signature and post schema and test data as mentioned in the article.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 27, 2009 at 3:09 pm
Jack Corbett (4/27/2009)
If I understand you correctly, which I'm not sure I do, I think this query will work:
SELECT DISTINCT
A.BUSINESS_UNIT,
A.LOAD_ID,
A.TMS_EXT_REF_ID,
A.SHIP_TO_CUST_ID,
A.ADDRESS_SEQ_NUM,
A.CUST_NAME,
C.CITY,
C.STATE,
C.POSTAL,
C.PHONE,
A.LINE_FIELD_C1_A,
A.LINE_FIELD_C10_B,
A.LINE_FIELD_C30_A,
D.TEXT254 AS LOAD_NOTE,
D.SEQ_NBR
FROM
PS_CUST_ADDRESS C JOIN
PS_IN_DEMAND A ON
C.CUST_ID = A.SHIP_TO_CUST_ID AND
C.ADDRESS_SEQ_NUM = A.ADDRESS_SEQ_NUM LEFT JOIN
PS_HM_LOAD_STAT_DT D ON
A.LOAD_ID = D.LOAD_ID AND
D.SEQ_NBR = A.TMS_EXT_REF_ID
WHERE
A.LOAD_ID ' ' AND
A.IN_FULFILL_STATE NOT IN ('10', '90') AND
C.SETID = 'CORP1'
Notice I used ANSI standard join syntax for all the joins as it is easier to read, especially when you use ANSI for the LEFT JOIN and not for the INNER.
In order to avoid confusion with your requirements you should read the first link in my signature and post schema and test data as mentioned in the article.
Jack -- This is PeopleSoft. If the code is being generated/maintained by the Query Tool, they don't have much choice when it comes to the join syntax. Having talked to our PeopleSoft Admin, the OUTER JOINS are created with the ANSI style, but INNER JOINS still use the old style syntax.
April 27, 2009 at 3:17 pm
This code omits the loads with a note.
April 27, 2009 at 3:55 pm
Thanks Lynn. That at least explains why the code is mixed.
twdavis,
Okay, I'm trying to understand your issue. I'm not sure what you are trying to gain from adding the new criteria. You already have all the note data being returned, what are missing?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 27, 2009 at 4:05 pm
First it does not matter if this is peoplesoft are not we are not writing this in peoplesoft query.
what I am looking for.
each load(LOAD_ID) has multiple stops (TMS_EXT_REF_ID) now I want to add notes(D.TEXT254) to the stops but not all loads or stops will have notes. So I should get data for all loads and stops and if there is a note for a stop I shoud get it.
April 27, 2009 at 4:15 pm
twdavis (4/27/2009)
First it does not matter if this is peoplesoft are not we are not writing this in peoplesoft query.what I am looking for.
each load(LOAD_ID) has multiple stops (TMS_EXT_REF_ID) now I want to add notes(D.TEXT254) to the stops but not all loads or stops will have notes. So I should get data for all loads and stops and if there is a note for a stop I shoud get it.
Okay, if you are writing this manually, you really shouldn't mix ANSI style OUTER JOINS and old style INNER JOINS in the same query. I help support our PeopleSoft developers so I have some understanding of the tools and how they work. Having recognized it was a PeopleSoft database being used I made a normal assumption that you were using the PeopleSoft tools. I apologize since it seems that i was wrong in this area.
Second, it would help if you would provide the DDL for the tables you are accessing (eliminate unused columns except key fields), sample data for the tables (in a consumable format), expected results based on the sample data.
For more information on what we need, please read the first article I have linked below in my signature block.
April 27, 2009 at 4:28 pm
I think this should work I could not see the forest for the trees so to speak.
SELECT DISTINCT A.BUSINESS_UNIT
,A.LOAD_ID
,A.TMS_EXT_REF_ID
,A.SHIP_TO_CUST_ID
,A.ADDRESS_SEQ_NUM
,A.CUST_NAME
,C.CITY
,C.STATE
,C.POSTAL
,C.PHONE
,A.LINE_FIELD_C1_A
,A.LINE_FIELD_C10_B
,A.LINE_FIELD_C30_A
,D.TEXT254 AS LOAD_NOTE
,D.SEQ_NBR
FROM
PS_CUST_ADDRESS C,
PS_IN_DEMAND A
LEFT JOIN PS_HM_LOAD_STAT_DT D ON A.LOAD_ID=D.LOAD_ID AND D.SEQ_NBR=A.TMS_EXT_REF_ID
WHERE A.LOAD_ID ' '
AND A.IN_FULFILL_STATE NOT IN ('10', '90')
AND C.SETID = 'CORP1'
AND C.CUST_ID=A.SHIP_TO_CUST_ID
AND C.ADDRESS_SEQ_NUM = A.ADDRESS_SEQ_NUM
Thanks let me know what you think
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply