RE: SQL QUERY -- MIDRANGE-L

Hi Sriniva

First of all you need to tell the program to move on a line after you write
a line to QPRINT. In the O-specs you can do this with the "Space After"
value which is positions 40-42. Simply adding a '1' in position 42 will
work as long as you don't overflow the page, so you need a mechanism to
start a new page when the page is full.

I can see that you're only going to write 10 records so you don't really
need the overflow processing, but I'll show you how its done in case you
need it in the future.

To do this you can add a second Except output spec that "skips" to line 1
(positions 46 to 48) before printing the line. The code should look
something like this:

O* Position to first line of page...
OQPRINT E TOPOFPAGE 1
O 1 ' '

O* Report detail line, we move on 1 line after writing...
OQPRINT E RECB 1
O NAME1

You now need to tell the program that you want to check for page overthrows
on QPRINT, so add the OFLIND keyword to the QPRINT File spec:

F* Assign indicator OF as the page overthrow flag...
FQPRINT O F 132 PRINTER OFLIND(*INOF)

Here you're telling the program you want to use indicator OF to flag for
page overthrow. You can code your own named indicator variable or any of
the standard 99 indicators if you wish.

You don't need the next bit, but if the first line was going to be anywhere
other than line 1 of the page then you would code an EXCEPT statement for
the "TOPOFPAGE" exception statement in the O spec:

C* Position to the top of the page...
C EXCEPT TOPOFPAGE

C DOW I <= 10

Finally, just before you do your EXCEPT RECB inside your loop, you would
check for a page overthrow and if found do another exception of the
TOPOFPAGE:

C* Check for page overflow and if needed, start new page...
C If *InOF
C Except Line1
C EndIf

C EXCEPT RECB

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Srinivas Boggula
Sent: 23 August 2007 14:43
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

----------------------------------------------------------------DISCLAIMER--
-------------------------------------------------------
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