All Forums Database
AB75151 20 posts Joined 06/09
07 Feb 2014
CROSS JOIN in for Rule Check

Hi Gurus,
I have a rule table which look slike
Mark_SL#      STRT_VAL       END_VAL        DESCR
1                     0                     35                  FAIL
2                     36                   60                  SECOND CLASS
3                     61                    70                 FIRST CLASS 
4                     70                   100                DISTINCTION
My Source data is
STUDENT_ID         MARK
123                           60
WHEN SUM(OUTPUT.MARK) OVER (PARTITION BY OUTPUT.STUDENT_ID) BETWEEN STRT_VAL AND END_VAL THEN DESCR
Now since there is no join condition between these 2 tables, I end up doing a cross join, which makes 1 row into 4, and SUM(MARKS) become 240 and DESC comes back null. I am sure there is a way to do this, but I am now for sure sitting blank! Any help is appreciated

Tags:
AB75151 20 posts Joined 06/09
07 Feb 2014

Never mind! I solved it. Thanks Guys!
SELECT
STUDENT_ID,
SUM(MARK) OVER (PARTITION BY OUTPUT.STUDENT_ID),
CASE WHEN SUM(MARK) OVER (PARTITION BY OUTPUT.STUDENT_ID) BETWEEN STRT_VAL AND END_VAL THEN DESCR
END AS DESCR
FROM OUTPUT
INNER JOIN MARK_CHECK
ON MARK BETWEEN STRT_VAL AND END_VAL

Adeel Chaudhry 773 posts Joined 04/08
08 Feb 2014

From table-names i assume the data-set wont be huge, otherwise the join-condition can slow down the query. And a very good practice of sharing the solution. Thanks :)

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.