Home » SQL & PL/SQL » SQL & PL/SQL » help with query
help with query [message #352769] Thu, 09 October 2008 08:23 Go to next message
jemkeith
Messages: 26
Registered: May 2006
Junior Member
I have three tables. A, B and C. A and B have a one to one relationship. B and C have a one to many relationship, up to 12 rows in C for each row in B. There are about 50 different jurisdiction types in C. I also know that every row in B will have a Jurisdiction type 1, 2 and 3. I need to create a query that will return all the data for A, B and C in a single row. I can't make any changes to the database, I can only query it. This is using an Oracle 9.2 database.
I've tried several things, and I can't figure it out. My last attempt is below.

A
A_Id (Int)
Name (VARCHAR)
Address (VARCHAR)
Property_ID (VARCHAR)

B
B_ID (Int)
A_ID (Int)
Value (Double)
Tax (Double)

C
C_ID (Int)
B_ID (Int)
JursidictionType (Int)
JurisdictionName (VARCHAR)
JurisdictionValue (Double)
JurisdictionRate (Double)

SELECT a.BILL_NO,b.TAX_BILL_DETAILS_ID,
    (
        SELECT JURISDICTION_NAME
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 1 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    StateName,
    (
        SELECT JURISDICTION_VALUE
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 1 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    StateValue,
    (
        SELECT JURISDICTION_RATE
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 1 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    StateRate,
    (
        SELECT JURISDICTION_NAME
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 2 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    CountyName,
    (
        SELECT JURISDICTION_VALUE
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 2 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    CountyValue,
    (
        SELECT JURISDICTION_RATE
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 2 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    CountyRate,
    (
        SELECT JURISDICTION_NAME
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 3 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    SchoolName,
    (
        SELECT JURISDICTION_VALUE
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 3 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    SchoolValue,
    (
        SELECT JURISDICTION_RATE
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 3 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    SchoolRate,
    (
        SELECT JURISDICTION_NAME
        FROM tax_bill_juris_details z
        WHERE z.TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID AND
            (JURISDICTION_TYPE,TAX_BILL_DETAILS_ID) IN
            (
                SELECT JURISDICTION_TYPE,TAX_BILL_DETAILS_ID
                FROM
                    (
                        SELECT JURISDICTION_TYPE,TAX_BILL_DETAILS_ID, row_number() over(order by JURISDICTION_TYPE) rn
                        FROM tax_bill_juris_details x
                        WHERE JURISDICTION_TYPE > 3 AND
                            x.TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
                    )
                WHERE rn = 1
                
            )
    )
    Juris1Name,
    
    
    
    
    (
        SELECT JURISDICTION_VALUE
        FROM tax_bill_juris_details z
        WHERE z.TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID AND
            JURISDICTION_TYPE IN
            (
                SELECT JURISDICTION_TYPE
                FROM
                    (
                        SELECT JURISDICTION_TYPE,TAX_BILL_DETAILS_ID, row_number() over(order by JURISDICTION_TYPE) rn
                        FROM tax_bill_juris_details x
                        WHERE JURISDICTION_TYPE > 3 AND
                            x.TAX_BILL_DETAILS_ID = 125551
                    )
                WHERE rn = 1
                
            )
    )
    Juris1Value,
    (
        SELECT JURISDICTION_RATE
        FROM tax_bill_juris_details
        WHERE JURISDICTION_TYPE = 99 AND
            TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
    )
    Juris1Rate
FROM tax_bill a, tax_bill_details b, property_details d
WHERE a.TAX_YEAR = 2007 AND
    a.PRINT_FLG <> 'X' AND
    d.CNTY_CODE = '007' AND
    b.TAX_BILL_ID = a.TAX_BILL_ID AND
    d.PROPERTY_ACCTNO = a.PROPERTY_ACCTNO
ORDER BY a.TAX_BILL_ID
Re: help with query [message #352772 is a reply to message #352769] Thu, 09 October 2008 08:54 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Post a sample dataset (small one), with expected results. Then somone will help you.

Kevin
Re: help with query [message #352867 is a reply to message #352769] Thu, 09 October 2008 23:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

(SELECT JURISDICTION_NAME
        FROM tax_bill_juris_details z
        WHERE z.TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID AND
            (JURISDICTION_TYPE,TAX_BILL_DETAILS_ID) IN
            (
                SELECT JURISDICTION_TYPE,TAX_BILL_DETAILS_ID
                FROM
                    (
                        SELECT JURISDICTION_TYPE,TAX_BILL_DETAILS_ID, row_number() over(order by JURISDICTION_TYPE) rn
                        FROM tax_bill_juris_details x
                        WHERE JURISDICTION_TYPE >= 3 AND
                        x.TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
                    )
                WHERE rn = 1
                
            )


Might have created the problem .


Here you cannot refer b.TAX_BILL_DETAILS_ID inside the inner query.

You can either ,

1.

(SELECT JURISDICTION_NAME
        FROM tax_bill_juris_details z
        WHERE z.TAX_BILL_DETAILS_ID = b.TAX_BILL_DETAILS_ID
        AND JURISDICTION_TYPE = ( SELECT MIN(JURISDICTION_TYPE)
                                  FROM   tax_bill_juris_details y
                                  WHERE  y.JURISDICTION_TYPE > 3
                                  AND    y.TAX_BILL_DETAILS_ID = z.TAX_BILL_DETAILS_ID)
)




2 Create a view to implement the above logic and do the correlated query here ..

I think you have to use >=3 instead of >3

Thumbs Up
Rajuvan.

[Updated on: Thu, 09 October 2008 23:40]

Report message to a moderator

Re: help with query [message #353672 is a reply to message #352769] Tue, 14 October 2008 09:27 Go to previous message
jemkeith
Messages: 26
Registered: May 2006
Junior Member
Thanks for your help!!!
Instead of doing everything in one query, I'm going to see if I can use Oracle Report Builder to get the data into the format I need. Thanks!!!
Previous Topic: Minus question
Next Topic: DB Link or Temp Table?
Goto Forum:
  


Current Time: Sat Dec 10 14:42:58 CST 2016

Total time taken to generate the page: 0.04489 seconds