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

User_HCKQU

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

1. 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

Hi @User_HCKQU,

Try the below:

1. 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