How to put sql inside sql

Question

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 )

You should add your tables strucutures to see if we can help you get those SUM and COUNT from the main query. — Filipe Silva, Nov 26 '13 at 19:23
Well, I found a solution: I created a field that is SQL computer. — Tigran, Nov 27 '13 at 07:14
Property NumberOfCoutries As %Integer [ Calculated, SqlComputeCode = { set n={idRuler} &sql(SELECT COUNT(DISTINCT IDstateddFK) INTO :data FROM RulerToState WHERE IDrulerdFK=:n) set {NumberOfCoutries}=data}, SqlComputed ]; — Tigran, Nov 27 '13 at 07:14

score 3 · Answer 1 · edited Feb 06 '16 at 17:14

3

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

edited Feb 06 '16 at 17:14

marc_s

732,580
175
1,330
1,459

answered Nov 26 '13 at 19:18

AaronLS

37,329
20
143
202

1

Wow how'd you reformat *and* answer in that time?! Specifically not column aliases in query at the same level, just in sub-queries under current level. – Nov 26 '13 at 19:19
I cannot make idRuler=idRuler, it doest not make sence. I want idRuler_outer_query=idRuler_inner_sum. How to make it??? – Tigran Nov 26 '13 at 19:22
I have updated. The outer query can have a table alias of r1 and the inner query a table alias of r2 – AaronLS Nov 26 '13 at 19:23
There is a problem. I work with Cache and it do not allow Ruler r1 for some reason, so it show an sql error – Tigran Nov 27 '13 at 05:55
@Tigran Try `Ruler as r1` instead. According to their documentation `Ruler r1` should work though as the `[AS]` in the grammar indicates it is optional, although maybe `r1` isn't a valid identifier in their system, see their link on valid identifiers: http://docs.intersystems.com/ens20091/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_from – AaronLS Nov 27 '13 at 16:27

How to put sql inside sql

1 Answers1