Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Cannot check column in where clause

    In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits
    e.g. 30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int

    SELECT af.AccomType, af.AccomRef, af.AccomName,af.address1, af.address2, bf1.RoomCode,
    Convert(Int,Replace(Replace(Replace(Replace(REPLAC E(Replace(MAX(bf1.EndBook),'','200'),'','204'),' 99','1999'),'97','1997'),'47',1947),'98','1998')) AS max_date,
    ...............

    WHERE
    af.Resort=@strResort
    AND
    (af.AccomType = 'H' OR af.AccomType = 'O')

    AND
    max_date>20040721

    order by max_date.


    Problem is I get an error saying invalid column max_date. It works in the order by clause when I get rid of the
    'max_date>20040721 '.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...it would help if you posted the table DDL, and the full SQL Statement and some sample data...


    But you can't refernce a derived column name like that....


    Try SELECT * FROM (...your statement) AS XXX ORDER BY max_Date

    Should do it if the syntax is correct....

    I get a feeling that you'll still have errors though....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    yeah, that works, I am pretty sure the synax is right, and I can execute the statement without any errors, if I leave out the max_date>20040721 part.

    SELECT
    BFCPN1.EndBook AS max_date
    FROM BFCPN1 where
    max_date>20040721
    ORDER BY max_date

    gives the same error. My problem is that I need to reference the max_date column in the where clause, and because I am replacing substrings and converting it to an int I have to assign it a name, and sql server won't allow me reference it in the where clause. It will allow me to reference it in the order by part.


    The bits missing from the statement are just lists of other columns I need to select, and a group by clause. Left them out for clarity. The tables being called are a big list of varchars, which unfortunately can't be changed. (they are loaded each night from a unix flat file.

    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •