locked
Incorrect Syntax near the Keyword from RRS feed

  • Question

  • User-1499457942 posted

    Hi

      I have below code on which i am getting above error

    select A.No ,  A.DocumentNo ,
    case when (Select [Date] from [Test]
    where [No] = A.DocumentNo) IS Null Then (Select [Date] from [Test1] where [No] = A.DocumentNo)
    else (Select [Date] from [Test] where [No] = A.DocumentNo) End as Date , from tblTmp as A LEFT JOIN [Test] AS B ON A.DocumentNo = B.[DocumentNo_] LEFT JOIN [Test1] AS C ON A.DocumentNo=C.[DocumentNo_]

    Thanks

    Friday, December 7, 2018 9:36 AM

All replies

  • User452040443 posted

    Hi,

    There is an extra comma before the From. Try removing it.

    You can also try as follows:

    coalesce
        ( (Select [Date] from [Test] where [No] = A.DocumentNo),
          (Select [Date] from [Test1] where [No] = A.DocumentNo) ) as [Date]

    Hope this help

    Friday, December 7, 2018 11:13 AM
  • User77042963 posted

    When you have a query question, please provide table structure and sample data and your expected result from your sample.  Your current query looks pretty ugly.

    Post create table, inserts and your expected result, you will have a better opportunity to get a working query. Thanks.

    Friday, December 7, 2018 2:49 PM
  • User753101303 posted

    Hi,

    you have an extra and you jhave already a join with Test1 and Test so I believe there is no need to do subqueries again and that you could just use something such as :

    select A.No ,  A.DocumentNo ,
    COALESCE(B.Date,C.DATE) AS DATE
    from tblTmp as A 
    LEFT JOIN [Test] AS B ON A.DocumentNo = B.[DocumentNo_]
    LEFT JOIN [Test1] AS C ON A.DocumentNo=C.[DocumentNo_]

    (and it"s unclear if Test tables are supposed to be linked on DocumentNo or No or if you really have two columns to link to your main table ??????)

    Friday, December 7, 2018 3:00 PM
  • User-1716253493 posted

    AFAIK, to check data exist or not is using EXISTS method

    CASE WHEN NOT EXISTS(Select [Date] from [Test] WHERE [No] = A.DocumentNo) THEN

    Friday, December 7, 2018 5:02 PM