Re: SQL Fun

use prefixes on the external DS...

Thanks,
Tommy Holden




Robert Munday <rwmunday@xxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
05/16/2007 10:07 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
SQL Fun






I am performing some validation using SQL to determine if a value exists
in a transaction file before submitting a report program. If the value
does not exist there is no reason to run the report. I have four report
selections to test but they appear in two files... control and detail. For
example, the group number is in the control file and the order number is
in the detail file. My problem is that I have some fields with idential
names in both files. Here is what I have thus far:

* Control File
D PDTOTCTL E DS ExtName(PDTOTCTL)

* Detail File
D PDTOTDTL E DS ExtName(PDTOTDTL)

C/Exec SQL
+ Select Count(QCGRP) Into :Count From FPTOTCTL
+ Where QCGRP = :$GROUP -- Count Group Records
C/End-Exec

If Count = 0, I know I have no group records and can notify the user.

C/Exec SQL
+ Select Count(QCORD#) Into :Count From FPTOTDTL
+ Where QCORD# = :$ORDER -- Count Order Records
C/End-Exec

Same here.

How do I use two externally described files for SQL in the one program
having duplicate field names? I have tried Prefix on the D-spec,
qualifying the name in the SQL statement and defining one file in the
F-spec and renaming the duplicated fields in the I-specs. None of these
has worked so far. Any solution to this?

Thanks,


Robert Munday
Munday Software Consultants
Montgomery, AL
having SQL fun in Dublin, OH