Link to home
Start Free TrialLog in
Avatar of programmher
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.
Avatar of nap0leon
nap0leon

If I'm reading this right, you are selecting everything into a temp table which you are then going to export to the flat-file.  You should be able to specify text to add into your output using something like the following.

SELECT 
  field1 & "|",
  field2 & "|",
  ...
  fieldn
FROM Temptbl

Open in new window

Avatar of programmher

ASKER

Nap0leon,

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

Open in new window

Same error message...
ASKER CERTIFIED SOLUTION
Avatar of nap0leon
nap0leon

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Guy Hengel [angelIII / a3]
don't use NULL, but '' for "empty" fields.
NULL will be concatenated to NULL with all other fields. ..
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||||||||