3 Replies Latest reply on Nov 28, 2016 11:13 PM by Vadim Tropashko-Oracle

    question re autotrace

    Murray Sobol

      I am trying to run "Autotrace" on a query but I keep getting this error: ORA-00933: SQL command not properly ended.

       

      But, when I run the query I get the expected results.

       

      Here is the SQL;

      SELECT s1_quantity_entry.plc_id,

             s1_name_and_address_a.parent_record_id,

             s1_quantity_entry.vendor_id,

             s1_quantity_entry.scale_ticket_relationship,

             s1_quantity_entry.ship_mode_code,

             s1_quantity_entry.delivery_sheet,

             s1_quantity_entry.scale_ticket_id,

             s1_quantity_entry.scale_ticket_trans_date,

             s1_quantity_entry.scale_ticket_trans_time,

             s1_quantity_entry.scale_ticket_trans_am_pm,

             s1_quantity_entry.vehicle_id,

             s1_quantity_entry.scale_ticket_gross_weight,

             s1_quantity_entry.scale_ticket_tare_weight,

             s1_quantity_entry.scale_uom_code,

             s1_contact_person.phone_nbr,

             s1_contact_person.toll_free_phone_nbr,

             s1_quantity_entry.quantity_entry_nbr,

             s1_quantity_entry.scale_ticket_inbound_outbound,

             s1_quantity_entry.commodity_id,

             s1_quantity_entry.source,

             s1_name_and_address_c.full_name,

             s1_quantity_entry.scale_ticket_comment,

             s1_name_and_address_a.full_name,

             s1_name_and_address_b.full_name,

             s1_quantity_entry.scale_ticket_out_time,

             s1_quantity_entry.scale_ticket_out_am_pm,

             s1_quantity_entry.bill_of_lading,

             s1_quantity_entry.scale_hold_quantity_lb,

             s1_quantity_entry.scale_quantity_lb,

             s1_quantity_entry.reference_code,

             s1_plc_data.sig_tkt_prn_flag,

             s1_quantity_entry.origin_city,

             s1_quantity_entry.scale_ticket_void_flag,

             s1_quantity_entry.city_name,

             s1_quantity_entry.scale_ticket_reject_flag,

             s1_quantity_entry.state_province_code,

             s1_uom_conv_10000_a.to_nbr_of_decimals,

             s1_uom_conv_10000_a.to_uom,

             s1_uom_conv_10000_b.to_uom,

             s1_uom_conv_10000_b.conv_factor,

             s1_uom_conv_10000_b.to_nbr_of_decimals,

             s1_uom_conv_10000_a.conv_factor,

             s1_uom_conv_10000_c.conv_factor,

             s1_quantity_entry.quantity_entry_status,

             s1_quantity_entry.scale_ticket_driver_flag,

             s1_quantity_entry.freight_uom_code,

             s1_quantity_entry.train_barge,

             s1_quantity_entry.gross_entry_status,

             s1_quantity_entry.tare_entry_status,

             s1_name_and_address_d.full_name,

             s1_quantity_entry.freight_carrier_id,

             s1_quantity_entry.invisible_vehicle_flag,

             s1_name_and_address_a.address_line_1,

             s1_name_and_address_a.address_line_2,

             s1_name_and_address_a.city,

             s1_name_and_address_a.state_province_code,

             s1_name_and_address_a.zip_postal_code,

             s1_name_and_address_a.state_province_name,

             s1_name_and_address_a.country_code,

             s1_name_and_address_a.print_prov_name_flag,

             s1_name_and_address_c.address_line_1,

             s1_name_and_address_c.address_line_2,

             s1_name_and_address_c.city,

             s1_name_and_address_c.state_province_code,

             s1_name_and_address_c.zip_postal_code,

             s1_name_and_address_c.state_province_name,

             s1_name_and_address_c.country_code,

             s1_name_and_address_c.print_prov_name_flag,

             s1_ship_mode.note,

             s1_plc_data.cacrb_ticket_print_flag,

          '                                                                                                                                ' as signature,

          s1_name_and_address_c.language_code,

          s1_ship_mode.description,

          s1_country_a.print_pc_before_city_flag,

          s1_country_b.print_pc_before_city_flag,

          s1_ship_mode.ticket_title,

          s1_name_and_address_a.print_country_on_document_flag,

          s1_name_and_address_c.print_country_on_document_flag,

          s1_plc_data.print_user_name_on_ticket_flag,

          s1_quantity_entry.oe_order_nbr,

          s1_quantity_entry.orderlog_nbr,

          s1_plc_data.print_ship_na_on_ticket_flag,

          s1_commodity_tester.tester_name,

          s1_commodity_tester.signature_file,

          s1_plc_data.print_on_tkt_comm_tester_flag,

             case when s1_quantity_entry.field_barn_id = '(None)'

                      then null

                      else s1_quantity_entry.field_barn_id

             end as field_barn_id

        FROM s1_quantity_entry

             LEFT OUTER JOIN s1_name_and_address s1_name_and_address_d

               ON s1_quantity_entry.freight_carrier_id = s1_name_and_address_d.name_and_address_id

             LEFT OUTER JOIN s1_commodity_tester

               ON s1_quantity_entry.tester_id = s1_commodity_tester.tester_id,

             s1_name_and_address s1_name_and_address_a,

             s1_contact_person,

             s1_name_and_address s1_name_and_address_b,

             s1_name_and_address s1_name_and_address_c,

             s1_plc_data,

             s1_plc_commodity,

             s1_uom_conv_10000 s1_uom_conv_10000_a,

             s1_uom_conv_10000 s1_uom_conv_10000_b,

             s1_uom_conv_10000 s1_uom_conv_10000_c,

             a1_user_location,

             s1_ship_mode,

             s1_country s1_country_a,

             s1_country s1_country_b

      WHERE ( s1_name_and_address_a.name_and_address_id = s1_contact_person.name_and_address_id )

         and ( s1_quantity_entry.plc_id = s1_name_and_address_a.name_and_address_id )

         and ( s1_name_and_address_a.parent_record_id = s1_name_and_address_b.name_and_address_id )

         and ( s1_quantity_entry.vendor_id = s1_name_and_address_c.name_and_address_id )

         and ( s1_quantity_entry.plc_id = s1_plc_data.plc_id )

         and ( s1_quantity_entry.plc_id = s1_plc_commodity.plc_id )

         and ( s1_quantity_entry.commodity_id = s1_plc_commodity.commodity_id )

         and ( s1_plc_commodity.receiving_uom_code = s1_uom_conv_10000_a.to_uom )

         and ( s1_plc_commodity.receiving_uom_code = s1_uom_conv_10000_c.to_uom )

         and ( s1_quantity_entry.scale_uom_code = s1_uom_conv_10000_c.from_uom )

         and ( s1_quantity_entry.plc_id = a1_user_location.plc_id )

         and ( s1_quantity_entry.ship_mode_code = s1_ship_mode.ship_mode_code )

         and ( s1_name_and_address_a.country_code = s1_country_a.description )

         and ( s1_name_and_address_c.country_code = s1_country_b.description )

         and ( s1_quantity_entry.scale_ticket_inbound_outbound = 'I' )

         AND ( s1_uom_conv_10000_a.from_uom = 'LB' )

         AND ( s1_uom_conv_10000_b.from_uom = 'LB' )

         AND (s1_quantity_entry.printed_flag like '%')

         AND ( s1_contact_person.contact_person_id = '1' )

         AND ( s1_quantity_entry.direct_ship_flag = 'N' )

         AND ( a1_user_location.user_id = 'MASTER' )

         AND ((s1_uom_conv_10000_c.commodity_id is NULL) OR

              (s1_uom_conv_10000_c.commodity_id = s1_plc_commodity.commodity_id)

             )

         AND ((s1_uom_conv_10000_a.commodity_id is NULL) OR

              (s1_uom_conv_10000_a.commodity_id = s1_plc_commodity.commodity_id)

             )

         AND ((s1_uom_conv_10000_b.commodity_id is NULL) OR

              (s1_uom_conv_10000_b.commodity_id = s1_plc_commodity.commodity_id)

             )

          AND ((('I' = 'I') AND

                (s1_uom_conv_10000_b.to_uom = s1_plc_commodity.buying_uom_code)

               ) OR

               (('I' = 'O') AND

                (s1_uom_conv_10000_b.to_uom = s1_plc_commodity.selling_uom_code)

               )

              )

          AND s1_quantity_entry.quantity_entry_status <> 'D'

          and (s1_quantity_entry.scale_ticket_trans_date >= to_date('2016-09-04','yyyy-mm-dd'))

          and (s1_quantity_entry.scale_ticket_trans_date < to_date('2016-09-11','yyyy-mm-dd'))

          and (s1_quantity_entry.plc_id = '1')

      /

      Thanks

      Murray