|
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 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.
midrange.com
