how to replace having - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to replace having

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-12 : 05:20:51

Dear all,
here is one query,

IF EXISTS (SELECT SUM(DEBIT_AMOUNT) FROM @Ledger
HAVING SUM(DEBIT_AMOUNT)<>SUM(CREDIT_AMOUNT))

i'm trying to tuning myprocedures. i think having is not good enough to use.

how can we replace having in this case.....

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 05:58:55

You cant compare aggreate values without using having clause
Can you give us more info on what you are trying to do?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 06:00:50

How do the table layout look like?
How do the data look like?

What is the purpose of the check? May some other approach will be more effecient?

E 12°55'05.25"
N 56°04'39.16"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-12 : 06:25:38

the concept is like this

IF EXISTS (SELECT * FROM @Acc_Ledger)
SELECT TOP 1 @strMsg = al.DOCUMENT_NO + ' Date: ' + CONVERT(CHAR(15),al.DOCUMENT_DATE,107),
@strInvoiceType=CASE al.TRANS_TYPE WHEN 'PIJV' THEN 'P' WHEN 'SI-JV' THEN 'S' ELSE '' END,
@strTopOrgId = (SELECT TOP 1 o.Top_Parent FROM vOrg o WHERE o.Org_Id=al.Trans_Org_Id),
@strTransType=Trans_Type
FROM @Acc_Ledger al

UPDATE Table28 SET Col13 = @strMsg WHERE Col2 = @strTransId

-- Checks Total Debit and Credit are tallied
IF EXISTS (SELECT SUM(DEBIT_AMOUNT) FROM @Acc_Ledger
HAVING SUM(DEBIT_AMOUNT)<>SUM(CREDIT_AMOUNT))
BEGIN
RAISERROR ('Total Debit and Credit are not tallied', 18, 1)
RETURN 0
END

Vinod
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 06:35:09

You have to scan entire table anyway because of the SUM thingies

SELECT TOP 1	@strMsg = al.DOCUMENT_NO + ' Date: ' + CONVERT(CHAR(15), al.DOCUMENT_DATE, 107),
		@strInvoiceType = CASE al.TRANS_TYPE WHEN 'PIJV' THEN 'P' WHEN 'SI-JV' THEN 'S' ELSE '' END,
		@strTopOrgId = o.Top_Parent,
		@strTransType = al.Trans_Type
FROM		@Acc_Ledger as al
LEFT JOIN	vOrg as o ON o.Org_Id = al.Trans_Org_Id

UPDATE	Table28
SET	Col13 = @strMsg
WHERE	Col2 = @strTransId 

IF (SELECT SUM(DEBIT_AMOUNT) FROM @Acc_Ledger) <> (SELECT SUM(CREDIT_AMOUNT) FROM @Acc_Ledger)
	BEGIN
		RAISERROR ('Total Debit and Credit are not tallied', 16, 1)
		RETURN (0)
	END

E 12°55'05.25"
N 56°04'39.16"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-12 : 07:04:54

thank a lot peso...
it is working fine

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-12 : 07:53:15

Also try


IF NOT EXISTS
(
	SELECT * FROM 
	(
		SELECT SUM(DEBIT_AMOUNT) as  DEBIT_AMOUNT, SUM(CREDIT_AMOUNT) as CREDIT_AMOUNT FROM @Acc_Ledger
	) AS T
	WHERE DEBIT_AMOUNT=CREDIT_AMOUNT 
)
        BEGIN
		RAISERROR ('Total Debit and Credit are not tallied', 16, 1)
		RETURN (0)
	END

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources