Home » SQL & PL/SQL » SQL & PL/SQL » Help with reading ranges from a different table
Help with reading ranges from a different table [message #243766] Fri, 08 June 2007 16:38 Go to next message
Stephanie123
Messages: 2
Registered: June 2007
Junior Member
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
)



Re: Help with reading ranges from a different table [message #243768 is a reply to message #243766] Fri, 08 June 2007 16:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Plz let me know what am I doing wrong
you are NOT reading & FOLLOWING the posting guidelines in the #1 STICKY post at the top of this forum.
Re: Help with reading ranges from a different table [message #243769 is a reply to message #243766] Fri, 08 June 2007 16:50 Go to previous message
Stephanie123
Messages: 2
Registered: June 2007
Junior Member
I'm sorry

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 
) 


Previous Topic: Reurning result sets as XML
Next Topic: Doubts abt various data objects...
Goto Forum:
  


Current Time: Wed Dec 07 12:35:06 CST 2016

Total time taken to generate the page: 0.10162 seconds