Need CASE Statement(s) to populate 5 fields based on values of 2

[jbaranski]

I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 1 field in a table to determine the output for multiple fields in the flatfile.

I think the best way to convey what i am trying accomplish is by;

If TABLE.FIELD1 = XXX then
then OUTPUT1 = Y
OUTPUT2 = N
OUTPUT3 = TABLE.FIELD3
OUTPUT4 = 3
OUTPUT5 = ("WORKING CASE STATEMENT")
else OUTPUT1 = N
OUTPUT2 = null
OUTPUT3 = null
OUTPUT4 = null
OUTPUT5 = null
If TABLE.FIELD1 = RRR then
then OUTPUT6 = Y
OUTPUT7 = N
OUTPUT8 = TABLE.FIELD3
OUTPUT9 = 3
OUTPUT10 = ("WORKING CASE STATEMENT")
else OUTPUT6 = N
OUTPUT7 = null
OUTPUT8 = null
OUTPUT9 = null
OUTPUT10 = null

Sorry if this is confusing, but what would be the best means of accomplishing this? I know a single CASE can't produce output for more than one field.

Thanks in advance.

[ck9663]

I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 1 field in a table to determine the output for multiple fields in the flatfile.

I think the best way to convey what i am trying accomplish is by;

If TABLE.FIELD1 = XXX then
then OUTPUT1 = Y
OUTPUT2 = N
OUTPUT3 = TABLE.FIELD3
OUTPUT4 = 3
OUTPUT5 = ("WORKING CASE STATEMENT")
else OUTPUT1 = N
OUTPUT2 = null
OUTPUT3 = null
OUTPUT4 = null
OUTPUT5 = null
If TABLE.FIELD1 = RRR then
then OUTPUT6 = Y
OUTPUT7 = N
OUTPUT8 = TABLE.FIELD3
OUTPUT9 = 3
OUTPUT10 = ("WORKING CASE STATEMENT")
else OUTPUT6 = N
OUTPUT7 = null
OUTPUT8 = null
OUTPUT9 = null
OUTPUT10 = null

Sorry if this is confusing, but what would be the best means of accomplishing this? I know a single CASE can't produce output for more than one field.

Thanks in advance.

I think you're looking for UNION

try:

select 
   OUTPUT1 = Y,OUTPUT2 = N, OUTPUT3 = TABLE.FIELD3, OUTPUT4 = 3, OUTPUT5 = 'WORKING CASE STATEMENT', 
OUTPUT6 = 'N', OUTPUT7 = null, OUTPUT8 = null, OUTPUT9 = null, OUTPUT10 = null 
from YourTable where TABLE.FIELD1 = XXX 
union 
select OUTPUT1 = 'N', OUTPUT2 = null, OUTPUT3 = null, OUTPUT4 = null, OUTPUT5 = null, 
OUTPUT6 = Y,OUTPUT7 = N, OUTPUT8 = TABLE.FIELD3, OUTPUT9 = 3, OUTPUT10 = 'WORKING CASE STATEMENT'
from yourtable where  TABLE.FIELD1 = RRR 

-- CK

[jbaranski]

UNION is not what I'm looking for. the value of the one fieild literally dictates the value of 20 others in one line of the report. i can have up to 3 types of insurance. if i have XXX then it populates those output fields 1-5, if i dont then it prints N in the first and leave the remaining blank. if i have YYY or ZZZ then it poulates fields 6-10 and 11-15, respectively.

[deepuv04]

UNION is not what I'm looking for. the value of the one fieild literally dictates the value of 20 others in one line of the report. i can have up to 3 types of insurance. if i have XXX then it populates those output fields 1-5, if i dont then it prints N in the first and leave the remaining blank. if i have YYY or ZZZ then it poulates fields 6-10 and 11-15, respectively.

try the following query hope will help you...

[code = sql]

SELECT Case TABLE.FIELD1
when 'xxx' or 'rrr' then 'y'
else 'n' end as output1 ,
Case TABLE.FIELD1
when 'xxx' or 'rrr' then 'n'
else null end as output2 ,
Case TABLE.FIELD1
when 'xxx' or 'rrr' then TABLE.FIELD3
else null end as output3 ,
Case TABLE.FIELD1
when 'xxx' or 'rrr' then 3
else null end as output4 ,
Case TABLE.FIELD1
when 'xxx' or 'rrr' then 'WORKING CASE STATEMENT'
else null end as output5

FROM TABLE_NAME

[/code]

[jbaranski]

Fantastic; crude but it gets the job done. thanks a bunch guys.

[jbaranski]

Update:

Ok, so it works, to an extent. While it does print the output in the correct fields, it only does so 1 type per line. If they have all 3 types of coverage, then they will have 3 seperate lines. It will correctly populate one of the 3 only once, while filling in the others as though they dont have that coverage. however, the remaining lines will follow suit; correctly state they have 1 type of covereage while claiming NO on the remaining, but i need all to appear on one line per person. i know this is confusing. if you need more clarification let me know.

thanks