April 6, 2009 at 3:54 pm
When I run my Query I get Multiple rows for each customer when I should get one.
My trouble is that tables B,C,D HAVE A EFFDT(effective date) and I need to get the MAX(EFFDTT)
I thing I want to use MAX EFFDT. How would I use it and can it be used in the where clause
MY SQL:
select A.CUST_ID,A.NAME1, B.ADDRESS1, B.CITY,B.STATE, B.POSTAL,C.PYMNT_TERMS_CD, D.CR_LIMIT
from PS_CUSTOMER (nolock) A ,PS_CUST_ADDRESS (nolock) B , PS_CUST_OPTION (nolock) C, PS_CUST_CREDIT (nolock) D
WHERE A.CUST_ID=B.CUST_ID AND C.CUST_ID=A.CUST_ID AND D.CUST_ID=A.CUST_ID AND A.CUST_STATUS='A' AND
A.BILL_TO_FLG='Y' AND (C.PYMNT_TERMS_CD LIKE 'CIT%' OR C.PYMNT_TERMS_CD LIKE 'BBT%')
April 6, 2009 at 4:58 pm
I'm guessing that you want to get only that row that corresponds to the max date.
first, I'd suggest you write using ANSI joins. That would be something like:
select A.CUST_ID,A.NAME1, B.ADDRESS1, B.CITY
,B.STATE, B.POSTAL,C.PYMNT_TERMS_CD, D.CR_LIMIT
from PS_CUSTOMER (nolock) A
inner join PS_CUST_ADDRESS (nolock) B
on A.CUST_ID=B.CUST_ID
inner join PS_CUST_OPTION (nolock) C
on C.CUST_ID=A.CUST_ID
inner join PS_CUST_CREDIT (nolock) D
on D.CUST_ID=A.CUST_ID
WHERE A.CUST_STATUS='A'
AND A.BILL_TO_FLG='Y'
AND (C.PYMNT_TERMS_CD LIKE 'CIT%'
OR C.PYMNT_TERMS_CD LIKE 'BBT%'
)
It's cleaner to read and it's easier to be sure your joins are working. Second, please format things so you can see what's being joined and compared. My old eyes (and many others' as well) struggle with things run together. This is your code, not ours, so it doesn't necessarily make sense at first.
To get an effective date, which table are you getting the max from? The hint I'll give is that you probably want a join to a row in that table. For the B table, you'd add to your where
and b.effectivedate = ( select max( effectivedate)
from PS_CUST_ADDRESS e
where b.PK = e.Pk
)
where the PK is your primary key. Or it could be the customer ID. without more information on the tables, hard to determine what makes sense.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 6, 2009 at 8:16 pm
Thanks
this is what I ended up with:
select A.CUST_ID,A.NAME1, B.ADDRESS1, B.CITY
,B.STATE, B.POSTAL,C.PYMNT_TERMS_CD, D.CR_LIMIT
from PS_CUSTOMER (nolock) A
inner join PS_CUST_ADDRESS (nolock) B
on A.CUST_ID=B.CUST_ID
inner join PS_CUST_OPTION (nolock) C
on C.CUST_ID=A.CUST_ID
inner join PS_CUST_CREDIT (nolock) D
on D.CUST_ID=A.CUST_ID
WHERE A.CUST_STATUS='A'
AND A.BILL_TO_FLG='Y'
AND (C.PYMNT_TERMS_CD LIKE 'CIT%'
OR C.PYMNT_TERMS_CD LIKE 'BBT%')
and B.EFFDT=(SELECT MAX(EFFDT) FROM PS_CUST_ADDRESS BB
WHERE B.CUST_ID=BB.CUST_ID)
And C.EFFDT=(SELECT MAX(EFFDT) FROM PS_CUST_OPTION CC
WHERE C.CUST_ID=CC.CUST_ID)
And D.EFFDT=(SELECT MAX(EFFDT) FROM PS_CUST_CREDIT DD
WHERE D.CUST_ID=DD.CUST_ID)
April 6, 2009 at 8:33 pm
If you have time to do a little testing, it might be interesting to see the performance difference of this approach against your current code. I understand some people may have an even faster approach using the ROW_NUMBER function.
Caution: Cut, pasted, and typed freehand after 9pm and not tested. May cause generation of Higgs Bosons.
SELECT A.CUST_ID,A.NAME1, B.ADDRESS1, B.CITY
,B.STATE, B.POSTAL,C.PYMNT_TERMS_CD, D.CR_LIMIT
FROM PS_CUSTOMER (nolock) A
--
CROSS APPLY (SELECT TOP 1 BB.ADDRESS1, BB.CITY, BB.[STATE], BB.POSTAL
FROM PS_CUST_ADDRESS BB (nolock)
WHERE BB.CUST_ID = A.CUSTID
ORDER BY BB.EFFDT DESC) B
--
CROSS APPLY (SELECT TOP 1 CC.PYMNT_TERMS_CD
FROM PS_CUST_OPTION (nolock) CC
WHERE CC.CUST_ID = A.CUST_ID
AND (CC.PYMNT_TERMS_CD LIKE 'CIT%' OR CC.PYMNT_TERMS_CD LIKE 'BBT%')
ORDER BY CC.EFFDT DESC) C
--
CROSS APPLY (SELECT TOP 1 DD.CR_LIMIT
FROM PS_CUST_CREDIT (nolock) DD
WHERE DD.CUST_ID = A.CUST_ID
ORDER BY DD.EFFDT DESC) D
--
WHERE A.CUST_STATUS='A'
AND A.BILL_TO_FLG='Y'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 6, 2009 at 9:38 pm
This is PeopleSoft. If the code is generated/maintained by the PeopleSoft tools, the poster unfortunately has little control over how the code is generated.
I'll pass this thread on to our PeopleSoft Admin, perhaps she can provide a bit more guidance.
April 7, 2009 at 6:39 am
You can tell that from the table and column names, I suppose.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 7, 2009 at 7:11 am
I will Test this to day to see what the performance difference is and post the results
Thanks
April 7, 2009 at 7:19 am
Thanks, tw. I'll look forward to hearing about it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 7, 2009 at 8:05 am
Bob Hovious (4/7/2009)
You can tell that from the table and column names, I suppose.
Yes. I provide SQL Server support for our PeopleSoft developers, so I have learned a bit about the database and the tables.
April 7, 2009 at 8:18 am
Yet the poster apparently has more control than you would think 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 7, 2009 at 8:25 am
Bob Hovious (4/7/2009)
Yet the poster apparently has more control than you would think 😉
Actually, depending on what and how they are doing things, there are "ways" around the tools. It all depends on how much they want or need to maintain things seperately from the tools. If I am remembering things correctly. I'm sure our PeopleSoft Admin will correct me if needed.
April 7, 2009 at 8:44 am
I have this sudden vision of a man in a Gestapo uniform staring at a PeopleSoft screen and thinking "We have ways of dealing with you."
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 7, 2009 at 1:20 pm
I uploaded the results of a showplan for the two SQL statements,
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply