CROSS JOIN in for Rule Check

Database
Enthusiast

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 (1)
2 REPLIES
Enthusiast

Re: CROSS JOIN in for Rule Check

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

Teradata Employee

Re: CROSS JOIN in for Rule Check

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 :)