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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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