DSNTIAUL and CASE - char(1) to varchar(3)?

Jessen Michael

DSNTIAUL and CASE - char(1) to varchar(3)?
DB2 on OS/390 V6.
I'm using the SQL parm with DSNTIAUL to create an unload dataset & the
associated load cards.

When I use a case expression within the sql, it's unloading a 3 character
varchar field instead of the actual 1 character format of the field.
My select:
SELECT T1.CR_AGRMT_KEY,

...
T1.ORIGL_ACCT_NUM,
CASE
WHEN T1.AGRMT_RVSN_IND = ' ' THEN 'N'
ELSE T1.AGRMT_RVSN_IND END AS AGRMT_RVSN_IND,
etc...

My unload cards using the actual field, not the case expression:
AGRMT_RVSN_IND POSITION( 121 )
CHAR( 1) ,
AGRMT_FNCE_AMT POSITION( 122: 130)

My unload cards using the case expression:
AGRMT_RVSN_IND POSITION( 121 )
VARCHAR ,
AGRMT_FNCE_AMT POSITION( 124: 132)
DECIMAL
NULLIF( 133)='?',

Has anyone else encountered this change in formatting that occurs? Is there
a way to use DSNTIAUL and the case expression and still get an unload card
in the original format?

Thanks!
Mike



Bill Kimball

Re: DSNTIAUL and CASE - char(1) to varchar(3)?
(in response to Jessen Michael)
Yes.... I ran into that.... try this:

CASE B.PME_ENTITY_TYPE_CD
WHEN '001' THEN SUBSTR('MED',1,3)
WHEN '002' THEN SUBSTR('MED',1,3)
WHEN '003' THEN SUBSTR('NON',1,3)
ELSE SUBSTR('ERR',1,3)
END

--- Jessen Michael <[login to unmask email]>
wrote:
> DB2 on OS/390 V6.
> I'm using the SQL parm with DSNTIAUL to create an
> unload dataset & the
> associated load cards.
>
> When I use a case expression within the sql, it's
> unloading a 3 character
> varchar field instead of the actual 1 character
> format of the field.
> My select:
> SELECT T1.CR_AGRMT_KEY,
>
> ...
> T1.ORIGL_ACCT_NUM,
> CASE
> WHEN T1.AGRMT_RVSN_IND = ' ' THEN 'N'
> ELSE T1.AGRMT_RVSN_IND END AS AGRMT_RVSN_IND,
> etc...
>
> My unload cards using the actual field, not the case
> expression:
> AGRMT_RVSN_IND POSITION(
> 121 )
> CHAR( 1) ,
> AGRMT_FNCE_AMT POSITION(
> 122: 130)
>
> My unload cards using the case expression:
> AGRMT_RVSN_IND POSITION(
> 121 )
> VARCHAR ,
> AGRMT_FNCE_AMT POSITION(
> 124: 132)
> DECIMAL
> NULLIF( 133)='?',
>
> Has anyone else encountered this change in
> formatting that occurs? Is there
> a way to use DSNTIAUL and the case expression and
> still get an unload card
> in the original format?
>
> Thanks!
> Mike
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/



Dave Nance

Re: DSNTIAUL and CASE - char(1) to varchar(3)?
(in response to Bill Kimball)
Try a substr.

SUBSTR(CASE
WHEN T1.AGRMT_RVSN_IND = ' ' THEN 'N'
ELSE T1.AGRMT_RVSN_IND END AS AGRMT_RVSN_IND,1,1)

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 07/05/01 03:27PM >>>
DB2 on OS/390 V6.
I'm using the SQL parm with DSNTIAUL to create an unload dataset & the
associated load cards.

When I use a case expression within the sql, it's unloading a 3 character
varchar field instead of the actual 1 character format of the field.
My select:
SELECT T1.CR_AGRMT_KEY,

...
T1.ORIGL_ACCT_NUM,
CASE
WHEN T1.AGRMT_RVSN_IND = ' ' THEN 'N'
ELSE T1.AGRMT_RVSN_IND END AS AGRMT_RVSN_IND,
etc...

My unload cards using the actual field, not the case expression:
AGRMT_RVSN_IND POSITION( 121 )
CHAR( 1) ,
AGRMT_FNCE_AMT POSITION( 122: 130)

My unload cards using the case expression:
AGRMT_RVSN_IND POSITION( 121 )
VARCHAR ,
AGRMT_FNCE_AMT POSITION( 124: 132)
DECIMAL
NULLIF( 133)='?',

Has anyone else encountered this change in formatting that occurs? Is there
a way to use DSNTIAUL and the case expression and still get an unload card
in the original format?

Thanks!
Mike






Yvan Roy

Re: DSNTIAUL and CASE - char(1) to varchar(3)?
(in response to Dave Nance)
The following change will keep the original format:
WHEN T1.AGRMT_RVSN_IND = ' ' THEN SUBSTR('N',1,1)

Yvan

--- Jessen Michael <[login to unmask email]> wrote:
> DB2 on OS/390 V6.
> I'm using the SQL parm with DSNTIAUL to create an unload dataset & the
> associated load cards.
>
> When I use a case expression within the sql, it's unloading a 3 character
> varchar field instead of the actual 1 character format of the field.
> My select:
> SELECT T1.CR_AGRMT_KEY,
>
> ...
> T1.ORIGL_ACCT_NUM,
> CASE
> WHEN T1.AGRMT_RVSN_IND = ' ' THEN 'N'
> ELSE T1.AGRMT_RVSN_IND END AS AGRMT_RVSN_IND,
> etc...
>
> My unload cards using the actual field, not the case expression:
> AGRMT_RVSN_IND POSITION( 121 )
> CHAR( 1) ,
> AGRMT_FNCE_AMT POSITION( 122: 130)
>
> My unload cards using the case expression:
> AGRMT_RVSN_IND POSITION( 121 )
> VARCHAR ,
> AGRMT_FNCE_AMT POSITION( 124: 132)
> DECIMAL
> NULLIF( 133)='?',
>
> Has anyone else encountered this change in formatting that occurs? Is there
> a way to use DSNTIAUL and the case expression and still get an unload card
> in the original format?
>
> Thanks!
> Mike
>
>
>
>
>


_______________________________________________________
Do You Yahoo!?
Get your free @yahoo.ca address at http://mail.yahoo.ca



RIBEIRO Paulo Jorge

) char
(in response to Yvan Roy)
Hello all,

I have an perl script that migrates Oracle data to a DB2 7.1 database. I'm
having a little problem concerning to ')' char. Some Oracle VarChar fields
have ')' chars, and I'm getting an error when I try to insert them in DB2.
Anyone can tell me how can I represent a ')' char on a sql statement?

Thanks

Paulo



James Campbell

Re: ) char
(in response to RIBEIRO Paulo Jorge)
What errors? What is the actual text of the insert command.

INSERT INTO my.table values ('abc','()');

will insert an open and a close parenthesis into the second
column.


James Campbell

On 17 Dec 2001, at 11:35, RIBEIRO Paulo Jorge wrote:

Date sent: Mon, 17 Dec 2001 11:35:22 -0000
Send reply to: DB2 Data Base Discussion List <[login to unmask email]>
From: RIBEIRO Paulo Jorge <[login to unmask email]>
Subject: [DB2-L] ) char
To: [login to unmask email]

> Hello all,
>
> I have an perl script that migrates Oracle data to a DB2 7.1 database. I'm
> having a little problem concerning to ')' char. Some Oracle VarChar fields
> have ')' chars, and I'm getting an error when I try to insert them in DB2.
> Anyone can tell me how can I represent a ')' char on a sql statement?
>
> Thanks
>
> Paulo
>
>
>


James A Campbell