Convert BIGINT data type to Character data type

Reply
Contributor
Posts: 37

Convert BIGINT data type to Character data type

Hello, I'm trying to convert a variable that is stored as a BIGINT from a Neteeza database to a character data type and have not had much luck in doing so.  I'm trying to convert this data type in a join without using a pass-through query and was wondering if this is possible.

 

Table1 as t1 Inner Join Table2 as t2 on t1.ID = put(t2.ID,20.)

 

Have to convert to a character with a length of 20.  Received errors with this piece of code.

 

Thanks in advance.

Respected Advisor
Posts: 4,737

Re: Convert BIGINT data type to Character data type

Posted in reply to PhatRam33

@PhatRam33

Please post the relevant section from your log or at least the full error message.

 

If you say pass-through: Implicit or explicit?

If explicit then you need to use Netezza functions - to_char()

https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_extn...

 

If implicit then be aware that the SAS put() function gets eventually not pushed to the data base meaning that SAS will pull all the data first into SAS before applying the join condition.

 

According to docu SAS can deal with Bigint and the put() function as such should work. So please show us some log so we can better understand what's going wrong.

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p07ge3f7fvfh5ln1msgdpqhbeh31.htm&docsetV...

Contributor
Posts: 37

Re: Convert BIGINT data type to Character data type

It is an implicit query.  Tried

dbsastype=(IM.Field1='char(20)')

 

Still receiving same error.

Respected Advisor
Posts: 4,737

Re: Convert BIGINT data type to Character data type

Posted in reply to PhatRam33

@PhatRam33

What's the error? Which SAS version are you using? Which library engine are you using?

Could it be related to: http://support.sas.com/kb/38/707.html ?

Contributor
Posts: 37

Re: Convert BIGINT data type to Character data type

It is the numeric value out of range error.  SAS EG 6.1 on UNIX. I think I may just stick with an explicit query as it works there. 

Ask a Question
Discussion stats
  • 4 replies
  • 157 views
  • 1 like
  • 2 in conversation