help with SELECT statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with SELECT statement

Author

Topic

apociecha
Starting Member

17 Posts

Posted - 2004-06-24 : 12:51:02

I have a SELECT statement that is working fine (I'm sure it can re-written MUCH better with a derived table which I only get to work properly partially), however, now I need to add a couple more things to the SELECT and I'm stuck. Here's what I have so far:

<code>

SELECT Status, Entered_By, Entered_On, Approved_By, Approved_On,
(SELECT LName + ', ' + FName As EnteredName FROM E WHERE SSN = A.Entered_By) As EnteredName,
(SELECT LName + ', ' + FName As ApprovedName FROM E WHERE SSN = A.Approved_By) As ApprovedName,
(SELECT Last_Modified_By AS Disapproved_By FROM A WHERE Status = 'DISAPPROVED' AND TransactionId= '1321') As Disapproved_By,
(SELECT Last_Modified_On AS Disapproved_On FROM A WHERE Status = 'DISAPPROVED' AND TransactionId= '1321') As Disapproved_On
FROM A
WHERE TransactionId= '1321'

</code>

Now I need to add a couple more things to this mess:

1. if the length of Entered_By > 0, but the length of EnteredName is = 0, I need to try to get the EnteredName from yet another table (table X)
2. if the length of Approved_By > 0, but the length of ApprovedName is = 0, I need to try to get the ApprovedName from yet another table (table X)
3. this one is a little different, I didn't try to get the DisapprovedName on the first run above from table E, because I wasn't sure how to get it, so now I need to try to get the DisapprovedName from table E and if it isn't found, then I need to try to get it from table X

Can someone help me rework this mess?

Many thanks!!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-24 : 13:15:41

I see that disapproved info is in the same table.. are there multiple rows?? How bout some layouts and sample data??

Aside From the Disapproved section:

Select
Status,
Entered_By,
Entered_On,
Approved_By,
Approved_On,
EnteredName = Case
When len(Entered_By) > 0 and len(EnteredName)=0 then (SELECT LName + ', ' + FName As EnteredName FROM X WHERE SSN = sub.Entered_By)
Else EnteredName end,
EnteredName = Case
When len(Approved_By) > 0 and len(ApprovedName)=0 then (SELECT LName + ', ' + FName As ApprovedName FROM X WHERE SSN = sub.Approved_By)
Else ApprovedName end
From
(
SELECT
Status,
Entered_By,
Entered_On,
Approved_By,
Approved_On,
EnteredName = (SELECT LName + ', ' + FName As EnteredName FROM E WHERE SSN = A.Entered_By),
ApprovedName = (SELECT LName + ', ' + FName As ApprovedName FROM E WHERE SSN = A.Approved_By),
Disapproved_By = (SELECT Last_Modified_By AS Disapproved_By FROM A WHERE Status = 'DISAPPROVED' AND TransactionId= '1321'),
Disapproved_On = (SELECT Last_Modified_On AS Disapproved_On FROM A WHERE Status = 'DISAPPROVED' AND TransactionId= '1321')
FROM A
WHERE TransactionId= '1321'
) sub

Corey

apociecha
Starting Member

17 Posts

Posted - 2004-06-24 : 14:00:46

Corey,

That worked great, I just had to rename one of the column names and that worked like a charm. THANK YOU!!!!

As for the disapproved part, the difference here is that disapproved info is not in columns like the enteredBy/enteredOn and approvedBy/approvedOn. The disapproved by and on info comes from the last_modified_by and last_modified_on fields when the status field is set to 'DISAPPROVED'. There are different statuses, but if it is DISAPPROVED, then I need to get the name first from table E and if not there, then from table X.

Thank you VERY MUCH for your help,

Agnes

quote:
Originally posted by Seventhnight

I see that disapproved info is in the same table.. are there multiple rows?? How bout some layouts and sample data??

Aside From the Disapproved section:

Select
Status,
Entered_By,
Entered_On,
Approved_By,
Approved_On,
EnteredName = Case
When len(Entered_By) > 0 and len(EnteredName)=0 then (SELECT LName + ', ' + FName As EnteredName FROM X WHERE SSN = sub.Entered_By)
Else EnteredName end,
EnteredName = Case
When len(Approved_By) > 0 and len(ApprovedName)=0 then (SELECT LName + ', ' + FName As ApprovedName FROM X WHERE SSN = sub.Approved_By)
Else ApprovedName end
From
(
SELECT
Status,
Entered_By,
Entered_On,
Approved_By,
Approved_On,
EnteredName = (SELECT LName + ', ' + FName As EnteredName FROM E WHERE SSN = A.Entered_By),
ApprovedName = (SELECT LName + ', ' + FName As ApprovedName FROM E WHERE SSN = A.Approved_By),
Disapproved_By = (SELECT Last_Modified_By AS Disapproved_By FROM A WHERE Status = 'DISAPPROVED' AND TransactionId= '1321'),
Disapproved_On = (SELECT Last_Modified_On AS Disapproved_On FROM A WHERE Status = 'DISAPPROVED' AND TransactionId= '1321')
FROM A
WHERE TransactionId= '1321'
) sub

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-24 : 14:26:10

so what you really need is:

quote:
Select
Status,
Entered_By,
Entered_On,
Approved_By,
Approved_On,
EnteredName = Case
When len(Entered_By) > 0 and len(EnteredName)=0 then (SELECT LName + ', ' + FName As EnteredName FROM X WHERE SSN = sub.Entered_By)
Else EnteredName end,
ApprovedName = Case
When len(Approved_By) > 0 and len(ApprovedName)=0 then (SELECT LName + ', ' + FName As ApprovedName FROM X WHERE SSN = sub.Approved_By)
Else ApprovedName end
DisapprovedName = Case
When len(Disapproved_By) > 0 and len(DisapprovedName)=0 then (SELECT LName + ', ' + FName As DisapprovedName FROM X WHERE SSN = sub.Disapproved_By)
Else DisapprovedName end
From
(
SELECT
Status,
Entered_By,
Entered_On,
Approved_By,
Approved_On,
Disapproved_By = case when Status = 'DISAPPROVED' then Last_Modified_By else null end,
Disapproved_On = case when Status = 'DISAPPROVED' then Last_Modified_On else null end,
EnteredName = (SELECT LName + ', ' + FName As EnteredName FROM E WHERE SSN = A.Entered_By),
ApprovedName = (SELECT LName + ', ' + FName As ApprovedName FROM E WHERE SSN = A.Approved_By),
DisapprovedName = case when Status = 'DISAPPROVED' then (SELECT LName + ', ' + FName As DisapprovedName FROM E WHERE SSN = A.Last_Modified_By) else null end
FROM A
WHERE TransactionId= '1321'
) sub

Corey

apociecha
Starting Member

17 Posts

Posted - 2004-06-24 : 14:47:57

Corey,

That worked GREAT!!!! THANK YOU SO MUCH!!!!

The only thing I ended up changing was the 3 cases:
len(EnteredName)=0
len(ApprovedName)=0
len(DisapprovedName)=0

to be:
EnteredName is null
ApprovedName is null
DisapprovedName is null

because the fields were being generated with a null when no entry was found in table E.

It's nice when you can get help and learn something too.
Thank you again,

Agnes

quote:
Originally posted by Seventhnight

so what you really need is:

quote:
Select
Status,
Entered_By,
Entered_On,
Approved_By,
Approved_On,
EnteredName = Case
When len(Entered_By) > 0 and len(EnteredName)=0 then (SELECT LName + ', ' + FName As EnteredName FROM X WHERE SSN = sub.Entered_By)
Else EnteredName end,
ApprovedName = Case
When len(Approved_By) > 0 and len(ApprovedName)=0 then (SELECT LName + ', ' + FName As ApprovedName FROM X WHERE SSN = sub.Approved_By)
Else ApprovedName end
DisapprovedName = Case
When len(Disapproved_By) > 0 and len(DisapprovedName)=0 then (SELECT LName + ', ' + FName As DisapprovedName FROM X WHERE SSN = sub.Disapproved_By)
Else DisapprovedName end
From
(
SELECT
Status,
Entered_By,
Entered_On,
Approved_By,
Approved_On,
Disapproved_By = case when Status = 'DISAPPROVED' then Last_Modified_By else null end,
Disapproved_On = case when Status = 'DISAPPROVED' then Last_Modified_On else null end,
EnteredName = (SELECT LName + ', ' + FName As EnteredName FROM E WHERE SSN = A.Entered_By),
ApprovedName = (SELECT LName + ', ' + FName As ApprovedName FROM E WHERE SSN = A.Approved_By),
DisapprovedName = case when Status = 'DISAPPROVED' then (SELECT LName + ', ' + FName As DisapprovedName FROM E WHERE SSN = A.Last_Modified_By) else null end
FROM A
WHERE TransactionId= '1321'
) sub

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-24 : 15:47:45

Glad to help

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources