× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2001

RE: Recommended PTF's for Dynamic Sql in RPG?

You have a JOIN keyword in there... we used to have to use the WHERE
clause to do joining, did that change while I was sleeping? I know 3rd
party SQL products, such as SEQUEL.

Bob Cozzi
cozzi@rpgiv.com
Visit the new on-line iSeries Forums at: http://www.rpgiv.com/forum

> -----Original Message-----
> From: rpg400-l-admin@midrange.com [mailto:rpg400-l-admin@midrange.com]
On
> Behalf Of Hatzenbeler, Tim
> Sent: Wednesday, August 29, 2001 3:42 PM
> To: 'rpg400-l@midrange.com'
> Subject: RE: Recommended PTF's for Dynamic Sql in RPG?
>
> This message is in MIME format. Since your mail reader does not
understand
> this format, some or all of this message may not be legible.
> --
> [ Picked text/plain from multipart/alternative ]
> And after some repeated testing, the dynamic sql works as long as my
where
> clause only looks at table a, but the moment I tell it to do a where
on
> table B also, thats when it locks up.. But if I do a debug, and copy
the sql
> expression and use it in ops nav, it works great... even with the
where
> clause on table B.
>
> tim
>
> I'm going to look into my :into part of the fetch, and see what
happens if I
> internally define those fields instead of externally defining them
from the
> database file...
>
> > -----Original Message-----
> > From:       Hatzenbeler, Tim [SMTP:thatzenbeler@clinitech.net]
> > Sent:       Wednesday, August 29, 2001 1:20 PM
> > To: 'rpg400-l@midrange.com'
> > Subject:    RE: Recommended PTF's for Dynamic Sql in RPG?
> >
> > This message is in MIME format. Since your mail reader does not
understand
> > this format, some or all of this message may not be legible.
> > --
> > [ Picked text/plain from multipart/alternative ]
> > The weird part is this... If I don't declare dynamicly, it works
just
> > fine...  So my datastructures that I have should be ok...
> >
> > but i'm still debugging and trying different things, to hopefully
narrow
> > down where the problem is...
> >
> > tim
> >
> > > -----Original Message-----
> > > From:     Phil [SMTP:sublime78ska@yahoo.com]
> > > Sent:     Wednesday, August 29, 2001 1:20 PM
> > > To:       rpg400-l@midrange.com
> > > Subject:  RE: Recommended PTF's for Dynamic Sql in RPG?
> > >
> > > (Sorry about that Tim! - my eyes played a trick on me.  I thought
I saw
> > > two
> > > /end-exec in a row.)
> > >
> > > In the past I've had to define the data structures internally as
one
> > data
> > > structure when I was fetching a result based on a join.  I don't
know
> > > whether that's a rule or just a way to do it.
> > >
> > > Phil
> > >
> > >
> > >
> > > > -----Original Message-----
> > > > From: rpg400-l-admin@midrange.com
> > [mailto:rpg400-l-admin@midrange.com]On
> > > > Behalf Of Hatzenbeler, Tim
> > > > Sent: Wednesday, August 29, 2001 3:54 PM
> > > > To: 'rpg400-l@midrange.com'
> > > > Subject: RE: Recommended PTF's for Dynamic Sql in RPG?
> > > >
> > > >
> > > > This message is in MIME format. Since your mail reader does not
> > > understand
> > > > this format, some or all of this message may not be legible.
> > > > --
> > > > [ Picked text/plain from multipart/alternative ]
> > > >         Tim,
> > > >
> > > >         Is this a cut/paste typo:
> > > >
> > > >         c/end-exec
> > > >
> > > >         c/EXEC SQL   OPEN C1
> > > >         c/end-exec
> > > >
> > > >         or is it like this in your source?
> > > >         ---------------------
> > > > It's in my code that way... but I have tried it both ways, and
> > > > that doesn't
> > > > seem to make a difference...
> > > >
> > > > When I do an eval in debug I get :
> > > >  Previous debug expressions
> > > >
> > > >
> > > >
> > > >  > EVAL sql
> > > >
> > > >    SQL =
> > > >
> > > >
> > > ....5...10...15...20...25...30...35...40...45...50...55...60
> > > >
> > > >         1   'Select * From (prdjrnusg  a  JOIN jpldcusgsm b  ON
> > a.pjstr#
> > > '
> > > >
> > > >        61   '= b.jpstr#) WHERE (a.pjhdat between 37110 and
37115)
> > > '
> > > >
> > > >       121   '
> > > '
> > > >
> > > >       181   '
> > > '
> > > >
> > > > And if I run this in strsql, or from ops nav. it works fine...
> > > >
> > > >
> > > > > -----Original Message-----
> > > > > From: Phil [SMTP:sublime78ska@yahoo.com]
> > > > > Sent: Wednesday, August 29, 2001 12:40 PM
> > > > > To:   rpg400-l@midrange.com
> > > > > Subject:      RE: Recommended PTF's for Dynamic Sql in RPG?
> > > > >
> > > >         Tim,
> > > >
> > > >         Is this a cut/paste typo:
> > > >
> > > >         c/end-exec
> > > >
> > > >         c/EXEC SQL   OPEN C1
> > > >         c/end-exec
> > > >
> > > >         or is it like this in your source?
> > > >
> > > > > This is how I debug sql statements:  I set a breakpoint where
the
> > SQL
> > > > > statement is built, then I copy/paste it into Ops Nav's ISQL.
I run
> > > it.
> > > > > (may have to massage it a bit which is fine as long as you
don't
> > > change
> > > > > anything material.  For instance, I usually have to insert the
> > library
> > > > > name
> > > > > into the statement.)
> > > > >
> > > > > Phil
> > > > >
> > > > > > -----Original Message-----
> > > > > > From: rpg400-l-admin@midrange.com
> > > > [mailto:rpg400-l-admin@midrange.com]On
> > > > > > Behalf Of Hatzenbeler, Tim
> > > > > > Sent: Wednesday, August 29, 2001 12:48 PM
> > > > > > To: 'rpg400-l@midrange.com'
> > > > > > Subject: Recommended PTF's for Dynamic Sql in RPG?
> > > > > >
> > > > > >
> > > > > > This message is in MIME format. Since your mail reader does
not
> > > > > understand
> > > > > > this format, some or all of this message may not be legible.
> > > > > > --
> > > > > > [ Picked text/plain from multipart/alternative ]
> > > > > >
> > > > > > Were running r4.5 with just latest cumulative tape..
> > > > > >
> > > > > > But for somereason, I can't get a dynamic sql statement to
> > > > work, it just
> > > > > > seems to hang on the fetch command...
> > > > > >
> > > > > > It's a simple Join, with one field, and the where clause is
> > > > looking for
> > > > > a
> > > > > > match using an item code on table a , and a department code
> > > > on table B.
> > > > > >
> > > > > > This works fine from the command link, and also as a
declared
> > > > > > statement with
> > > > > > the fields hardcoded..
> > > > > >
> > > > > > This is the declared version that did work...
> > > > > >
> > > > > > c*exec sql
> > > > > > c* declare  c1 cursor for
> > > > > > c*      select *
> > > > > > c*
> > > > > > c*      from (prdjrnusg a join jpldcusgsm b on
> > > > > > c*              a.pjstr# = b.jpstr#)
> > > > > > c*
> > > > > > c*      where (a.pjhdat between 37110 and 37115) and
> > > > > > c*      a.pjprod in ('1804','1310','2153') and
> > > > > > c*      b.rddid = 'LAB'
> > > > > > c*end-exec
> > > > > >
> > > > > > And this is the one that doesn't... I took out all the
variables,
> > to
> > > > > test
> > > > > > this...
> > > > > >
> > > > > > And it's very likely (probable) that the reason it's not
working,
> > > > > > is because
> > > > > > I might have a coding error, so I'm including most of the
relavant
> > > > > parts..
> > > > > >
> > > > > > Dprdjrnusg      E DS                  extname(PRDJRNUSG) inz
> > > > > > d                                     prefix(a_)
> > > > > > Djpldcusgsm     E DS                  extname(jpldcusgsm)
inz
> > > > > > d                                     prefix(b_)
> > > > > >
> > > > > > d sql             s           1000    varying
> > > > > >
> > > > > > c/exec sql
> > > > > > c+ declare  c1 cursor for DynSqlStmt
> > > > > > c/end-exec
> > > > > >
> > > > > > c                  eval      sql=
> > > > > > c                            'Select * ' +
> > > > > > c                            'From (prdjrnusg  a  JOIN '+
> > > > > > c                                  'jpldcusgsm b  '+
> > > > > > c                                  'ON a.pjstr# = b.jpstr#)
'+
> > > > > > c                            'WHERE (a.pjhdat between
37110'+
> > > > > > c                            ' AND 37115) ' +
> > > > > > c                            'and a.pjprod in '  +
> > > > > > c                            '(''1804'',''1310'',''2153'')'
+
> > > > > > c                            ' and b.rddid = ''LAB'' '
> > > > > > c/exec sql
> > > > > > c+ Prepare DynSqlStmt from :sql
> > > > > > c/end-exec
> > > > > >
> > > > > > c/EXEC SQL   OPEN C1
> > > > > > c/end-exec
> > > > > >
> > > > > > c                   dou       Done
> > > > > > c/EXEC SQL
> > > > > > c+ FETCH next from c1 INTO :prdjrnusg, :jpldcusgsm
> > > > > > c/END-EXEC
> > > > > >
> > > > > > c                   if        sqlcod <> 0
> > > > > > c                   leave
> > > > > > c                   endif
> > > > > >
> > > > > >
> > > > > >
> > > > > > Below was the orginal non hardcoded version...
> > > > > > c*                  eval      sql=
> > > > > > c*                            'Select * ' +
> > > > > > c*                            'From (PrdJrnUsg  a  JOIN '+
> > > > > > c*                                  'JPLDCusgsm b  '+
> > > > > > c*                                  'ON a.pjstr# = b.jpstr#)
'+
> > > > > >
> > > > > > c*                            'WHERE (a.pjhdat between '+
> > > > > > c*
%editc(startdateh:'Z') +
> > > > > > c*                            ' AND ' +
> > > > > > c*                                    %editc(EndDateH:'Z') +
') '+
> > > > > >
> > > > > > c*                  if        %trim(dspwhere) <> *blanks
> > > > > > c*                  eval      sql = sql + 'AND
'+%trim(dspwhere)
> > > > > > c*                  endif
> > > > > >
> > > > > > Thanks, tim
> > > > > > _______________________________________________
> > > > > > This is the RPG programming on the AS400 / iSeries
(RPG400-L)
> > > mailing
> > > > > list
> > > > > > To post a message email: RPG400-L@midrange.com
> > > > > > To subscribe, unsubscribe, or change list options,
> > > > > > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> > > > > > or email: RPG400-L-request@midrange.com
> > > > > >
> > > > >
> > > > >
> > > > > _________________________________________________________
> > > > > Do You Yahoo!?
> > > > > Get your free @yahoo.com address at http://mail.yahoo.com
> > > > >
> > > > > _______________________________________________
> > > > > This is the RPG programming on the AS400 / iSeries (RPG400-L)
> > > > mailing list
> > > > > To post a message email: RPG400-L@midrange.com
> > > > > To subscribe, unsubscribe, or change list options,
> > > > > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> > > > > or email: RPG400-L-request@midrange.com
> > > > _______________________________________________
> > > > This is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing
> > > list
> > > > To post a message email: RPG400-L@midrange.com
> > > > To subscribe, unsubscribe, or change list options,
> > > > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> > > > or email: RPG400-L-request@midrange.com
> > > >
> > >
> > >
> > > _________________________________________________________
> > > Do You Yahoo!?
> > > Get your free @yahoo.com address at http://mail.yahoo.com
> > >
> > > _______________________________________________
> > > This is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing
> > list
> > > To post a message email: RPG400-L@midrange.com
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> > > or email: RPG400-L-request@midrange.com
> > _______________________________________________
> > This is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing list
> > To post a message email: RPG400-L@midrange.com
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> > or email: RPG400-L-request@midrange.com
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.