Syntax error converting from a character string to uniqueidentifier

[tjcombo]

I am trying to create a view that is joined on by two unique identifiers. The view is successfully created, but when I try to see what info I got..this message appears "Syntax error converting from a character string to uniqueidentifier."

Here is the sql statements:
create view rvs_case_masters_keyid as select caseno,hospitalid,admitdt,status,
ADMITPROV_KEYID,cm.COMPANY_ID, MEMBID, MEMBNM
from case_masters cm inner join prov_company_v pv on pv.provid=cm.admitprov_keyid
group by caseno,caseno,hospitalid,admitdt,status,
ADMITPROV_KEYID,MEMBID, MEMBNM, cm.company_id

What can I do...???
Thanks
Woody

[jkoopmann]

please post your database and version
and the table definitions

[tjcombo]

SQL Server 2000 with SPA 3

Table Definitions:

Column Name Type Computed Length
Prov_Company
PROV_KEYID uniqueidentifier no 16
ROWID timestamp no 8
PROV_MPI_NO uniqueidentifier no 16
PROVID varchar no 20
COMPANY_ID varchar no 10
ACCOUNT varchar no 4
SUBACCT varchar no 3
CLASS varchar no 2
CONTRACT varchar no 2
FROMDATE datetime no 8
TERMDATE datetime no 8
MAXENROLL int no 4
OUTPATDISCOUNT decimal no 5
PAYMENT varchar no 2
WITHSET int no 4
CARRIER varchar no 6
LOCALITY varchar no 3
FLAGGEDDATE datetime no 8
OUTLDSTATUS varchar no 1
OUTLDCODE varchar no 1
OUTLDDATE datetime no 8
CREATEBY int no 4
CREATEDATE datetime no 8
LASTCHANGEBY int no 4
LASTCHANGEDATE datetime no 8

Case Masters
CASENO varchar no 20
HOSPITALID varchar no 20
MEMB_KEYID uniqueidentifier no 16
CASETYPE varchar no 15
ATTENDPROVID varchar no 20
ADMITPROVID varchar no 20
MEMBID varchar no 20
MEMBNM varchar no 60
MANAGERID varchar no 5
STATUS varchar no 1
CASEPRIORITY varchar no 1
OPENDT datetime no 8
CLOSEDT datetime no 8
URGENT smallint no 2
CHRONIC smallint no 2
SURGERYDT datetime no 8
ADMITDT datetime no 8
RECLOS smallint no 2
DISCHDT datetime no 8
ONSETDT datetime no 8
SURGERY smallint no 2
HOSPITAL smallint no 2
FIRSTDT datetime no 8
LASTDT datetime no 8
UDF1 varchar no 10
UDF2 varchar no 10
UDF3 varchar no 10
UDF4 varchar no 20
UDF5 varchar no 20
UDF6 datetime no 8
UDF7 datetime no 8
UDF8 int no 4
CREATEBY int no 4
CREATEDATE datetime no 8
ADMITPROV_KEYID uniqueidentifier no 16
HOSPITAL_KEYID uniqueidentifier no 16
ATTENDPROV_KEYID uniqueidentifier no 16
LASTCHANGEBY int no 4
LASTCHANGEDATE datetime no 8
COMPANY_ID varchar no 10
ROWID timestamp no 8

[rmiao]

Seems provid and admitprov_keyid have different data type/length. How do values in those columns look like?

[skhanal]

Can you run the select statement of the view?

[skhanal]

create view rvs_case_masters_keyid as select caseno,hospitalid,admitdt,status,
ADMITPROV_KEYID,cm.COMPANY_ID, MEMBID, MEMBNM
from case_masters cm inner join prov_company_v pv on pv.provkey_id=cm.admitprov_keyid
group by caseno,caseno,hospitalid,admitdt,status,
ADMITPROV_KEYID,MEMBID, MEMBNM, cm.company_id