October 7, 2015 at 1:13 pm
I am trying to produce a query result that will be using a Case statement to determine values based on scores in a table for each row. The result needs to be exported to be used to upload to a state reporting website. My problem is that the state requires in the CSV file that is uploaded a lot of fields that we do not actually have in the database table we are creating the result set from. After I receive my result set using the Case statement, is there a way to add additional columns that don't actually exist in a table so I can export directly from SQL?
October 7, 2015 at 1:31 pm
SELECT col1, col2, col3, CAST(NULL as BIT) as newCol4, CAST(NULL as VARCHAR(6)) as newCol5 FROM yourTable
in this example, newCol4 and newCol5 are your non-existent columns. Using the CAST, ensures that they are the correct datatype, but you could also use either of these methods:
SELECT col1, col2, col3, NULL as newCol4, '' as newCol5 FROM yourTable
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 7, 2015 at 1:33 pm
Jason Selburg (10/7/2015)
SELECT col1, col2, col3, CAST(NULL as BIT) as newCol4, CAST(NULL as VARCHAR(6)) as newCol5 FROM yourTablein this example, newCol4 and newCol5 are your non-existent columns. Using the CAST, ensures that they are the correct datatype, but you could also use either of these methods:
SELECT col1, col2, col3, NULL as newCol4, '' as newCol5 FROM yourTable
I'm not exactly sure how the CSV file will honour CASTed columns.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 7, 2015 at 1:38 pm
Phil Parkin (10/7/2015)
Jason Selburg (10/7/2015)
SELECT col1, col2, col3, CAST(NULL as BIT) as newCol4, CAST(NULL as VARCHAR(6)) as newCol5 FROM yourTablein this example, newCol4 and newCol5 are your non-existent columns. Using the CAST, ensures that they are the correct datatype, but you could also use either of these methods:
SELECT col1, col2, col3, NULL as newCol4, '' as newCol5 FROM yourTable
I'm not exactly sure how the CSV file will honour CASTed columns.
We can't be bothered with those pesky details.... 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 7, 2015 at 1:38 pm
Jason Selburg (10/7/2015)
SELECT col1, col2, col3, CAST(NULL as BIT) as newCol4, CAST(NULL as VARCHAR(6)) as newCol5 FROM yourTablein this example, newCol4 and newCol5 are your non-existent columns. Using the CAST, ensures that they are the correct datatype, but you could also use either of these methods:
SELECT col1, col2, col3, NULL as newCol4, '' as newCol5 FROM yourTable
I don't think I need to cast them because the data type itself doesn't need to be set, the column just has to be present in the CSV file or else the upload fails. Thank you for your help. I will give that a try.
October 7, 2015 at 2:21 pm
What about specifying a specific value for a column in all rows?
October 7, 2015 at 3:07 pm
bsmith 63193 (10/7/2015)
What about specifying a specific value for a column in all rows?
SELECT col1, col2, col3, 0 as newCol4, 'ABC' as newCol5 FROM yourTable
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply