Skip to main content

UniQuery and ECL Reference Manual and Training

  • February 18, 2025
  • 10 replies
  • 0 views
Richard Lucibella

Hey all-

I've managed to locate the Unidata Dictionary, Commands, Basic and Administrator manuals. But, after years of being on other platforms, I'm really struggling with queries and report formatting in UniData. Operators like BY-EXP, ID.SUPP, HEADING, etc, keep popping into my recollection, but I'm still having a difficult time with syntax and order. 

The UniBasic manual helps to some extent, but I'm hoping there might be a more in-depth Manual (or training) specific to creating and formatting reports from ECL.

thanks in advance for any assistance-

Rich



------------------------------
Richard Lucibella
Director
Rocket Forum Shared Account
------------------------------

    10 replies

    Stuart Boydell001
    • Stuart Boydell001
    • Participating Frequently
    • 26 replies
    • February 18, 2025

    Hey all-

    I've managed to locate the Unidata Dictionary, Commands, Basic and Administrator manuals. But, after years of being on other platforms, I'm really struggling with queries and report formatting in UniData. Operators like BY-EXP, ID.SUPP, HEADING, etc, keep popping into my recollection, but I'm still having a difficult time with syntax and order. 

    The UniBasic manual helps to some extent, but I'm hoping there might be a more in-depth Manual (or training) specific to creating and formatting reports from ECL.

    thanks in advance for any assistance-

    Rich



    ------------------------------
    Richard Lucibella
    Director
    Rocket Forum Shared Account
    ------------------------------
    Hi Richard,

    There's the XDEMO account for some examples and playground. Use LISTPA (works in UV, not sure about UD?) to find examples of paragraphs that use UniQuery.

    You can use SQL syntax if you're more comfortable with that (it's a fairly archaic implementation of SQL but has extensions for U2/MV).

    Or use Python to access the db (see XDEMO).

    Stack Overflow has some UD UniQuery examples. UniVerse is almost identical, so you can search for those examples too.

    Using an AI agent like Copilot in MSEdge and VSCode will also build fairly successful UniQuery select statements to get you started. You will need to prompt with your file and field names, and it will probably take a few iterations to build a successful statement (the VSCode version may use more contextual information about your db if you're connected through the Remote Development extension) but they're generally U2 syntax aware.
    Hope that helps.


    ------------------------------
    Stuart Boydell
    AU
    ------------------------------
    G
    • Greg Clitheroe
    • Participating Frequently
    • 21 replies
    • February 18, 2025
    Hi Richard,

    There's the XDEMO account for some examples and playground. Use LISTPA (works in UV, not sure about UD?) to find examples of paragraphs that use UniQuery.

    You can use SQL syntax if you're more comfortable with that (it's a fairly archaic implementation of SQL but has extensions for U2/MV).

    Or use Python to access the db (see XDEMO).

    Stack Overflow has some UD UniQuery examples. UniVerse is almost identical, so you can search for those examples too.

    Using an AI agent like Copilot in MSEdge and VSCode will also build fairly successful UniQuery select statements to get you started. You will need to prompt with your file and field names, and it will probably take a few iterations to build a successful statement (the VSCode version may use more contextual information about your db if you're connected through the Remote Development extension) but they're generally U2 syntax aware.
    Hope that helps.


    ------------------------------
    Stuart Boydell
    AU
    ------------------------------

    Richard was asking about the syntax for formatting which is essentially the same in both RetrieVe and UniVerseSQL statements except the SQL must use single quotes around the arguments.

    Google Guide To RetrieVe

    I use both the IBM and Rocket documentation as they cover aspects of UniVerse differently. I've spent a lot of time searching pdf documents for what I need to know and often rely on looking at my own examples now.

    Things like FMT "20L" go straight after the field they relate to while those relating to the whole statement (NO.PAGE, HDR.SUPP, HEADER etc) go at the end.

    Personally I find SQL's GROUP BY  and UNNEST a lot more useful than RetrieVe's BY.EXP

    If you use UniVerseSQL with multivalues, make sure you understand the difference and how to use WHERE and WHEN clauses.

    The JOIN capabilities are very limited and often perform poorly. Using TRANS () in the DICT of the main file is a better solution to connecting data from other files. EVAL is useful for this for ad hoc queries.

    BTW For easier reporting and interfacing with other databases, I set up file based UniVerse as an ODBC linked server from MS SQL Server. Four part naming is supported if you configure it correctly and reaslise there is no schema, but they drag the whole table over.

    OPENQUERY is better if you can use a where clause to reduce the number of records selected. The ODBC is single threaded, one connection at a time, so using Temp tables is often necessary for good performance on complex queries.

    Native queries are supported via ODBC, are slightly faster then plain OPENQUERY, and don't required the HS configuration, but be aware you need to use the DICT names for the SQL while the column names returned are those defined in F4 of the DICT items unless you alias them in the SQL.
    ------------------------------
    Greg Clitheroe
    Rocket Forum Shared Account

    Richard Lucibella
    • Richard Lucibella
    • Author
    • Participating Frequently
    • 20 replies
    • February 19, 2025
    Hi Richard,

    There's the XDEMO account for some examples and playground. Use LISTPA (works in UV, not sure about UD?) to find examples of paragraphs that use UniQuery.

    You can use SQL syntax if you're more comfortable with that (it's a fairly archaic implementation of SQL but has extensions for U2/MV).

    Or use Python to access the db (see XDEMO).

    Stack Overflow has some UD UniQuery examples. UniVerse is almost identical, so you can search for those examples too.

    Using an AI agent like Copilot in MSEdge and VSCode will also build fairly successful UniQuery select statements to get you started. You will need to prompt with your file and field names, and it will probably take a few iterations to build a successful statement (the VSCode version may use more contextual information about your db if you're connected through the Remote Development extension) but they're generally U2 syntax aware.
    Hope that helps.


    ------------------------------
    Stuart Boydell
    AU
    ------------------------------

    Thanks, Stuart. These are great leads.



    ------------------------------
    Richard Lucibella
    Director
    Rocket Forum Shared Account
    ------------------------------
    Richard Lucibella
    • Richard Lucibella
    • Author
    • Participating Frequently
    • 20 replies
    • February 19, 2025

    Richard was asking about the syntax for formatting which is essentially the same in both RetrieVe and UniVerseSQL statements except the SQL must use single quotes around the arguments.

    Google Guide To RetrieVe

    I use both the IBM and Rocket documentation as they cover aspects of UniVerse differently. I've spent a lot of time searching pdf documents for what I need to know and often rely on looking at my own examples now.

    Things like FMT "20L" go straight after the field they relate to while those relating to the whole statement (NO.PAGE, HDR.SUPP, HEADER etc) go at the end.

    Personally I find SQL's GROUP BY  and UNNEST a lot more useful than RetrieVe's BY.EXP

    If you use UniVerseSQL with multivalues, make sure you understand the difference and how to use WHERE and WHEN clauses.

    The JOIN capabilities are very limited and often perform poorly. Using TRANS () in the DICT of the main file is a better solution to connecting data from other files. EVAL is useful for this for ad hoc queries.

    BTW For easier reporting and interfacing with other databases, I set up file based UniVerse as an ODBC linked server from MS SQL Server. Four part naming is supported if you configure it correctly and reaslise there is no schema, but they drag the whole table over.

    OPENQUERY is better if you can use a where clause to reduce the number of records selected. The ODBC is single threaded, one connection at a time, so using Temp tables is often necessary for good performance on complex queries.

    Native queries are supported via ODBC, are slightly faster then plain OPENQUERY, and don't required the HS configuration, but be aware you need to use the DICT names for the SQL while the column names returned are those defined in F4 of the DICT items unless you alias them in the SQL.
    ------------------------------
    Greg Clitheroe
    Rocket Forum Shared Account

    Thank you, Greg. This is great information for a bit further down my development road. 

    I do seem to recall that PICK and UniData both published Query manuals back in the day.
    Rich



    ------------------------------
    Richard Lucibella
    Director
    Rocket Forum Shared Account
    ------------------------------
    E
    • Elaine Litchfield
    • Participating Frequently
    • 16 replies
    • February 19, 2025

    Hey all-

    I've managed to locate the Unidata Dictionary, Commands, Basic and Administrator manuals. But, after years of being on other platforms, I'm really struggling with queries and report formatting in UniData. Operators like BY-EXP, ID.SUPP, HEADING, etc, keep popping into my recollection, but I'm still having a difficult time with syntax and order. 

    The UniBasic manual helps to some extent, but I'm hoping there might be a more in-depth Manual (or training) specific to creating and formatting reports from ECL.

    thanks in advance for any assistance-

    Rich



    ------------------------------
    Richard Lucibella
    Director
    Rocket Forum Shared Account
    ------------------------------

    The thing you want is called UniQuery Commands Reference. 

    For 8.2.4 the Book number: UDT-824-UNQR-1

    Should be available with the rest of your U2 documentation.



    ------------------------------
    Elaine Litchfield
    Elaine Litchfield Consulting
    San Diego CA US
    ------------------------------
    Richard Lucibella
    • Richard Lucibella
    • Author
    • Participating Frequently
    • 20 replies
    • February 19, 2025

    The thing you want is called UniQuery Commands Reference. 

    For 8.2.4 the Book number: UDT-824-UNQR-1

    Should be available with the rest of your U2 documentation.



    ------------------------------
    Elaine Litchfield
    Elaine Litchfield Consulting
    San Diego CA US
    ------------------------------

    Thanks Elaine. I'm locating and downloading these manuals piecemeal. Should I have expected a link to all with my product purchase?

    Rich



    ------------------------------
    Richard Lucibella
    Director
    Rocket Forum Shared Account
    ------------------------------
    E
    • Elaine Litchfield
    • Participating Frequently
    • 16 replies
    • February 19, 2025

    Thanks Elaine. I'm locating and downloading these manuals piecemeal. Should I have expected a link to all with my product purchase?

    Rich



    ------------------------------
    Richard Lucibella
    Director
    Rocket Forum Shared Account
    ------------------------------

    If you have an RBC login it's under Resources, Product Availability. If I remember right (which I probably don't) there used to be a zip file with all the downloadable docs from that page. Now it seems to point to Rocket Software Documentation

     

    The links you get in the license details are to download and authorize the product. No doc link that I know of but maybe someone from Rocket will chime in.

    Rocketsoftware remove preview
    Rocket Software Documentation
    © Rocket Software, Inc. Rocket® is a registered trademark of Rocket Software, Inc. All other trademarks cited herein are the property of their respective owners.
    View this on Rocketsoftware >

    i



    ------------------------------
    Elaine Litchfield
    Elaine Litchfield Consulting
    San Diego CA US
    ------------------------------
    Jonathan Smith
    Forum|alt.badge.img
    • Jonathan SmithRocketeer
    • Rocketeer
    • 356 replies
    • February 20, 2025

    If you have an RBC login it's under Resources, Product Availability. If I remember right (which I probably don't) there used to be a zip file with all the downloadable docs from that page. Now it seems to point to Rocket Software Documentation

     

    The links you get in the license details are to download and authorize the product. No doc link that I know of but maybe someone from Rocket will chime in.

    Rocketsoftware remove preview
    Rocket Software Documentation
    © Rocket Software, Inc. Rocket® is a registered trademark of Rocket Software, Inc. All other trademarks cited herein are the property of their respective owners.
    View this on Rocketsoftware >

    i



    ------------------------------
    Elaine Litchfield
    Elaine Litchfield Consulting
    San Diego CA US
    ------------------------------

    All the docs moved to being on line over a year ago, since then we have not added them in the old way we used with the product download. Follow the link below. From the online version you can download a whole manual or just a particular subject with the manual.

    https://docs.rocketsoftware.com/



    ------------------------------
    Jonathan Smith
    UniData ATS
    Rocket Support
    ------------------------------
    Richard Lucibella
    • Richard Lucibella
    • Author
    • Participating Frequently
    • 20 replies
    • February 20, 2025

    All the docs moved to being on line over a year ago, since then we have not added them in the old way we used with the product download. Follow the link below. From the online version you can download a whole manual or just a particular subject with the manual.

    https://docs.rocketsoftware.com/



    ------------------------------
    Jonathan Smith
    UniData ATS
    Rocket Support
    ------------------------------

    Exactly what I was looking for.
    Thanks much, Jonathan and Elaine.



    ------------------------------
    Richard Lucibella
    Director
    Rocket Forum Shared Account
    ------------------------------

    P
    • Phillip Potter
    • Participating Frequently
    • 12 replies
    • February 20, 2025

    Exactly what I was looking for.
    Thanks much, Jonathan and Elaine.



    ------------------------------
    Richard Lucibella
    Director
    Rocket Forum Shared Account
    ------------------------------

    The manuals you want are:

    unidata_uniquery_commands_reference_version_8.3.1_2-19-2025.pdf
    unidata_using_uniquery_version_8.3.1_2-19-2025.pdf

    They can be found in the list of manuals at:


    https://docs.rocketsoftware.com/bundle?labelkey=unidata_8.3.1



    ------------------------------
    Phillip Potter
    V.P. R&D
    Data Management Associates Inc DMA
    Cincinnati OH US
    ------------------------------


    Terms & ConditionsAccessibility statement