none
Using WHERE clause with CROSS APPLY get error RRS feed

  • Question

  • Dear All,

    I have a select statement below that return a result set without any error: 

    SELECT * FROM

    SVY.SQL_SSV_QA_OUTLET_DATA AS F
    CROSS APPLY REF.SplitX(F.RE_TEXT, ',')

    The error come when I try to add the where clause before the CROSS APPLY

    SELECT * FROM
    SVY.SQL_SSV_QA_OUTLET_DATA AS F
    WHERE F.START_DATE BETWEEN  '2014-03-01' AND '2014-03-30'
    CROSS APPLY REF.SplitX(F.RE_TEXT, ',')

    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'CROSS'.

    Am I doing anything wrong here?

    SAM

    Friday, May 16, 2014 7:03 AM

Answers

  • TRY:
    SELECT * FROM
     SVY.SQL_SSV_QA_OUTLET_DATA AS F
     CROSS APPLY REF.SplitX(F.RE_TEXT, ',')
      WHERE F.START_DATE BETWEEN  '2014-03-01' AND '2014-03-30'

    Think of CROSS APPLY as "JOIN".


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Friday, May 16, 2014 7:04 AM
    Moderator
  • Am I doing anything wrong here?

    Obviously, since you get an error message. :-)

    The SELECT statement has the following main structure, as taken from Books Online:

    [ WITH <common_table_expression>]
    SELECT select_list
    [ INTO new_table ]
    [ FROM table_source ]
    [ WHERE search_condition ]
    [ GROUP BY group_by_expression ]
    [ HAVING search_condition ]
    [ ORDER BY order_expression [ ASC | DESC ] ]

    The CROSS APPLY operator is part of the FROM clause which can include JOIN and APPLY operators. APPLY is like a join operator, but permits the right side to be correlated with the left side.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 16, 2014 7:22 AM
  • CROSS APPLY is just like a join with only difference being it works on basis of correlated subquery. So WHERE condition should come after the CROSS APPLY.

    Also if your date field stores time part also the condition should be written as below

    SELECT * FROM
    SVY.SQL_SSV_QA_OUTLET_DATA AS F
    CROSS APPLY REF.SplitX(F.RE_TEXT, ',')
    WHERE F.START_DATE >= '2014-03-01' 
    AND F.START_DATE < '2014-03-31'

    see

    http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, May 16, 2014 7:55 AM
  • Thank you for your support. Your solution works!

    Hi SSamnang,

    Please refer to the following logical uuery processing phases in brief:

    Reference from: Inside Microsoft SQL Server 2008: T-SQL Querying (Developer Reference)

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, May 19, 2014 8:30 AM
    Moderator

All replies

  • TRY:
    SELECT * FROM
     SVY.SQL_SSV_QA_OUTLET_DATA AS F
     CROSS APPLY REF.SplitX(F.RE_TEXT, ',')
      WHERE F.START_DATE BETWEEN  '2014-03-01' AND '2014-03-30'

    Think of CROSS APPLY as "JOIN".


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Friday, May 16, 2014 7:04 AM
    Moderator
  • Am I doing anything wrong here?

    Obviously, since you get an error message. :-)

    The SELECT statement has the following main structure, as taken from Books Online:

    [ WITH <common_table_expression>]
    SELECT select_list
    [ INTO new_table ]
    [ FROM table_source ]
    [ WHERE search_condition ]
    [ GROUP BY group_by_expression ]
    [ HAVING search_condition ]
    [ ORDER BY order_expression [ ASC | DESC ] ]

    The CROSS APPLY operator is part of the FROM clause which can include JOIN and APPLY operators. APPLY is like a join operator, but permits the right side to be correlated with the left side.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 16, 2014 7:22 AM
  • CROSS APPLY is just like a join with only difference being it works on basis of correlated subquery. So WHERE condition should come after the CROSS APPLY.

    Also if your date field stores time part also the condition should be written as below

    SELECT * FROM
    SVY.SQL_SSV_QA_OUTLET_DATA AS F
    CROSS APPLY REF.SplitX(F.RE_TEXT, ',')
    WHERE F.START_DATE >= '2014-03-01' 
    AND F.START_DATE < '2014-03-31'

    see

    http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, May 16, 2014 7:55 AM
  • Thank you for your support. Your solution works!

    Friday, May 16, 2014 10:40 AM
  • Thank you for your support. Your solution works!

    Hi SSamnang,

    Please refer to the following logical uuery processing phases in brief:

    Reference from: Inside Microsoft SQL Server 2008: T-SQL Querying (Developer Reference)

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, May 19, 2014 8:30 AM
    Moderator
  • Thank you for your support. Your solution works!

    Can you mark relevant post as answer rather than marking your own post?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, May 19, 2014 9:36 AM