Home » SQL & PL/SQL » SQL & PL/SQL » How to get or concatenate a value from inner most query (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0, Windows 10 Professional)
How to get or concatenate a value from inner most query [message #676105] Tue, 14 May 2019 15:45 Go to next message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
I have a query below, besides what it retrieves in the SELECT clause, is there a way to retrieve the DOCUMENT STATE NUMBER(doc_state_no).

The query is:


SELECT tax_type_no,
             tax_centre_no,
             tax_period_no,
             tper_start_date,
             tper_end_date,
             tper_due_date,
             tper_payment_date,
             tper_month,
             tper_year,
             tper_week,
             form_no,
             version_no
        FROM (SELECT DISTINCT tfc.tax_type_no,
                              nvl(tac.tax_centre_no, ta.tax_centre_no) tax_centre_no,
                              tfc.form_no,
                              tfc.form_ird_no,
                              tpc.version_no,
                              tpc.tax_period_no,
                              tpc.tper_year,
                              tpc.tper_month,
                              tpc.tper_week,
                              tpc.imposition_base_no,
                              pck_tax_period.txprd_date_fn(tpc.tax_period_no,
                                                           ta.tax_payer_no,
                                                           tt.enterprise_no,
                                                           2,
                                                           1) tper_start_date,
                              pck_tax_period.txprd_date_fn(tpc.tax_period_no,
                                                           ta.tax_payer_no,
                                                           tt.enterprise_no,
                                                           1,
                                                           1) tper_end_date,
                              pck_tax_period.duedt_file(tpc.tax_period_no,
                                                        ta.tax_payer_no,
                                                        ta.tax_account_no,
                                                        NULL,
                                                        NVL(NULL,
                                                            tt.estab_no),
                                                        NULL) tper_due_date,
                              CASE
                                WHEN tpc.tax_on_roll_fl = 'N' THEN
                                 pck_tax_period.get_payment_date(tpc.tax_period_no,
                                                                 ta.tax_payer_no,
                                                                 ta.tax_account_no,
                                                                 NVL(NULL,
                                                                     tt.estab_no),
                                                                 NULL,
                                                                 NULL,
                                                                 NULL)
                                ELSE
                                 NULL
                              END AS tper_payment_date
                FROM tax_account ta,
                     (SELECT ta1.tax_account_no,
                             te.estab_no,
                             est.enterprise_no,
                             te.regist_date,
                             te.end_date,
                             NULL lic_base_no,
                             NULL license_no,
                             'Y' chargeable_fl
                        FROM tax_account   ta1,
                             tax_type      tt1,
                             tax_estab     te,
                             ENTERPRISE    ep,
                             establishment est
                       WHERE ta1.tax_payer_no = 31554214
                         AND ta1.tax_type_no = tt1.tax_type_no
                         AND ta1.tax_type_no = te.tax_type_no
                         AND ta1.tax_account_no = te.tax_account_no
                         AND te.estab_no = est.estab_no
                         AND est.enterprise_no = ep.enterprise_no
                         AND tt1.tax_basis_no = 1 --ESTABLISHMENT
                      UNION
                      SELECT ta2.tax_account_no,
                             NULL estab_no,
                             NULL enterprise_no,
                             nvl(lic2.regist_date, ta2.reg_date) regist_date,
                             nvl(lic2.end_date, ta2.close_date) end_date,
                             lic2.lic_base_no,
                             lic2.license_no,
                             lic2.chargeable_fl
                        FROM tax_account ta2, tax_type tt2, license lic2
                       WHERE ta2.tax_payer_no = 31554214
                         AND ta2.tax_type_no = tt2.tax_type_no
                         AND tt2.tax_basis_no != 1 --ESTABLISHMENT
                         AND ta2.tax_account_no = lic2.tax_account_no(+)
                      UNION
                      SELECT ta1.tax_account_no,
                             lic.estab_no,
                             est.enterprise_no,
                             lic.regist_date,
                             lic.end_date,
                             lic.lic_base_no,
                             lic.license_no,
                             lic.chargeable_fl
                        FROM tax_account   ta1,
                             tax_type      tt1,
                             license       lic,
                             ENTERPRISE    ep,
                             establishment est
                       WHERE ta1.tax_payer_no = 31554214
                         AND ta1.tax_type_no = tt1.tax_type_no
                         AND ta1.tax_type_no = lic.tax_type_no
                         AND ta1.tax_account_no = lic.tax_account_no
                         AND lic.estab_no = est.estab_no
                         AND est.enterprise_no = ep.enterprise_no
                         AND tt1.tax_basis_no = 1) tt,
                     tax_form tf,
                     tax_period tp,
                     tax_period tpc,
                     attached_tax_form atf,
                     tax_form tfc,
                     tax_account tac,
                     tax_estab te,
                     license lic,
                     tax_acct_taxable_obj tato
               WHERE atf.consol_form_no = 309
                 AND atf.consol_version_no = 1
                 AND atf.attached_form_no = tf.form_no
                 AND atf.consol_form_no = tfc.form_no
                 AND atf.consol_form_no = tpc.form_no
                 AND atf.consol_version_no = tpc.version_no
                 AND atf.attached_form_no = tp.form_no
                 AND tp.tper_year = tpc.tper_year
                 AND NVL(tp.tper_month, -1) = NVL(tpc.tper_month, -1)
                 AND NVL(tp.tper_week, -1) = NVL(tpc.tper_week, -1)
                 AND NVL(tp.imposition_base_no, -1) =
                     NVL(tpc.imposition_base_no, -1)
                 AND (tp.imposition_base_no = NULL OR
                     NULL IS NULL)
                 AND tp.tper_type_no = tpc.tper_type_no
                 AND ta.tax_type_no = tf.tax_type_no
                 AND ta.tax_payer_no = 31554214
                 AND ta.tax_account_no = tt.tax_account_no
                 AND tac.tax_payer_no(+) = 31554214
                 AND tac.tax_type_no(+) = tfc.tax_type_no
                 --
                 AND te.tax_account_no(+)  = ta.tax_account_no
                 AND (tp.tper_start_date  <= nvl(te.end_date,tp.tper_start_date+1) AND tp.tper_end_date >= nvl(te.regist_date,tp.tper_end_date-1))
                 --
                 AND lic.tax_account_no(+) = ta.tax_account_no
                 AND (tp.tper_start_date  <= nvl(lic.end_date,tp.tper_start_date+1) AND tp.tper_end_date >= nvl(lic.regist_date,tp.tper_end_date-1))
                 --
                 AND tato.tax_account_no(+)= ta.tax_account_no
                 AND (tp.tper_start_date  <= nvl(tato.end_date,tp.tper_start_date+1) AND tp.tper_end_date >= nvl(tato.start_date,tp.tper_end_date-1))
                 --
                 AND nvl(tt.chargeable_fl, 'Y') = 'Y'
                 AND (ta.tax_centre_no = NULL OR NULL IS NULL)
                 AND ((tp.tper_type_no = 2 AND
                     tp.tper_year >=
                     (SELECT MIN(fy.fiscal_year_from)
                          FROM fiscal_year fy
                         WHERE fy.tax_payer_no = 31554214)) OR
                     tp.tper_type_no != 2)
                 AND (ta.reg_date <
                     trunc(PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
                                                         ta.tax_payer_no,
                                                         tt.enterprise_no,
                                                         1)))
                 AND ta.close_date IS NULL
                 AND NOT EXISTS
                     (SELECT 1
                        FROM assessment ass
                       WHERE tax_account_no = ta.tax_account_no
                         AND ass.tax_period_no = tp.tax_period_no
                         AND NVL(ass.estab_no, -1) =
                             NVL(NVL(NULL, tt.estab_no), -1)
                         AND NVL(ass.license_no, -1) =
                             NVL(NVL(NULL, tt.license_no), -1)
                         AND ass.taxable_object_no IS NULL)
/*                 AND ((tt.end_date IS NULL AND
                     tt.regist_date <=
                     trunc(pck_tax_period.txprd_date_fn(tp.tax_period_no,
                                                          ta.tax_payer_no,
                                                          tt.enterprise_no,
                                                          1,
                                                          1))) OR
                     (tt.estab_no IS NULL AND tt.license_no IS NULL))
*/
                 AND NOT EXISTS
                     (SELECT d.DOC_STATE_NO 
                        FROM document d
                       WHERE d.doc_type_no   = 1
                         AND d.tax_period_no = tpc.tax_period_no
                         AND d.tax_payer_no  = 31554214
                         AND d.tax_type_no   = 273
                         AND d.tax_centre_no = ta.tax_centre_no
                         AND doc_state_no <> 3
                         AND (d.received_date IS NOT NULL OR
                             d.received_incomplete_date IS NOT NULL OR
                             d.printed_date IS NOT NULL))
                    --
                 AND ((pck_tax_roll.check_inactivity_period(ta.tax_account_no,
                                                            tt.estab_no,
                                                            tt.license_no,
                                                            NULL,
                                                            PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
                                                                                         31554214,
                                                                                         tt.enterprise_no,
                                                                                         2),
                                                            PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
                                                                                         31554214,
                                                                                         tt.enterprise_no,
                                                                                         1),
                                                            2,
                                                            1) = 0) OR
                     NULL = 'Y')
                 AND (pck_tax_roll.check_inactivity_period(ta.tax_account_no,
                                                           tt.estab_no,
                                                           tt.license_no,
                                                           NULL,
                                                           PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
                                                                                        ta.tax_payer_no,
                                                                                        tt.enterprise_no,
                                                                                        2),
                                                           PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
                                                                                        ta.tax_payer_no,
                                                                                        tt.enterprise_no,
                                                                                        1),
                                                           5,
                                                           1) = 0)
                 AND (pck_tax_roll.check_inactivity_period(ta.tax_account_no,
                                                           tt.estab_no,
                                                           tt.license_no,
                                                           NULL,
                                                           PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
                                                                                        31554214,
                                                                                        tt.enterprise_no,
                                                                                        2),
                                                           PCK_TAX_PERIOD.TXPRD_DATE_FN(tp.tax_period_no,
                                                                                        31554214,
                                                                                        tt.enterprise_no,
                                                                                        1),
                                                           4,
                                                           1) = 0)
              --
              ) unfil
       WHERE unfil.tper_start_date BETWEEN TO_DATE('11/01/2018', 'MM/DD/YYYY') AND TO_DATE('11/30/2018', 'MM/DD/YYYY')
         AND unfil.tper_end_date   BETWEEN TO_DATE('11/01/2018', 'MM/DD/YYYY') AND TO_DATE('11/30/2018', 'MM/DD/YYYY')
         AND ((unfil.tax_period_no = 2836
              AND
               2836     = 2836)
            OR
             (2836 <> 2836));

The above query retrieves one row.

The document state can be found at the bottom or the last query (near AND NOT EXISTS in the inline view) i.e.

 AND NOT EXISTS
                     (SELECT d.DOC_STATE_NO 
                        FROM document d
                       WHERE d.doc_type_no   = 1
                         AND d.tax_period_no = tpc.tax_period_no
                         AND d.tax_payer_no  = 31554214
                         AND d.tax_type_no   = 273
                         AND d.tax_centre_no = ta.tax_centre_no
                         AND doc_state_no <> 3
                         AND (d.received_date IS NOT NULL OR
                             d.received_incomplete_date IS NOT NULL OR
                             d.printed_date IS NOT NULL))

Re: How to get or concatenate a value from inner most query [message #676114 is a reply to message #676105] Wed, 15 May 2019 03:44 Go to previous messageGo to next message
cookiemonster
Messages: 13708
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's zero way to get any values out of a NOT EXISTS sub-query.
If you think about it it should be obvious that that is logically impossible - you only want rows from the main-query when the sub-query returns no rows.
In fact with (NOT) EXISTS sub-queries the columns/values selected make zero difference to the outcome so may as well just SELECT NULL.

If you want a doc_state_no in your result set then you're going to need a seperate join to the document table.
What that should look like or where it should go we can't possible say since we know nothing about your tables or the relationships between them.
Re: How to get or concatenate a value from inner most query [message #676141 is a reply to message #676114] Wed, 15 May 2019 15:05 Go to previous message
buggleboy007
Messages: 207
Registered: November 2010
Location: Canada
Senior Member
I approached the issue in the following way (a two step process) and resolved my concern:

a) first one is using the original query as it is and fetching the required values
b) second is creating a new query that looks at DOCUMENT table with certain parameters in the WHERE clause

I did the above even before I posted my question. My main motive was to see if we could retrieve it in one go. It appears that it cannot be. Thank you CM for your feedback and yes, through your explanation I learnt something new (logically to reason it out; though I could not express it).







[Updated on: Wed, 15 May 2019 15:06]

Report message to a moderator

Previous Topic: Update in EXECUTE IMMEDIATE
Next Topic: month as columns
Goto Forum:
  


Current Time: Mon Nov 11 18:22:46 CST 2019