Create or Convert to Dataset failed

Gopal Chopperla

I am in the process of Creating your own metadata dictionary in FDI using this blog.

As part of this I need create two data sets and able to create 1 data set w.r.t ADW Model details successfully,

While creating the 2nd Dataset for pillar-wise subject area using Manual Query option is failing with below error message —

"Failed with Invalid Logical Statement."

Can you guide me what went wrong with below query?

Query is as below:

SELECT
"Content Explorer - Subject Areas"."Application"."Application Name" ,
"Content Explorer - Subject Areas"."Module"."Active Flag" ,
"Content Explorer - Subject Areas"."Module"."Module Name",
"Content Explorer - Subject Areas"."Subject Area"."Subject Area Description" ,
"Content Explorer - Subject Areas"."Subject Area"."Subject Area Name" ,
DESCRIPTOR_IDOF("Content Explorer - Subject Areas"."Application"."Application Name") Pillar ,
DESCRIPTOR_IDOF("Content Explorer - Subject Areas"."Module"."Module Name") Module_code,
DESCRIPTOR_IDOF("Content Explorer - Subject Areas"."Subject Area"."Subject Area Description") SubjectArea,
"Content Explorer - Subject Areas"."Release"."Release Code"
FROM "Content Explorer - Subject Areas"
ORDER BY 9 DESC NULLS LAST
FETCH FIRST 500001 ROWS ONLY

Ashish-Oracle

@Gopal Chopperla What you are observing is an Expected behavior. You are trying to run a logical SQL against a ADW database which will not work.

Also there is no option to change the Connection type after you have started creating a dataset.
You have to create a new Dataset, select the Local Subject Area Option while creating the dataset then edit the Manual Query and enter the below logical SQL

SELECT "Content Explorer - Subject Areas"."Application"."Application Name" , "Content Explorer - Subject Areas"."Module"."Active Flag" , "Content Explorer - Subject Areas"."Module"."Module Name", "Content Explorer - Subject Areas"."Subject Area"."Subject Area Description" , "Content Explorer - Subject Areas"."Subject Area"."Subject Area Name" , DESCRIPTOR_IDOF("Content Explorer - Subject Areas"."Application"."Application Name") Pillar , DESCRIPTOR_IDOF("Content Explorer - Subject Areas"."Module"."Module Name") Module_code, DESCRIPTOR_IDOF("Content Explorer - Subject Areas"."Subject Area"."Subject Area Description") SubjectArea, "Content Explorer - Subject Areas"."Release"."Release Code"

FROM "Content Explorer - Subject Areas"ORDER BY 9 DESC NULLS LAST

FETCH FIRST 500001 ROWS ONLY

Sumanth V -Oracle

@Gopal Chopperla - The query seems to be working without any issues. PFA the screen shot showing the same:

Please check if you are adding some extra characters by any chance while copying it to the dataset creation page once.

Gopal Chopperla

@Sumanth V -Oracle , thank you for quick reply. There is no extra spaces and I am unable to run this query in SQLDeveloper connected with ADW instance as well.

Actually, I noticed, from your screen shot, you are using connection as Local Subject Area and Connection Type as Logical SQL.

Whereas I am using connection type to my ADW instance connected with this FDI instance and I don't see option to change the connection to Local Subject Area.

how can we set the connection to Local Subject Area?

Gopal Chopperla

Thank you @Ashish-Oracle . I am able create the dataset using Local subject area for this query.