Home » SQL & PL/SQL » SQL & PL/SQL » How to Display a Result based on Conditions?
How to Display a Result based on Conditions? [message #214913] Thu, 18 January 2007 09:45 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
select r.t_date,
t.t_num,
do.d_desc, --lookup tab
tmp.l_num,
tmp.p_num,
dt.c_amt, -- default L_NUM like '%D%' see below
-- or mt.m_amt -- amt is listed w/ HDR if L_NUM like '%M%'
from d_lookup do,
tmp t,
regrec r,
d_trans dt,
m_tran mt,
maintab mtb -- main table
where dt.num = r.num
and mt.num = r.num
and mtb.rnum = r.num
and mtb.num = mt.num
and mtb.num = dt.num
and mtb.id = dt.id
and mtb.t_num = dt.t_num
and mtb.t_num = mt.t_num
and mtb.d_id = mt.d_id
and mtb.d_id = do.d_id
and mtb.f_id = dt.f_id -- '001' when 'D' is found
and mtb.f_id = mt.f_id -- '002' " 'M' "
and mtb.l_num = t.l_num
and mtb.p_num = t.p_num
/

output looks like so:

T_DATE T_NUM D_DESC L_NUM P_NUM C_AMT ??
-------- ------------------------------- --------- -----
05-DEC-00 0700-006 COMMISSION D0001127 001 20000
05-DEC-00 0200-208 DUES D0001128 002 50000
05-DEC-00 0500-426 TAXES M0001229 003 $$

Note:
?? is to illustrate where M_AMT and $$ amount s/b when criteria
are met.
Not sure how to accomplish that part. Please help!

Thks in advance!
Re: How to Display a Result based on Conditions? [message #214950 is a reply to message #214913] Thu, 18 January 2007 13:13 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Not sure what you are trying to accomplish, but a wild guess: look up DECODE in the SQL reference.
Re: How to Display a Result based on Conditions? [message #214959 is a reply to message #214950] Thu, 18 January 2007 14:18 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks for replying. You hit it right on the head. Someone else did suggest the same, but use it (decode) in my where clause would be ideal.

Just to clarify what I would like to achieve see output below:
-------------------------------------------------------------

where clause would use decode to indicate when L_NUM like '%D'
THEN select c_amt and print it.
ELSE L_NUM like '%M%' select m_amt and print it; which is the blank column of output. I'd like to put both the HDR m_amt would be where ?? is and $$ amount where L_NUM start w/ '%M%'. It is an either or scenario.


T_DATE T_NUM D_DESC L_NUM P_NUM C_AMT ??
-------- -------- ---------- -------- ---- ------- ------
05-DEC-00 0700-006 COMMISSION D0001127 001 $20000
05-DEC-00 0200-208 DUES D0001128 002 $50000
05-DEC-00 0500-426 TAXES M0001229 003 $$

Many thnks!


Re: How to Display a Result based on Conditions? [message #214972 is a reply to message #214959] Thu, 18 January 2007 15:52 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
This could be something like (I've used case instead of decode):

SELECT ...
,      CASE WHEN l_num LIKE '%D' 
       THEN c_amt 
       WHEN l_num LIKE '%M%' 
       THEN m_amt END amt
FROM   ...
CASE Output not switching [message #215732 is a reply to message #214959] Tue, 23 January 2007 12:52 Go to previous message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
select r.t_date,
t.t_num,
do.d_desc, --lookup tab
tmp.l_num,
tmp.p_num,
case
when f_id = '011'
then c_amt
when f_id = '012'
then m_amt
end m_amt -- default
from d_lookup do,
tmp t,
regrec r,
d_trans dt,
m_tran mt,
maintab mtb -- main table
where dt.num = r.num
and mt.num = r.num
and mtb.rnum = r.num
and mtb.num = mt.num
and mtb.num = dt.num
and mtb.id = dt.id
and mtb.t_num = dt.t_num
and mtb.t_num = mt.t_num
and mtb.d_id = mt.d_id
and mtb.d_id = do.d_id
and mtb.f_id = dt.f_id
and mtb.f_id = mt.f_id
and mtb.l_num = t.l_num
and mtb.p_num = t.p_num
/

output looks like so:

T_DATE T_NUM D_DESC L_NUM P_NUM M_AMT
-------- ------------------------------- ------- -----
05-DEC-00 0700-006 COMMISSION D0001127 001
05-DEC-00 0200-208 COMMISSION D0001128 002
05-DEC-00 0500-426 COMMISSION M0001229 003
...
I am getting the same date and description and No amount is displayed.
I tested a smaller sample with only one table it works like a charm.
Please what am I missing?

Here is my small sampler:

this code yields the same result:
select t_num, d_id, f_id,
CASE
WHEN c_amt != 0 AND c_amt IS NOT NULL
THEN c_amt END c_amt
from d_tax
where d_id like '%01%'
or d_id like '%02%'
and f_id like '%011%'
/

T_NUM D_TYPE F_TYPE C_AMT
--------- -------- --- -----------------
1 01001001 011 50000
2 01001001 011 170000
1 01001001 011
1 01001001 011 500000
1 01001001 011
2 01001001 011 162567.26
1 01001001 011
1 01001001 011
1 01002001 011
1 01001001 011 28000
2 01001001 011 7000
3 01006001 011


Also, How can get rid of the blanks? if there is no $ I dont't want that record.

many thks!
Previous Topic: ORA-00942 ERROR
Next Topic: sql query to find the string
Goto Forum:
  


Current Time: Sat Dec 03 12:12:24 CST 2016

Total time taken to generate the page: 0.15590 seconds