Post reply

Incorrect Syntax near the keyword select , from

  • December 19, 2019 at 9:07 am

    Go to Answer

    #3708982

    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

  • December 19, 2019 at 9:21 am

    #3708985

    I recommend that you lay out your code to make it readable.  You'll find it much easier to spot errors that way.  I think what you need is a ")" before the "-" and a "(" after it.

    John

  • December 19, 2019 at 9:32 am

    #3708988

    Hi

    Now it is giving Incorrect Syntax near keyword from

    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 = 'C001'

    Thanks

  • December 19, 2019 at 9:44 am

    Answer

    #3708990

    It's still almost impossible to read if you put it all on one or two lines.  Try something like this (the only thing I've changed is the layout):

    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 = 'C001'

    Now count the number of "("s and compare it to the number of ")"s.

    John

     

  • December 19, 2019 at 12:17 pm

    #3709023

    Brrr...

    Can't believe I see this kind of coding in the present time.

    SELECT
    T0.CardCode as CardCode,
    T0.CardName as CardName,
    ISNULL(T1.Balance,0) as Balance 
    FROM Ocrd T0
    LEFT JOIN (
    SELECT T10.ShortName, 
    Sum(CASE WHEN T11.TransId IS NULL THEN ISNULL(T10.Credit, 0) ELSE 0 end ) Balance 
    FROM Jdt1 T10
    LEFT JOIN Orct T11 ON T10.TransId = T11.TransId and T11.Canceled = 'N'
    GROUP BY T10.ShortName
    ) T1 ON T1.ShortName = T0.CardCode
    --where T0.CardCode = 'C001'
    -- This, more efficient, query may be used for retrieving balances for multiple cards at once

    _____________
    Code for TallyGenerator

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply