Datacom

 View Only
  • 1.  Can Datacom process SQL commands with subquery in FROM clause?

    Posted Aug 04, 2020 10:26 PM
    Hello,
    I don't know if CA Datacom accept SQL command with SUBquery in FROM clause, like in:
    select * from (select columnName from Table) as tempTable
    I try this and it doesn't work so it's likely that CA Datacom doesn't support nested queries or subqueries in From clause?


  • 2.  RE: Can Datacom process SQL commands with subquery in FROM clause?

    Broadcom Employee
    Posted Aug 05, 2020 03:36 AM
    Hello Thang,
    as far as I can see, what you are describing is a Nested Table Expression (NTE). This feature is an enhancement to SQL available on Datacom 15.1 via PTF SO01886:

    ENHANCEMENT DESCRIPTION:
    This enhancement provides Nested Table Expressions (NTE). An NTE is a SELECT
    that is written in the FROM clause in place of a table reference.


    An NTE may contain an NTE, nested to any depth. However, at least initially,
    an NTE may not contain a view.


    Note that an NTE is "materialized" as a temporary table. (At a later date,
    an NTE may be folded into the query for better performance.)



    IMPORTANT NOTE: PTF SO01886 has been marked in error, so PTFs SO04015, SO05190 and SO08452 must be applied.

    Please see "Subselect" section of "SELECT Statement" page on CA Datacom 15.1 documentation for full description. 

    Umberto

    ------------------------------
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 3.  RE: Can Datacom process SQL commands with subquery in FROM clause?

    Posted Aug 05, 2020 05:53 AM
    Hi Thang,

    Umberto is correct that Nested Table expressions (and Scalar Table Expressions, and many more SQL syntax enhancements) are available at the latest maintenance level of Datacom 15.1.

    The Datacom syntax for Nested Table Expressions requires the TABLE keyword. So your query would need to be..

    select * from TABLE(select columnName from Table) as tempTable;


    Cheers,
    Owen

    ------------------------------
    Technical Consultant
    Dixons Carphone
    ------------------------------



  • 4.  RE: Can Datacom process SQL commands with subquery in FROM clause?

    Posted Aug 05, 2020 01:12 PM
    Edited by THANG TRAN Aug 05, 2020 01:13 PM
    Thank you, both Umberto and Owen. Now i know how to run subqueries in FROM clause; the key is the "TABLE" keyword.
    However I get stuck in "fetch first 10 rows only" (i.e. select top(1) *) in the subquery:
    select * from (select top(10)* from TableX) as tempTable
    I put this as a new thread,
    https://community.broadcom.com/mainframesoftware/communities/community-home/digestviewer/viewthread?GroupId=2035&MessageKey=90482930-40a2-4a04-9c66-4330bfecc43c&CommunityKey=c66cac08-5724-4f3e-8734-56dffb11f900&tab=digestviewer&ReturnUrl=%2fmainframesoftware%2fcommunities%2fcommunity-home%2fdigestviewer%3fcommunitykey%3dc66cac08-5724-4f3e-8734-56dffb11f900%26tab%3ddigestviewer%26SuccessMsg%3dThank%2byou%2bfor%2bsubmitting%2byour%2bmessage.
    can you please help me? Thank you.