Home » SQL & PL/SQL » SQL & PL/SQL » What is wrong with my logic?
What is wrong with my logic? [message #39011] Thu, 06 June 2002 09:35 Go to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
Hi,

I have three tables: cust c,
cust_prof cp,
case_par gcp.
If case_par is empty I need to select data from two first tables (full extract). But if case_par has any records (usually few) I have to join two first tables with case_par (limited extract).
I tried to do it in one select:
SELECT DISTINCT
-- c.cust_id,
c.prt_id,
c.name_last,
c.name_first,
cp.case_cust_id,
cp.case_id_nmbr,
cp.cust_type,
cp.payee_status
FROM cust c,
cust_prof cp,
case_par
WHERE (c.c_id = cp.c_id --Part 1
AND NOT EXISTS (SELECT rowid FROM case_par) )
OR (c.c_id = cp.c_id --Part 2
AND cp.case_id=gcp.case_id);

When case_par has records it works (part 2), but when it does not, the query returns no results (part 1)

I am still working on it, but if you let me know
what is wrong with my logic I will appriciate it.

Thank you
Re: What is wrong with my logic? [message #39012 is a reply to message #39011] Thu, 06 June 2002 10:07 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Try a union:

SELECT ...
FROM cust c
,cust_prof cp
,case_par gcp
WHERE c.c_id = cp.c_id
AND cp_case_id = grc.case_id
AND EXISTS (SELECT 'x' FROM case_par)
UNION
SELECT ...
FROM cust c
,cust_prof cp
WHERE c.c_id = cp.c_id
AND NOT EXISTS (SELECT 'x' FROM case_par)
;
Re: What is wrong with my logic? [message #39016 is a reply to message #39011] Thu, 06 June 2002 12:13 Go to previous message
sverch
Messages: 582
Registered: December 2000
Senior Member
The Union gives me same result: when table # 3 has records it works, but when it is empty it does not.

But thank you anyway
Previous Topic: Can We Create a user of our choise entered through Parameters of a Procedure.
Next Topic: Help with View
Goto Forum:
  


Current Time: Wed Nov 14 21:51:14 CST 2018