ORA-01789: query block has incorrect number of result columns

450
January 15, 2018, at 5:34 PM

When I tried to execute the given query through java I'm getting 'query block has an incorrect number of result columns' error. But when I run it separately it is producing the accurate results. Don't know what's wrong with the query. Could anyone help me with this? I am struggling with this for more than two days.

"select Project_Code"+
                          " ,trunc(Po_Issued_Date)"+
                          "  ,trunc(Expected_Receipt_Date)"+
                           " ,trunc(actual_receipt_date) "+
                           " ,Supplier_Name "+
                           " ,PO_Number"+
                           " ,Release_Num"+
                           " ,Item_Code"+
                           " ,Item_Description"+
                           " ,Supplier_Item_Number "+
                           " ,Mfg_Part_Number "+
                           " ,Currency_Code"+
                           " ,Unit_Price  "+
                           " ,PO_Quantity  "+
                           " ,sum(quantity_received)"   +                 
                           " ,(Unit_Price * sum(quantity_received)) "+
                           " ,receipt_num"+
                           " ,Buyer_Name "+
                           " ,invoice_num"+
                          "  ,invoice_date "+
                           " ,po_header_id "+
                             " ,po_line_id"+
                       " from (select msi.ATTRIBUTE13"+
                           " ,pha.approved_date"+
                          "  ,rsh.expected_receipt_date"+
                           " ,rt.transaction_date"+
                           " ,aps.vendor_name"+
                           " ,pha.segment1"+
                           " ,null"+
                           " ,msi.segment1"+
                           " ,pla.item_description"+
                          "  ,(select primary_vendor_item from apps.po_approved_supplier_list"+
                               " where item_id        = pla.item_id"+
                               " and vendor_id        = pha.vendor_id"+
                              "  and vendor_site_id    = pha.vendor_site_id"+
                              "  and nvl(disable_flag,'N')='N'"+
                            " )    Supplier_Item_Number"+
                           " ,msi.ATTRIBUTE12"+
                           " ,pha.currency_code"+
                           " ,pla.unit_price"+
                           " ,pla.quantity"+
                           " ,rsl.quantity_received"+
                           " ,rsh.receipt_num"+
                           " ,(select full_name from apps.per_all_people_f"+
                               " where person_id    = pha.agent_id"+
                              "  and sysdate between nvl(effective_start_date,sysdate) and nvl(effective_end_date,sysdate)"+
                           "  )Buyer_Name"+
                          " ,aia.invoice_num"+
                          " ,aia.invoice_date"+
                           " ,pha.po_header_id "+
                           " ,msi.inventory_item_id"+
                           " ,pla.po_line_id"+
                           " ,pll.line_location_id"+
                           " ,pda.po_distribution_id"+
                           " ,rsl.shipment_line_id"+
                           " ,rt.transaction_id"+
                       " from apps.po_headers_all            pha"+
                           " ,apps.po_lines_all            pla"+
                           " ,apps.po_line_locations_all    pll"+
                          "  ,apps.po_distributions_all    pda"+
                          "  ,apps.mtl_system_items        msi"+
                          "  ,apps.ap_suppliers            aps"+
                           " ,apps.ap_supplier_sites_all    ass"+
                           " ,apps.rcv_shipment_headers    rsh"+
                           " ,apps.rcv_shipment_lines        rsl"+
                         "   ,apps.rcv_transactions        rt"+
                         " ,apps.ap_invoices_all        aia"+
                         " ,apps.ap_invoice_lines_all    ail"+
                       " where pha.org_id                = 81"+
                       " and trunc(rsh.expected_receipt_date) between '01-Jan-2017' and '30-Sep-2017'"+
                       " and pha.type_lookup_code        = 'STANDARD'"+
                       " and msi.item_type                = 'IDM'"+
                       " and rt.transaction_type            ='DELIVER'"+
                       " and pha.vendor_id                = aps.vendor_id"+
                       " and pha.vendor_site_id            = ass.vendor_site_id"+
                       " and pha.org_id                    = ass.org_id"+
                       " and pha.org_id                    = pla.org_id"+
                       " and pha.po_header_id            = pla.po_header_id"+
                       " and pla.org_id                    = pll.org_id"+
                       " and pla.po_header_id            = pll.po_header_id"+
                       " and pla.po_line_id                = pll.po_line_id"+
                       " and pll.org_id                    = pda.org_id"+
                       " and pll.po_header_id            = pda.po_header_id"+
                       " and pll.po_line_id                = pda.po_line_id"+
                       " and pll.line_location_id        = pda.line_location_id"+
                       " and pla.item_id                    = msi.inventory_item_id"+
                       " and msi.organization_id            = pll.ship_to_organization_id"+
                       " and pda.po_header_id            = rsl.po_header_id"+
                       " and pda.po_line_id                = rsl.po_line_id"+
                       " and pda.line_location_id        = rsl.po_line_location_id"+
                       " and pda.po_distribution_id        = rsl.po_distribution_id"+
                       " and rsl.shipment_header_id        = rsh.shipment_header_id"+
                       " and rsl.shipment_header_id        = rt.shipment_header_id"+
                       " and rsl.shipment_line_id        = rt.shipment_line_id"+
                       " and pda.po_header_id                = ail.po_header_id(+)"+
                       " and pda.po_line_id                    = ail.po_line_id(+)"+
                       " and pda.line_location_id        = ail.po_line_location_id(+)"+
                       " and pda.po_distribution_id    = ail.po_distribution_id(+)"+
                       " and ail.line_type_lookup_code(+)= 'ITEM'"+
                       " and ail.invoice_id                    = aia.invoice_id(+)"+
                       " union"+
                       " select msi.ATTRIBUTE13"+
                           " ,pra.approved_date"+
                           " ,rsh.expected_receipt_date"+
                           " ,rt.transaction_date"+
                           " ,aps.vendor_name"+
                           " ,pha.segment1"+
                           " ,pra.release_num"+
                           " ,msi.segment1"+
                           " ,pla.item_description"+
                           " ,(select primary_vendor_item from apps.po_approved_supplier_list"+
                               " where item_id        = pla.item_id"+
                               " and vendor_id        = pha.vendor_id"+
                               " and vendor_site_id    = pha.vendor_site_id"+
                               " and nvl(disable_flag,'N')='N'"+
                             " )    Supplier_Item_Number"+
                           " ,msi.ATTRIBUTE12"+
                           " ,pha.currency_code"+
                           " ,pla.unit_price"+
                           " ,pll.quantity"+
                           " ,rsl.quantity_received"+
                           " ,rsh.receipt_num"+
                           " ,(select full_name from apps.per_all_people_f"+
                              " where person_id    = pha.agent_id"+
                               " and sysdate between nvl(effective_start_date,sysdate) and nvl(effective_end_date,sysdate)"+
                                " )Buyer_Name"+
                          " ,aia.invoice_num"+
                          " ,aia.invoice_date"+
                           " ,pha.po_header_id "+
                           " ,msi.inventory_item_id"+
                           " ,pla.po_line_id"+
                           " ,pll.line_location_id"+
                           " ,pda.po_distribution_id"+
                           " ,rsh.shipment_header_id"+
                           " ,rsl.shipment_line_id"+
                           " ,rt.transaction_id"+
                       " from apps.po_headers_all            pha"+
                           " ,apps.po_releases_all        pra"+
                           " ,apps.po_lines_all            pla"+
                          " ,apps.po_line_locations_all    pll"+
                           " ,apps.po_distributions_all    pda"+
                           " ,apps.mtl_system_items        msi"+
                          "  ,apps.ap_suppliers            aps"+
                           " ,apps.ap_supplier_sites_all    ass"+
                          " ,apps.rcv_shipment_headers    rsh"+
                          "  ,apps.rcv_shipment_lines        rsl"+
                         "   ,apps.rcv_transactions        rt"+
                         " ,apps.ap_invoices_all        aia"+
                         " ,apps.ap_invoice_lines_all    ail"+
                       " where pha.org_id            = 81"+
                       " and trunc(rsh.expected_receipt_date) between '01-Jan-2017' and '30-Sep-2017'"+
                       " and msi.item_type            = 'IDM'"+
                       " and rt.transaction_type        = 'DELIVER'"+
                       " and pha.type_lookup_code    = 'BLANKET'"+
                       " and pha.org_id                = pra.org_id"+
                       " and pha.po_header_id        = pra.po_header_id"+
                       " and pha.org_id                = pla.org_id"+
                       " and pha.po_header_id        = pla.po_header_id"+
                       " and pla.org_id                = pll.org_id"+
                       " and pla.po_header_id        = pll.po_header_id"+
                       " and pla.po_line_id            = pll.po_line_id"+
                       " and pra.po_release_id        = pll.po_release_id"+
                       " and pll.org_id                = pda.org_id"+
                       " and pll.po_header_id        = pda.po_header_id"+
                       " and pll.po_line_id            = pda.po_line_id"+
                       " and pll.line_location_id    = pda.line_location_id"+
                       " and pll.po_release_id        = pda.po_release_id"+
                       " and pla.item_id                = msi.inventory_item_id"+
                       " and pll.ship_to_organization_id=msi.organization_id"+
                       " and pha.vendor_id            = aps.vendor_id"+
                       " and pha.org_id                = ass.org_id"+
                       " and pha.vendor_site_id        = ass.vendor_site_id"+
                       " and pll.ship_to_organization_id=rsl.to_organization_id"+
                       " and pda.po_header_id        = rsl.po_header_id"+
                       " and pda.po_release_id        = rsl.po_release_id"+
                       " and pda.po_line_id            = rsl.po_line_id"+
                       " and pda.line_location_id    = rsl.po_line_location_id"+
                       " and pda.po_distribution_id    = rsl.po_distribution_id"+
                       " and rsl.shipment_header_id    = rsh.shipment_header_id"+
                       " and rsl.shipment_header_id    = rt.shipment_header_id"+
                       " and rsl.shipment_line_id    = rt.shipment_line_id"+
                       " and pda.org_id                            = ail.org_id(+)"+
                       " and pda.po_header_id                = ail.po_header_id(+)"+
                       " and pda.po_line_id                    = ail.po_line_id(+)"+
                       " and pda.line_location_id        = ail.po_line_location_id(+)"+
                       " and pda.po_distribution_id    = ail.po_distribution_id(+)"+
                       " and ail.line_type_lookup_code(+)= 'ITEM'"+
                       " and ail.invoice_id                    = aia.invoice_id(+)"+
                       ") temp"+
                       " group by Project_Code"+
                             ",trunc(Po_Issued_Date)"+
                           ",trunc(Expected_Receipt_Date)"+
                           ",trunc(actual_receipt_date)"+
                          " ,Supplier_Name"+
                          " ,PO_Number"+
                          " ,Release_Num"+
                          " ,Item_Code"+
                          " ,Item_Description"+
                          " ,Supplier_Item_Number"+
                          " ,Mfg_Part_Number"+
                          " ,Currency_Code"+
                           ",Unit_Price"+
                           ",PO_Quantity"+
                          ",receipt_num"+
                           ",Buyer_Name"+
                       ",invoice_num"+
                          " ,invoice_date"+
                           ",po_header_id "+
                            " ,po_line_id"+
                       " order by PO_Number"+
                          ",item_code"
Answer 1

When having a UNION SELECT statement, number & type of columns fetched by each of them must match. If I saw it correctly, your first SELECT (starts at line 23) contains 26 columns, while its UNION pair (starts at line 102) contains 27 columns.

I didn't took time to match them one by one, I'll let you do it.

Rent Charter Buses Company
READ ALSO
How do you return an array and print it out in java? [on hold]

How do you return an array and print it out in java? [on hold]

I'm stuck on this questionMore specifically its the class with the countFeatures

191
Extract script element from jsp file

Extract script element from jsp file

I havejsp files in a local directory

268
JavaFX TreeView ChangeListener old value is always null

JavaFX TreeView ChangeListener old value is always null

I have a TreeView of ObjectsI want to add a ChangeListener that will draw my object on right pane and remove old one if present

322