CODEWITHSUNDEEP
sqlintersystems-cache
Tigran
Tigran

Reputation: 1057

How to put sql inside sql

select 
    idRuler AS CURRID, 
    beginDate,
    endDate, 
    (SELECT SUM(TotalYearsAtService) 
     FROM Ruler WHERE idRuler=CURRID 
    ) AS PeriodTotal,
    (SELECT COUNT(DISTINCT IDstateddFK)),
    nameRuler AS Name 
FROM Ruler, Position, RulerToState
where Ruler.idRuler=IDrulerFK 
  and RulerToState.IDrulerdFK=Ruler.idRuler
GROUP BY nameRuler

It does not see CURRID inside (SELECT SUM(TotalYearsAtService) FROM Ruler WHERE idRuler=CURRID )

Upvotes: 0

Views: 179

Answers (1)

AaronLS
AaronLS

Reputation: 38384

CURRID is an alias idrules AS CURRID, and in most cases you can't reference column aliases from inner queries or other places like group by's. While you can use table aliases, you can't use column aliases(in most cases) from within the same query. This is primarily because the SELECT happens last.

Instead, use idrules in the inner query instead of CURRID, use table aliases to differentiate the inner and outer Rule table:

SELECT
    idRuler, 
    beginDate,
    endDate, 
    (SELECT SUM(TotalYearsAtService) 
     FROM Ruler r2 WHERE r1.idRuler = r2.idRuler
    ) AS PeriodTotal,
    (SELECT COUNT(DISTINCT IDstateddFK)),
    nameRuler AS Name 
FROM 
    Ruler r1, Position, RulerToState
WHERE
    Ruler.idRuler = IDrulerFK 
    AND RulerToState.IDrulerdFK = Ruler.idRuler
GROUP BY 
    nameRuler

Upvotes: 3

Related Questions