SQL Language Question!!

[piggy213]

I have the question as following:
The field "UNIT_VALUE" and the field "UNIT_POINTER" are in the table "UNIT".
Now, i have to select "UNIT_VALUE" when {"UNIT_POINTER" = 4 and 9 and 283}.
I want to got last answer is multipling by the above three condition's "UNIT_VALUE".
Thank you very much!!



piggy

 

[JamesLean]

I'm not sure I completely understand the question(!) but am I right in thinking you will have a unique row for each UNIT_POINTER and you want to take the UNIT_VALUEs for 4, 9, and 283 and multiply them together? If so use this:

SELECT (SELECT unit_value FROM tablename WHERE unit_pointer = 4) * (SELECT unit_value FROM tablename WHERE unit_pointer = 9) * (SELECT unit_value FROM tablename WHERE unit_pointer = 283)

--James

 

[osjohnm]

Hi

This will return the unit_vlaue field where the unit_pointer = 4, 9, 283

select unit_value from unit
where unit_pointer in (4, 9, 283)

I'm not exactly sure of what you are trying to do with regards to "multiplying"? Perhaps you can explain some more.

John

 

[piggy213]

Sorry,my english is poor.
Thank you James&John's suggestion.
I try to explain clearly.

For example,when
"UNIT_POINTER"=4 then "UNIT_VALUE"=2,
"UNIT_POINTER"=9 then "UNIT_VALUE"=5,
"UNIT_POINTER"=283then "UNIT_VALUE"=7,
I want answer is 2*5*7=70.

Somebody suggest me that I can write like as below:

select "UNIT_VALUE" as A
when "UNIT_POINTER"=4
select "UNIT_VALUE" as B
when "UNIT_POINTER"=9
select "UNIT_VALUE" as C
when "UNIT_POINTER"=283
Then A*B*C


But I don't know how to write it exactly...
Anybody knows?

ps.I use oracle SQL PLUS.

Thanks piggy

 

[JamesLean]

My query should give you the result you need. Have you tried it?

PS. This is the SQL Server forum. You may get better results posting in the Oracle forum. --James