Home » SQL & PL/SQL » SQL & PL/SQL » Using Decode in Where Clause
Using Decode in Where Clause [message #360090] |
Wed, 19 November 2008 10:02  |
zaneta
Messages: 3 Registered: November 2008
|
Junior Member |
|
|
In bold, I am trying to use decode in where clause but condition is not working...I need to only pull records with a primary source('P') or record has both primary and secondary source('S'). But if record has only a secordary source I need to pull it...What am I doing wrong?????Please help....Are there any good PL/SQL tutorials out there....
select cv.last_name || ' '|| cv.first_name DocName, initcap(p.patient_last || ', ' || p.patient_first || DECODE(p.patient_middle, NULL, NULL, ' ') || p.patient_middle) as PatientFullName,
p.pid, p.orgpatid, dl.activity_start_date,dl.log_type,dl.cpt_code,NVL(rfs.PCFEE, NVL(rfs.MAXFEE,0)) Rate,
rfs.carrier,decode(cv.contact_type,'0','A','P') empstatus,RANK.RANK,decode(rank.rank,'P',cp.company_name,'UNKNOWN') COMPANY_NAME
From daily_log dl
,patient_paysource pps
,patients p
,ref_fee_schedule rfs
,contacts_view cv
, patient_paysource_rank rank
,contacts_view cp
Where dl.pid = p.pid
and(pps.intakeid = dl.intakeid or pps.intakeid is null)
and(rank.intakeid = dl.intakeid or rank.intakeid is null)
and p.orgpatid = dl.orgpatid
and p.pid = pps.pid(+)
and dl.pid = rank.pid(+)
and trunc(decode(NVL(:filterdate,'S'),'S',dl.activity_start_date,dl.createdate)) between :sdate and :edate
and cv.last_name like upper(:lastname) || '%'
and cv.contactid = dl.md_contactid(+)
and cv.contactid = dl.md_contactid
AND rfs.carrier = decode( pps.paysource_type ,null, 'CARE', decode(pps.paysource_type,'COMM','CARE','SELF','CARE','CHAR','CAID',pps.paysource_type))
and (pps.paysource_type = nvl( :paysource_type, pps.paysource_type ) or pps.paysource_type is null)
and trunc(dl.activity_start_date) between trunc(nvl(rank.startdate, :sdate)) and trunc( nvl( rank.enddate, :edate))
--+and rank.rank <> DECODE(rank.rank, 'P','S',-1)
and rank.rank <> DECODE(rank.rank, 'P','S',-1)
and (pps.psid = rank.psid or pps.psid is null or rank.psid is null)
and rfs.code = dl.cpt_code
and NVL(:cptcode,dl.cpt_code) = dl.cpt_code
and rfs.code_year = to_char(dl.activity_start_date, 'yyyy')
and pps.paysource_contactid = cp.contactid(+)
and dl.log_type='MD'
and decode(cv.contact_type,'0','A','P') like NVL(:status,'%')
and p.orgpatid IN ('20080626')--'20080851',
--and p.orgpatid IN ('20080851')
order by dl.activity_start_date, PatientFullName
|
|
|
Re: Using Decode in Where Clause [message #360093 is a reply to message #360090] |
Wed, 19 November 2008 10:14   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Since "is not working" could be anything:
Is your computer plugged in?
Decode in the where clause works in general:
SQL> SELECT * FROM dual
2 WHERE 1 = Decode(1,1,1);
D
-
X
I suspect you have a datatype mismatch in the decode, since you seem to return a varchar in one case and a number as default, which does not work.
Post your entire formatted SQL Session including any errors, and the required information as described in the Forum Guide, which should read first.
[Updated on: Wed, 19 November 2008 10:15] Report message to a moderator
|
|
|
Re: Using Decode in Where Clause [message #360096 is a reply to message #360090] |
Wed, 19 November 2008 10:33   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The only way this:
and rank.rank <> DECODE(rank.rank, 'P','S',-1)
doesn't evaluate to TRUE is if rank is -1.
I rather suspect that's not what you wanted but I'm not completely clear on want you do want.
|
|
|
Re: Using Decode in Where Clause [message #360097 is a reply to message #360090] |
Wed, 19 November 2008 10:36   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | .I need to only pull records with a primary source('P') or record has both primary and secondary source('S'). But if record has only a secordary source I need to pull it
|
Not sure, what is the difference between "pull records" and "I need to pull it", but you may check existence of another row using EXISTS condition with properly correlated sub-query.
I suppose, that one row may be either a primary source('P') or secondary source('S'), not both.
|
|
|
Re: Using Decode in Where Clause [message #360098 is a reply to message #360096] |
Wed, 19 November 2008 10:39   |
zaneta
Messages: 3 Registered: November 2008
|
Junior Member |
|
|
I guess what I mean is....If I have two records..One is primary and the other is secondary...I need to get the primary record and not the seconary....If I only have one record and this record can be primary or secondary...I need to pull that one record. Sorry if I am not explaining correctly...I am still in school...
|
|
|
|
|
Re: Using Decode in Where Clause [message #360103 is a reply to message #360100] |
Wed, 19 November 2008 11:00   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Another option for "get me one row of those, with an order of precedence" would be to have a select that returns those rows in their order of precedence and then you select the first row of that. For example
select * from (
select * FROM my_tab
where [some condition]
order by something
)
where rownum = 1;
|
|
|
Re: Using Decode in Where Clause [message #360105 is a reply to message #360100] |
Wed, 19 November 2008 11:18   |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
As an alternative to the exists (and I presume a union all query), you can also consider using analytics. You want the first row of a group of rows, which is clearly an analytics question. You will want to do performance testing on both alternatives. Depending upon your data there may or may not be a big difference.
It would have been nice if you had posted some table creates and insert statements so that we would have an idea of the kind of data you are working with.
Here is an example of a possible analytics solution. In particular, please pay attention to the check constraint and the unique constraint. These are necessary because they crystalize the meaning of the data with respect to the problem space. Naturally the code would still work without them, as long as the data was correct but then again, it is these constraints that form the guarantee that the data is correct. I am not suggesting that this is the only correct interpretation of the data, only that you should have an interpretation and that it should be enforced when possible by some kind of declarative mechanism in the database. Otherwise we run the risk of answers changing over time and solutions breaking down over time.
SQL> drop table t_somerows
2 /
Table dropped.
SQL>
SQL> create table t_somerows
2 (
3 a_pk number not null primary key
4 ,a_user_key varchar2(10) not null
5 ,primary_secondary_indicator varchar2(1) not null
6 ,check (primary_secondary_indicator in ('P','S'))
7 ,unique (a_user_key,primary_secondary_indicator)
8 )
9 /
Table created.
SQL>
SQL> insert into t_somerows values (1,'ACCOUNT 1','P');
1 row created.
SQL> insert into t_somerows values (2,'ACCOUNT 1','S');
1 row created.
SQL> insert into t_somerows values (3,'ACCOUNT 2','P');
1 row created.
SQL> insert into t_somerows values (4,'ACCOUNT 3','S');
1 row created.
SQL>
SQL> commit
2 /
Commit complete.
SQL>
SQL> select * from t_somerows
2 /
A_PK A_USER_KEY P
---------- ---------- -
1 ACCOUNT 1 P
2 ACCOUNT 1 S
3 ACCOUNT 2 P
4 ACCOUNT 3 S
4 rows selected.
SQL> select *
2 from (
3 select t_somerows.*
,row_number () over (
partition by a_user_key
order by decode(primary_secondary_indicator,'P',1,2)
) group_rowno
4 from t_somerows
5 )
6 where group_rowno = 1
7 /
A_PK A_USER_KEY P GROUP_ROWNO
---------- ---------- - -----------
1 ACCOUNT 1 P 1
3 ACCOUNT 2 P 1
4 ACCOUNT 3 S 1
3 rows selected.
SQL>
Good luck, Kevin
|
|
|
Re: Using Decode in Where Clause [message #360128 is a reply to message #360090] |
Wed, 19 November 2008 12:55  |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
zaneta wrote on Wed, 19 November 2008 11:02 |
trunc(decode(NVL(:filterdate,'S'),'S',dl.activity_start_date,dl.createdate)) between :sdate and :edate
|
This surely no good as you are comparing a DATE column to two strings. You need to review the documentation about how to properly use the function TO_DATE.
|
|
|
Goto Forum:
Current Time: Mon Feb 17 04:23:14 CST 2025
|