Incorrect Syntax near the keyword select , from

Question

Post reply

Incorrect Syntax near the keyword select , from

jsshivalik

SSChasing Mays

Points: 612
More actions
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

John Mitchell-245523

SSC Guru

Points: 148809

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

jsshivalik SSChasing Mays Points: 612 More actions · Answer 2

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

Sergiy SSC Guru Points: 110209 More actions · Answer 3

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

Incorrect Syntax near the keyword select , from

Cookies on SQLServerCentral