Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2006
    Posts
    5

    Unanswered: Correlated subquery - Sql Server 2000 verses Sql server 2005

    Original code (works in Sql Server 2000 but has error in Sql Server 2005) --
    AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))

    Error message in Sql Server 2005 --
    Server: Msg 8180, Level 16, State 1, Line 38
    Statement(s) could not be prepared.
    Server: Msg 107, Level 16, State 1, Line 38
    The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.

    Corrected code --

    AND SUBSTRING(CONVERT(CHAR,A_ED.EFFDT,121), 1, 10) <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
    Last edited by oceanwoodcarver; 11-14-06 at 14:08. Reason: type-o

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you still have a type-o

    the error message mentions a table prefix 'Tbl1001' but the code you are showing doesn't have that

    perhaps you might consider showing the entire query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    5

    The query doesn't contain reference to Tbl1001

    "Tbl1001" is a generic field name used within the Microsoft error message. It's not an actual table field name within the query.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by oceanwoodcarver
    "Tbl1001" is a generic field name used within the Microsoft error message. It's not an actual table field name within the query.
    While I suppose that is possible, I really doubt it. I suspect there is a problem in some part of your query that is causing problems with the preparation of the statement.

    -PatP

  5. #5
    Join Date
    Nov 2006
    Posts
    5

    Smile Code works

    The original subquery included this clause, which works in Sql Server 2000 but has error in Sql Server 2005.
    AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))

    Error message in Sql Server 2005 --
    Server: Msg 8180, Level 16, State 1, Line 38
    Statement(s) could not be prepared.
    Server: Msg 107, Level 16, State 1, Line 38
    The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.

    The corrected clause (shown below) works in Sql Server 2005.

    AND SUBSTRING(CONVERT(CHAR,A_ED.EFFDT,121), 1, 10) <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))

    If you doubt what I'm saying, write your own queries and test it for yourself.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oceanwoodcarver
    If you doubt what I'm saying, write your own queries and test it for yourself.
    okay, i did

    and i got exactly the results i expected
    Code:
    create table effdates
    ( id integer not null primary key identity
    , effdate datetime
    )
    insert into effdates(effdate) values ('2006-09-09 01:23')
    insert into effdates(effdate) values ('2006-10-10 02:34')
    insert into effdates(effdate) values ('2006-11-11 03:45')
    insert into effdates(effdate) values ('2006-12-12 04:09:37')
    
    /* now the tough part, to create a subquery */
    
    select count(*) as c
      from effdates
     where effdate in 
           ( select effdate
               from effdates
              where effdate < SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)
           )
           
    c
    3
    no mention of 'Tbl1001'
    Last edited by r937; 11-14-06 at 15:58.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmm. I get:
    Code:
    Server: Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ')'.
    from this:
    Code:
    create table test1
    (col1 int,
    EFFDT datetime)
    
    
    select *
    from test1
    where EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
    There appears to be an extra trailing parenthesis. This was on SQL 2005, and I had no error after removing the trailing parenthesis.

  8. #8
    Join Date
    Nov 2006
    Posts
    5

    The clause is part of a subquery

    You don't execute a select against the subquery's clause.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mine worked
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2006
    Posts
    5
    -- r937 has excellent code. I modified a copy of his t-sql query to look more like mine. No errors.

    select a.effdate
    from effdates a
    where a.effdate =
    (SELECT MAX(A_ED.effdate) FROM effdates A_ED
    WHERE A_ED.effdate <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)
    )

    -- My original code had references to linked servers. Perhaps the error is related to it. Here is the Sql Server 2005 error.

    Msg 8180, Level 16, State 1, Line 1
    Statement(s) could not be prepared.
    Msg 107, Level 16, State 2, Line 1
    The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
    Msg 107, Level 16, State 2, Line 1
    The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.



    -- Afterwards, for posting, I changed the server name, database name and table name.

    SELECT A.DEPTID, A.DESCR, A.LOCATION
    FROM LS_Production_Server.Production_Database.dbo.Depar tment_Tbl A
    WHERE A.EFFDT =
    (SELECT MAX(A_ED.EFFDT) FROM LS_Production_Server.Production_Database.dbo.Depar tment_Tbl A_ED
    WHERE A.DEPTID = A_ED.DEPTID
    AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))

  11. #11
    Join Date
    Jul 2005
    Posts
    7
    Quote Originally Posted by oceanwoodcarver
    ...My original code had references to linked servers. Perhaps the error is related to it. Here is the Sql Server 2005 error. ...
    I think you are right there. I'm getting this same error when I use a corelated subquery with a link 2000 server.

    jamison

Posting Permissions

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