How can I create a blank/hardcoded column in a sql query?
Solution 1
SELECT
hat,
shoe,
boat,
0 as placeholder
FROM
objects
And '' as placeholder
for strings.
Solution 2
This should work on most databases. You can also select a blank string as your extra column like so:
Select
Hat, Show, Boat, '' as SomeValue
From
Objects
Solution 3
For varchars, you may need to do something like this:
select convert(varchar(25), NULL) as abc_column into xyz_table
If you try
select '' as abc_column into xyz_table
you may get errors related to truncation, or an issue with null values, once you populate.
Solution 4
The answers above are correct, and what I'd consider the "best" answers. But just to be as complete as possible, you can also do this directly in CF using queryAddColumn.
See http://www.cfquickdocs.com/cf9/#queryaddcolumn
Again, it's more efficient to do it at the database level... but it's good to be aware of as many alternatives as possible (IMO, of course) :)
tylercomp
Updated on January 26, 2020Comments
-
tylercomp over 4 years
I want have a query with a column that is a hardcoded value not from a table, can this be done? I need it basically as a placeholder that I am going to come back to later and fill in.
example:
SELECT hat, shoe, boat, somevalue = 0 as placeholder FROM objects
then I would loop through this query later and fill in the placeholder
in this example someValue is not a field in objects, I need to fake it. I am doing this in coldfusion and using two datasources to complete one query. I have tried the space() function but have been unable to get it to work.
Thanks.
-
Leigh about 9 yearsYes, when using
select...into
to create a new table it is important to be more explicit about data type and capacity. Otherwise, the db will use the empty string to "guess" about the target column's properties. In the case of an empty string - it will almost certainly come up with the wrong answer. -
Leigh about 8 yearsThis is a repeat of the existing answers.
-
Jobs over 4 yearsHow about for type timestamptz?