April 6, 2012 at 5:36 am
Hi,
I am getting this error message when running the query below:
Msg 4145, Level 15, State 1, Line 39
An expression of non-boolean type specified in a context where a condition is expected, near ')'
SELECT *
FROM [PROV].[dbo].[DA]
where (
SELECT [SLA]
,[PCCode]
,[PrCode]
,[LocalID]
,[DateOfBirth]
,[Number]
,[ATariff]
,[DSeen]
,[MPI]
,[Service]
,[POD]
,[Device]
,[ActivityDate]
,[SeenDate]
,[DateActivity]
,count ([Key])
,[Period]
FROM [PROV].[dbo].[DA]
group by
[SLA]
,[PCCode]
,[PrCode]
,[LocalID]
,[DateOfBirth]
,[Number]
,[ATariff]
,[DSeen]
,[MPI]
,[Service]
,[POD]
,[Device]
,[ActivityDate]
,[SeenDate]
,[DateActivity]
,[Period]
HAVING COUNT () > 1)
what am i doing wrong?
April 6, 2012 at 5:50 am
Try this:
select
[SLA]
,[PCCode]
,[PrCode]
,[LocalID]
,[DateOfBirth]
,[Number]
,[ATariff]
,[DSeen]
,[MPI]
,[Service]
,[POD]
,[Device]
,[ActivityDate]
,[SeenDate]
,[DateActivity]
,[Period]
, Count(*) [Count]
FROM [PROV].[dbo].[DA]
group by
[SLA]
,[PCCode]
,[PrCode]
,[LocalID]
,[DateOfBirth]
,[Number]
,[ATariff]
,[DSeen]
,[MPI]
,[Service]
,[POD]
,[Device]
,[ActivityDate]
,[SeenDate]
,[DateActivity]
,[Period]
having count(*) > 1
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 6, 2012 at 5:54 am
If you want to see the duplicate records in the table, you don't need that subselect in the where clause.
SELECT
count ([Key])
,[SLA]
,[PCCode]
,[PrCode]
,[LocalID]
,[DateOfBirth]
,[Number]
,[ATariff]
,[DSeen]
,[MPI]
,[Service]
,[POD]
,[Device]
,[ActivityDate]
,[SeenDate]
,[DateActivity]
,[Period]
FROM [PROV].[dbo].[DA]
GROUP BY
[SLA]
,[PCCode]
,[PrCode]
,[LocalID]
,[DateOfBirth]
,[Number]
,[ATariff]
,[DSeen]
,[MPI]
,[Service]
,[POD]
,[Device]
,[ActivityDate]
,[SeenDate]
,[DateActivity]
,[Period]
HAVING COUNT () > 1
Edit: Phil wins!
April 6, 2012 at 5:56 am
thanks - but how do i display both duplicate records in the same query?
April 6, 2012 at 6:07 am
bharatgi (4/6/2012)
thanks - but how do i display both duplicate records in the same query?
Do you have a unique key on this table?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 6, 2012 at 6:11 am
the key field is a concatenate of LocalID + DSeen + POD
April 6, 2012 at 6:15 am
bharatgi (4/6/2012)
the key field is a concatenate of LocalID + DSeen + POD
Hmm, clearly not, as you have duplicates 🙂
I'm therefore guessing that this table does not have a PK, would that be correct?
In which case, why would you want to see all duplicates, as they are exactly the same?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 6, 2012 at 6:50 am
Phil Parkin (4/6/2012)
bharatgi (4/6/2012)
the key field is a concatenate of LocalID + DSeen + PODHmm, clearly not, as you have duplicates 🙂
I'm therefore guessing that this table does not have a PK, would that be correct?
In which case, why would you want to see all duplicates, as they are exactly the same?
correct, the table does not have a PK.
the duplicates will have different values in the ATariff field, hence wanting to see both lines
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply