CODEWITHSUNDEEP
sqlcoldfusion
tylercomp
tylercomp

Reputation: 901

How can I create a blank/hardcoded column in a sql query?

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.

Upvotes: 42

Views: 229577

Answers (7)

GStav
GStav

Reputation: 1166

If you do not want to initiate your column with zeros or empty strings but with NULL, the correct way to do this in PostgreSQL would be to cast the Null values to whatever type you want your new column to have:

SELECT
       hat
       , shoe
       , boat
       , null::int as myint
       , null::float as myfloat
       , null::bool as mybool
       , null::varchar as myvarchar
FROM 
   object

Upvotes: 0

mapping dom
mapping dom

Reputation: 1955

Thank you, in PostgreSQL this works for boolean 

SELECT
hat,
shoe,
boat,
false as placeholder
FROM
objects

Upvotes: 0

Shiraj Momin
Shiraj Momin

Reputation: 695

SELECT
    hat,
    shoe,
    boat,
    0 as placeholder -- for column having 0 value    
FROM
    objects


--OR '' as Placeholder -- for blank column    
--OR NULL as Placeholder -- for column having null value

Upvotes: 0

Benjamin Sternlieb
Benjamin Sternlieb

Reputation: 41

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.

Upvotes: 4

Galz
Galz

Reputation: 6842

SELECT
    hat,
    shoe,
    boat,
    0 as placeholder
FROM
    objects

And '' as placeholder for strings.

Upvotes: 102

charliegriefer
charliegriefer

Reputation: 3382

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) :)

Upvotes: 2

g.d.d.c
g.d.d.c

Reputation: 48028

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

Upvotes: 10

Related Questions