[RBASE-L] - Re: UNION SELECT ....

[Dennis McGrath]

Choose may not accept a "UNION" syntax but
choose will accept a properly formed "UNION ALL" statement.

Here is one I use routinely.

set var vTakenByDefault = 'CEM'

  CHOOSE vTmp FROM #VALUES +
    FOR (LastName + ',' & FirstName ),(CTXT(.vTakenByDefault)) +
    FROM SalesmanList WHERE DSlsMn = .vTakenByDefault +
    UNION ALL SELECT SalesMan,(CTXT(DSlsMn)) FROM SlsMenu +
        CAPTION 'Select TakenBy. ESC to abort' +
        LINES 20 FORMATTED

This may work for you:

Choose vPick1 FROM #VALUES FOR DISTINCT FilName FROM FILIALEN +
UNION ALL SELECT '---everybody---' FROM FILIALEN WHERE COUNT = 1

Tip:
When designing CHOOSE,  to reduce development time, create a SELECT statement 
first to test.

SELECT DISTINCT FilName FROM FILIALEN +
UNION ALL SELECT '---everybody---' FROM FILIALEN WHERE COUNT = 1

When it is working, remove SELECT and paste after the FOR in your choose 
command 

Dennis McGrath
Software Developer
QMI Security Solutions
1661 Glenlake Ave
Itasca IL 60143
630-980-8461
dmcgr...@qmiusa.com
-----Original Message-----
From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On Behalf Of A. Razzak Memon
Sent: Wednesday, July 16, 2014 12:08 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: UNION SELECT ....

At 11:59 AM 7/16/2014, Armin Thoma wrote:

>I know how to use CHOOSE to show the option '--- everybody---' in addition
>
>Choose vPick1 FROM #VALUES FOR DISTINCT FilName FROM FILIALEN +
>UNION SELECT '---everybody---' FROM FILIALEN WHERE COUNT = 1
>So I tried the same technic within the WHERE clause of a Variable Lookup
>Combo Box or Variable Lookup List Box - no chance.
>
>Ok, you could define a TempTab ....., but I'm sure there is a better
>possibility already published in the list?


Armin,

By design and by definition, the CHOOSE command does not support the UNION
command.

However, you can create a dynamic TEMPORARY VIEW to accomplish the desired
results.

Here's an example based on RRBYW18 sample database ...

-- Dynamic Data For CHOOSE Command
-- Author: A. Razzak Memon
-- Date Created: July 16, 2014
-- Last Updated:
    CONNECT RRBYW18
    SET ERROR MESSAGE 677 OFF
    DROP VIEW DataForCHOOSE
    SET ERROR MESSAGE 677 ON
    CREATE TEMPORARY VIEW `DataForCHOOSE` +
    (InvoiceNum, InvoiceTot) +
    AS SELECT t1.TransID,(SUM(t2.Price)) +
    FROM InvoiceHeader t1,InvoiceDetail t2 +
    WHERE t2.TransID = t1.TransID +
    GROUP BY t1.TransID +
    UNION SELECT t3.TransID,$0.00 FROM InvoiceHeader t3 +
    WHERE NOT EXISTS +
    (SELECT TransID FROM InvoiceDetail WHERE InvoiceDetail.TransID = 
t3.TransID)
    COMMENT ON VIEW `DataForCHOOSE` IS 'Sample Union View for CHOOSE Command'
    SET VAR vInvoiceNumTxt TEXT = NULL
    SET VAR vInvoiceNum INTEGER = NULL
    CLS
    CHOOSE vInvoiceNumTxt FROM #VALUES FOR +
    ((LJS((CTXT(InvoiceNum)),12))&(RJS((CTXT(InvoiceTot)),14))),InvoiceNum +
    FROM DataForCHOOSE +
    CHKBOX 1 TITLE 'Select Invoice' +
    CAPTION 'Customer Invoices' LINES 30 FORMATTED +
    OPTION WINDOW_BACK_COLOR [R245,G222,B179] +
    |LIST_FONT_COLOR NAVY +
    |LIST_BACK_COLOR [R245,G222,B179] +
    |TITLE_BACK_COLOR [R245,G222,B179] +
    |TITLE_FONT_NAME Tahoma +
    |TITLE_FONT_COLOR NAVY +
    |TITLE_FONT_SIZE 12 +
    |BUTTONS_SHOW_GLYPH ON +
    |BUTTONS_BACK_COLOR WHITE
IF vInvoiceNumTxt IS NULL OR vInvoiceNumTxt = '[Esc]' THEN
    GOTO Done
ELSE
    SET VAR vInvoiceNum = (INT(.vInvoiceNumTxt))
ENDIF
    -- Do what you have to do here with vInvoiceNum variable
LABEL Done
    DROP VIEW DataForCHOOSE
    CLEAR VAR vInvoiceNumTxt,vInvoiceNum
    CLS
    RETURN

Hoffe, dass Sie dies hilfreich!

Very Best R:egards,

Razzak.

www.rbase.com
www.facebook.com/rbase
-- 
31 years of continuous innovation!
16 Years of R:BASE Technologies, Inc. making R:BASE what it is today!
--