select a predefined value - is this possible?

[junkjones]

What is the proper way to do this?

select column1, column2, "2003/3/11" as variable
from table

The 'variable' is a predefined value that i would like to include in the return of my query. It seems to work when I run it in access, but through my web server (coldfusion) it doesn't work. (Which is another weirdness in itself, because they should both be sending the same code to the same database!)

When I run it from Coldfusion I get "[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name '2003/3/11'."

TIA for any help!

 

[fluteplr]

Don't use double quotes in sql server, it thinks you are delimiting a field name with spaces in it. Use single quotes.

Also are you trying to create a character or date field?

The code you have now is going to create a character field with a date in it.

 

[junkjones]

thanks! that worked.... it is a date field. Is there a way I can use it now in the group by statement? It doesn't seem to like that either...

 

[junkjones]

Sorry - I should have posted the error I'm getting:

[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'variable'.

Thanks!

 

[swampBoogie]

What would be the purpose of using a constant in a group by clause?

It's no problem having constant expressions in the select list when using group by.

 

[junkjones]

The records currently contain date ranges. I want to pull out a record for each date. I've got it doing this, but I want to group by the dates (there are multiple records for each date that need to be SUM()ed. It says I cannot group by the date variable because it is an invalid column name.

 

[swampBoogie]

Could you post the SQL you are using?

 

[junkjones]

sure!

select MAX(o.order_ID) as order_ID, n.nom_ref as nom_ref, MIN(n.eff_date) as eff_date, MAX(n.end_date) as end_date, SUM(e.energy) as energy, SUM(e.energy) as total, '#DateFormat(CountDate, "YYYY-MM-DD&quot#' as dateis

from [ORDER_] as o, nomination as n, nom_estimate as e

where company_ID = 'BDWK'
and o.order_ID = n.dn_order_id
and n.nom_ref = e.nom_ref
and (n.eff_date <= '#DateFormat(CountDate, 'MM-DD-YYYY')#'
and n.end_date >= '#DateFormat(CountDate, 'MM-DD-YYYY')#')
group by dateis

This is being generated by Coldfusion, so the dates (##) are being generated dynamically and will be looping through a bunch of dates.

 

[moonshadow]

Firstly, I'd put this into a stored procedure then call it from ColdFusion passing the Date as a parameter.
Try just grouping on n.nom_ref, as the date is effectively a constant, there's no need to mention it in the group by clause.