value comparison in SQL

[maesb]

Hi, i'm working on an SQL statement, but am not sure how can i make comparison between values in SQL.

I've got a temporary table which stores some value, and i need to get the values to update another table in batch.

Here's my SQL statement :

UPDATE copart a SET
qono=NVL(qono,0)-NVL((SELECT <!SOMETHING HERE!> FROM prt0009_temp
WHERE crid='CC' AND DECODE(altr,' ',oprt,po_oprt)=a.part
AND raid='1111111111111'),0),lmdt=sysdate, lmid='1132'
WHERE crid='CC' and part in
(SELECT DECODE(altr,' ',oprt,po_oprt) as oprt FROM prt0009_temp
WHERE crid='CC' AND raid='1111111111111')

i need the following statement to replace the <!SOMETHING HERE!> in the above SQL.

let's say i've got 2 values,
ACTQ = 5
OUTS = 6

and i want to retrieve either one of the value depending on the following situation :
if ACTQ>OUTS then
retrieve OUTS
else
retrieve ACTQ
end if

Can i actually do all that comparison in an SQL Statement?
Thanks.

Maegan.

 

[maesb]

select dorq, actq, decode(dorq-actq, <0, dorq, actq) as temp from ptgrnd

how do i specify the <0(negative) condition? i don't think so i can use decode. or can i??

 

[HimanB]

Hi,
No you cannot use Decode but you can use Sign for this as follows:

select dorq, actq, decode(sign(dorq-actq), -1, dorq, actq) as temp from ptgrnd;

HTH
Regards
Himanshu

 

[maesb]

Himanshu,

thanks a lot. that works great!

Maegan.