Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SAS : Help Required for Updating a DB2 table from a SAS DS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Fri Jan 09, 2009 6:28 pm    Post subject: SAS : Help Required for Updating a DB2 table from a SAS DS
Reply with quote

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
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10308
Location: italy

PostPosted: Fri Jan 09, 2009 6:31 pm    Post subject: Reply to: SAS : Help Required for Updating a DB2 table from
Reply with quote

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
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Fri Jan 09, 2009 6:53 pm    Post subject:
Reply with quote

No its not a typo....
Back to top
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Sat Jan 10, 2009 3:19 pm    Post subject:
Reply with quote

Any updates....?? :-(
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jan 10, 2009 11:59 pm    Post subject:
Reply with quote

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 icon_wink.gif
Back to top
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Mon Jan 12, 2009 10:24 am    Post subject: Reply to: SAS : Help Required for Updating a DB2 table from
Reply with quote

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
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Mon Jan 12, 2009 10:25 am    Post subject:
Reply with quote

The thing is that I am comparing the oranges only.. but those are from different trees....
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 13, 2009 3:06 am    Post subject:
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8152
Location: East Dubuque, Illinois, USA

PostPosted: Tue Jan 13, 2009 6:31 am    Post subject:
Reply with quote

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
View user's profile Send private message
rajesh1183

New User


Joined: 07 Jan 2008
Posts: 98
Location: Hyderabad

PostPosted: Tue Jan 13, 2009 10:10 am    Post subject: Reply to: SAS : Help Required for Updating a DB2 table from
Reply with quote

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
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8152
Location: East Dubuque, Illinois, USA

PostPosted: Tue Jan 13, 2009 3:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us