Re: How to force select to return exactly one row

Martin,

>Also I too am confused by "empty row". Are you trying to loop through the
>results in code and it fails if there are no rows at all?
Or some other equally odd thing? =)
>Anyway here is an example UNION that I think would work (but note, this row
>will always be included even when your statement returns something, so it
>might not work for you).
>(YOUR SELECT HERE)
>UNION
(SELECT '','',1,1,perfectly_matched_datatype_cols_here); --those first
couple are just examples
>Mind you, I think this is nasty and would highly suggest taking another
>look at the code that is using this statement to see if you can deal more
>gracefully with an empty resultset.

Returned row is used to enter report parameters, search conditions etc. in
dialog forms where exactly one row should be
present always.
Code is simpler if it can assume that single row is always returned: in this
case it can generate only update statement.
Otherwize separate branch should check for insert or update clause. This
makes app code complicated.

I changed appl code to:

1. Execute original select statement.
2. If it returns no rows, add one row:

insert into ko (primarykey) ('primarykeyvalue');

3. Re-execute original select statement.

This requires 3 database calls from application and two times to execute
query.

How to implement this using single db call and execute query only once ?

if it possible to use

CREATE TEMP TABLE temp AS
original_select ON COMMIT DROP;

IF (SELECT COUNT(*) FROM temp) =0 THEN
INSERT INTO temp DEFAULT_VALUES;
ENDIF;

SELECT * FROM temp;

Andrus.