RE: SQL Question Part 2

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

I'm not exactly sure why you get this error, but try removing the
parentheses from the file declarations. You might also try an explicit join
type. Joins behave differently depending on the type of join specified. For
example, if you join two files on loan number and loan# 123abc exists in
file1 and not in file2, by specifying INNER JOIN, the record would be
discarded and not returned. A LEFT OUTER JOIN would return the record from
file1 with the joined fields from file2 being null. An EXCEPTION JOIN would
return all the records from file1 that don't exist in file2, based upon the
join criteria in the ON clause.         I'd probably also use SMA450 BETWEEN
17 AND 31 to clarify that little bit of your WHERE. Otherwise, I can see
nothing that looks like trouble.....

hth,

Eric DeLong
Sally Beauty Company
MIS-Sr. Programmer/Analyst
940-898-7863 or ext. 1863
 


-----Original Message-----
From: Cyndi Bradberry [mailto:CyndiB@IHFA.ORG]
Sent: Wednesday, June 20, 2001 11:31 AM
To: 'Midrange-L@midrange. com' (E-mail)
Subject: SQL Question Part 2


We worked out the problem with the ISO dates, and have moved on to another
program.  I am trying to Join together 3 files with a common field (LOAN
NUMBER).  This Select statement works in ISQL, but I get this error when I
try to put it into a program:

 MSG ID  SEV  RECORD  TEXT

 SQL0199  30      61  Position 34 Keyword JOIN not expected. Valid tokens: )
,.    

    57        C/Exec SQL

    58        C+ Declare MasterTable Cursor

    59        C+   For Select M1LOAN, SMA450, SMA240

    60        C+              A1FNAM, A1LNAM, A1HARA, A1HPFX, A1HNBR,

    61        C+   From ( LDATAQ1C/SRVMLD JOIN LDATAQ1C/SRVALT1 On

    62        C+    M1LOAN = A1LOAN )  Join LDATAQ1C/SRVDSRLZZ On

    63        C+    A1LOAN = CATL#

    64        C+   Where SMA240 > 0 and

    65        C+         (SMA450 >= 17 and SMA450 <= 31) and

    66        C+         A1SEQ = 01

    67        C+   Order By A1LNAM

    68        C+   For Fetch Only         

Can anyone help me to see where I have gone wrong ?  I really can't see the
forest for the trees.  We are at V4R4, current on CUM and Group PTF's (Just
applied a bunch of SQL PTF's this morning ;-).

Cyndi B.
Boise, ID

PS:  Is there anyone else from Boise/Nampa/Caldwell on the list ?  Direct
mail please !                                       
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---