RE: SQL QUERY

Hi Mike Krebs,

Thanks a lot for your valuable suggestions
I got my solution and its working fine.

Best Regards,
Srinivas






-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Krebs
Sent: Friday, August 24, 2007 4:29 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL QUERY

One more problem I can able to print only one record

You still have more problems than one, but glad you made it this far.

Code:

FDSPPF IF E K DISK

When you use SQL to do your file input, you wouldn't ordinarily code an
F
spec for the file. SQL doesn't use it and you aren't using it in your
program. If you need the fields defined for some other purpose, use and
externally described data structure.

DDAT1 DS
DBYY 1 2
DBMM 4 5
DBDD 7 8
C*
DDAT2 DS
DEYY 1 2
DEMM 4 5
DEDD 7 8
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

As explained in other posts, this probably is not doing what you expect
if
you are using different years or months. It looks like you are getting
YY-MM-DD from the screen. You should probably get used to using real
date
data types to do comparisons (it is MY preference over using strings or
numbers that look like dates). You could easily change the screen fields
to
real dates with something like this:
* define date fields
d fromDate s d datfmt(*iso)
d toDate s d datfmt(*iso)
* convert screen fields to dates

c eval fromDate = %date(fdate:*ymd)
c eval toDate = %date(tdate:*ymd)

Then change the file fields to real dates in the sql select (substitute
for
your select above):
SELECT NAME
FROM DSPPF
WHERE DATE(CASE
WHEN FYY > 40 THEN '19'
ELSE '20'
END
||DIGITS(FYY)
||'-'
||DIGITS(FMM)
||'-'
||DIGITS(FDD))
BETWEEN :fromDate
AND :toDate

C/EXEC SQL WHENEVER NOT FOUND GO TO DONE1
C/END-EXEC

This is largely preference and discipline, but my SQL teacher led me to
believe that "whenevers" were used to catch global errors. Most loops
(using
fetch) would check for SQLStt (SQLState) or SQLCOD (SQLCode). If it was
my
code, I would take the "10" loop off and "dow *on" (or DOU sqlstt =
'02000'
or some other looping construct). Then, after the fetch, check the
sqlstt
for end of file or other error. There is a lot of information about
SQLStt
on the web including some very good error message checks.

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 SELECT
* end of file
C WHEN %subst(sqlstt:1:2) = '02'
C LEAVE
C WHEN %subst(sqlstt:1:2) <> '00'
C 'SQLERR' DSPLY
C ENDSL

Good luck, embedded SQL can be very powerful and fun!

Mike Krebs