Stored Procedure Question - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Question

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-27 : 16:51:09

Trying to list pending cases... cases that are in both of these tables:

currentmmcases and lastmmcases

Wrote this

select c.fo, c.sfo, c.claim, c.personclaim,
c.claim - l.claim as Claim
from currentmmcases c
join lastmmcases l
on c.fo = l.fo

Got this error message:

Operand data type char is invalid for subtract operator.

What am I missing?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-27 : 16:52:51

What is the point of this: c.claim - l.claim as Claim?

Do you really want to subtract them? Or was that a typo and you intended to concatenate them? If so, then use + instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-27 : 16:53:37

Oh and if you do really want to subtract them, which seems unlikely, then convert them to a numeric data type first.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-27 : 17:02:22

I don't want to subtract I want to compare two tables and place the ones that are still in both tables to go into a new table called Pending.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-27 : 17:24:35

So what are you trying to do with that then: c.claim - l.claim as Claim?

Could you show us some sample data and expected output?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-27 : 21:01:41

I have two tables that I need to compare each week to see if the records that are in LastmmCases are still in the CurrentmmCases table. The PersonClaim should match the ClaimName. Reps will go into the mainframe to fix the mismatch. So they put them in a text file and I brought into to determine the mismatches.

Since Stan and Tracy are in both tables they are still pending and should go into the pending table

Karen is only in Lastmmcases and not in Currentmmcases so her recored is cleared and should go into the cleared table. Is this possible


CurrentmmCases
FO    SFO   ClaimName       PersonClaim
458    a    Stan Jones      Stan T Jones
457    k    Tracy Brown     Tracy E Brown


FO    SFO   ClaimName       PersonClaim
458    a    Stan Jones      Stan T Jones
457    k    Tracy Brown     Tracy E Brown
587    c    Karen Smith     Karen D Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources