WHERE Clause question - 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

WHERE Clause question

Author

Topic

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-13 : 13:56:28

Hello Everyone,

I have the following code:


USE CHEC
SELECT  
	[DATE_CONVERSION_TABLE_NEW].MONTH, 
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
	DAT01.[_@550] AS LoanType,
	DAT01.[_@051] AS Branch,
	DAT01.[_@TP] AS ProdTypeDescr,
	SMT_Branches.[BranchTranType] AS TranType,
	SMT_Branches.[AUCode] AS AuCode,
	Count(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006' 
	And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	SMT_Branches.[BranchTranType],
	SMT_Branches.[AUCode],
	[DATE_CONVERSION_TABLE_NEW].MONTH,
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH, DAT01.[_@051],
	DayofMonth ASC,
	SMT_Branches.[AUCode] ASC

What is the correct syntax to get the Branch in this line:

DAT01.[_@051] AS Branch,

In the Where clause to have it display the results for Branch 540?

TIA.

Kurt

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-13 : 14:02:50

You need to add either

AND DAT01.[_@051] = 540
or
AND DAT01.[_@051] = '540'
or
AND rtrim(DAT01.[_@051]) = '540'

to the WHERE clause, depending on the data type of the column.

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-13 : 14:11:29

Hello snSQL,

You violating Yak!!

It works!

Thanks!!

Kurt

Kristen
Test

22859 Posts

Posted - 2006-10-13 : 14:15:15

I reckon this isn't right


	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE	...
	AND SMT_BRANCHES.[BranchTranType] = 'RETAIL'

and you probably want something like this instead


	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchCode] = DAT01.[_@051]
	AND SMT_Branches.[BranchTranType] = 'RETAIL'

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources