I am writing a SELECT script in Pervasive 10.3 I need the logic to have a SELECT within an IF or CASE tried the statements below but that does not seem to work. Once I do any of the two below my SELECT select fewer rows.
CASE WHEN a.Item = 1 THEN ( SELECT ISNULL(SUM(o.Amount),0) FROM Debtors AS o WHERE RTRIM(LTRIM(o.CScode)) = RTRIM(LTRIM(a.CustomerCode)))
WHEN a.Item = 0 THEN (a.DBbalance) END
or like
IF ( a.Item = 0, (a.DBbalance) , ( SELECT ISNULL(SUM(o.Amount),0) FROM Debtors AS o WHERE RTRIM(LTRIM(o.CScode)) = RTRIM(LTRIM(a.CustomerCode))) )
Please help !!!
CASE WHEN a.Item = 1 THEN ( SELECT ISNULL(SUM(o.Amount),0) FROM Debtors AS o WHERE RTRIM(LTRIM(o.CScode)) = RTRIM(LTRIM(a.CustomerCode)))
WHEN a.Item = 0 THEN (a.DBbalance) END
or like
IF ( a.Item = 0, (a.DBbalance) , ( SELECT ISNULL(SUM(o.Amount),0) FROM Debtors AS o WHERE RTRIM(LTRIM(o.CScode)) = RTRIM(LTRIM(a.CustomerCode))) )
Please help !!!