Re: SQL with concatenate

[CRPence <CRPbottle@xxxxxxxxx>]

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.