A (more difficult) method to use the age ranges from their own table (separate from the other data) would be to use a correlated subquery. Then you could have "cleaner" SQL. You can find examples of this on the web.
OR I think there may be limited support for saving query formatting like discussed here: https://forum.openoffice.org/en/forum/viewtopic.php?f=61&t=39997 but I don't have any experience with how this works or its limitations. On Mon, May 9, 2016 at 12:00 AM, Gary Dale <garyd...@torfree.net> wrote: > Thanks Bruce. I appreciate the help. I was trying values from a table that > translates age ranges into text. I hate hard coding numbers, let alone the > number of ranges, because I handle events for two different groups who have > their own definitions. They used to be encoded in the different > certificates... > > My sql was > > SELECT "results".*, "Categories".* FROM { oj "results" LEFT OUTER JOIN > "Categories" ON "results"."age" BETWEEN "Categories"."low" AND > "Categories"."high" } > > which doesn't work but does work when I use "results"."age" = > "Categories"."low", leading to my complaint that BETWEEN doesn't work. > > Your use of a CASE statement with just the one table looks like it will do > the trick, even if the code will be ugly. I'll need 2 different CASE > statements with a total of 8 different cases to do the translations. > > > > On 08/05/16 09:43 PM, Bruce Hohl wrote: > >> Gary, >> See attached for a working case when in LO Base. You do have to be very >> careful when using Base Queries (SQL) that everything is exactly correct >> like where commas are placed (or not) and how quotes / half quotes are >> used >> (or not). So setting this up in Base will likely take more time than in >> Calc. You will have to judge if the final result in Base is best for your >> needs. Good Luck! >> >> On Sun, May 8, 2016 at 4:31 PM, Gary Dale <garyd...@torfree.net> wrote: >> >> The first option is what I have been doing - it uses a calc file to drive >>> a mailing list. The logic is in the document, not in the calc file, so >>> that >>> I can bring in new data without a lot of effort - the raw data can just >>> be >>> a .csv file. >>> >>> The second option, using a "smart" calc file, moves the logic to the data >>> file which is what I don't want to do. It means importing the data or >>> calculation into a calc file each time. The second option, moving the >>> logic >>> into a base file seems more promising. >>> >>> However, after much wailing and gnashing of teeth, I have to question >>> whether base can do what I want. I can't get it to take a query using a >>> BETWEEN criterion (e.g., youth is AGE between 0 AND 18). >>> >>> It won't (apparently) do any queries on calc sheets. Fortunately >>> copy/pasting to create a new base table is easy enough. Unfortunately >>> without being able to match the main table (containing participant >>> information) against age categories, etc., it's not much good. >>> >>> I suppose I can use a brute force technique and create a table row for >>> every possible age (for example) but that seems like a ludicrous kludge >>> when the basic problem is that Writer's form letter functions can't >>> compare >>> numbers. >>> >>> >>> On 28/04/16 09:58 PM, Bruce Hohl wrote: >>> >>> Two more ideas for this problem: (1) Do not use a Calc file - see >>>> LO_Labels_howto.odt, (2) Move the logic from Writer into either Calc or >>>> a >>>> Base query. While I can't explain how this might work for your >>>> Certificate >>>> mail merge I can show you through the attached examples how I used these >>>> two ideas to solve my mail merge problem. Good Luck and I hope you can >>>> make this work for you in LO. >>>> >>>> On Wed, Apr 27, 2016 at 9:17 PM, Gary Dale <garyd...@torfree.net >>>> <mailto: >>>> garyd...@torfree.net>> wrote: >>>> >>>> I can browse to a spreadsheet directly without first attaching it >>>> to a database, but that's just a kludge. LibreOffice creates the >>>> base file when you select the spreadsheet, which brings me back to >>>> the same problem. >>>> >>>> >>>> >>>> On 27/04/16 07:24 PM, Bruce Hohl wrote: >>>> >>>> Reportedly, as of LO 5.1 you can create a mail merge without a >>>> Base file - >>>> reference this: >>>> http://vmiklos.hu/blog/mail-merge-embedding.html You might >>>> try to recreate your Certificate mail merge without the Base >>>> file being >>>> careful to select cell formats for your data that work with >>>> Writer. (If >>>> you have good spreadsheet skills you could likely get the job >>>> done entirely >>>> in Calc.) >>>> >>>> On Wed, Apr 27, 2016 at 6:18 PM, Gary Dale >>>> <garyd...@torfree.net <mailto:garyd...@torfree.net>> wrote: >>>> >>>> I've tried it with Debian/Stretch v5.1.2.2.0+ and Windows >>>> 7 v5.1.3. If I >>>> was running anything old or unusual, I would have noted >>>> it. The document is >>>> simple, as is the spreadsheet and the base file that >>>> connects them. >>>> >>>> I tried changing the numbers to text but Base doesn't seem >>>> to allow that. >>>> It's decided that the columns for age and place are >>>> decimal numbers. >>>> >>>> So far as I can see, either the number comparisons operate >>>> differently >>>> from the text comparisons (which the documentation doesn't >>>> mention) or the >>>> number comparisons are broken. Normally I'd consider that >>>> unlikely, but my >>>> past experience with printing "form letters" and labels is >>>> that these areas >>>> don't get a lot of attention. >>>> >>>> >>>> On 27/04/16 03:32 PM, Bruce Hohl wrote: >>>> >>>> Can you give more details of your set-up. What is in >>>> the Writer, Calc >>>> and / or Base components of LO. >>>> >>>> On Wed, Apr 27, 2016 at 11:44 AM, Gary Dale >>>> <garyd...@torfree.net <mailto:garyd...@torfree.net> >>>> <mailto:garyd...@torfree.net >>>> <mailto:garyd...@torfree.net>>> wrote: >>>> >>>> I'm trying to print certificates for a group of >>>> athletes and need >>>> to have them reflect their standing in the event. >>>> The certificate >>>> has a number of conditional text fields, such as >>>> to translate >>>> their gender from M/F to male/female, which are >>>> working. >>>> >>>> However the ones that are doing numeric >>>> comparisons aren't. The >>>> comparison always seems to return "true" so the >>>> "then" condition >>>> is printed. For example Run Results.Race.place == >>>> 1 is the >>>> condition but the translation text is always >>>> "first place", never >>>> empty. Since I have 5 different conditions (first >>>> 3 positions, >>>> participate, supported), I get a lot of text >>>> printed that shouldn't. >>>> >>>> The slightly more complex Run Results.Race.age > >>>> 18 AND Run >>>> Results.Race.age < 60 always prints adult. At one >>>> point I had it >>>> print an else (senior) but that branch was never >>>> taken even when >>>> the age was well past 60. >>>> >>>> I've checked the Run Results.odb file (which >>>> links to a >>>> spreadsheet) and the age & place fields are both >>>> of type decimal. >>>> >>>> What am I doing wrong? >>>> >>>> -- To unsubscribe e-mail to: >>>> users+unsubscr...@global.libreoffice.org >>>> <mailto:users%2bunsubscr...@global.libreoffice.org> >>>> <mailto: >>>> users%2bunsubscr...@global.libreoffice.org >>>> <mailto:users%252bunsubscr...@global.libreoffice.org>> >>>> Problems? >>>> >>>> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ >>>> Posting guidelines + more: >>>> http://wiki.documentfoundation.org/Netiquette >>>> List archive: >>>> http://listarchives.libreoffice.org/global/users/ >>>> All messages sent to this list will be publicly >>>> archived and >>>> cannot be deleted >>>> >>>> >>>> >>>> -- >>>> To unsubscribe e-mail to: >>>> users+unsubscr...@global.libreoffice.org >>>> <mailto:users%2bunsubscr...@global.libreoffice.org> >>>> Problems? >>>> >>>> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ >>>> Posting guidelines + more: >>>> http://wiki.documentfoundation.org/Netiquette >>>> List archive: >>>> http://listarchives.libreoffice.org/global/users/ >>>> All messages sent to this list will be publicly archived >>>> and cannot be >>>> deleted >>>> >>>> >>>> >>>> -- To unsubscribe e-mail to: >>>> users+unsubscr...@global.libreoffice.org >>>> <mailto:users%2bunsubscr...@global.libreoffice.org> >>>> Problems? >>>> >>>> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ >>>> Posting guidelines + more: >>>> http://wiki.documentfoundation.org/Netiquette >>>> List archive: http://listarchives.libreoffice.org/global/users/ >>>> All messages sent to this list will be publicly archived and >>>> cannot be deleted >>>> >>>> >>>> >>>> -- >>> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org >>> Problems? >>> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ >>> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette >>> List archive: http://listarchives.libreoffice.org/global/users/ >>> All messages sent to this list will be publicly archived and cannot be >>> deleted >>> >>> > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette > List archive: http://listarchives.libreoffice.org/global/users/ > All messages sent to this list will be publicly archived and cannot be > deleted > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
