RE: SQL QUERY

["Lunde, Peter" <plunde@xxxxxxxxxxxxxxxxxx>]

Srinivas,

Did I see a GO TO in that program ?

Peter Lunde
Manager, IT
WellSpring Pharmaceutical Canada Corp
400 Iroquois Shore Rd
Oakville, ON, L6H 1M5
PLunde@xxxxxxxxxxxxxxxxxx
Phone: 905-337-4505
Cell:     416-948-5863
Fax:     905-337-7239 

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Srinivas Boggula
Sent: Thursday, August 23, 2007 9:43 AM
To: Midrange Systems Technical Discussion
Subject: SQL QUERY


Hi every one,

Thanks a lot I got it.

One more problem I can able to print only one record But I want to print
all the records, how do I put QPRINT in a loop So that I can print all
the records below is the code for your reference.

Please can any one guide me in this regard?

Best Regards,
Srinivas



Code:

     FDSPPF     IF   E           K DISK
     FDSPDATE   CF   E             WORKSTN
     FQPRINT    O    F  132        PRINTER
     D*
     DDAT1             DS
     DBYY                      1      2
     DBMM                      4      5
     DBDD                      7      8
     C*
     DDAT2             DS
     DEYY                      1      2
     DEMM                      4      5
     DEDD                      7      8
     C*
     DINVDETL          DS
     DNAME1                    1     20
     C*
     C                   EXFMT     FIRST
     C                   MOVE      FDATE         DAT1
     C                   MOVE      TDATE         DAT2
     C*
     C/EXEC SQL
     C+ DECLARE A DYNAMIC SCROLL CURSOR FOR SELECT NAME FROM DSPPF WHERE
     C+ FYY <= :BYY AND FYY >= :EYY AND FMM <= :BMM AND FMM >= :EMM AND
     C+ FDD <= :BDD AND FDD >= :EDD
     C/END-EXEC
     C*
     C/EXEC SQL
     C+ OPEN A
     C/END-EXEC
     C*
     C/EXEC SQL WHENEVER NOT FOUND GO TO DONE1
     C/END-EXEC
     C*
     C                   Z-ADD     1             I                 2 0
     C                   DOW       I <= 10
     C/EXEC SQL
     C+ FETCH NEXT FROM A INTO :NAME1
     C/END-EXEC
     C*
     C                   EVAL      I = I+1
     C                   EXCEPT    RECB
     C                   END
     C     DONE1         TAG
     C*
     C/EXEC SQL
     C+ CLOSE A
     C/END-EXEC
     C                   SETON                                        LR
     OQPRINT    E            RECB
     O                       NAME1


-Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Tuesday, August 21, 2007 8:27 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL QUERY

Srinivas,

I usually start with an interactive SQL session to test my assumptions.

I can't quite tell from your post but if the year month and day are all
two digit numeric fields, then you'll need to parse out similar fields
from your prompt and then do the compare.  I'll assume you know how to
pass the parameters to the RPG and then parse out the relevant year
month and date.

host variables are pbmm,pbdd,pbyy (for the parameters for the beginning
date) and pemm,pedd,peyy (for the parameters for the ending date).  I'll
also assume that your date fields in the file are fmm,fdd,fyy

That said, the SQL should look something like:

select name from myfile where fyy>=byy and fyy <= eyy and fdd >= bdd and
fdd >= edd and fmm >= bmm and fmm <= emm. 

You could use the "between" keyword as well.  You could concatenate the
fields in the file and compare them. There are many ways to do this and
there are probably more and less efficient ways to do it.  However, I
recommend that you use interactive SQL to test your assumptions and then
copy/paste that into your code and add your parameters as host
variables.

Pete Helgren

Srinivas Boggula wrote:
> Hi every one,
>
> My client has an urgent requirement he needs a sample report.
>
> I have a physical file with 4 fields 1) YEAR 2) MONTH 3) DAY 4) NAME 
> With length of 2.
>
> He wants to generate a report based on selection criteria.
>
> For example: In display file I have 2 fields.
>
>                    From date: 08/01/2007
>                    
>                    To date   : 08/21/2007
>
> He wants to know the NAME which fall under these days
>
> I used SQLRPG to retrieve the data but I failed to do.
>
> Can any one please send the code or give guidance.
>
>
> Thanks in Advance.
>
> Best Regards,
> Srinivas







----------------------------------------------------------------DISCLAIM
ER---------------------------------------------------------
Information transmitted by this EMAIL is proprietary to iGATE Group of
Companies and is intended for use only by the individual or entity to
whom it is addressed and may contain information that is privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient of this EMAIL immediately notify the sender
at iGATE or mailadmin@xxxxxxxxx and delete this EMAIL including any
attachments


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.