'AND ON' is incorrect.
AND ON OEORH41.OHSFX# = NOTEH6.UASFX#
Should just be 'AND'
AND OEORH41.OHSFX# = NOTEH6.UASFX#
Paul
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Crystal Reports
Sent: Saturday, May 18, 2013 9:43 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Differences in SQL syntax
HI I wrote this stored procedure as for the sql server. I took this code and
ran in on AS/400 using the STRSQL and pasting it. It has several errors at
"ON" I didnt' 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.
Also, Would it be better to create a logical file to do this, or leave as
SQL. It would need to run whenever the user runs the report in Crystal for
the most current data set.
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 ON OEORH41.OHORD# = OETRA95.OTORD#
INNER JOIN NOTEH6 ON OEORH41.OHENT# = NOTEH6.UAENT# AND ON OEORH41.OHSFX# =
NOTEH6.UASFX# INNER JOIN NOTEP1 ON NOTEH6.UANOTT = NOTEP1.UCNOTT AND ON
NOTEH6.UANTK = NOTEP1.UCNTK AND ON NOTEH6.UANOT# = NOTEP1.UCNOT# WHERE
NOTEH6.UARIDC = "UA" and OETRA95.OTTRNC = "001" and OEORH41.OHORDD =
{@TodaysDate} and OEORH41.OHORDT = "COC"
OEORH41.OHOSTC <> "CN" and
REFER1.RFCAT = "0023"
UNION ALL
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 ON OEORH41.OHORD# = OETRA95.OTORD#
INNER JOIN NOTEH6 ON OEORH41.OHENT# = NOTEH6.UAENT# AND ON OEORH41.OHSFX# =
NOTEH6.UASFX# INNER JOIN NOTEP1 ON NOTEH6.UANOTT = NOTEP1.UCNOTT AND ON
NOTEH6.UANTK = NOTEP1.UCNTK AND ON NOTEH6.UANOT# = NOTEP1.UCNOT# WHERE
NOTEH6.UARIDC = "UA" and OETRA95.OTTRNC = "001" and OEORH41.OHORDD =
{@TodaysDate} and OEORH41.OHORDT = "COE"
OEORH41.OHOSTC <> "CN" and
REFER1.RFCAT = "0023"
UNION ALL
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 ON OEORH41.OHORD# = OETRA95.OTORD#
INNER JOIN NOTEH6 ON OEORH41.OHENT# = NOTEH6.UAENT# AND ON OEORH41.OHSFX# =
NOTEH6.UASFX# INNER JOIN NOTEP1 ON NOTEH6.UANOTT = NOTEP1.UCNOTT AND ON
NOTEH6.UANTK = NOTEP1.UCNTK AND ON NOTEH6.UANOT# = NOTEP1.UCNOT# WHERE
NOTEH6.UARIDC = "UA" and OETRA95.OTTRNC = "001" and OEORH41.OHORDD =
{@TodaysDate} and OEORH41.OHORDT = "COF"
OEORH41.OHOSTC <> "CN" and
REFER1.RFCAT = "0023"
UNION ALL
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 ON OEORH41.OHORD# = OETRA95.OTORD#
INNER JOIN NOTEH6 ON OEORH41.OHENT# = NOTEH6.UAENT# AND ON OEORH41.OHSFX# =
NOTEH6.UASFX# INNER JOIN NOTEP1 ON NOTEH6.UANOTT = NOTEP1.UCNOTT AND ON
NOTEH6.UANTK = NOTEP1.UCNTK AND ON NOTEH6.UANOT# = NOTEP1.UCNOT# WHERE
NOTEH6.UARIDC = "UA" and OETRA95.OTTRNC = "001" and OEORH41.OHORDD =
{@TodaysDate} and OEORH41.OHORDT = "COR"
OEORH41.OHOSTC <> "CN" and
REFER1.RFCAT = "0023"
UNION ALL
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 ON OEORH41.OHORD# = OETRA95.OTORD#
INNER JOIN NOTEH6 ON OEORH41.OHENT# = NOTEH6.UAENT# AND ON OEORH41.OHSFX# =
NOTEH6.UASFX# INNER JOIN NOTEP1 ON NOTEH6.UANOTT = NOTEP1.UCNOTT AND ON
NOTEH6.UANTK = NOTEP1.UCNTK AND ON NOTEH6.UANOT# = NOTEP1.UCNOT# WHERE
NOTEH6.UARIDC = "UA" and OETRA95.OTTRNC = "001" and OEORH41.OHORDD =
{@TodaysDate} and OEORH41.OHORDT = "COS"
OEORH41.OHOSTC <> "CN" and
REFER1.RFCAT = "0023"
UNION ALL
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 ON OEORH41.OHORD# = OETRA95.OTORD#
INNER JOIN NOTEH6 ON OEORH41.OHENT# = NOTEH6.UAENT# AND ON OEORH41.OHSFX# =
NOTEH6.UASFX# INNER JOIN NOTEP1 ON NOTEH6.UANOTT = NOTEP1.UCNOTT AND ON
NOTEH6.UANTK = NOTEP1.UCNTK AND ON NOTEH6.UANOT# = NOTEP1.UCNOT# WHERE
NOTEH6.UARIDC = "UA" and OETRA95.OTTRNC = "001" and OEORH41.OHORDD =
{@TodaysDate} and OEORH41.OHORDT = "COT"
OEORH41.OHOSTC <> "CN" and
REFER1.RFCAT = "0023"
Thank you,
Paul
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
