× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.