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
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
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
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
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy