Where In statement in SQL Server

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

Where In statement in SQL Server

Author

Topic

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2012-02-07 : 08:58:06

My where in statement is not working in sql server. I am posting my code: the code is in SAS connecting via ODBC to the sql server. If I take the where in statement out it returns 17 million records. Doing this same code connecting to our DB2 returns 486k records but that is from another claims table and not Oxford. The code I am using came from another employee who does the below pull for our Galaxy claims. I have to do the same for our Oxford claims. So I know their code works via DB2 because I ran it. But Oxford unfortunately is on our SQL Server on not DB2 and it never will be on DB2 and I cannot put it on DB2. I tried a where exists statement but cannot figure out how to get the member_id and the date in that statement. Someone said to try a join but I have never done a join on the same table. Only joins from one table to another. I am not sure how to construct a join on the same table.

proc sql;
connect to odbc(database=oxford);
create table reminder.clms_mrg as
select * from connection to odbc
(select distinct
member_id,
year(earliest_service_date_key) as year,
earliest_service_date_key as fst_srvc_dt,
self_fully_insured as finc_arng_cd,
market as state,
CASE WHEN procedure_code IN ('93510','93511','93524','93526','93508','93452','93453','93454','93455','93456','93457',
'93458','93459','93460','93461') THEN 1 END AS CATH,
case when procedure_code in ('33206','33207','33208','33212','33213','33214','33225','33240','33249') then 1 end as EP,
case when procedure_code in ('92982','92983','92984','92980','92981','G0290','G0291','92995','92996') then 1 end as PCI,
sum(allowed_amount) as allw_amt
from oxford2009claims
where (member_id,earliest_service_date_key) in
(select distinct
member_id,
earliest_service_date_key
from oxford2009claims
where
member_id>0
and
self_fully_insured='FI'
and
procedure_code in ('54023', '90685', '90867')
and
lob_code in ('CO', 'SF'))
and
self_fully_insured = 'FI'
group by member_id,earliest_service_date_key,self_fully_insured,procedure_code,market)
order by member_id,fst_srvc_dt,finc_arng_cd,state;
disconnect from odbc;
quit;

tina m miller

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-07 : 09:36:43

Replace the FROM and WHERE clause with this:

	....
	SUM(allowed_amount) AS allw_amt
FROM
	oxford2009claims oa
	CROSS APPLY
	(
		SELECT 1 as Dummy FROM oxford2009claims ob
		WHERE ob.member_id = oa.member_id
		AND oa.earliest_service_date_key = ob.earliest_service_date_key
		AND member_id > 0
       	AND self_fully_insured = 
       	    'FI'
       	AND procedure_code IN ('54023', '90685', '90867')
       	AND lob_code IN ('CO', 'SF')
	) ob
WHERE
	self_fully_insured = 'FI'
.....

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2012-02-07 : 09:54:03

Well now I am getting this error:

The
conversion of the varchar value '9065415501' overflowed an int column. Maximum integer
value exceeded.

tina m miller

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-07 : 10:47:58

That seems like a problem somewhere in the data - which I am unable to offer any help on. The only thing I can suggest is to take the inner query and run it from an SSMS window to try to identify what rows/columns are causing it to happen.

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2012-02-07 : 12:27:48

Thanks Sunita. It is something wrong with the table. I will have to investigate what it is. I used your suggestion and tried running my Oxford2010claims and it is running now with no errors. So it is definately something in that 2009 table in the procedure_code field it does not like. Thanks!!!!

tina m miller

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:28:05

which column is having this value? try casting it to bigint

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2012-02-10 : 17:55:47

actually it is an ID code and I had to change it from >0 to is the column name not null

tina m miller

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources