Re: SQL with concatenate

[Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>]

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
> --