SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JJ297
Aged Yak Warrior

940 Posts

Posted - 08/27/2010 :  16:51:09  Show Profile  Reply with Quote
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

USA
37466 Posts

Posted - 08/27/2010 :  16:52:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 08/27/2010 16:53:03
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 08/27/2010 :  16:53:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts

Posted - 08/27/2010 :  17:02:22  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37466 Posts

Posted - 08/27/2010 :  17:24:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

JJ297
Aged Yak Warrior

940 Posts

Posted - 08/27/2010 :  21:01:41  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000