Home » SQL & PL/SQL » SQL & PL/SQL » conditional select
conditional select [message #203949] Fri, 17 November 2006 01:00 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
how would i do this?


select distinct to_char(trunc(eff_start_date),'MM/DD/RRRR') a
      ,to_char(trunc(eff_start_date), 'MM/DD/RRRR') b 
from   cs_sas_header 
where  bran_code = 'MNL' 
and    cust_code = 'BB486-00000-12' 
and    coty_code = 'RV' 
and    cosi_code = '40FTR' 
and    cocl_code = 'C' 
and    laden_flag = 'N' 
and    factor = 1 
and    basis = 'V' 
and    cosi_code <> 'ALL' 
and    coty_code <> 'ALL' 
and    cocl_code <> 'ALL'
union
select to_char(trunc(sysdate), 'MM/DD/RRRR') a, to_char(trunc(sysdate), 'MM/DD/RRRR') b
from dual

A                                                                           B
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
10/25/2006                                                                  10/25/2006
10/27/2006                                                                  10/27/2006
11/04/2006                                                                  11/04/2006
11/17/2006                                                                  11/17/2006



im trying to do that when the first select returns some rows the sysdate in second query should not return, but if the first query has no result the second should return, in this query the 2nd query still returns, what should i use? thanks very much =)


another sample :



SQL> create table t(x int, y varchar2(3));

Table created

SQL> insert into t values(1, 'aaa');

1 row inserted

SQL> insert into t values(1, 'bbb');

1 row inserted

SQL> insert into t values(1, 'ccc');

1 row inserted

SQL> commit;

Commit complete

SQL> 
SQL> select y from t where x = 1
  2  union
  3  select 'ddd' from dual
  4  /

Y
--------------------------------
aaa
bbb
ccc
ddd

SQL> 




ddd should not return because the first select returned a row.

[Updated on: Fri, 17 November 2006 03:09] by Moderator

Report message to a moderator

Re: conditional select [message #203973 is a reply to message #203949] Fri, 17 November 2006 02:49 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Based on your second example, you can try this one. This may not be the optimal solution though:
select y
  from t,
      (select decode(count(1),0,0,1) val
         from t
        where x = 1) g 
where g.val = 1
union
select 'ddd'
  from dual,
      (select decode(count(1),0,0,1) val
         from t
        where x = 1) g 
where g.val = 0;
Re: conditional select [message #203974 is a reply to message #203973] Fri, 17 November 2006 02:52 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
SELECT y FROM t WHERE x = 1
UNION 
SELECT 'ddd' FROM dual
  WHERE NOT EXISTS (SELECT NULL FROM t WHERE x = 1);
Re: conditional select [message #203976 is a reply to message #203974] Fri, 17 November 2006 02:59 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Yep! that's it!! right, ehegagoka!!
Re: conditional select [message #203982 is a reply to message #203976] Fri, 17 November 2006 03:37 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
thank you so much for the reply =) it worked =)


SQL> select distinct to_char(trunc(eff_start_date), 'MM/DD/RRRR') a, to_char(trunc(eff_start_date), 'MM/DD/RRRR') b from cs_sas_header where bran_code = 'MNL' and cust_code = 'BB486-00000-12' and coty_code = 'RV' and cosi_code = '40FTR' and cocl_code = 'C' and laden_flag = 'N' and factor = 1 and basis = 'V' and cosi_code <> 'ALL' and coty_code <> 'ALL' and cocl_code <> 'ALL'
  2  union
  3  select to_char(trunc(sysdate), 'MM/DD/RRRR') a, to_char(trunc(sysdate), 'MM/DD/RRRR') b
  4  from dual
  5  where not exists (select distinct to_char(trunc(eff_start_date), 'MM/DD/RRRR') a, to_char(trunc(eff_start_date), 'MM/DD/RRRR') b from cs_sas_header where bran_code = 'MNL' and cust_code = 'BB486-00000-12' and coty_code = 'RV' and cosi_code = '40FTR' and cocl_code = 'C' and laden_flag = 'N' and factor = 1 and basis = 'V' and cosi_code <> 'ALL' and coty_code <> 'ALL' and cocl_code <> 'ALL')
  6  /

A                                                                           B
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
10/25/2006                                                                  10/25/2006
10/27/2006                                                                  10/27/2006
11/04/2006                                                                  11/04/2006

SQL> 






Previous Topic: PLS-00363 problem..
Next Topic: How to find object dependency in oracle
Goto Forum:
  


Current Time: Wed Dec 07 05:09:30 CST 2016

Total time taken to generate the page: 0.13948 seconds