Query error - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query error

Author

Topic

Andhra
Starting Member

2 Posts

Posted - 2008-06-25 : 02:31:57

i am beginer for writing query in sqlserver 2005 i am getting fallowing error while executing the below query please help me

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

(SELECT tt.MachineNo,COUNT(distinct tt.Entry_Timestamp) AS EntryTransactions,
(
SELECT COUNT(tt1.Exit_Timestamp)
FROM dbo.tbl1
WHERE (tt1.MachineNo is Not Null)) as ExitTransactions

FROM dbo.tbl tt
WHERE (tt.MachineNo is Not Null)
group by tt.MachineNo)

union

((select count(tt2.EntryLaneId) as pass1,
(
select count(tt2.EntryLaneId)
fROM dbo.tblTicketTransaction tt2
where (tt2.MachineNo is Null) and tt2.EntryLaneId='2'
)as pass2
fROM dbo.tblTicketTransaction tt2
where (tt2.MachineNo is Null) and tt2.EntryLaneId='1'))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 02:38:03

as error suggests you cant have different number of fields on both side of union. writing you query

SELECT tt.MachineNo,COUNT(distinct tt.Entry_Timestamp) AS EntryTransactions, 
(
SELECT COUNT(tt1.Exit_Timestamp) 
FROM dbo.tbl1
WHERE (tt1.MachineNo is Not Null)) as ExitTransactions

FROM dbo.tbl tt 
WHERE (tt.MachineNo is Not Null)
group by tt.MachineNo)

union

select count(case when tt2.EntryLaneId='1' then tt2.EntryLaneId else null end) as pass1,
 count(case when tt2.EntryLaneId='2' then tt2.EntryLaneId else null end) as pass2,fieldmissing
fROM dbo.tblTicketTransaction tt2
where tt2.MachineNo is Null

as you see you've only 2 fields on second batch while you've 3 in first. thats the error. also your second query can be written in a better way as shown.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-25 : 02:38:35

you need to have same number of column / expressions in your 2nd part of the UNION

KH
[spoiler]Time is always against us[/spoiler]

Andhra
Starting Member

2 Posts

Posted - 2008-06-25 : 07:25:04

quote:
Originally posted by visakh16

as error suggests you cant have different number of fields on both side of union. writing you query

SELECT tt.MachineNo,COUNT(distinct tt.Entry_Timestamp) AS EntryTransactions,
(
SELECT COUNT(tt1.Exit_Timestamp)
FROM dbo.tbl1
WHERE (tt1.MachineNo is Not Null)) as ExitTransactions

FROM dbo.tbl tt
WHERE (tt.MachineNo is Not Null)
group by tt.MachineNo)

union

select count(case when tt2.EntryLaneId='1' then tt2.EntryLaneId else null end) as pass1,
count(case when tt2.EntryLaneId='2' then tt2.EntryLaneId else null end) as pass2,fieldmissing
fROM dbo.tblTicketTransaction tt2
where tt2.MachineNo is Null

as you see you've only 2 fields on second batch while you've 3 in first. thats the error. also your second query can be written in a better way as shown.

thank you for your suggestion i will do what you suggested

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources