Query to show null - joined values

[t16turbo]

hi folks,

I have the following query:

select CHG.PREXTERNALID, CHG.PRNAME, CST.DSTI_PROJ_REF, A.PRACTSUM, A.PRRESOURCEID
FROM 
NIKU.PRTASK T,
NIKU.PRCHARGECODE CHG,
NIKU.PRASSIGNMENT A,
NIKU.ODF_CA_PROJECT CST
WHERE 
CST.ID = T.PRPROJECTID AND
A.PRTASKID (+)= T.PRID  AND
T.PRCHARGECODEID = CHG.PRID AND
CST.DSTI_PROJ_REF LIKE 'UKE04706%'
--AND A.PRACTSUM 
ORDER BY PREXTERNALID

this lists the Chargecode, TaskName, ProjectNumber, Actuals and Resource Code.
Each task can have several entries.
some of the entries will have a null value in the PRRESOURCEID field and PRACTSUM field.
I need to change this query so that it will show those PREXTERNALID's that ONLY have null values in the PRRESOURCEID field.
heres an exmaple of the results:

PREXTERNALID, PRNAME, DSTI_PROJ_REF, PRACTSUM, PRRESOURCEID
LDEV5120 Coding UKE1234 15 5000123
LDEV5120 Coding UKE1234 25 5000123
LDEV5120 Coding UKE1234
LDEV5080 Proj. Spec UKE1234
LDEV5080 Proj. Spec UKE1234
LDEV5080 Proj. Spec UKE1234
LDEV5222 Design UKE1234 5 5000134
LDEV5222 Design UKE1234 15 5000123
LDEV5222 Design UKE1234 23 5000160
LDEV5222 Design UKE1234 10 5000160
LDEV5000 Testing UKE1234
LDEV5000 Testing UKE1234 15 5000123

in the above exmaple (the results from my query)
I need to change the query so that I only retrieve
one result:
LDEV5080 Proj. Spec UKE1234

notice that all others have PRACTSUM values against PREXTERNALID

how is this possible?

 

[Turkbear]

Hi,
Have you tried:

select CHG.PREXTERNALID, CHG.PRNAME, CST.DSTI_PROJ_REF, A.PRACTSUM, A.PRRESOURCEID
FROM 
NIKU.PRTASK T,
NIKU.PRCHARGECODE CHG,
NIKU.PRASSIGNMENT A,
NIKU.ODF_CA_PROJECT CST
WHERE 
[COLOR=red]
A.PRRESOURCEID IS NULL AND
[/color]
CST.ID = T.PRPROJECTID AND
A.PRTASKID (+)= T.PRID  AND
T.PRCHARGECODEID = CHG.PRID AND
CST.DSTI_PROJ_REF LIKE 'UKE04706%'
--AND A.PRACTSUM 
ORDER BY PREXTERNALID

What results?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[t16turbo]

no, cos some of the PREXTERNALID's have null values in some rows and values in other rows.

 

[SantaMufasa]

T16,

Since I have no test data to test this code, you will need to advise us whether it works, but try this:

select [COLOR=red]DISTINCT[/color] CHG_A.PREXTERNALID, CHG_A.PRNAME, CST.DSTI_PROJ_REF, A.PRACTSUM, A.PRRESOURCEID
FROM 
NIKU.PRTASK T,
NIKU.PRCHARGECODE CHG_A,
NIKU.PRASSIGNMENT A,
NIKU.ODF_CA_PROJECT CST
WHERE
[COLOR=red]not exists
(select 'x' from 
NIKU.PRTASK T,
NIKU.PRCHARGECODE CHG_B,
NIKU.PRASSIGNMENT A,
NIKU.ODF_CA_PROJECT CST
WHERE 
CST.ID = T.PRPROJECTID AND
A.PRTASKID (+)= T.PRID  AND
T.PRCHARGECODEID = CHG_B.PRID AND
CST.DSTI_PROJ_REF LIKE 'UKE04706%' AND
[b]CHG_A.PREXTERNALID = CHG_B.PREXTERNALID AND
A.PRRESOURCEID IS NOT NULL[/b]) AND[/color]
CST.ID = T.PRPROJECTID AND
A.PRTASKID (+)= T.PRID  AND
T.PRCHARGECODEID = CHG.PRID AND
CST.DSTI_PROJ_REF LIKE 'UKE04706%'
--AND A.PRACTSUM 
ORDER BY PREXTERNALID

The RED code is newly added code; the BOLD RED code is the code of particular note since the first such line correlates the red code with the original code and the second line is what checks for NOT NULL resulting rows...and if it finds a NOT NULL row amongst the red results, an 'x' results. Then the not exists looks for just those rows where the code did not find a resulting 'x'.

The DISTINCT means you will receive just one row per "all NULL PRRESOURCEID" per PREXTERNALID.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.