locked
Incorrect Syntax near the keyword select , Incorrect Syntax near the keyword from, RRS feed

  • Question

  • User-797751191 posted

    Hi

      Below is the query


    SELECT T0.CardCode as CardCode,T0.CardName as CardName,(Select (Isnull(Sum(Credit),0)) from Jdt1 where ShortName = T0.CardCode - Select(isnull(Sum(T10.Credit),0) from [dbo].[JDT1] T10 inner Join Orct T11 on T10.TransId = T11.TransId and T11.Canceled = 'N' and T10.ShortName = T0.CardCode) as Balance
    FROM Ocrd T0 where T0.CardCode = 'C01'

    Thanks

    Thursday, December 19, 2019 8:26 AM

All replies

  • User475983607 posted

    The code is incomplete.   

    When asking SQL questions always share the table schema, data, and complete query.  Explain or share the expected results.    Lastly format your code before posting.

    Thursday, December 19, 2019 11:26 AM
  • User753101303 posted

    Hi,

    More likely a non balanced closing ). Count them or edit your statement on multiple lines to better see the structure and find the culprit.

    Make it work first but it seems it could be rewritten with joins and groups. I tend to avoid subqueries in the SELECT clause when possible as it is IMO often less clear.

    Thursday, December 19, 2019 11:53 AM
  • User452040443 posted

    Hi,

    I didn't understand your query, I don't know if you will get the expected result, but try this:

    SELECT 
        T0.CardCode as CardCode,
        T0.CardName as CardName,
        (Select Isnull(Sum(Credit), 0)
         from Jdt1 
         where ShortName = T0.CardCode) - 
    
        (Select isnull(Sum(T10.Credit), 0)
         from [dbo].[JDT1] T10 
         inner Join Orct T11 on T10.TransId = T11.TransId 
    where T11.Canceled = 'N' and T10.ShortName = T0.CardCode) as Balance FROM Ocrd T0 where T0.CardCode = 'C01'

    Hope this help

    Thursday, December 19, 2019 1:04 PM
  • User288213138 posted

    Hi jsshivalik,

    SELECT T0.CardCode as CardCode,T0.CardName as CardName,(Select (Isnull(Sum(Credit),0)) from Jdt1 where ShortName = T0.CardCode - Select(isnull(Sum(T10.Credit),0) from [dbo].[JDT1] T10 inner Join Orct T11 on T10.TransId = T11.TransId and T11.Canceled = 'N' and T10.ShortName = T0.CardCode) as Balance
    FROM Ocrd T0 where T0.CardCode = 'C01'

    If you try the imapsp's code, then there is no Incorrect Syntax in compilation. 

    But If you still have questions, please post more information about your requirement. 

    Best regards,

    Sam

    Thursday, December 26, 2019 3:30 AM