How to do CASE statements in syncsort?

joseph murphy · New User Joined: 24 Nov 2008 Posts: 17 Location: USA

Hi all,

I am doing performance tuening for SQL quries using syncsort,i have CASE like this,

COALESCE(
SUBSTR(
CASE(WHEN ZONE_ID IS NULL
THEN '00000000'
ELSE DIGITS(ZONE_ID)
END)1,8)
SUBSTR('00000000'),1,8)

Note:-
ZONE_ID is a Decimal(8,0),PD format

I have to do formatting in syncsort outrec,how to do it?

Arun Raj · Posted: Fri Dec 12, 2008 6:21 pm

joseph murphy,

You are already using DIGITS to format the ZONE_ID field. Still what formatting you need to do?

joseph murphy · New User Joined: 24 Nov 2008 Posts: 17 Location: USA

Arun,
I just want to make my question clear to you,I am doing Performance fix for Jobs(contains Sql Quries),The SELECT contains

COALESCE(
SUBSTR(
CASE(WHEN ZONE_ID IS NULL
THEN '00000000'
ELSE DIGITS(ZONE_ID)
END)1,8)
SUBSTR('00000000'),1,8)
Note:-
ZONE_ID is a Decimal(8,0),PD format

I want to know the replacement for CASE in syncsort,I tried with CHANGE,but it didnt work,

OUTREC=(1:3,5,CHANGE=(8,C'0000000000',C'00000000'),
NOMATCH=(3,5,PD,EDIT=(TTTTTTTT))

dick scherrer · Posted: Fri Dec 12, 2008 10:43 pm

Hello,

Try IFTHEN / WHEN / OVERLAY.

Arun Raj · Posted: Sat Dec 13, 2008 12:23 am

joseph murphy,

It would be better if you post the entire SQL. Also I am not sure whether you'll be able to handle the NULL CASE in sort. See what do you get in output for NULL fields.

joseph murphy · New User Joined: 24 Nov 2008 Posts: 17 Location: USA

How to specify Low-values(null)?

zone_id(decimal(8,0)) i.e length=5

how to specify this in outrec(Syncsort)?

Arun Raj · Posted: Mon Dec 15, 2008 11:17 pm