Help on Advanced Querie

Help on Advanced Querie

  
Hi,

I want to re-use a calculated field in an Advanced Query. If I do, there is an error message stating the fields are invalid.
In this case the fields ABAPHours and Contengincy

How to do this ?

SELECT 
 {Project}.[Code], {Project}.[Name],{Project}.[RiskFactor], {Project}.[EnterpriseMaturity], {Project}.[Maturity]
,{SAPCOMPONENT}.[Code], {SAPCOMPONENT}.[Name], {Component}.[Template], {Component}.[InfluenceTemplate]
,{SAPFUNCTION}.[Code], {SAPFUNCTION}.[Name] 
,{SAPSUBFUNCTION}.[Code], {SAPSUBFUNCTION}.[Name]
,{SAPCONFIGVARIANT}.[Code], {SAPCONFIGVARIANT}.[Name], {ConfigVariant}.[Hours], {ConfigVariant}.[VolumeFactor], {ConfigVariant}.[Nr]
,{VolumeParameter}.[Text]

, 0 AS ABAPHours
, ({Project}.[RiskFactor] + ( {Project}.[EnterpriseMaturity] + {Project}.[Maturity])/2) AS Contingency
, (({ConfigVariant}.[Hours]) * {ConfigVariant}.[VolumeFactor]) AS HoursPlusVolume
, ABAPHours * Contengincy AS HoursContengincy
, 0

FROM (((((((({Project} Inner JOIN {Component} ON ({Component}.[ProjectId] = {Project}.[Id])) Inner JOIN {SAPCOMPONENT} ON ({Component}.[SapcomponentId] = {SAPCOMPONENT}.[Id])) Inner JOIN {ConfigVariant} ON ({ConfigVariant}.[ProjectId] = {Project}.[Id])) Inner JOIN {SAPCONFIGVARIANT} ON ({ConfigVariant}.[SapconfigvariantId] = {SAPCONFIGVARIANT}.[Id])) Inner JOIN {SAPSUBFUNCTION} ON ({SAPCONFIGVARIANT}.[SapsubfunctionId] = {SAPSUBFUNCTION}.[Id])) Inner JOIN {SAPFUNCTION} ON ({SAPSUBFUNCTION}.[SapfunctionId] = {SAPFUNCTION}.[Id])) Inner JOIN {Function} ON ({Function}.[SapfunctionId] = {SAPFUNCTION}.[Id])) Inner JOIN {VolumeParameter} ON ({ConfigVariant}.[VolumeParameterId] = {VolumeParameter}.[Id]))
WHERE {Project}.[CustomerId]= @CustomerId
Why not repeat the formulas. Otherwise I think you need to declare variables but that is, as far as I know, not supported in advanced Queries.

(But in this sample it makes no sense because ABAPHours = 0 so ABAPHours * Contengincy will always be 0.
 So  0 as HoursContengincy will solve the problem here.)

Greetings,

Dick Dokter

Hi Joop,

You can do that, but since Sql syntax does not allow uses of Alias in other expressions you need to do it as a subquery

SELECT SubQuery.*, SubQuery.ABAPHours * SubQuery.Contengincy HoursContengincy, 0
FROM (SELECT 
 {Project}.[Code], {Project}.[Name],{Project}.[RiskFactor], {Project}.[EnterpriseMaturity], {Project}.[Maturity]
,{SAPCOMPONENT}.[Code], {SAPCOMPONENT}.[Name], {Component}.[Template], {Component}.[InfluenceTemplate]
,{SAPFUNCTION}.[Code], {SAPFUNCTION}.[Name] 
,{SAPSUBFUNCTION}.[Code], {SAPSUBFUNCTION}.[Name]
,{SAPCONFIGVARIANT}.[Code], {SAPCONFIGVARIANT}.[Name], {ConfigVariant}.[Hours], {ConfigVariant}.[VolumeFactor], {ConfigVariant}.[Nr]
,{VolumeParameter}.[Text]

, 0 ABAPHours
, ({Project}.[RiskFactor] + ( {Project}.[EnterpriseMaturity] + {Project}.[Maturity])/2) Contingency
, (({ConfigVariant}.[Hours]) * {ConfigVariant}.[VolumeFactor]) HoursPlusVolume

FROM (((((((({Project} Inner JOIN {Component} ON ({Component}.[ProjectId] = {Project}.[Id])) Inner JOIN {SAPCOMPONENT} ON ({Component}.[SapcomponentId] = {SAPCOMPONENT}.[Id])) Inner JOIN {ConfigVariant} ON ({ConfigVariant}.[ProjectId] = {Project}.[Id])) Inner JOIN {SAPCONFIGVARIANT} ON ({ConfigVariant}.[SapconfigvariantId] = {SAPCONFIGVARIANT}.[Id])) Inner JOIN {SAPSUBFUNCTION} ON ({SAPCONFIGVARIANT}.[SapsubfunctionId] = {SAPSUBFUNCTION}.[Id])) Inner JOIN {SAPFUNCTION} ON ({SAPSUBFUNCTION}.[SapfunctionId] = {SAPFUNCTION}.[Id])) Inner JOIN {Function} ON ({Function}.[SapfunctionId] = {SAPFUNCTION}.[Id])) Inner JOIN {VolumeParameter} ON ({ConfigVariant}.[VolumeParameterId] = {VolumeParameter}.[Id]))
WHERE {Project}.[CustomerId]= @CustomerId) SubQuery


Note1: Yes, it will give a warning in SS saying it can't verify the field count ..you can enumerate all the SubQuery fields instead of using SubQuery.* if you really want to remove the warning (just that it makes my example and the resulting query a bit unreadable)

Note2: Also removed all the "AS" to make it SQL standard compatible :)

Regards,
João Rosado
@Dick: you are right ... :-)
@Joao: thanks for the detailed explanation

And got some answers form support as well: it's not possible this way.
It was an MS-Access query which I tried to move to OutSystems (SQL Server).
I found my way through by going through the whole recordlist and adding the calculated values myself ...

Thanks all