Data type error message - 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

Data type error message

Author

Topic

MonaSQL
Starting Member

3 Posts

Posted - 2009-05-11 : 10:52:57

Hi, I am getting data type error message when I run this query in PROC SQL, while:
Data type for T7.SPRV_NUM is CHAR 14
Data type for T3.CPT_PROC_CD is CHAR 6.
---error-----
496 and T7.SPRV_NUM = '70010000C15057'

497 and T3.CPT_PROC_CD = '62284');

ERROR: Expression using IN has components that are of different data types.

NOTE: The IN referred to may have been transformed from an OR to an IN at some point during

PROC SQL where clause optimization.

-----------------
Thanks,
Nayer

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-05-11 : 10:54:07

What is the query you are trying to execute - Please post code here

MonaSQL
Starting Member

3 Posts

Posted - 2009-05-11 : 10:55:47

This is the query that I run in SAS:

proc sql;

Create TABLE DW.lc AS

( select distinct

T9.MODEL,

T7.SPRV_NUM,

T7.SPRV_FULL_NAME,

T8.BPRV_NUM,

T8.BPRV_FULL_NAME,

T3.CPT_PROC_CD,

T1.INCURRED_DT_DAY,

T5.LINE_STAT_DESC,

T5.LINE_MSG_CD,

T2.PAID_DT_DAY,

T4.INT_CLM_NUM,

T4.CLM_LINE_NUM,

T4.PRORATE_AMT,

T4.SUBM_CHARGE_AMT

from DW.V_INCURRED_DATE T1 join

DW.V_MEDICAL_SERVICE T4 on

T4.INCURRED_DT_KEY = T1.INCURRED_DT_KEY

Join

DW.V_PAID_DATE T2

on T4.PAID_DT_KEY = T2.PAID_DT_KEY

join

DW.V_CLAIM_PROCEDURE T3

on T4.CLM_PROC_KEY = T3.CLM_PROC_KEY

join

DW.V_MEDICAL_SERVICE_INFO T5

on T4.MEDSRV_INFO_KEY = T5.MEDSRV_INFO_KEY

join

DW.V_CLAIM_MEMBER_PROFILE T10

on

T4.MEM_PROF_KEY = T10.MEM_PROF_KEY

join

DW.V_CLAIM_MEMBER_DEMO T6

on T6.MEM_DEMO_KEY = T10.MEM_DEMO_KEY

join

DW.V_SRV_PROVIDER_PROFILE T11

on

T4.SPRV_PROF_KEY = T11.SPRV_PROF_KEY

join

DW.V_SRV_PROVIDER_DEMO T7

on

T7.SPRV_DEMO_KEY = T11.SPRV_DEMO_KEY

join

DW.V_BILL_PROVIDER_DEMO T8

on T4.BPRV_DEMO_KEY = T8.BPRV_DEMO_KEY

join

DW.V_CLAIM_GROUP_FIN_PROFILE T9

on

T4.GRP_FIN_PROF_KEY = T9.GRP_FIN_PROF_KEY

where

(T1.INCURRED_DT_DAY between '2005-09-01'

and '2007-09-01' )

and T7.SPRV_NUM = '70010000C15057'

and T3.CPT_PROC_CD = '62284');

quit;

----------------Error message-------

493 where

494 (T1.INCURRED_DT_DAY between '2005-09-01'

495 and '2007-09-01' )

496 and T7.SPRV_NUM = '70010000C15057'

497 and T3.CPT_PROC_CD = '62284');

ERROR: Expression using IN has components that are of different data types.

NOTE: The IN referred to may have been transformed from an OR to an IN at some point during

PROC SQL where clause optimization.

498 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.06 seconds

cpu time 0.03 seconds

------------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:05:02

are you using SQL SErver? you cant create table like this sql server. you need to use SELECT...INTO...FROM...

MonaSQL
Starting Member

3 Posts

Posted - 2009-05-11 : 11:09:05

not in SQL server, in SAS. I think the error is related to data format not those 2 fields that I mentioned. So INCURRED_DT_DAY in NUM type, I guess I should update this filter to
T1.INCURRED_DT_DAY between 2005-09-01

and 2007-09-01 )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:12:37

this is a SQL Server forum. please post in some SAS forum for getting more help on this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources