Number - 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

Number

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-06-14 : 08:32:43

Hi,

Thank you in advance for your help
I am trying to find duplicates for a recordset which follows some conditions.
The code below generates the error:
Incorrect syntax near the keyword 'where'.

select a.JobKey,a.BUKey,a.ClientKey,a.ICTypeKey,b.ICTypeID,a.Periodkey,a.LedgerKey,COUNT(*)
from DB_AOT a inner join D_JobAttributes b
on a.JobKey = b.JobKey
group by a.JobKey,a.BUKey,a.ClientKey,a.ICTypeKey,b.ICTypeID,a.Periodkey,a.LedgerKey
having COUNT(*) >1
where a.ICTypeKey <> b.ICTypeID
and LEID = '01009'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-06-14 : 08:46:09

select a.JobKey,a.BUKey,a.ClientKey,a.ICTypeKey,b.ICTypeID,a.Periodkey,a.LedgerKey,COUNT(*)
from DB_AOT a inner join D_JobAttributes b
on a.JobKey = b.JobKey
where a.ICTypeKey <> b.ICTypeID
and LEID = '01009'
group by a.JobKey,a.BUKey,a.ClientKey,a.ICTypeKey,b.ICTypeID,a.Periodkey,a.LedgerKey
having COUNT(*) >1

Madhivanan

Failing to plan is Planning to fail

tariq2
Posting Yak Master

125 Posts

Posted - 2011-06-14 : 10:55:45

Thank you very much Madhivanan

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-14 : 17:34:36

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

What you did post is a total mess. We do not use "_key" in a data element name; that is HOW it is used in one table and not WHAT it is by its nature. There is no such thing as a "type_id"; a data element can be a "<something>_type" or a "<something>_id" but not this impossible hybrid.

Please read a book on basic data modeling. I will try to get you up to ISO Standards and common sense when you post DDL. If you did not know, posting DDL is basic Netiquette; then you post DML.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources