Skip to main content
Solved

SQL query error when trying to reuse calculated field

  • May 18, 2023
  • 2 replies
  • 143 views
H
Sidekick (Customer)
Forum|alt.badge.img+5

Hi

I got an error with the below  line in red when execute. Any clues please.

 

select X.*,

IFSAPP.SUB_PROJECT_API.Get_Description(x.PROJECT_ID, x.SUB_PROJECT_ID) Sub_Proj_Des,

x.budget_base_curr/120 ITD_BUD,
x.actual - ITD_BUD as  "Deviation",

IFSAPP.ACTIVITY_API.Get_Description(X.ACTIVITY_SEQ) activity_des,
IFSAPP.Project_Forecast_Item_API.Get_Project_Cost_Element_Desc(x.FORECAST_ID, x.PROJECT_COST_ELEMENT) cost_elm_des

from

project_forecast_item_all X

where 

project_id = '20062' and 

forecast_version_id = 'M1003'

 

Regards

Housseiny
 

Best answer by Furkan Zengin

Hi @Housseiny 

Result is not fully rendered in your query, therefore no value is assigned for ITD_BUD 

You can have 2 option to resolve this. Option one to make calculation in the same query. 

x.budget_base_curr/120 ITD_BUD,
x.actual - (x.budget_base_curr/120) as  "Deviation",

 

Option 2 is to create a subquery where result is rendered and built your query on top of that

 

select T.*,

T.actual-T.ITD_BUD as “Deviation”,

IFSAPP.SUB_PROJECT_API.Get_Description(t.PROJECT_ID, t.SUB_PROJECT_ID) Sub_Proj_Des,

IFSAPP.ACTIVITY_API.Get_Description(t.ACTIVITY_SEQ) activity_des,
IFSAPP.Project_Forecast_Item_API.Get_Project_Cost_Element_Desc(t.FORECAST_ID, t.PROJECT_COST_ELEMENT) cost_elm_des

from

(select X.*, x.budget_base_curr/120 ITD_BUD

from

project_forecast_item_all X) T

where 

T.project_id = '20062' and 

T.forecast_version_id = 'M1003'

 

Hope it is clear

2 replies

Furkan Zengin
Ultimate Hero (Partner)
Forum|alt.badge.img+21
  • Furkan Zengin
  • Ultimate Hero (Partner)
  • Answer
  • May 19, 2023

Hi @Housseiny 

Result is not fully rendered in your query, therefore no value is assigned for ITD_BUD 

You can have 2 option to resolve this. Option one to make calculation in the same query. 

x.budget_base_curr/120 ITD_BUD,
x.actual - (x.budget_base_curr/120) as  "Deviation",

 

Option 2 is to create a subquery where result is rendered and built your query on top of that

 

select T.*,

T.actual-T.ITD_BUD as “Deviation”,

IFSAPP.SUB_PROJECT_API.Get_Description(t.PROJECT_ID, t.SUB_PROJECT_ID) Sub_Proj_Des,

IFSAPP.ACTIVITY_API.Get_Description(t.ACTIVITY_SEQ) activity_des,
IFSAPP.Project_Forecast_Item_API.Get_Project_Cost_Element_Desc(t.FORECAST_ID, t.PROJECT_COST_ELEMENT) cost_elm_des

from

(select X.*, x.budget_base_curr/120 ITD_BUD

from

project_forecast_item_all X) T

where 

T.project_id = '20062' and 

T.forecast_version_id = 'M1003'

 

Hope it is clear

https://superp.solutions/
H
H
Sidekick (Customer)
Forum|alt.badge.img+5
  • Housseiny
  • Author
  • Sidekick (Customer)
  • May 21, 2023

Hi gumabs,

              Thanks again for your help. Much appreciated!!

Terms & ConditionsCookie settingsAccessibility statement