On 18 May 2013 19:43, Crystal Reports wrote:
I wrote this stored procedure as for the sql server.
Seemed to be just a SELECT statement. No other language elements
were apparent except the apparent /variable/ reference {@TodaysDate}.
Does that make it a /procedure/ ?
I took this code and ran in on AS/400 using the STRSQL and pasting
it.It has several errors at "ON" I didn't write down the error code
and now am not at work. But looking at this syntax generally, can you
tell me which terms are used differently.
As others have pointed out, the ON-clause for the join syntax uses
logical predicates, each separated by a logical operator; albeit
possibly also parenthetically. A new ON-clause must not be started for
each new predicate; i.e. the correct "ON pred1 AND pred2" versus the
incorrect "ON pred1 AND ON pred2"
Even after that correction however:
- There is a missing logical operator in the predicates on the WHERE
clause.
- The constant [literal] specification of a [character] string is
expected to be delimited by apostrophes rather than the double quote
[except in COBOL apparently].
- As someone else noted, the parenthetical /variable/ specification is
not allowed; either a special register, a global variable name, a scalar
user-defined function, or an expression involving one of those is
probably desirable.
- As someone else noted, the use of UNION ALL seems daft, given all of
the queries are identical except allowing for one additionally included
literal value for selection.
FWiW: When a statement is /formatted/ nicely, e.g. well-aligned,
mistakes in the syntax are more conspicuous.
Also, Would it be better to create a logical file to do this, or
leave as SQL?
Depends on what is best for the requirements. Hmm... I presume the
implication for "logical file" was for a DDS LF vs a SQL VIEW LF? If
using SQL vs RLA, then choose a SQL VIEW LF over a DDS LF.
It would need to run whenever the user runs the report in Crystal for
the most current data set.
Presumably the Crystal feature is an SQL interface, so probably use
the SQL statement. But if the logic in that statement is typical for
other interfaces to want to know\extract from the files, then
encapsulate the statement in a VIEW logical file or stored procedure to
expose the logic to other interfaces, to avoid having to repeat the
statement from many interfaces.
<<SNIP six nearly identical queries combined in UNION ALL>>
The following reformatted version of the original query, is written
as just one query without any UNION ALL subqueries, having aligned the
predicates in the ON and WHERE clauses; comments inline to the WHERE
predicates are corrections for the literal delimiters, the variable
reference, the missing logical operator, and the change from the equal
predicate to the IN predicate to enable combining the effect of the
logic using a union of the data:
SELECT
NOTEH6.UARIDC
, NOTEH6.UANOTL
, NOTEP1.UCCMT
, OEORH41.OHORD#
, OEORH41.OHOSTC
, OEORH41.OHORDT
, OEORH41.OHORDD
, OEORH41.OHPO#
, OEORH41.OHSHTC
, OEORH41.OHSLR#
, OETRA95.OTTRNC
, REFER1.RFCAT
, REFER1.RFDAT
FROM OEORH41
INNER JOIN REFER1
ON OEORH41.OHSHTC = REFER1.RFSLC
INNER JOIN OETRA95
ON OEORH41.OHCOM# = OETRA95.OTCOM#
AND OEORH41.OHORD# = OETRA95.OTORD#
INNER JOIN NOTEH6
ON OEORH41.OHENT# = NOTEH6.UAENT#
AND OEORH41.OHSFX# = NOTEH6.UASFX#
INNER JOIN NOTEP1
ON NOTEH6.UANOTT = NOTEP1.UCNOTT
AND NOTEH6.UANTK = NOTEP1.UCNTK
AND NOTEH6.UANOT# = NOTEP1.UCNOT#
WHERE NOTEH6.UARIDC = 'UA'
and OETRA95.OTTRNC = '001'
/* !! Note: use special reg below !! */
and OEORH41.OHORDD = CURRENT DATE
/* !! Note: use IN predicate below !! */
and OEORH41.OHORDT IN ('COC', 'COE', 'COF', 'COR', 'COS')
/* !! Note: missing operator below !! */
AND OEORH41.OHOSTC <> 'CN' /* <-- "AND" was presumed+added */
and REFER1.RFCAT = '0023'
midrange.com
