Mayank Tripathi
asked on
Please help with the below query - SQL Server
TableA - Query result pasted below :
Please have a look below select statement where i am using EXCEPT keyword. but something goes wrong... i'm expecting 12 rows but somehow i am getting only 10 rows .
TableB contains no rows.
Select ClientKey,CarbonLocationKe y LK ,c.[Location Size] LSize,c.[Location Size Effective From*] EffFrom ,1 IsCurrent,Getdate()F,Getda te()T,'I' Type
from TableA
EXCEPT
Select ClientKey,CarbonLocationKe y LK ,c.[Location Size] LSize,c.[Location Size Effective From*] EffFrom ,1 IsCurrent,Getdate()F,Getda te()T,'I' Type
from TableB
Table A
ClientKey LK LSize EffFrom IsCurrent F T Type
1 419 44352.64995 00:00.0 1 33:22.0 33:22.0 I
1 420 24289.17091 00:00.0 1 33:22.0 33:22.0 I
1 421 1898203 00:00.0 1 33:22.0 33:22.0 I
1 422 398211 00:00.0 1 33:22.0 33:22.0 I
1 423 71886.70615 00:00.0 1 33:22.0 33:22.0 I
1 424 53507.3469 00:00.0 1 33:22.0 33:22.0 I
1 425 55218.807 00:00.0 1 33:22.0 33:22.0 I
1 426 65682.39419 00:00.0 1 33:22.0 33:22.0 I
1 427 24289.17091 00:00.0 1 33:22.0 33:22.0 I
1 428 16736.46519 00:00.0 1 33:22.0 33:22.0 I
1 429 38247.90407 00:00.0 1 33:22.0 33:22.0 I
1 430 16736.46519 00:00.0 1 33:22.0 33:22.0 I
Thanks,
Mayank
Please have a look below select statement where i am using EXCEPT keyword. but something goes wrong... i'm expecting 12 rows but somehow i am getting only 10 rows .
TableB contains no rows.
Select ClientKey,CarbonLocationKe
from TableA
EXCEPT
Select ClientKey,CarbonLocationKe
from TableB
Table A
ClientKey LK LSize EffFrom IsCurrent F T Type
1 419 44352.64995 00:00.0 1 33:22.0 33:22.0 I
1 420 24289.17091 00:00.0 1 33:22.0 33:22.0 I
1 421 1898203 00:00.0 1 33:22.0 33:22.0 I
1 422 398211 00:00.0 1 33:22.0 33:22.0 I
1 423 71886.70615 00:00.0 1 33:22.0 33:22.0 I
1 424 53507.3469 00:00.0 1 33:22.0 33:22.0 I
1 425 55218.807 00:00.0 1 33:22.0 33:22.0 I
1 426 65682.39419 00:00.0 1 33:22.0 33:22.0 I
1 427 24289.17091 00:00.0 1 33:22.0 33:22.0 I
1 428 16736.46519 00:00.0 1 33:22.0 33:22.0 I
1 429 38247.90407 00:00.0 1 33:22.0 33:22.0 I
1 430 16736.46519 00:00.0 1 33:22.0 33:22.0 I
Thanks,
Mayank
Except will give you duplicates.
ASKER
No Pawan. all are unique
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Which are the 2 extra rows that you're expecting?
Is the UNION return the same rows?
Is the UNION return the same rows?
Select ClientKey,CarbonLocationKey LK ,c.[Location Size] LSize,c.[Location Size Effective From*] EffFrom ,1 IsCurrent,Getdate()F,Getdate()T,'I' Type
from TableA
UNION
Select ClientKey,CarbonLocationKey LK ,c.[Location Size] LSize,c.[Location Size Effective From*] EffFrom ,1 IsCurrent,Getdate()F,Getdate()T,'I' Type
from TableB
ASKER
Vitor,
I'm using EXCEPT not using UNION
Thanks,
MT
I'm using EXCEPT not using UNION
Thanks,
MT
ASKER
Pawan,
Let me verify with the data types. will get back to you soon.
Thanks,
MT
Let me verify with the data types. will get back to you soon.
Thanks,
MT
I'm using EXCEPT not using UNIONI'm just asking you to confirm if both are returning the same.
ASKER
@Vitor
In case of UNION i'm getting 22 rows.
and in case of Except i'm getting 10 rows.
In case of UNION i'm getting 22 rows.
and in case of Except i'm getting 10 rows.
Have you tried mine?
ASKER
Thanks Pawan,
you are right there is a datatype mismatch between two fields date/Datetime and decimal/Float corresponding to TableA and TableB. Once i cast these column i start getting desired result.
Thanks
Mayank
you are right there is a datatype mismatch between two fields date/Datetime and decimal/Float corresponding to TableA and TableB. Once i cast these column i start getting desired result.
Thanks
Mayank
In case of UNION i'm getting 22 rows.Thank you.
and in case of Except i'm getting 10 rows.
So, I just need the answer for the other question:
- Which are the 2 extra rows that you're expecting?