Re: Syntax for cast?

On 29-Jul-2016 09:29 -0500, Hoteltravelfundotcom wrote:

this one is failing,

Failing how?

it's SQL Server syntax,

This is a forum for IBM i related topics. Would not a question about SQL Server syntax, better be directed to a forum about that product?

I want to process only the previous week's sales.

SELECT `OEINH2`.`IHDOCD`
, `OEINH2`.`IHINV#`
, `ADRES1`.`ADINTA`
FROM
ASTDTA`.`OEINH2` `OEINH2`
INNER JOIN `ASTDTA`.`ADRES1` `ADRES1`
ON `OEINH2`.`IHGRC#` = `ADRES1`.`ADENT#` + `ADRES1`.`ADSFX#`
WHERE
CAST(CONVERT(VARCHAR, `OEINH2`.`IHDOCD`) AS DATE)
BETWEEN dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) -1
AND dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 6
ORDER BY `OEINH2`.`IHINV#`

The following syntax is valid DB2 for i SQL, essentially having changed *only* all of the "`" characters to '"' characters, and adding that missing delimiter on the first identifier for the first table-reference. Despite being syntactically correct, there is no implication that the function invocations referenced as expressions in the SELECT query are available or even valid.

SELECT "OEINH2"."IHDOCD"
, "OEINH2"."IHINV#"
, "ADRES1"."ADINTA"
FROM
"ASTDTA"."OEINH2" "OEINH2" /* Added a missing " */
INNER JOIN "ASTDTA"."ADRES1" "ADRES1"
ON "OEINH2"."IHGRC#" = "ADRES1"."ADENT#" + "ADRES1"."ADSFX#"
WHERE
CAST(CONVERT(VARCHAR, "OEINH2"."IHDOCD") AS DATE)
BETWEEN dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) -1
AND dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 6
ORDER BY "OEINH2"."IHINV#"

Nonetheless, I offer a syntax diagram for the /expression/ referred to as the CAST specification [despite being little different in usage and appearance from the built-in scalar functions], for the DB2 for i SQL, can be found here:

IBM i 7.1->Database->Reference->SQL reference->Language elements->Expressions->CAST specification
[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzcast.htm]