Logical SQL Query - 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

SQL Server Development (2000)

Logical SQL Query

Author

Topic

nssjari
Starting Member

46 Posts

Posted - 2005-07-07 : 03:44:27

Table Name: DocumentStatus

DocNo - TransmittalNo - Reply Status - ReplyTransmittalNo - Comments
BS1 - TR1 - Approved - RT1 - Xxxx
BS2 - TR2 - ReviseIt - RT2 - Yyyy
BS3 - TR3 - - -
BS4 - TR4 - Approved - RT4 - Zzzz
BS5 - TR5 - - -

Now i want to check for Reply Status in select clause
If Reply Status is NOT NULL then show details else show null values

---
Below query gives me records like BS1, BS2,BS4 depending on the value of apr1.apr_stat , when apr1.apr_stat is not null

But does not show BS3 and BS5 in case when apr1.apr_stat is null.
How do I modify the existing query so that it gives me proper output.

I want it to check value of apr1.apr_stat and then

Case1 :If not null ... it gives me result of existing query by applying last 3 group of conditions in where clause ... tra2, csd3, trd2

Case2: But If null ... It should not apply those conditions below so as to give me proper results ...

How do I do it ... please give me ur comments ... Thanks in advance.
---

select

csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi, csd1.csd_issu, csd1.csd_altr,
tra1.tra_trno,
csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi, csd2.csd_issu, csd2.csd_altr,
trd1.trd_trno, trd1.trd_recd,
apr1.apr_docu, apr1.apr_reqd,

apr1.apr_stat,

tra2.tra_trno,
csd3.csd_orig + csd3.csd_subj + csd3.csd_type + csd3.csd_numb + csd3.csd_revi, csd3.csd_issu, csd3.csd_altr,
trd2.trd_trno, trd2.trd_recd

from

e0437csd csd1, e0437tra tra1, e0437csd csd2, e0437trd trd1, e0437apr apr1, e0437tra tra2, e0437csd csd3, e0437trd trd2

where

csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi like '%SUBJECTCODE%' and
csd1.csd_ownr = 'BESIX' and
csd1.csd_aclas = 'CLASSCODE / TYPECODE' and

tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra1.tra_copy = 1 and
tra1.tra_appr = True and
tra1.tra_part = 'PARSO' and
tra1.tra_type = 'A' and

csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi = tra1.tra_trno and
csd2.csd_ownr = 'BESIX' and
csd2.csd_aclas = 'T' and

trd1.trd_trno = csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi and
trd1.trd_part = 'PARSO' and
trd1.trd_type = 'A' and
trd1.trd_cc = '0' and

apr1.apr_docu = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
apr1.apr_part = 'PARSO' and
apr1.apr_type = 'A' and

tra2.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra2.tra_copy = 1 and
tra2.tra_appr = False and
tra2.tra_part = 'BESIX' and
tra2.tra_type = 'I' and

csd3.csd_orig + csd3.csd_subj + csd3.csd_type + csd3.csd_numb + csd3.csd_revi = tra2.tra_trno and
csd3.csd_ownr = 'PARSO' and
csd3.csd_aclas = 'T' and

trd2.trd_trno = csd3.csd_orig + csd3.csd_subj + csd3.csd_type + csd3.csd_numb + csd3.csd_revi and
trd2.trd_part = 'BESIX' and
trd2.trd_type = 'I' and
trd2.trd_cc = '0'

order by

csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi

Jari
Computer Engg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources