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 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #360099 is a reply to message #360090] Wed, 19 November 2008 10:45 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then as flyboy says you need you use an EXISTS condition - or NOT EXISTS in your case. Decode is of no use to you for this check.

Re: Using Decode in Where Clause [message #360100 is a reply to message #360099] Wed, 19 November 2008 10:47 Go to previous messageGo to next message
zaneta
Messages: 3
Registered: November 2008
Junior Member
ok will try EXISTS condition.thanks
Re: Using Decode in Where Clause [message #360103 is a reply to message #360100] Wed, 19 November 2008 11:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 Go to previous message
joy_division
Messages: 4640
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.
Previous Topic: [merged] better way of doing this / not going inside FOR loop
Next Topic: Create HTML with htp.formOpen
Goto Forum:
  


Current Time: Sun Dec 04 16:40:13 CST 2016

Total time taken to generate the page: 0.23436 seconds