Off Topic: OpenOffice.org Base HSQLD Help

[Larry Hartman]

On Tuesday 01 January 2008 03:09:09 am Larry Hartman wrote:
> I have a table that holds generic personal info:
>
> TABLE1:
> AUTHID: INTEGER auto-indexed, key
> LNAME: VARCHAR
> FNAME: VARCHAR
> MNAME: VARCHAR
>
> I have LISTBOX1 in a subform that uses QUERY1 for listing contents.
>
> QUERY1 has this HSQLDB code in it:
>
> SELECT CASE WHEN "MNAME" <> '' THEN CONVERT("AUTHID",
> VARCHAR) || '-' || "LNAME" || ', ' || "FNAME" || ' ' || "MNAME" ELSE
> CONVERT("AUTHID", VARCHAR) || '-' || "LNAME" || ', ' || "FNAME" END AS
> "NAME" FROM "TBL-AUTHORS"
>
> The HSQLDB code converts the "AUTHID" INTEGER to VARCHAR and concatenates
> it with LNAME, FNAME, and MNAME and places the results into a single
> VARCHAR field NAMEID.  The codes has a test to determine if MNAME is empty.
>  I tested QUERY1 by itself and it appears functional.
>
> The LISTBOX1 is intended to take output from QUERY1 for its list.  When
> selected information will go into NAMEID field of TABLE2.
>
> Despite the fact that QUERY1 works by itself, LISTBOX1 will not load its
> list when pointed to QUERY1 as its list source.  Neither will it update its
> list if I use the code from QUERY1 as an SQL [Native] statement.  I have
> also tried making a table view of QUERY1 as TABLE3.  When I set LISTBOX1 to
> source its list to TABLE3, I still get an empty list.
>
> Any ideas what I am missing here?
>
> Larry

As an answer to my earlier question above for anyone looking to load a single 
column list box with data from multiple columns.  I placed the code below in 
a BASE Query, then assigned that query as the source of the listbox.

SELECT COALESCE( "TBL-AUTHORS"."LNAME", '' ) + CHAR( 44 ) + CHAR ( 32 ) + 
COALESCE( "TBL-AUTHORS"."FNAME", '' ) + CHAR( 32 ) + 
COALESCE( "TBL-AUTHORS"."MNAME", '' ) AS "NAMEID", "AUTHID" 
FROM "TBL-AUTHORS" ORDER BY "NAMEID" ASC

The listbox shows a singel column with all three fields LNAME, FNAME, and 
MNAME.  The AUTHID field is not shown but loaded.  The bound property of the 
Listbox is set to "1" and looks toward "AUTHID" for the listbox value when 
selection is made.  Listbox apparently does not recognize any SQL concatenate 
function statements....I don't know the internals well enough to understand 
why.  CHAR ( 44) is a comma, CHAR( 32 ) is a space.



Larry