Re: SQL with concatenate

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.