programmher
asked on
Displaying Blank Fields with Empty Pipes
I have an output to a flat file from the below query.
SELECT TITLE_UNIT AS BOOK_TYPE
,ITEM AS SKU
,'' AS Title_Date
,'' AS 'BOOK SKU'
,TITLE_DESC AS 'Description'
,'' AS 'Title Cost'
,NULL AS 'Title Costa'
,NULL AS 'Title Costb'
,'' AS 'Title Costc'
,'' AS 'Book Dept'
INTO Temptbl
FROM BookINVENTORY
WHERE Genre = 'Fiction
The delimiter is a pipe [|] I need the NULL values (or blank fields) to display with empty pipes
like this:
The Self Help Guru|7.28||||||||
I am using an SSIS package to output the file. How do I display the empty pipes? This is a customer requirement.
SELECT TITLE_UNIT AS BOOK_TYPE
,ITEM AS SKU
,'' AS Title_Date
,'' AS 'BOOK SKU'
,TITLE_DESC AS 'Description'
,'' AS 'Title Cost'
,NULL AS 'Title Costa'
,NULL AS 'Title Costb'
,'' AS 'Title Costc'
,'' AS 'Book Dept'
INTO Temptbl
FROM BookINVENTORY
WHERE Genre = 'Fiction
The delimiter is a pipe [|] I need the NULL values (or blank fields) to display with empty pipes
like this:
The Self Help Guru|7.28||||||||
I am using an SSIS package to output the file. How do I display the empty pipes? This is a customer requirement.
ASKER
Nap0leon,
I get this message when I try your suggestion:
Invalid column name '|'.
I get this message when I try your suggestion:
Invalid column name '|'.
Too much web-coding... I get my syntaxes mixed up. To get text added to your query result, it might just be comma delimited...
SELECT
field1,"|",
field2,"|",
...
fieldn
FROM Temptbl
ASKER
Same error message...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have set the delimiter via the ssis packge; but, if the field is blank, it does dispay an empty pipe as in my first post. The customer requires an empty pipe be dispplayed if the field is blank or null.
Ah sorry - I misunderstood your question.
In your example, it is returning only:
The Self Help Guru|7.28|
instead of the desired:
The Self Help Guru|7.28||||||||
?
This sounds like something that would be addressed inside your particular SSIS Tool and not at an SQL level.
In your example, it is returning only:
The Self Help Guru|7.28|
instead of the desired:
The Self Help Guru|7.28||||||||
?
This sounds like something that would be addressed inside your particular SSIS Tool and not at an SQL level.
don't use NULL, but '' for "empty" fields.
NULL will be concatenated to NULL with all other fields. ..
NULL will be concatenated to NULL with all other fields. ..
ASKER
Nap0lean,
You are correct, in fact, I was considering closing out this question (because I did get one of your suggestions to work) and re-wording my question so it is SSIS package specific.
Angelll,
I tried passing the fields as empty but my output was still The Self Help Guru|7.28| instead of The Self Help Guru|7.28||||||||
You are correct, in fact, I was considering closing out this question (because I did get one of your suggestions to work) and re-wording my question so it is SSIS package specific.
Angelll,
I tried passing the fields as empty but my output was still The Self Help Guru|7.28| instead of The Self Help Guru|7.28||||||||
Open in new window