proper UNION form

• Christopher Klein

  SSCrazy

  Points: 2648

  More actions

  October 31, 2007 at 8:50 am

  #183537

  Hi there,

  I have a simple little select statement:

  SELECT PrimeType1 ,

  SystemTradeDate ,

  SecurityId ,

  AccountCode ,

  SecurityLongDescription ,

  CONVERT (VARCHAR(10),EffectiveMaturity,101 )as EffectiveDate,

  PrimeDuration

  FROM @tableD

  That I have a process that runs and spits the results out to a CSV file to be opened up in excel. The problem is I have no column headers.

  for the life of me I cannot get a UNION statement to work:

  SELECT 'Prime_Type_1','System_Trade_Date','Security_ID','Account_Code','Security_Long_Description','Effective_Date','Prime_Duration'

  I get an error if I do a UNION/UNION ALL that it cannot convert a datetime to a character.

  I've done this with views where I've gotten the column headers of the view and joined them in but I can't get it to work for this 'simpler' item.

  Thanks,

  Chris

• Christopher Klein

  SSCrazy

  Points: 2648

  More actions

  October 31, 2007 at 9:29 am

  #747141

  well I can't delete this so I might as well post my solution.

  I had to cast some of the columns to varchar, not sure why it was coming back with a conversion error but...

  SELECT col1,col2,col3,col4,col5,col6,col7

  FROM (

  SELECT 0 as SortCol,

  'Prime_Type_1' AS col1,

  'System_Trade_Date' AS col2 ,

  'Security_ID' AS col3,

  'Account_Code' AS col4,

  'Security_Long_Description' AS col5,

  'Effective_Date' AS col6,

  'Prime_Duration' AS col7

  UNION

  SELECT 1,PrimeType1 ,

  CAST(SystemTradeDate AS VARCHAR(11)) ,

  SecurityId,

  AccountCode,

  SecurityLongDescription ,

  CONVERT (VARCHAR(10),EffectiveMaturity,101 ),

  CAST (PrimeDuration AS VARCHAR)

  FROM @tableD

  )ppp

  ORDER BY Sortcol,col1,col2,col3