Re: SQL equivalent for DDS?

FWiW, A quick test with real data [especially negative] would easily show that the "Retrieved SQL source" as built by the v5r3m0 QSQGNDDL API is incorrect for its JOIN ON expression [beyond the incorrect column expression already discussed in other replies]. What the DDS LF effects for CONCAT of numeric is *not* what that SQL might imply. If that defect still exists on newer releases, hopefully somebody would report it; as with the column expression in the SELECT list. And hopefully the SQE does not make the same error in mapping that DDS definition; e.g. if such support exists in query rewrite, index\key definitions, or ??.

IIRC the effect of the CONCAT is documented more like the following SQL expression, which seems with a test, to act more like the DDS LF:

cast( int(sign(arfbrs))
* decimal(digits(arfbex) concat digits(arfbln)
concat digits(arfbrs) , 9)
as numeric(9, 0) )

Regards, Chuck

On 03 Oct 2012 18:45, Dennis Lovelady wrote:

This got me wondering how IBM would do it. So I dusted off my
RTVSQLSRC command, put a couple of files together, and documented the
results here:
http://code.midrange.com/0f2a9ba153.html

R RPFILE2
ARFBEX 3S 0
ARFBLN 5S 0
ARFBRS 1S 0
...
R JOINED JFILE(PFILE1 PFILE2)
J JOIN(PFILE1 PFILE2)
JFLD(U01095 ARFBEXLNRS)
ARFBEXLNRS CONCAT(ARFBEX ARFBLN ARFBRS)
...
FROM <lib>/PFILE1 AS Q01 INNER JOIN <lib>/PFILE2 AS Q02
ON ( Q01.U01095 = CAST ( Q02.ARFBEX CONCAT
Q02.ARFBLN CONCAT
Q02.ARFBRS AS NUMERIC(9, 0) ) )

In an experimental study for my research project on substituting a
join for a PF:

I have the following in a join logical's DDS:
. . .
JFLD(U01095 ARFBEXLNRS) . . .
ARFBEXLNRS CONCAT(ARFBEX ARFBLN ARFBRS)
. . .

where U01095 is a zoned decimal field in our "stub" PF, and
ARFBEX, ARFBLN, and ARFBRS are zoned decimal fields in the "native"
file, whose values, concatenated together, match up with U01095.

How would I do the equivalent in an SQL join view?