|
View previous topic :: View next topic
|
| Author |
Message |
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
Hi,
I am trying to Update a DB2 table from a SAS dataset in below manner...
| Code: |
PROC SQL;
CREATE WORK.DS1
AS SELECT A.FIRST_NAME
A.PREMIUM_PAID
B.PREMIUM_VALUE
B.PREMIUM_CURR
FROM DB2LIB.TAB_HOLDER A
DB2LIB.PREMIUM_RATES B
WHERE A.POLICY_NO=B.POLICY_NO
RUN;
PROC SQL;
UPDATE DB2LIB.TAB_HOLDER A
SET (PREMIUM_VALUE,PREMIUM_CURRENCY)=
(SELECT (PREMIUM_VALUE,PREMIUM_CURR) FROM WORK.DS1 B WHERE A.POLICY_NO = B.POLICY_NO)
WHERE A.ACTIVE_DATE=200811
RUN; |
When I execute in the above manner, I am receiving an error as
"ERROR: Expression using equals (=) has components that are of different data types".
When I have executed the PROC CONTENTS to see the data types of fields in both the tables/SAS data set, I see no difference....
Any clue.. how to solve this....??
Thanks
Rajesh
Edited: Please use BBcode when You post some code/error, that's rather readable, Thanks...Anuj |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
SET (PREMIUM_VALUE,PREMIUM_CURRENCY)=
(SELECT (PREMIUM_VALUE,PREMIUM_CURR) FROM WORK.DS1 B WHERE A.POLICY_NO = B.POLICY_NO) |
is it a typo in the post or in the query ??? |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
| No its not a typo.... |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
| Any updates....?? :-( |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Unless you post the attribute definition of every name used in the code, you will have to determine which causes this: "has components that are of different data types".
Basically that error says you cannot compare apples to oranges  |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
Here are the required definitions...
| Code: |
The CONTENTS Procedure
Data Set Name DB2LIB.TAB_HOLDER
Member Type DATA
Engine DB2
Created .
Last Modified .
Protection
Data Set Type
Label
Data Representation Default
Encoding Default
Variable Type Len Format Informat Label
PREMIUM_PAID Num 8 30.6 30.6 PREMIUM_PAID
The CONTENTS Procedure
Data Set Name DB2LIB.PREMIUM_RATES
Member Type DATA
Engine DB2
Created .
Last Modified .
Protection
Data Set Type
Label
Data Representation Default
Encoding Default
Variable Type Len Format Informat Label
PREMIUM_VALUE Num 8 21.2 21.2 PREMIUM_VALUE
PREMIUM_CURRENCY Num 8 21.2 21.2 PREMIUM_CURRENCY
The CONTENTS Procedure
Data Set Name WORK.DS1
Member Type DATA
Engine V9
Created Friday, January 09, 2009 07:26:09 AM
Last Modified Friday, January 09, 2009 07:26:09 AM
Protection
Data Set Type
Label
Data Representation MVS_32
Encoding open_ed-1047 Western (OpenEdition)
Variable Type Len Format Informat Label
PREMIUM_VALUE Num 8 21.2 21.2
PREMIUM_CURR Num 8 21.2 21.2 |
|
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
| The thing is that I am comparing the oranges only.. but those are from different trees.... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Not all of the variable definitions have been posted. The problem may be with some other variable.
| Quote: |
| "ERROR: Expression using equals (=) has components that are of different data types". |
What generated this? It does not appear to be from db2. What message id was presented with it?
| Code: |
Data Representation MVS_32
Encoding open_ed-1047 Western (OpenEdition) |
If this is not the "same" as the default, might this cause a problem? The premium data is of different format (21.2/30.6)?
I suspect that somewhere there is additional disgnostic information. |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
Dick: That looks like a SAS error to me.
Rajesh: you've got two PROC SQL statements with a RUN; between them. Which one of the two is generating the error message? And what do the three POLICY_NO fields look like (i.e., what are their attributes)? PROC CONTENTS will work for the two tables in DB2LIB but WORK.DS1 is coming from where -- and what does its POLICY_NO variable look like? |
|
| Back to top |
|
|
rajesh1183
New User
Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad
|
|
|
|
Hello Robert,
1.) The 2nd PROC SQL stmt is generating the error during updation.
2) The policy no.s format and informats are of length 19 with Numeric data type.
3) and the WORK.DS1 is created from the first PROC SQL stmt.. I am creating this using CREATE stmt....
Finally.. I dont see any data type mismatch in any of the variable.. but I am enoucntering this.. Do we need to mention any SAS option while updating a DB2 table using a SAS dataset to avoid any data type conversions....?? |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA
|
|
|
|
| Where do you pick up the POLICY_NO variable in WORK.DS1? You use the variable as part of the first SELECT but it's not in the output dataset. I suspect what's happening is that the WORK.DS1 POLICY_NO is missing and has default attributes. I don't think the default attributes for a SAS numeric variable will handle a 19-digit number, which would explain the error. Add POLICY_NO as a variable to WORK.DS1 in the first SELECT and see if your error goes away. |
|
| Back to top |
|
|
|
|
