Help with reading ranges

[Olivia123]

Hello,I have two tables ...
Table_2

GLRD_ACCT \ GLRD_ACCT1\GLRD_CC\GLRD_CC1\GLRD_USER1\GLRD_DESC\GLRD_ENTITY
4001.........\..........4002........\.................\...................\ .....10.01.......\.........NS.......\...........1000
6700.........\.........6700..........\.................\..................\......136.01.....\.........PV.......\...........1000
8500.........\..........8799.........\.....0600.....\.....0647......\........148.......\.........SGA.....\...........1000
8500........\..........8799..........\....0649......\.....0650.......\........148.......\........SGA.....\............1000
8000........\..........8999..........\.....0700.....\......0755......\........150........\.......SGA....\............1000

This is my table_1 (transactional)

KL_ACC/KL_SA/ KL_CC/ KL_ENT/ KL_AMT
4002...../1100..../..2630../......1000/
6006...../1100..../..2630../......1000/
8876...../1100.../...2630../......1000/
4980...../1100.../...2630../.......1000/

I need my script to add from table_2 GLRD_USER1 (HYPE_ACCT) to table_1 according to KL_ACC and KL_CC.........
basically
if the KL_ACC.table_1 is between GLRD_ACCT.table_2 and GLRD_ACCT1.table_2 and KL_CC.table_1 is Bewteen GLRD_CC.table_2 and GLRD_CC1.table_2 then GLRD_USER1 (HYPE_ACCT)....


This is my script, it executes, but it gives me no value in HYPE_ACCT, and, the join of two tables won't probably work here, because in table_2 I have ranges to read from, I don't have that actual account to join with.....Plz let me know what am I doing wrong ....Thank you

SELECT *
FROM (SELECT a.KL_SA 
,a.KL_ENT
,a.KL_CC
,a.KL_ACC
,a.KL_PERIOD
,a.KL_AMT,



CASE WHEN a.KL_ENT = b.GLRD_ENTITY

THEN

CASE
WHEN (a.KL_ACC BETWEEN b.GLRD_ACCT AND b.GLRD_ACCT1) AND (a.KL_CC BETWEEN b.GLRD_CC AND b.GLRD_CC1)
OR (b.GLRD_CC IS NULL)
OR (a.KL_CC = b.GLRD_CC1)


THEN b.GLRD_USER1



END

END AS HYPE_ACCT

FROM TABLE_1a,
TABLE_2 b 
)

 

[blom0344]

It is still possible to write a join between 2 tables without using an equal sign. In your case the join-type is a 'non-equi' join where you use between as operator.

In your case the expression:

(a.KL_ACC BETWEEN b.GLRD_ACCT AND b.GLRD_ACCT1) AND (a.KL_CC BETWEEN b.GLRD_CC AND b.GLRD_CC1)

defines a relationship between the tables a and b.

It is quite hard to fathom what you really want to do, but I have the feeling that the CASE construction is inappropriate. Did you look up where it is used for?



Ties Blom