How to reference a table in a Sub-Sub Query

Question

I have the following tables:

Bradford_Score_Bands

BandNo    InclusiveScore
------------------------
1         0
2         150
3         500

Bradford_Scores

ClockNo     Dated        Score
--------------------------------
2           30/10/14     123
99          30/10/14     3
2           29/10/14     101
99          29/10/14     8

Employees

ClockNo
--------------------
2
3
99

My aim is to work out the BandNo for each ClockNo for today and yesterday based on their score

I can find the correct BandNo based on a score value like this:

SELECT MIN(BandNo) FROM Bradford_Score_Bands WHERE InclusiveScore >= 123

I can find the score for today and yesterday for each person like this:

SELECT DISTINCT EMP.ClockNo, 
ISNULL((SELECT Score FROM Bradford_Scores BFT WHERE Dated = '2014-10-30' AND BFT.ClockNo = EMP.ClockNo), 0) As ScoreToday,
ISNULL((SELECT Score FROM Bradford_Scores BFT WHERE Dated = '2014-10-29' AND BFT.ClockNo = EMP.ClockNo), 0) As ScoreYesterday
FROM Employees EMP

But I can't seem to be able to combine the two. I thought something like this would work:

SELECT DISTINCT EMP.ClockNo, 
(SELECT MIN(BandNo) FROM Bradford_Score_Bands WHERE InclusiveScore >= 
(SELECT Score FROM Bradford_Scores BFT1 WHERE Dated = '2014-10-30' AND BFT1.ClockNo = EMP.ClockNo)),
(SELECT MIN(BandNo) FROM Bradford_Score_Bands WHERE InclusiveScore >= 
(SELECT Score FROM Bradford_Scores BFT2 WHERE Dated = '2014-10-29' AND BFT2.ClockNo = EMP.ClockNo))
FROM Employees EMP

But the parts in the subquery where I reference BFTX.ClockNo = EMP.ClockNo seem to be causing the query to fail. I get the helpful pervasive error "Data Record ManagerCurrency not on a record"

EDIT: I tried this exact same query in SQL Server and it works, so is there a way to re-write this to make it more Pervasive friendly?

Answer 1

Now this is tagged with SQL Server I don't feel the need to write a pervasive query that works.

I took your original query and rewrote it in a simpler fashion. Maybe try this and see if it solves your problem?

DECLARE @Bradford_Score_Bands TABLE (BandNo INT, InclusiveScore INT);
INSERT INTO @Bradford_Score_Bands VALUES (1, 0);
INSERT INTO @Bradford_Score_Bands VALUES (2, 150);
INSERT INTO @Bradford_Score_Bands VALUES (3, 500);
DECLARE @Bradford_Scores TABLE (ClockNo INT, Dated DATE, Score INT);
INSERT INTO @Bradford_Scores VALUES (2, '20141030', 123);
INSERT INTO @Bradford_Scores VALUES (99, '20141030', 3);
INSERT INTO @Bradford_Scores VALUES (2, '20141029', 101);
INSERT INTO @Bradford_Scores VALUES (99, '20141029', 8);
DECLARE @Employees TABLE (ClockNo INT);
INSERT INTO @Employees VALUES (2);
INSERT INTO @Employees VALUES (3);
INSERT INTO @Employees VALUES (99);

--Original Query
SELECT DISTINCT
    EMP.ClockNo, 
    (SELECT MIN(BandNo) FROM @Bradford_Score_Bands WHERE InclusiveScore >= (SELECT Score FROM @Bradford_Scores BFT1 WHERE Dated = '2014-10-30' AND BFT1.ClockNo = EMP.ClockNo)),
    (SELECT MIN(BandNo) FROM @Bradford_Score_Bands WHERE InclusiveScore >= (SELECT Score FROM @Bradford_Scores BFT2 WHERE Dated = '2014-10-29' AND BFT2.ClockNo = EMP.ClockNo))
FROM 
    @Employees EMP;

--New query
SELECT  
    e.ClockNo,
    MIN(bsbt.BandNo),
    MIN(bsby.BandNo)
FROM 
    @Employees e
    LEFT JOIN @Bradford_Scores bst ON bst.ClockNo = e.ClockNo AND bst.Dated = '20141030'
    LEFT JOIN @Bradford_Scores bsy ON bsy.ClockNo = e.ClockNo AND bsy.Dated = '20141029'
    LEFT JOIN @Bradford_Score_Bands bsbt ON bsbt.InclusiveScore >= bst.Score
    LEFT JOIN @Bradford_Score_Bands bsby ON bsby.InclusiveScore >= bsy.Score
GROUP BY
    e.ClockNo;

I got exactly the same results for both queries when running this on SQL Server.