Re: Need to concat on a query against the ibm i

On 01-Aug-2016 20:04 -0500, Hoteltravelfundotcom wrote:

On Mon, Aug 1, 2016 at 8:59 PM, Hoteltravelfundotcom wrote:

I would like to make a join as follows:

SELECT
"OEINH2"."IHDOCD", "OEINH2"."IHINV#"
, "ADRES1"."ADINTA", "ADRES1"."ADENT#", "ADRES1"."ADSFX#"
FROM
"ASTDTA"."OEINH2" "OEINH2"
INNER JOIN
"S1047N4M"."ASTDTA"."ADRES1" "ADRES1"
ON "OEINH2"."IHENT#"="ADRES1"."ADENT#"


the inner join is not what we want.

So do not code INNER, and instead code OUTER, CROSS, or EXCEPTION.?

we want to join on a field called OEINH2.IHGRC# to a concat of

ADRES1.ADENT# + ADRES1.ADSFX#

So the desire is to concatenate those two columns, rather than to add them? If so, then use the proper operator; string-expression vs arithmetic\numeric-expression.

ON "OEINH2".IHGRC# = ("ADRES1"."ADENT#" CONCAT ADRES1.ADSFX#)

But without having divulged the DDL, use of that expression might be a very poor idea, with regard to achieving desirable results.

I am not attaining success in solving this riddle.

I tried this but it fails ' + Use not valid'

Perhaps because "The infix operators, +, -, *, /, and **, specify addition, subtraction, multiplication, division, and exponentiation, respectively."? That is, the + operand is a request to add the operands, not to concatenate.

SELECT
"OEINH2"."IHDOCD", "OEINH2"."IHINV#"
, "ADRES1"."ADINTA", "ADRES1"."ADENT#", "ADRES1"."ADSFX#"
, concat (adres1.adent# + adres1.adsfx#) as grc#

The above is syntactically correct; if the arithmetic expression is valid according to data checking, still, again, beware the effects without explicit casting -- and often DIGITS can be preferable to simple\implicit casting from numeric to character.

[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafzintro.htm]
_Expressions_
"An expression specifies a value. …
…
_With arithmetic operators_ [http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafzwarithop.htm?view=kc]
If arithmetic operators are used, the result of the expression is a number derived from the application of the operators to the values of the operands.
…
_With the concatenation operator_ [http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafzwtco.htm?view=kc]
If the concatenation operator (CONCAT or ||) is used, the result of the expression is a string.
…"

[http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafzintro.htm]
_With arithmetic operators_
"The infix operators, +, -, *, /, and **, specify addition, subtraction, multiplication, division, and exponentiation, respectively."