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."
midrange.com
