case when after a where clause in select

[Crystalboy1]

HI all,
I want to add a Case When statment after the where clause of a select statement. example given below, its not working please help.

select counter,sum(a),sum(b),sum(c) from
tablename
where
tablename.startdate=20080705
and case counter
when 24 then
tablename.employeenumber not in (20,30,40,50)
else
tablename.employeenumber not in (20,30,40)
end
order by counter
group by counter

 

[SantaMufasa]

Crystalboy,

In Oracle, a CASE statement produces, as its result, a data value, not executable code. Therefore, with your code construct, it would be simlar to saying:

...
WHERE tablename.startdate=20080705
  AND 216
ORDER BY counter
GROUP BY counter

So, as you can see, that wouldn't make sense (syntactically).

If you could re-state, in a non-syntactical, functional narrative, what you would like to do, then I guarantee that we can propose SQL code that does what you want.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Crystalboy1]

Mufasa, i got a select statment which returns a total of 4 fields. the first field is the day taken out of the transation date, and the rest of the three fields are sum of intrest, principal,other receipts of differnt loans group by the transation date. now the problem is, there are some loan numbers which are enter wrongly and they want me to exclude some loans numbers on 24th and others on all other days? my acutal code is given below, if that make any sence.


select to_number(substr(c.pwlb_transaction_date, 7, 2)) as daynumber,
0 as waysandmeans,
sum(c.pwlb_nostro_repayment) as principal,
sum(c.pwlb_nostro_interest) as interest
from dmo_pwlb_counterparty_nostros c
where c.pwlb_activity is null
and c.pwlb_borrower_code <> 'NLF'
and dmo_pwlb_reports.financial_generation(c.pwlb_transaction_date) =
dmo_pwlb_reports.financial_generation(20080705)
and to_date(c.pwlb_transaction_date, 'yyyymmdd') <=
last_day(to_date(20080705, 'yyyymmdd')) - 3
group by substr(c.pwlb_transaction_date, 7, 2)


Mufasa i want to add the ''case when'' given below want to add to the end of the above statment after the where clause.



case substr(c.pwlb_transaction_date, 7, 2)
when 24 then c.pwlb_loan_number not in ('PW487153', 'PW482770', 'PW454355',
'PW481714', 'PW482299', 'PW484964', 'PW482710', 'PW482787')
else c.pwlb_loan_number not in ('PW487153', 'PW482770', 'PW454355', 'PW481714',
'PW482299', 'PW484964', 'PW482710')
end

 

[SantaMufasa]

Thanks, Crystalboy, for the clarification.

One of the many solutions available to resolve your logic need includes this construct:

...
where c.pwlb_activity is null
   and c.pwlb_borrower_code <> 'NLF'
   and dmo_pwlb_reports.financial_generation(c.pwlb_transaction_date) =
       dmo_pwlb_reports.financial_generation(20080705)
   and to_date(c.pwlb_transaction_date, 'yyyymmdd') <=
       last_day(to_date(20080705, 'yyyymmdd')) - 3
       group by substr(c.pwlb_transaction_date, 7, 2)
   and case when substr(c.pwlb_transaction_date, 7, 2) = 24
             and c.pwlb_loan_number not in
                 ('PW487153', 'PW482770', 'PW454355','PW481714', 'PW482299'
                 ,'PW484964', 'PW482710', 'PW482787') then 'Do it'
            when c.pwlb_loan_number not in
                 ('PW487153', 'PW482770', 'PW454355', 'PW481714','PW482299'
                 ,'PW484964', 'PW482710') then 'Do it'
            else 'Do not do it' end = 'Do it'
/

Let us know your thoughts about this construct.

Also, you apparently store your DATE/TIME information in a numeric data item. In Oracle, it is poor form to store date and/or time information in any data-item format besides DATE.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Crystalboy1]

Mufasa. it worked but its very slow. Do u have any other idea about this which will work fast ? thanks

 

[SantaMufasa]

Some questions:[ul][li]What does this code mean:

dmo_pwlb_reports.financial_generation(c.pwlb_transaction_date) =
       dmo_pwlb_reports.financial_generation(20080705)

It appears that "dmo_pwlb_reports.financial_generation" is possibly a user-defined function. Is that the case? If not, then please tell us more about it.

[/li][li]How many rows are in the table?[/li][li]How long does the query take presently?[/li][li]Do you have indexes on these columns:[/li][ul][li]c.pwlb_activity[/li][li]c.pwlb_borrower_code[/li][li]c.pwlb_transaction_date[/li][li]c.pwlb_loan_number[/li][/ul][li]What is the date and time that you (or someone else) last gathered statistics on the "dmo_pwlb_counterparty_nostros" table?[/li][/ul]Your answers will help us identify methods to help you improve performance.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Crystalboy1]

Yes "dmo_pwlb_reports.financial_generation" is a Under defined function which generate a unique number for each month. so actually i m doing calculation for the whole month.
IT took a good few hours to run. The results are wrong too. it seems like they got almost double or tripple.
there no no indexes on any of the field this is the biggest problem.

 

[SantaMufasa]

"...dmo_pwlb_reports.financial_generation" is a Under defined (I presume you meant "User"-defined) function which generate a unique number for each month. so actually i m doing calculation for the whole month.

Who knows how long this step, alone, might take? Depending upon the code within dmo_pwlb_reports.financial_generation, this step, alone, could be consuming between 1 second and 1 hour!


If I were in your position, and if I wanted to understand the performance hits of each component of the WHERE represented, I might split out the WHERE clauses with this type of code:

set timing on
set time on
select count(*)
  from (select to_number(substr(c.pwlb_transaction_date, 7, 2)) as daynumber,
               0 as waysandmeans,
               sum(c.pwlb_nostro_repayment) as principal,
               sum(c.pwlb_nostro_interest) as interest
          from dmo_pwlb_counterparty_nostros c
         where <place one "AND" of the WHERE clause here>);

(Then re-do the above SELECT with a different "AND" of the WHERE clause in this SELECT.)

et cetera.

It would be helpful to run, then post here, the result of an EXPLAIN PLAN of your code, as well.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Crystalboy1]

Mufasa, i m using PLSQL developer and i get error for the command set timing on, or set time on. just to get the work done for timebeing, i have split the query into 2 portions. have a look at it.

(select to_number(substr(c.pwlb_transaction_date, 7, 2)) as daynumber,
0 as waysandmeans,
sum(c.pwlb_nostro_repayment) as principal,
sum(c.pwlb_nostro_interest) as interest,
0 as premiumdiscount,
0 as overpayments
from dmo_pwlb_counterparty_nostros c
where
c.pwlb_activity is null
and c.pwlb_borrower_code<>'NLF'
and dmo_pwlb_reports.financial_generation(c.pwlb_transaction_date) =
dmo_pwlb_reports.financial_generation(processingdate)
and to_date(c.pwlb_transaction_date, 'yyyymmdd') <=
last_day(to_date(processingdate, 'yyyymmdd')) - num
substr(c.pwlb_transaction_date, 7, 2)<>24
and c.pwlb_loan_number not in('PW487153','PW482770','PW454355'
,'PW481714','PW482299','PW484964','PW482710')
group by substr(c.pwlb_transaction_date, 7, 2)

union all
(select to_number(substr(c.pwlb_transaction_date, 7, 2)) as daynumber,
0 as waysandmeans,
sum(c.pwlb_nostro_repayment) as principal,
sum(c.pwlb_nostro_interest) as interest,
0 as premiumdiscount,
0 as overpayments
from dmo_pwlb_counterparty_nostros c
where
c.pwlb_activity is null
and c.pwlb_borrower_code<>'NLF'
and dmo_pwlb_reports.financial_generation(c.pwlb_transaction_date) =
dmo_pwlb_reports.financial_generation(processingdate)
and to_date(c.pwlb_transaction_date, 'yyyymmdd') <=
last_day(to_date(processingdate, 'yyyymmdd')) - num
and c.pwlb_loan_number not in('PW487153','PW482770','PW454355'
,'PW481714','PW482299','PW484964','PW482710','PW482787')
and substr(c.pwlb_transaction_date, 7, 2)=24
group by substr(c.pwlb_transaction_date, 7, 2)

 

[ChrisHunt]

Just returning to the original question, isn't using a CASE in the where clause a bit cumbersome? Instead of

select counter,sum(a),sum(b),sum(c) from 
tablename 
where 
tablename.startdate=20080705
and case counter
     when 24 then 
      tablename.employeenumber not in (20,30,40,50)
     else 
tablename.employeenumber not in (20,30,40)
end 
order by counter
group by counter

why not just do this?

select counter,sum(a),sum(b),sum(c) from 
tablename 
where 
tablename.startdate=20080705
and tablename.employeenumber not in (20,30,40)
and not (counter= 24 and tablename.employeenumber = 50)
order by counter
group by counter

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd