RE: SQL QUERY

["Srinivas Boggula" <Srinivas.Boggula@xxxxxxxxx>]

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