DFF Lexicals in a Non-Standard SQL DataModel

Paul Hassall

Hi,

We have the requirement to write our data model using the 'Non-Standard SQL' SQL type because we need the use of CURSOR statements to return data back in a nested structure because it will include multiple levels and hierarchies. We also need to include lexicals for DFFs in our query. But its resulting in this error:

Here is a cut-down query that results in an error when run in the data model:

select 

 supplier.vendor_id,

 supplier.vendor_name,

 address_line1

 cursor (

  select 

    sites.vendor_site_id,

    sites.vendor_site_name,

    sites.country

  from poz_supplier_sites_v sites

    sites.vendor_id = supplier.vendor_id

 ) supplier_sites,

&DFF_SELECT

from 

 POZ_SUPPLIERS_V supplier

 where

 supplier.vendor_name = 'XXX'

This is the Lexical setup:

Any help is appreciated!