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
------------------------------
Original Message:
Sent: 08-20-2019 09:55 AM
From: Mark Vickers
Subject: Subselect in SQL Select
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
------------------------------