April 23, 2008 at 3:28 pm
Im trying to fish out & display the differences between two sets of tables & here is what I wrote so far.
SELECT MIN(TableName) as TableName, SA_SS, CL1, Cl2, CL4
FROM
(
SELECT 'Table A' as TableName, COUNT(*) NDUPS, A.SA_SS, A.CL1, A.Cl2, A.CL4
FROM (select bb.beenumber as SA_SS, bbd.nacdate as CL1, bbd.ConsultantPromDt as CL2, bbd. TeamLeadPromDt as CL4 from beebusiness bb
join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid) A
GROUP BY A.SA_SS, A.CL1, A.Cl2, A.CL4
UNION ALL
SELECT 'Table B' as TableName, COUNT(*) NDUPS, B.SA_SS, B.CL1, B.Cl2, B.CL4
FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B
GROUP BY B.SA_SS, B.CL1, B.Cl2, B.CL4
) tmp
GROUP BY NDUPS, SA_SS, CL1, Cl2, CL4
HAVING COUNT(*) = 1
ORDER BY SA_SS
Now the problem is when I run the following query, I get this error - Error converting data type varchar to numeric.
I forgot to mention that sa_ss feild in ml table is char datatype & beenumber is int datatype.
Please advise !!!
May 21, 2008 at 1:50 am
Without seeing the actual data (you haven't posted any) all I can suggest is to CAST the integer values to character values, so that you can compare them. It seems there is data in the char column that cannot be converted to integer (which is what the database engine is trying to do).
Also consult Books Online on data type precedence.
http://msdn.microsoft.com/en-us/library/ms190309.aspx?n=0
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 21, 2008 at 10:35 am
ML,
I got this sorted out a while ago but I never kept up with this forum as such. Actually, I used cast & rolled sa_ss to INT & then it all worked out fine.
Here is the actual Sql ...
SELECT MIN(TableName) as TableName, SA_SS, CL1, Cl2, CL4
FROM
(
SELECT 'Table A' as TableName, COUNT(*) NDUPS, A.SA_SS, A.CL1, A.Cl2, A.CL4
FROM (select bb.beenumber as SA_SS, bbd.nacdate as CL1, bbd.ConsultantPromDt as CL2, bbd. TeamLeadPromDt as CL4 from beebusiness bb
join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid) A
GROUP BY A.SA_SS, A.CL1, A.Cl2, A.CL4
UNION ALL
SELECT 'Table B' as TableName, COUNT(*) NDUPS, cast(B.SA_SS as INT), B.CL1, B.Cl2, B.CL4
FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B
where isnumeric(b.sa_ss) = 1
GROUP BY cast(B.SA_SS as INT), B.CL1, B.Cl2, B.CL4
) tmp
GROUP BY NDUPS, SA_SS, CL1, Cl2, CL4
HAVING COUNT(*) = 1
ORDER BY SA_SS
Thank you very much for your interest.
May 21, 2008 at 2:54 pm
Isn't the answer worth so much more if you find it yourself? 🙂
Anyway, next time post back with what you've learned, so that others may learn from you.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
May 21, 2008 at 5:38 pm
ML,
Cant agree with you more on this one !!! It certainly makes me walk home tall that evening when I successfully sort out issues on my own.
Thanks anyways.
Kumar.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply