No data elements displayed in the subject area created by manual query

Question

Hi community! I have created a manual query that I was able to run and preview the data as shown but cannot see any data displayed while creating a workbook. Could someone please help? This is the full query;

select A.*,

case when "Actuals/Targets" >= 1 then '>=100%' when "Actuals/Targets" >= 0.75 then ' >=75-100%' when "Actuals/Targets" >= 0.5 then ' >=50-75%' when "Actuals/Targets" >= 0.25 then ' >=25-50%' when "Actuals/Targets" >= 0.01 then ' >=1-25%' else ' 0%' end "Performance Category"

from(

select *,

(ifnull( XSA(Dataset)."Actuals",0))/(ifnull(XSA(Dataset)."Targets",0)) "Actuals/Targets"

from XSA(Dataset))

)A

* While breaking the queries into two parts to troubleshoot, the query below about creating "Actuals/Targets", I was able to see and use all of the measures and attributes in creating the workbook.

select *,

(ifnull( XSA(Dataset)."Actuals",0))/(ifnull(XSA(Dataset)."Targets",0)) "Actuals/Targets"

from XSA(Dataset))

2. However while adding the "Performance Category" shown below I cannot see any data elements display even though I can see all of the data elements in inspect:

case when "Actuals/Targets" >= 1 then '>=100%' when "Actuals/Targets" >= 0.75 then ' >=75-100%' when "Actuals/Targets" >= 0.5 then ' >=50-75%' when "Actuals/Targets" >= 0.25 then ' >=25-50%' when "Actuals/Targets" >= 0.01 then ' >=1-25%' else ' 0%' end "Performance Category"

Riyaz Ali-Oracle · Answer

Hi @User_HCKQU,

Try the below:* Rename Columns Without Special Characters

Change "Actuals/Targets" to a simpler alias like "ActualsToTargets":

sql

select A.*,

case

when ActualsToTargets >= 1 then '>=100%'

when ActualsToTargets >= 0.75 then '>=75-100%'

when ActualsToTargets >= 0.5 then '>=50-75%'

when ActualsToTargets >= 0.25 then '>=25-50%'

when ActualsToTargets >= 0.01 then '>=1-25%'

else '0%'

end as PerformanceCategory

from (

select *,

(ifnull(XSA(Dataset)."Actuals",0)) / (ifnull(XSA(Dataset)."Targets",0)) as ActualsToTargets

from XSA(Dataset)

) A

2. Flatten the Query

Avoid nesting if possible. Try writing the query as a single layer with all calculated fields inline.

3. Validate Data Types

Ensure that Actuals and Targets are numeric and not strings. Use CAST() if needed.

4. Preview in Dataset Editor

Use the Dataset Editor to inspect the column types and preview the transformed data. You can also change column types manually there.

Thanks,

Riyaz Ali