Home » SQL & PL/SQL » SQL & PL/SQL » Hey Guro: Need Urgent Help on writing Query
Hey Guro: Need Urgent Help on writing Query [message #227770] Thu, 29 March 2007 06:32 Go to next message
itroome
Messages: 34
Registered: June 2005
Location: DUBAI, UAE
Member

Salam to Guros

I am facing the problem in query building. Kindly help ASAP

There are three tables

SQL> DESC HRMPMSPRTAXRULE
Name Null? Type
------------------------------- -------- ----
PRTAXRULE NOT NULL NUMBER [PK]
PRPROC NUMBER
CO VARCHAR2(3)
PRA NUMBER
PRACALPERIOD NUMBER
TAXRULENO VARCHAR2(10)
FORMULA VARCHAR2(4000)
DESCR VARCHAR2(200)
TAXCALBASE VARCHAR2(1)
FAGE NUMBER
TAGE NUMBER
GENDER VARCHAR2(1)
BIFURTAXSAL VARCHAR2(1)
TAXCALMETHOD VARCHAR2(1)
STATUS VARCHAR2(1)

SQL> DESC HRMPMSPRTAXCAL
Name Null? Type
------------------------------- -------- ----
PRTAXCAL NOT NULL NUMBER [PK]
PRTAXCALNO VARCHAR2(10)
PRTAXRULE NUMBER
SALMIN NUMBER
SALMAX NUMBER
PERT NUMBER
CATE VARCHAR2(1)



SQL> DESC HRMPMSPREMPTAXCAL_w
Name Null? Type
------------------------------- -------- ----
PREMPTAXCAL_W NOT NULL NUMBER [PK]
PREMPTAXRTHRES_W NUMBER
PREMPTAXEXEMPT_W NUMBER
PRTAXCAL NUMBER
TAXABLEAMT NUMBER
PERT NUMBER
EMPTAXCALAMT NUMBER
CFEMPTAXCALAMT NUMBER
REMAINTAXABLEAMT NUMBER
CATE VARCHAR2(1)
PRTAXRULE NUMBER
PRPROC NUMBER
PRPROCEMPLST NUMBER

================================================================
SQL> SELECT * FROM HRMPMSPRTAXRULE;

PRTAXRULE PRPROC CO PRA PRACALPERIOD TAXRULENO
---------- ---------- --- ---------- ------------ ----------
1 3 100 1 17 TAX-2005
2 3 100 1 17 TAX-2006

SQL> SELECT * FROM HRMPMSPRTAXCAL;

PRTAXCAL PRTAXCALNO PRTAXRULE SALMIN SALMAX PERT C
---------- ---------- ---------- ---------- ---------- ---------- -
1 1 1 1 3000 2 C
2 5 1 5001 6000 2 R
3 2 1 3001 4000 4 C
4 3 1 4001 5000 6 C
5 4 1 5001 6000 8 C
6 5 1 6001 7000 10 C
7 1 1 1 2000 10 R
8 2 1 2001 3000 8 R
9 3 1 3001 4000 6 R
10 4 1 4001 5000 4 R
11 1 2 1 3000 5 C
12 2 2 3001 4000 7 C
13 3 2 4001 5000 9 C
14 4 2 5001 7000 11 C
15 5 2 7001 90000 13 C
16 1 2 1 3000 13 R
17 2 2 3001 4000 9 R

PRTAXCAL PRTAXCALNO PRTAXRULE SALMIN SALMAX PERT C
---------- ---------- ---------- ---------- ---------- ---------- -
18 3 2 4001 6000 7 R
19 4 2 6001 7000 5 R
20 5 2 7001 8000 3 R
21 6 2 8001 90000 2 R

21 rows selected.

PREMPTAXCAL_W PREMPTAXRTHRES_W PREMPTAXEXEMPT_W PRTAXCAL TAXABLEAMT PERT EMPTAXCALAMT CFEMPTAXCALAMT REMAINTAXABLEAMT

C PRTAXRULE PRPROC PRPROCEMPLST
------------- ---------------- ---------------- ---------- ---------- ---------- ------------ -------------- ----------------

- ---------- ---------- ------------
1 1 1 1 3000 2 60 60 407.64

C 1 3 1
2 1 1 3 407.64 4 16.3056 76.3056 0

C 1 3 1
3 2 3 15 24807.54 13 3224.9802 3224.9802 0

C 2 3 1
4 2 3 21 3224.9802 2 64.499604 64.499604 0

R 2 3 1
5 3 4 1 3000 2 60 60 5403.588

C 1 3 2
6 3 4 3 4000 4 160 220 1403.588

C 1 3 2
7 3 4 4 1403.588 6 84.21528 304.21528 0

C 1 3 2
8 4 6 15 22558.418 13 2932.59434 2932.59434 0

C 2 3 2
9 4 6 21 2932.59434 2 58.6518868 58.6518868 0

R 2 3 2


===================================
Result should be combination of upper three table
where clause based on
JOINS WILL BE
HRMPMSPREMPTAXCAL.TAXRULE = HRMPMSPRTAXRULE.TAXRULE
AND HRMPMSPREMPTAXCAL_W.PRTAXCAL = HRMPMSPRTAXCAL.PRTAXCAL

CATE = 'C'
PRPROC = 3
PRPROCEMPLST = 1
PRTAXRULE = 1





PRPROC PRPROCEMPLST PRTAXRULE SALMIN SALMAX TAXABLEAMT EMPTAXCALAMT
------- ------------ ---------- ---------- ---------- ---------- ------------
3 1 1 1 3000 3000 60
3 1 1 3001 4000 407.64 16.3056
3 1 1 4001 5000
3 1 1 5001 6000
3 1 1 6001 7000
  • Attachment: PROBLEM.txt
    (Size: 6.94KB, Downloaded 96 times)
Re: Hey Guro: Need Urgent Help on writing Query [message #227773 is a reply to message #227770] Thu, 29 March 2007 06:40 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Is there a specific problem here ? It just sounds like a very simple exercise of joining 3 tables together.
Re: Hey Guro: Need Urgent Help on writing Query [message #227808 is a reply to message #227773] Thu, 29 March 2007 08:08 Go to previous messageGo to next message
itroome
Messages: 34
Registered: June 2005
Location: DUBAI, UAE
Member

Salam

Thanks to reply soon but it is not simple join among three tables.


1-Super Table HRMPRTAXRULE having multiple Rules [Primary Key, PRTAXRULE ]

2-Actually I want to retrieve all the rows from HRMPMSPRTAXCAL [Primary Key ,PRTAXCAL, Foreign Key, PRTAXRULE]
[Where Clause PRTAXRULE e.g. [PRTAXRULE = 1] And CATE = [having two values 'C','R'], I need 'C'].

3-All he Matched Join Rows from the HRMPMSPREMPTAXCAL_W
[Primary Key, PREMPTAXCAL_W], Foreign Key, PRTAXCAL, PRTAXRULE]
[Where Clause PRTAXRULE And CATE = [having two values 'C','R'] ].

Here there may need the outer join between HRMPMSPRTAXCAL and HRMPMSPREMPTAXCAL_W. but it is not working.


The Sample Query that I have tried is :

1 SELECT
2 TR.PRPROC,
3 TC.PRTAXRULE ,
4 TC.SALMIN ,
5 TC.SALMAX ,
6 ETC_W.TAXABLEAMT ,
7 CASE
8 WHEN ETC_W.TAXABLEAMT IS NOT NULL THEN ETC_W.EMPTAXCALAMT
9 END EMPTAXCALAMT
10 ---------------
11 FROM
12 HRMPMSPRTAXRULE TR,
13 HRMPMSPREMPTAXCAL_W ETC_W,
14 HRMPMSPRTAXCAL TC
15 --------------
16 WHERE
17 TR.PRTAXRULE = TC.PRTAXRULE
18 AND TC.PRTAXCAL = ETC_W.PRTAXCAL
19 AND TC.PRTAXRULE = ETC_W.PRTAXRULE
20 AND TC.PRTAXRULE = 1
21 AND TC.CATE = 'C'
22* AND PRPROCEMPLST = 1
SQL> /

PRPROC PRTAXRULE SALMIN SALMAX TAXABLEAMT EMPTAXCALAMT
--------- ---------- ---------- ---------- ---------- ------------
3 1 1 3000 3000 60
3 1 3001 4000 407.64 16.3056

But I need Result like as


PRPROC PRTAXRULE SALMIN SALMAX TAXABLEAMT EMPTAXCALAMT
--------- ---------- ---------- ---------- ---------- ------------
3 1 1 3000 3000 60
3 1 3001 4000 407.64 16.3056
3 1 4001 5000
3 1 5001 6000
  • Attachment: PROBLEM.txt
    (Size: 8.68KB, Downloaded 90 times)
Re: Hey Guro: Need Urgent Help on writing Query [message #227814 is a reply to message #227770] Thu, 29 March 2007 08:29 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
If you need an outer join, why have you made no attempt to put one in your query ? Try:

SELECT
TR.PRPROC,
TC.PRTAXRULE ,
TC.SALMIN ,
TC.SALMAX ,
ETC_W.TAXABLEAMT ,
CASE
WHEN ETC_W.TAXABLEAMT IS NOT NULL THEN ETC_W.EMPTAXCALAMT
END EMPTAXCALAMT
FROM
HRMPMSPRTAXRULE TR,
HRMPMSPREMPTAXCAL_W ETC_W,
HRMPMSPRTAXCAL TC
WHERE
TR.PRTAXRULE = TC.PRTAXRULE (+)
AND TC.PRTAXCAL = ETC_W.PRTAXCAL (+)
AND TC.PRTAXRULE = ETC_W.PRTAXRULE (+)
AND TC.PRTAXRULE (+) = 1
AND TC.CATE (+)  = 'C'
AND ETC_W.PRPROCEMPLST (+) = 1


Could you use [code] tags to format your output, as it is very difficult to read and make an effort to keep the text as short as possible by removing useless lines like '--------'. It might also be a good idea to disguise your table names in future as well. When people start to see things like 'TAXRULE' and 'TAXABLEAMT', they may be less inclined to help !
Previous Topic: Left Outer Join in Oracle
Next Topic: what is difference
Goto Forum:
  


Current Time: Tue Dec 06 02:36:51 CST 2016

Total time taken to generate the page: 0.06830 seconds