if statement in query

[jayjaybigs]

I have a table (mytable) and here is the description

select person_id,
name,
job_type,
job_code,
job_comp,
desc

from mytable
where rownum < 5

I got the following.

3445, Joe Manny, janitor, a310, 310.00, kitchen filler.
5456, klay Gerry, driver, b456, 420.00, mail delievery.
2363, kevein Mah, forklift, f489, 790.00, wharehouse.

I am trying to do the following.

select person_code,
name,
(select (job_comp * 2) if job_type like 'driver' and job_code = b456) as "temporary_raise"
from mytable

Basically I want the third field (temporary_raise) in my result
to be based on the values in 5th, 3rd and 4th in the table.
Note that these fileds are different for all employees.

Thanks for your help

 

[r937]

select person_code
     , name
     , case when job_type = 'driver' 
             and job_code = 'b456'
            then job_comp * 2
            else 0 end  as "temporary_raise"
  from mytable

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[jayjaybigs]

Thank you fro your suggestion.

However, I tried to enlarge above principle as follows:

select person_code
,Name
,cost_center
,account
,leave_type_code
,ac_code
,accrual_value
,CASE when ac_code = 'A900'
AND leave_type_code LIKE 'Vacation'
then "Vacation Carryover" = accrual_value/4
when ac_code = 'A290'
and leave_type_code LIKE 'Vacation'
then "Vacation Entitlement" = accrual_value*2
else accrual_value end "Vacation"
from HR_VLEAVE_LIABBILTY

IN the then clauses that is:
then "Vacation Carryover" = accrual_value/4
then "Vacation Entitlement" = accrual_value*2

My objective here is to have new column names (Vacation Carryover and Vacation Entitlement) as a result of evaluation of accrual_value/4 and accrual_value*2 respectivelly.
However this is giving me error ORA-00095. please Help and thanx

 

[r937]

each CASE expression is just a single value and maps to just one column in the result set

for what you want to do, you need 3 columns

, CASE when ac_code = 'A900'
AND leave_type_code = 'Vacation'
then accrual_value/4
else null
end as "Vacation Carryover"
, CASE when ac_code = 'A290'
and leave_type_code = 'Vacation'
then accrual_value*2
else accrual_value
end as "Vacation Entitlement"
, accrual_value as "Vacation"

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[jayjaybigs]

when I use the following:
, CASE when ac_code = 'A900'
AND leave_type_code = 'Vacation'
then accrual_value/4
else null
end as "Vacation Carryover"
, CASE when ac_code = 'A290'
and leave_type_code = 'Vacation'
then accrual_value*2
else accrual_value
end as "Vacation Entitlement"
, accrual_value as "Vacation"

It gives error ORA-00932 Inconsistence datatypes: expected NUMBER got CHAR

I changed line 10 to:
else NULL.

The results was not what I was expecting. Both Columns Vacation Carryover and Vacation Entitlement were NULL though I know there are records where the conditions were true.

Please advise.

 

[r937]

perhaps you should post a new question in the oracle forum, now that you're catching on to how CASE works

this is the ANSI SQL forum, for non-proprietary sql language

double check your actual expressions, i wasn't sure i split your single expression into three properly



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[jayjaybigs]

Thanks for the advise

 

[jayjaybigs]

Does anyone knows why this code is hanging my system forever. Sorry folks I would haev posted in oracle forum but know one is home in that forum.

select A300.PERSON_CODE
,A300.NAME
,A300.COST_CENTER
,A300.account
,A300.leave_type_code
,A300.ac_code
,A300.accrual_value
, CASE when A900.ac_code = 'A900'
AND A900.leave_type_code = 'VACATION'
then A900.accrual_value/4
else null
end as "Vacation Carryover"
, CASE when A290.ac_code = 'A290'
and A290.leave_type_code = 'VACATION'
then A290.accrual_value*2
else NULL
end as "Vacation Entitlement"
, CASE when A300.ac_code = 'A300'
and A300.leave_type_code = 'VACATION'
then A300.accrual_value
else NULL
end as "Vacation Time Taken"
, CASE when A900.ac_code = 'A900'
or A300.ac_code = 'A300'
or A290.ac_code = 'A290'
and A300.leave_type_code = 'VACATION'
then ((A900.accrual_value/4) + (A290.accrual_value*2) + (A300.accrual_value))
else NULL
end as "Remaining Leave Balance 1st Q"
, CASE when A900.ac_code = 'A900'
and A900.leave_type_code = 'EBP'
then A900.accrual_value
else NULL
end as "EBP Carryover"
, CASE when A300.ac_code = 'A300'
and A300.leave_type_code = 'EBP'
then A300.accrual_value
else NULL
end as "EBP Time Taken"
, CASE when A300.ac_code = 'A300'
and A300.leave_type_code = 'ACC SICK'
then A300.accrual_value
else NULL
end as "ACC Sick Time Taken"
, CASE when A900.ac_code = 'A900'
and A900.leave_type_code = 'ACC SICK'
then A900.accrual_value
else NULL
end as "ACC Sick Carryover"
, CASE when A700.ac_code = 'A700'
and A700.leave_type_code = 'CTO'
then A700.accrual_value
else NULL
end as "CTO Balance"
, CASE when A700.ac_code = 'A700'
and A700.leave_type_code = 'STAT EXCHANGE'
then A700.accrual_value
else NULL
end as "Stat Exchange Balance"
, CASE when A700.ac_code = 'A700'
and A700.leave_type_code = 'STAT LIEU'
then A700.accrual_value
else NULL
end as "Stat Lieu Balance"
from BCH_HR_VLEAVE_LIABILITY A290,
HR_VLEAVE_LIABILITY A300,
HR_VLEAVE_LIABILITY A700,
HR_VLEAVE_LIABILITY A900

 

[PHV]

Are you sure you want such cross join (cartesian product) ?
I guess you have to add a WHERE clause to join your 4 tables ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

no oracle folks at home here either ;-)

my first guess is you are getting a humungous cross join

let's say BCH_HR_VLEAVE_LIABILITY has 5000 rows, and let's say HR_VLEAVE_LIABILITY has 20000 rows, then the result set you are returning will have 40,000,000,000,000,000 rows

that's forty quadrillion rows

oracle's probably just running out of disk space, that's all



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[jayjaybigs]

Even when I used the following where clause:

where A290.PERSON_CODE = A300.PERSON_CODE
AND A300.person_code = A700.PERSON_CODE
AND A700.PERSON_CODE = A900.PERSON_CODE

note that there is only one table, and I am just using four aliases to differentiate the accrual_values. Also, the result should be giving me one row per PERSON_CODE with all the resulting values in each column.

 

[fredericofonseca]

Is person_code a index of the tables?


I would also try the following selects one at the time to see if it impacts on speed. Note that if person_code is not an index then speed will always be a issue.
The purpose is to exclude the "case selection" bits from the speed issues. Once you figure out how to "speed" up the selection of records you can then add the remaining bit.

select A290.PERSON_CODE
,A300.NAME
,A300.COST_CENTER
,A300.ac_code
from BCH_HR_VLEAVE_LIABILITY A290,
     HR_VLEAVE_LIABILITY A300

where A290.PERSON_CODE = A300.PERSON_CODE

select A290.PERSON_CODE
,A300.NAME
,A300.COST_CENTER
,A300.ac_code
,A900.accrual_value
from BCH_HR_VLEAVE_LIABILITY A290,
     HR_VLEAVE_LIABILITY A300,
     HR_VLEAVE_LIABILITY A900

where A290.PERSON_CODE = A300.PERSON_CODE
AND A290.PERSON_CODE = A900.PERSON_CODE

select A290.PERSON_CODE
,A300.NAME
,A300.COST_CENTER
,A300.ac_code
,A900.accrual_value
,A300.accrual_value
,A700.accrual_value
from BCH_HR_VLEAVE_LIABILITY A290,
     HR_VLEAVE_LIABILITY A300,
     HR_VLEAVE_LIABILITY A700,
     HR_VLEAVE_LIABILITY A900

where A290.PERSON_CODE = A300.PERSON_CODE
AND A290.person_code = A700.PERSON_CODE
AND A290.PERSON_CODE = A900.PERSON_CODE

And now for the bit that puzzles me.

IF you DON'T have any where selection what is the point of
HR_VLEAVE_LIABILITY A300,
HR_VLEAVE_LIABILITY A700,
HR_VLEAVE_LIABILITY A900

if they are the same table?

This would make sense if your "where" clause was something like
where a300.my_other_field = "A"

AND A300.PERSON_CODE = A300.PERSON_CODE
AND A700.my_other_field = "B"

AND A300.PERSON_CODE = A900.PERSON_CODE
AND A900.my_other_field = "C"




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com