Razzak, Dennis
thank you for your help.
Best regards
Armin
> From: [email protected]
> To: [email protected]
> Date: Wed, 16 Jul 2014 13:02:41 -0500
> Subject: [RBASE-L] - Re: UNION SELECT ....
>
> Correction:
>
> If you use distinct in your syntax you get weird results, even in the SELECT
>
> So my tip is the way to make sure your select returns the desired data before
> you commit it to a choose command.
>
>
>
> Dennis McGrath
> Software Developer
> QMI Security Solutions
> 1661 Glenlake Ave
> Itasca IL 60143
> 630-980-8461
> [email protected]
>
> -----Original Message-----
> From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath
> Sent: Wednesday, July 16, 2014 12:39 PM
> To: RBASE-L Mailing List
> Subject: [RBASE-L] - Re: UNION SELECT ....
>
> 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
> [email protected]
> -----Original Message-----
> From: [email protected] [mailto:[email protected]] 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!
> --
>
>
