Conditional Statement problem..

[nikol]

I have 2 tables answer & secunsec
SELECT answer.uid,
CASE
when answer.question_id=1212 then secunsec.val_float
end as rent
from answer FULL OUTER JOIN secunsec
on answer.uid=secunsec.uid
QUESTION_ID=1212 means rent/uid

This query gives me output as
UID QUESTION_ID VAL_FLOAT
345 1212 $234
567 1212
45 1212 $569
678 1212 $3450
Now, I want if uid has NULL rent(VAL_FLOAT) then take values from
QUESTION_ID=1213 and QUESTION_ID=1214 (which means minimum rent & maximum rent)
EX..Question_id=1213 = $450
Question_id=1214 = $780
for uid=567
O/P should be like "$450 + $780"

UID QUESTION_ID VAL_FLOAT
345 1212 $234
567 1212 $450 + $780
45 1212 $569
678 1212 $3450

 

[vongrunt]

To eliminate some doubts, posted sample data for both tables would be nice.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[bborissov]

I agree with vongurt. Post some data and tell us what is your expectations.

Borislav Borissov

 

[nikol]

Following is the query I'm using on two table "Answer" & "Secunsec"
ANSWER SECUNSEC
============= =============
UID CLIENT_id QUESTION_ID UID VAL_FLOAT
3 678 1212 3 $345
10 67 1213 10 $300
10 67 1214 10 $500
11 67 1214 11 $300
11 78 1213 11 $690
12 45 1212 12 $567
13 68 1212 13 $670
13 68 1213 13 $340
13 68 1214 13 $456
14 56 1213 14 $300
14 56 1214 14 $800

WHERE question_id=1212(RENT)
=1213(MINIMUM RENT)
=1214(MAXIMUM RENT)

SELECT answer.uid,answer.client_id,question_id, secunsec.val_float
FROM da_answer FULL OUTER JOIN secunsec ON answer.uid=secunsec.uid
WHERE da_answer.question_id=1214)

& The output from this query is :
UID Client_id Question_id Val_Float
10 67 1212 $345
11 78 1212
12 45 1212 $567
13 68 1212 $670
14 56 1212
At Present , I have this output. Now I want IF a CLIENT_ID has NULL RENT) with Question_id=1212
THEN that CLIENT_ID should display "MINIMUM RENT"+"MAXIMUM RENT"...
I hope this will help u in solving my problem.
FINAL OUTPUT AS :
UID Client_id Question_id Val_Float
10 67 1212 $345
11 78 1212 $300+$690
12 45 1212 $567
13 68 1212 $670
14 56 1212 $300+$800

 

[SQLBill]

Something like:

SELECT answer.uid,answer.client_id,question_id, CASE WHEN secunsec.val_float IS NULL THEN (SELECT CAST(MIN(secunsec.val_float) AS VARCHAR(5)) + CAST(MAX(secunsec.val_float) AS VARCHAR(5)) FROM secunsec) ELSE secunsec.val_float

-SQLBill

Posting advice: FAQ481-4875

 

[nikol]

Hi SQLBill,
Ur query is working but the output is not what I want.
I want if a client's question_id=1212(rent) is NULL then place
"Question_id=1213"+"question_id=1214" @that place.
UID Client_id Question_id Val_Float
10 67 1212 $345
11 78 1212 NULL $300+$690
12 45 1212 $567
13 68 1212 $670
14 56 1212 NULL $300+$800


I was using this simple query to get rent's for all clients.

SELECT answer.uid,answer.client_id,
case
when answer.question_id=1212 then secunsec.val_float
end as rent
FROM answer FULL OUTER JOIN secunsec ON answer.uid=secunsec.uid

 

[vongrunt]

This looks like fraggin IQ test ... (I feel stupid)

For starters, sample data looks wrong. Shouldn't UID 3 be actually 10? and CLIENT_ID 678 actually 67?

Also: there is no chance you could get posted output with full outer join on partial non-unique key and filter on question_id=1214.

I see overall logic though, but there is one problem: SQL known no record numbers, so you cannot relate records 1:1 from both tables the way they appear on screen. Is there any other column in SECUNSEC table that together with UID provides 1:1 relationship to ANSWER table?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)