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! --
