Page 1 of 2

Outer Join Query?

PostPosted: Fri Apr 24, 2009 2:05 pm
by gokulNmf
Hi All,

I am using a Right Outer join between 2 tables and selecting 3 fields from each, into a ps data set(D.S). The fields add upto 34 in length, i.e.,the data must end at 34th column in the D.S, but there are some extra columns inserted inbetween the fileds(of the Left Table) in the D.S. :? . :( .
Why is it happening:?: , is there any way to avoid this? :? :?:

Re: Outer Join Query?

PostPosted: Fri Apr 24, 2009 8:17 pm
by swd
could you post your SQL and some sample output so that someone can have a look at it. You'll stand more of a chance at getting an answer.

Cheers
Steve

Re: Outer Join Query?

PostPosted: Sat Apr 25, 2009 1:53 am
by dick scherrer
Hello,

Are there any varchar columns being selected?

Re: Outer Join Query?

PostPosted: Tue Apr 28, 2009 1:27 pm
by gokulNmf
Hi,
The query and output is given below..

SELECT  T100.VEND_ID,                             
        T100.VEND_CRT_DT,                             
        T800.VEND_ID,                                 
        T800.VEND_CRT_DT                             
     FROM DEVA.TVND100_VENDOR T100 RIGHT OUTER JOIN         
     DEVA.TVND800_VENDOR T800 ON                       
     T100.VEND_ID = T800.VEND_ID,
     DEVA.TCTL003_BTCH_PARM T003   
     WHERE T800.AUD_CHG_TYP_CD IN ('U', 'I', 'D')
WITH UR;


O/P
the file is openned in browse mode along with COLS on
123456789-123456789-123456789-123456789-
000001   .1990-06-22.000001   1990-06-22
003000   .1995-11-09.003000   1995-11-09
003001   .2001-05-10.003001   2001-05-10
?                ?
004005   1992-07-05

It is not clear where the ?s should be

Thanks steve for ur suggestion!

Thanks,
Gokul

Re: Outer Join Query?

PostPosted: Tue Apr 28, 2009 4:59 pm
by swd
Gokul

What position are the extra columns in. Is it the fullstops (or periods) in columns 8 and 19? giving you 2 extra bytes and making your record length 36 instrad of 34?

I'm not sure myself, so perhaps someone else may have a better idea than me, but can I ask how you are executing this and getting the output to a dataset. Are you using the DB2 unload utility DSNTIAUL?, if so, are the columns optional? If so then these could be the null indicator fields that will be unloaded. You may need to put a CHAR around the select of these fields so as to get a fixed length character string.

Cheers
Steve

Re: Outer Join Query?

PostPosted: Wed Apr 29, 2009 1:35 am
by dick scherrer
Hello,

When posting source and data, you should use the "Code" tag for readability.

If you re-post the data with HEX On as well as COLS and use the Code tag it will help us help you.

Re: Outer Join Query?

PostPosted: Thu Apr 30, 2009 10:17 am
by gokulNmf
those where periods comming immediatly after the each field...
hereafter i ll use code tag.. i dint no its usage.. thanks!!

Re: Outer Join Query?

PostPosted: Thu Apr 30, 2009 1:17 pm
by swd
OK, If I understand you, it is the T100.VEND_ID and the T100.VEND_CRT_DT fields where the problem is. And these fields have the period appearing after the field, and you are asking what these periods are doing in the file. Is this what you are asking? As mentioned above in my last post, are these fields OPTIONAL on the table, and how are you the getting the data into a dataset. Are you using the DSNTIAUL DB2 unload utility. Without this information I can't really help. As Dick mentions above, the data displayed with HEX ON will also help.
Cheers
Steve

Re: Outer Join Query?

PostPosted: Fri May 08, 2009 5:21 pm
by gokulNmf
Yes, that was my question,they are not optional fields in the table. I am using IKJEFT01 TO UNLOAD from the table. Cheking with HEX ON, i found that they were null charecters.

Re: Outer Join Query?

PostPosted: Fri May 08, 2009 6:56 pm
by swd
Gokul,
Well, I'm not really sure. You say you are using IKJEFT01 to unload, I take it you mean you are running a SPUFI in batch to do this? so you are executing the DSNTEP2 DB2 program? (look at your SYSTSIN parm to confirm this). Have you tried using DSNTIAUL DB2 unload utility to so this. If not I suggest you do and see what results you get with that. DSNTIAUL is also executed using IKJEFT01, but the SYSTSIN calls program DSNTIAUL rather then DSNTEP2.

DSNTEP2 Example
//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) -
LIBRARY('CSOFT.DSN1.RUNLIB.LOAD')
END

DSNTIAUL Example
//SYSTSIN DD *
DSN SYSTEM(DSN1)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL,200') -
LIB('CSOFT.DSN.RUNLIB.LOAD')

It's worth a try.

Steve.