Correct me

Question

Post reply

Correct me

upendramkumar

SSC Veteran

Points: 281
More actions
April 24, 2008 at 11:01 am

#189419

Came up with the following script to find differences between 2 tables & here it is.....
SELECT MIN(Table_Name) AS Table_Name, beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt
FROM
(
SELECT 'Table A' AS Table_Name, beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt
FROM
(select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt from beebusiness bb
join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A
WHERE NOT EXISTS
(SELECT 1 FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B
WHERE A.beenumber= convert(char(13), ltrim(rtrim(stuff(B.sa_ss,1,patindex('%[^0]%', B.sa_ss)-1,''))))
AND isnull(A.nacdate,0) = isnull(B.CL1,0)
AND isnull(A.ConsultantPromDt,0) = isnull(B.cl2,0)
AND isnull(A.TeamLeadPromDt,0) = isnull(B.cl4,0)
)
UNION ALL
SELECT 'Table B' AS Table_Name, sa_ss as beenumber, cl1 as nacdate, cl2 as ConsultantPromDt, cl4 as TeamLeadPromDt
FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B
WHERE NOT EXISTS
(SELECT 1 FROM (select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt from beebusiness bb
join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A
WHERE convert(char(13), ltrim(rtrim(stuff(B.sa_ss,1,patindex('%[^0]%', B.sa_ss)-1,''))))=A.beenumber
AND isnull(A.nacdate,0) = isnull(B.CL1,0)
AND isnull(A.ConsultantPromDt,0) = isnull(B.cl2,0)
AND isnull(A.TeamLeadPromDt,0) = isnull(B.cl4,0)
)
) tmp
group by beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt
having count(*) = 1
order by beenumber
However, after 20 mins of serious thinking, it responds back saying "Error converting data type varchar to numeric. " Im not sure why it is throwing this error as I corrected the data type differences too.
Please correct my script !!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

its quite difficult to help on this one with out the data. BUT

comment out most of your code and debug it bit by bit, that is the best way for you to find where the problem lies.

Everything you can imagine is real.

upendramkumar SSC Veteran Points: 281 More actions · Answer 2

Bledu,

I followed your advice & threw in bunch of validations & here is where Im struck at now...

SELECT 'Table B' AS Table_Name, b.sa_ss as beenumber, cl1 as nacdate, cl2 as ConsultantPromDt,

cl4 as TeamLeadPromDt

FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B where isnumeric(b.sa_ss) = 1 and b.sa_ss not like '99999%'

WHERE NOT EXISTS

(SELECT 1 FROM

(select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt

from beebusiness bb

join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A

WHERE

cast(b.sa_ss as INT) = A.beenumber and isnumeric(b.sa_ss) = 1 and b.sa_ss not like '99999%'

AND isnull(A.nacdate,0) = isnull(B.CL1,0)

AND isnull(A.ConsultantPromDt,0) = isnull(B.cl2,0)

AND isnull(A.TeamLeadPromDt,0) = isnull(B.cl4,0)

)

It is throwing a syntax error at line 6( point that says - WHERE NOT EXISTS) --

Incorrect syntax near the keyword 'WHERE'.

Thank you very much for your help !!!

cbaker-591171 SSC Veteran Points: 248 More actions · Answer 3

cbaker-591171

SSC Veteran

Points: 248

April 24, 2008 at 12:17 pm

#806803

I think you are missing a closing paren.

upendramkumar SSC Veteran Points: 281 More actions · Answer 4

I think I finally got it... & here it is .... Thanks a lot 'Bledu' & 'Cbaker'

SELECT MIN(Table_Name) AS Table_Name, beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt

FROM

(

SELECT 'Table A' AS Table_Name, beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt

FROM

(select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt from beebusiness bb

join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A

WHERE NOT EXISTS

(SELECT 1 FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B

WHERE A.beenumber= cast(b.sa_ss as INT)

and isnumeric(b.sa_ss) = 1 and b.sa_ss not like '999999%'

AND isnull(A.nacdate,0) = isnull(B.CL1,0)

AND isnull(A.ConsultantPromDt,0) = isnull(B.cl2,0)

AND isnull(A.TeamLeadPromDt,0) = isnull(B.cl4,0)

)

UNION ALL

SELECT 'Table B' AS Table_Name, bb.sa_ss as beenumber, bb.cl1 as nacdate, bb.cl2 as ConsultantPromDt,

bb.cl4 as TeamLeadPromDt

FROM

(select sa_ss, cl1, cl2, cl4 from

[hqvm03\hqsqlinst05].sdk.dbo.ml where isnumeric(sa_ss) = 1 and sa_ss not like '99999%')BB

WHERE NOT EXISTS

(SELECT 1 FROM

(select bb.beenumber, bbd.nacdate, bbd.ConsultantPromDt, bbd. TeamLeadPromDt

from beebusiness bb

join beebusinessdate bbd on bbd.beebusinessguid = bb.beebusinessguid)A

WHERE

cast(bb.sa_ss as INT) = A.beenumber and isnumeric(bb.sa_ss) = 1 and bb.sa_ss not like '99999%'

AND isnull(A.nacdate,0) = isnull(Bb.CL1,0)

AND isnull(A.ConsultantPromDt,0) = isnull(Bb.cl2,0)

AND isnull(A.TeamLeadPromDt,0) = isnull(Bb.cl4,0)

)

) tmp

group by beenumber, nacdate, ConsultantPromDt, TeamLeadPromDt

having count(*) = 1

order by beenumber

bledu SSChampion Points: 12335 More actions · Answer 5

upendramkumar (4/24/2008)
Bledu,
I followed your advice & threw in bunch of validations & here is where Im struck at now...
FROM [hqvm03\hqsqlinst05].sdk.dbo.ml B where isnumeric(b.sa_ss) = 1 and b.sa_ss not like '99999%'
WHERE NOT EXISTS
(SELECT 1 FROM

you had put 2 where statements which I have put in bold

Everything you can imagine is real.

upendramkumar SSC Veteran Points: 281 More actions · Answer 6

Bledu,

You have noticed the correct thing but it is in its place & what I did to over come this problem is to create a derived table and then do my lil validation in there & then I just called the value without doing any more validation.

But for now, my issue is solved & I posted my complete sql statement that works now.

sincere thanks !

bledu SSChampion Points: 12335 More actions · Answer 7

bledu

SSChampion

Points: 12335

April 24, 2008 at 12:51 pm

#806834

its all good.

Everything you can imagine is real.