Concat Issue

[mjfour]

Hi All,
I have a lagacy database that I need to run a query on like the following. Can anyone tell me how I can achieve the following. Right now I am getting an error that PubYear is not a valid column. Thank you!

SELECT AdINFO_Publication,
ADINFO_PUBLICATIONDATE_YEAR+ '-' + ADINFO_PUBLICATIONDATE_MONTH + '-01' AS PubYear
FROM crAdInfo
AND PubYear BETWEEN #dateformat(startdate,"yyyy-mm-dd")# AND #dateformat(enddate,"yyyy-mm-dd")#

Any Help is appreciated

[Guest]

I don't believe you can query it like that.. (please correct me if I'm wrong).

But, you could query like that if you did the first select statement and then do a following query to query the first query. So the datasource of the second query would be "query" and you can use the where clause calling that column name.

[Dan_Bracuk]

Your query is crashing because you are using a column alias in the where clause. That can't be done, end of discussion.

Repeating the concatonation in your where clause will probably work. It's certainly worth a shot.

[cf_dev2]

You can't reference an alias in the where clause like that. The usual method is to use the actual table column names in the where clause. Though I would probably convert the value to a valid date or datetime object, assuming the legacy database supports them.

WHERE ADINFO_PUBLICATIONDATE_YEAR+ '-' + ADINFO_PUBLICATIONDATE_MONTH + '-01' ....etc.

Another option is to use a derived table, if your db supports them

SELECT t.AdINFO_Publication, t.PubYear
FROM
(
SELECT AdINFO_Publication,
ADINFO_PUBLICATIONDATE_YEAR+ '-' + ADINFO_PUBLICATIONDATE_MONTH + '-01' AS PubYear
FROM crAdInfo
) AS t
WHERE t.PubYear BETWEEN ....start date... AND ....other date...

Failing that, you could also try using a query of queries as amers suggested.

[cf_dev2]

> AND PubYear BETWEEN #dateformat(startdate,"yyyy-mm-dd")# AND ....

Even if the column did exist, you'd probably get a syntax error because string values need to be enclosed in single quotes.

[mjfour]

Thank You so much for all this info. I am going try out these suggestions. THANK YOU!