September 15, 2009 at 9:42 am
I am trying to write to a temp table, which will then be BCP'd to a flat file, I have a potential of ten columns of numbers but want to populate each of them only if data exists, and each in order if values exist. So, say for input column 1, if a value exists, it goes into output column 1, however if input column 1 is blank (or null) but input column 2 has a value, that then goes into output column 1, and so forth for each input value. Essentially, left-justifying the data based on value, in a fixed width format, but leaving all subsequent values blank, as necessary. I've tried a CASE clause, but that fills each field based on value and will attempt to populate each field, not simply "to the left":
+ '21' -- OON Deductible
+ SPACE(24) +
CASE when NET_DEDUCTIBLE <> 0 AND NETSTATUS='0'
then dbo.udf_convIBMZoned(NET_DEDUCTIBLE, 12)
ELSE
'00000000000{'
END
+ SPACE(13)
I've tried to add an "If" clause to the stored procedure but it complains about syntax:
If NET_DEDUCTIBLE <> 0 AND NETSTATUS='I'
begin
+ '21' -- OON Deductible
+ SPACE(24)
+ dbo.udf_convIBMZoned(NET_DEDUCTIBLE, 12)
+ SPACE(13)
END
I cannot see the forest for the trees.....Anyone have any insight? I can provide additional detail as necessary.
-- You can't be late until you show up.
September 15, 2009 at 10:12 am
I'm confused by your code snippets, but I think I understand your text.
If you want the result set to have multiple columns that you will subsequently concatenate, you could use nested CASE expressions, e.g. CASE Col1 WHEN '' THEN CASE Col2 WHEN '' THEN Col3 ELSE Col2 ELSE Col1 END. You'll need to get all the expressions cast to the same data type, of course.
If you want the result set as the one concatenated column, then just replace values you don't want with an empty string (''), e.g.,
SELECT CASE WHEN Col1 = 0 OR Col1 IS NULL THEN '' ELSE REPLACE(STR(Col1,12),' ','0') +
CASE WHEN Col2 = 0 OR Col2 IS NULL THEN '' ELSE REPLACE(STR(Col2,12),' ','0') + ...
FROM YourTable
--Jonathan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply