Re: SQL with concatenate

The folks that alluded to the || being the problem with translation were on to something. The problem was not "||" vs. CONCAT, but rather the web server itself did not have the correct CCSID set for it. ( this LPAR was set at the IBM default of 65535 ) Once that was discovered, the web server was set at "037" and all is well. This has also made us go back and look at the tables in the application to specifically set the CCSID on those during creation.

Chuck, this goes back to your point as well to be sure the results were really not not null. In fact it was due to translation issues. The reason it worked from the command line/Squirrel/iNav SQL was the CCSID was set at the job level.

Thanks to all for the help!

Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects

On 7/8/13 2:02 PM, CRPence wrote:

On 08 Jul 2013 06:31, Jim Oberholtzer wrote:

><<SNIP>>
>SELECT
> FRBKNBR as REQ
>, FRBKRQST as RQST
>, (FRBKNBR || ' - ' || FRBKRQST) as DESCRIPTION
>
>We get REQ and RQST just fine, but DESCRIPTION comes back as null
>
>SQL gurus: Any thoughts?
>

Might be worthwhile to verify with the DB that the NULL value indeed
is the result of the expression versus possibly instead that the result
might be [an empty string or] something else; both IFNULL and LENGTH
functions can assist. Additionally of possible worth, to determine what
the explicit cast to character effects for the values of the two
columns, because the expression using the CONCAT would implicitly effect
casting of those column references to CHAR or VARCHAR. Also try
explicitly casting the concatenation expression to CHAR vs VARCHAR. e.g.:

SELECT
char(FRBKNBR) as REQ , varchar(FRBKNBR) as REQ
, char(FRBKRQST) as RQST , varchar(FRBKRQST) as RQST
, ifnull(FRBKNBR || ' - ' || FRBKRQST, '*NullValue') as DESCRIPTION
, length(FRBKNBR || ' - ' || FRBKRQST) as DESClen
, char (FRBKNBR || ' - ' || FRBKRQST) as DESCchar

As alluded above, the result of the concatenation expression will be
VARCHAR vs CHAR, so there may be an issue with how the web interface [as
noted in other replies] is dealing with the result of a variable length
expression vs how the the other column data types are being processed
[apparently successfully]. For example if the web interface is
incorrectly interpreting the data for the expression as/string/ data
such that the null-character [i.e. the byte hex zero; x'00'] is seen as
the string terminator, then that interface might be manifesting every
result as the empty string because the first character of the VARCHAR is
very likely always the string terminator character.

-- Regards, Chuck
--