Selecting fields without null values

[Jalr2003]

I have a table like this

primary_key param1 param2 param3 param4 param5 param6 param7
1 10 3 5 3 7
2 3 4 12 4 4 9 8
3 4 6 6 8 6
4 4 7 7 9 9
5 1 1 1 1 1 1 1
6 1 1 4 6 9 8
7 3 5 4 3 7 2
8 5 5 8 2 2 1

I need to come up with a way to retrieve one tuple at the time containing only the fields that are not null by querying against the primary key.

The table I am querying has more that 50 columns, and not all records need to have
values in all fields.

If I write

Select * from Table where primary_key=1, I get:

primary_key param1 param2 param3 param4 param5 param6 param7
1 10 3 5 3 7

I need to get something like this

primary_key param1 param2 param4 param5 param7
1 10 3 5 3 7

Is it posible to write a sql statement to do that?

Thanks,

Jalr2003

 

[fluteplr]

No.

I would suggest that your table design is incorrect.

If it were designed as.

primarykey (new key)
key (old primary key)
paramnum
value

Then the query becomes trivial

select
key,
paramnum
value
from mytable where value is not null

 

[MarcLodge]

Would it be possible to use the CASE statement?

 

[swampBoogie]

No a case statement will not help.

You could use a case expression to get a kludge solution

select primarykey, case when param1 is null then ''
else 'Param1:' || cast(param1) as varchar(10)) end ||
case when param2 is null then ''
else 'Param2:' || cast(param2) as varchar(10)) end
....
from t

I agree with fluteplr about the redesign.

 

[fluteplr]

Boy, you tell some one you think they should redesign a table and they just never talk to you again.

 

[Jalr2003]

I apoligize for not getting back to you earlier.

Redesigning the database is not an option, for I am not the DBA, and I am not sure I agree it should be redesinged.

I did not explain the reasons for having tables like this. Basically, we have a table where an aplication can grab test parameters from. Depending on the test type, which is identified by the primery key, certain parameters are chosen. If a certain test plan is modified, a new primary key is assigned, and the old one is stored in a different table.

What I intended to do was to detect new entries, and show the respective parameters. I did not want to have results with 40 null fields.

I tried the case expression and it worked like a charm!

Thanks for your help,

Jalr2003

 

[fluteplr]

Glad it worked. Its still a bad design, but whatever works for you.