CA Datacom

Expand all | Collapse all

Subselect in SQL Select

  • 1.  Subselect in SQL Select

    Posted 08-20-2019 09:56 AM
    Subselect seems to work within the predicates, but when used in the column selection, datacom barks at it ?

    SELECT C.CHAIN_NUMBER                                  

          ,C.ITEM_CODE
          ,M.ICOST                                          

          ,(SELECT VND.EXPANDED_AP_VENDOR_NUMBER           

              FROM  VENDOR VND                         

            WHERE VND.VENDOR_NUMBER =                     

                CASE                                      

                  WHEN M.SUB_VENDOR_NUMBER_NEW > 0        

                     THEN M.SUB_VENDOR_NUMBER_NEW         

                  ELSE                                    

                    M.VENDOR                              

                  END                                     

            )                    AS DOESNT_WORK           

      FROM   CUSTOMER C                                                  

        INNER JOIN MASTER M                                                     

        ON C.ITEM_CODE = M.ITEM_CODE                

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Error during Prepare
    42601(-20)[CA][ODBC CA Datacom/DB Driver][CA DATACOM/DB] BAD SYNTAX: <,M.ICOST

    Is this a restriction of datacom ?
    Thanks,
    Mark.



    ------------------------------
    Mark Vickers
    ------------------------------


  • 2.  RE: Subselect in SQL Select

    Posted 08-20-2019 03:11 PM
    Hi Mark,

    That's odd. I don't have the same tables as you, but using tables that are available in all Datacom environments my sample query works fine...

    select occurrence "Database",
    (select count(*) from sysadm.dir_area ara
    where ara.dbid = bas.dbid) "Areas" ,
    (select count(*) from sysadm.dir_table tbl
    where tbl.dbid = bas.dbid) "Tables" ,
    (select count(*) from sysadm.dir_key key
    where key.dbid = bas.dbid) "Keys" ,
    (select count(*) from sysadm.dir_element elm
    where elm.dbid = bas.dbid) "Elements" ,
    (select count(*) from sysadm.dir_column fld
    where fld.dbid = bas.dbid) "Edited cols"
    from sysadm.dir_database bas;

    Does that query work for you? Scalar subselects were only introduced to the SQL syntax very recently so you need to be on the latest Datacom 15.1 SQL maintenance (SO07544 or later). 

    Cheers,
    Owen

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