Outer Join Query?



IBM's flagship relational database management system

Outer Join Query?

Postby gokulNmf » Fri Apr 24, 2009 2:05 pm

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? :? :?:
Cheers,
Gokul
User avatar
gokulNmf
 
Posts: 118
Joined: Sat Mar 28, 2009 6:41 pm
Location: India
Has thanked: 2 times
Been thanked: 0 time

Re: Outer Join Query?

Postby swd » Fri Apr 24, 2009 8:17 pm

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
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: Outer Join Query?

Postby dick scherrer » Sat Apr 25, 2009 1:53 am

Hello,

Are there any varchar columns being selected?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Outer Join Query?

Postby gokulNmf » Tue Apr 28, 2009 1:27 pm

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
Cheers,
Gokul
User avatar
gokulNmf
 
Posts: 118
Joined: Sat Mar 28, 2009 6:41 pm
Location: India
Has thanked: 2 times
Been thanked: 0 time

Re: Outer Join Query?

Postby swd » Tue Apr 28, 2009 4:59 pm

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
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: Outer Join Query?

Postby dick scherrer » Wed Apr 29, 2009 1:35 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Outer Join Query?

Postby gokulNmf » Thu Apr 30, 2009 10:17 am

those where periods comming immediatly after the each field...
hereafter i ll use code tag.. i dint no its usage.. thanks!!
Cheers,
Gokul
User avatar
gokulNmf
 
Posts: 118
Joined: Sat Mar 28, 2009 6:41 pm
Location: India
Has thanked: 2 times
Been thanked: 0 time

Re: Outer Join Query?

Postby swd » Thu Apr 30, 2009 1:17 pm

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
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: Outer Join Query?

Postby gokulNmf » Fri May 08, 2009 5:21 pm

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.
Cheers,
Gokul
User avatar
gokulNmf
 
Posts: 118
Joined: Sat Mar 28, 2009 6:41 pm
Location: India
Has thanked: 2 times
Been thanked: 0 time

Re: Outer Join Query?

Postby swd » Fri May 08, 2009 6:56 pm

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.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post