5 Replies Latest reply on Oct 12, 2007 5:32 PM by mjfour

    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
        • 1. Re: Concat Issue
          amers
          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.
          • 2. Re: Concat Issue
            Dan Bracuk Level 5
            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.

            • 3. Re: Concat Issue
              cf_dev2 Level 1
              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.
              • 4. Re: Concat Issue
                cf_dev2 Level 1
                > 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.
                • 5. Re: Concat Issue
                  mjfour Level 1
                  Thank You so much for all this info. I am going try out these suggestions. THANK YOU!