Re: Ability to see Embedded SQL Select Statement

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

And Rob, if you are using a recent version of RDi, and your source 
member is an SQLRPGLE type, you can select the statement and go to the 
Source menu - there's and item there to "Launch Run SQL Scripts".

Once in there, you can use CREATE VARIABLE statements to set values for 
your various host variables - you have to take the colons out of the 
statement but can use the same names, at least I've been able to.

This is a really really very cool feature!!

Cheers
Vern

On 12/11/2018 2:18 PM, Robert Rogerson wrote:
> Hi Kim, I'm not sure if this helps or I misunderstood your requirement.
>
> Personally, when I'm modifying a large sql statement I copy the 
> statement into RunSql scripts and modify the sql there.  That will 
> tell me right away if I have formatting/syntax  errors and also I can 
> confirm that  I am getting the results I'm expecting.
>
> HTH,
>
> Rob
>
> On 12/11/2018 2:43 PM, Fry, Kim wrote:
> > Hi everyone,
> >
> > The program that I currently updating contains the following SQL 
> > statement.
> >
> > exec sql
> >    DECLARE housesBefore CURSOR for
> >      SELECT ALAJNB,
> >             ALBHNB,
> >             ALBFCD,
> >             ALBGCD,
> >             ALBKCD,
> >             ALBLTX,
> >             ALBMTX,
> >             ALBNTX
> >        FROM homrtv01
> >        WHERE ((upper(ALBFCD) < :@AddrLoc) or
> >              (upper(ALBFCD) = :@AddrLoc and
> >              upper(ALBGCD) < :@Fraction) or
> >              (upper(ALBFCD) = :@AddrLoc and
> >              upper(ALBGCD) = :@Fraction and
> >              upper(ALBKCD) <= :@Apartment)) and
> >              upper(ALBHCD) = :@PreDirect and
> >              upper(ALBITX) like '%' || trim(:@StrWord1) || '%' and
> >              upper(ALBITX) like '%' || trim(:@StrWord2) || '%' and
> >              upper(ALBITX) like '%' || trim(:@StrWord3) || '%' and
> >              upper(ALBITX) like '%' || trim(:@StrWord4) || '%' and
> >              upper(ALBITX) like '%' || trim(:@StrWord5) || '%' and
> >              upper(ALBJTX) = :@City and
> >              upper(ALBICD) = :@State and
> >              ALBTST = ' '
> >        ORDER BY upper(ALBFCD) desc,
> >                 upper(ALBGCD) desc,
> >                 upper(ALBKCD) desc;
> >
> >
> > exec sql open housesBefore;
> >
> > dou sqlstt <> '00000';
> >
> >    exec sql
> >      fetch housesBefore into :houseData;
> >
> >    if sqlstt = '00000';
> >
> >
> > The SQL statement works correctly, but I need to add additional 
> > selection criteria information into it but I've been encountering 
> > issues with the formatting of the select statement and was wondering 
> > if there is a way through debug to see the actual SQL statement that 
> > is being created so that I can determine where the formatting problem 
> > is so that I can fix it.
> >
> > I'm using STRDBG command to view the variables in the program.
> >
> >
> > I've worked on SQLRPGLE programs where I've build the select 
> > statement from scratch and then declared it to a cursor. For example:
> > Prepare sel from :selectStmt
> > Declare c1 cursor for sel
> >
> >
> > With this method I was able to debug my SQL statement that was built 
> > by using the EVAL on the selectStmt variable.
> >
> > Any help on how to display the select statement that is being built 
> > without having to update the current program to move the select 
> > builds into a variable would be appreciated.
> >
> > Thank you
> >
> >   _____
> > Kimberly Fry