February 9, 2007 at 6:29 am
I am trying to get the large select statement to reference only itinerary.itin_booking_num referenced in the first select statement.
Not sure if it is possible to cache the first tables results and then run the second statement against it or can the two statements be joined and run in one go.
Any help much appreciated.
select distinct itinerary.itin_booking_num, itinerary.itin_date_booked from itinerary where itinerary.itin_date_booked = today
select itinerary.itin_booking_num, itinerary.itin_revision_num, itinerary.itin_delta_num, itinerary.itin_item_id_num, itinerary.itin_line_num, itinerary.itin_parent_id, itinerary.itin_service_type, itinerary.itin_operator_code, itinerary.itin_service_info, itinerary.itin_service_class, itinerary.itin_departure, itinerary.itin_arrival, itinerary.itin_from_city, itinerary.itin_from_city_loc, itinerary.itin_to_city, itinerary.itin_to_city_loc, itinerary.itin_status, itinerary.itin_date_booked, itinerary.itin_option_date, itinerary.itin_supplier_code, itinerary.itin_pricing_group, itinerary.itin_fare_basis_1, itinerary.itin_fare_basis_2, itinerary.itin_consultant, itinerary.itin_request_codes, itinerary.itin_request_flags, itinerary.itin_sale_price, itinerary.itin_number_pax, itinerary.itin_short_desc, itinerary.itin_item_ticketed, itinerary.itin_invoiced_rev, issued_mop.mop_locator, issued_mop.mop_type, issued_mop.mop_description, issued_mop.mop_amount, issued_mop.mop_tax_cost, issued_mop.mop_tax_info, issued_mop.mop_xchg_tkt_info, issued_tkt.tkt_booking_num, issued_tkt.tkt_item_id_num, issued_tkt.tkt_invoice_rev, issued_tkt.tkt_supplier, issued_tkt.tkt_number, issued_tkt.tkt_pax_name, issued_tkt.tkt_routing, issued_tkt.tkt_date, issued_tkt.tkt_fare, issued_tkt.tkt_nett, issued_tkt.tkt_comm, issued_tkt.tkt_mop_locator, issued_tkt.tkt_location, issued_tkt.tkt_division, issued_tkt.tkt_rebate_code, issued_tkt.tkt_rebate_underp, issued_tkt.tkt_fare_basis, issued_tkt.tkt_tour_code, issued_tkt.tkt_rate_of_exchg, issued_tkt.tkt_consultant, issued_tkt.tkt_comm_amt, itinerary,issued_mop,issued_tkt
from itinerary,issued_mop,issued_tkt,itinerary
where itinerary.itin_booking_num = issued_tkt.tkt_booking_num and issued_tkt.tkt_item_id_num = itinerary.itin_item_id_num and issued_mop.mop_locator = issued_tkt.tkt_mop_locator
Thanks in advance
Frank
February 9, 2007 at 7:05 am
make the first statement a subquery and JOIN it to the second, e.g.
SELECT i.itin_booking_num, i.itin_revision_num, i.itin_delta_num, i.itin_item_id_num, i.itin_line_num,
i.itin_parent_id, i.itin_service_type, i.itin_operator_code, i.itin_service_info, i.itin_service_class,
i.itin_departure, i.itin_arrival, i.itin_from_city, i.itin_from_city_loc, i.itin_to_city, i.itin_to_city_loc,
i.itin_status, i.itin_date_booked, i.itin_option_date, i.itin_supplier_code, i.itin_pricing_group,
i.itin_fare_basis_1, i.itin_fare_basis_2, i.itin_consultant, i.itin_request_codes, i.itin_request_flags,
i.itin_sale_price, i.itin_number_pax, i.itin_short_desc, i.itin_item_ticketed, i.itin_invoiced_rev,
mop.mop_locator, mop.mop_type, mop.mop_description, mop.mop_amount, mop.mop_tax_cost,
mop.mop_tax_info, mop.mop_xchg_tkt_info, tkt.tkt_booking_num, tkt.tkt_item_id_num, tkt.tkt_invoice_rev,
tkt.tkt_supplier, tkt.tkt_number, tkt.tkt_pax_name, tkt.tkt_routing, tkt.tkt_date, tkt.tkt_fare,
tkt.tkt_nett, tkt.tkt_comm, tkt.tkt_mop_locator, tkt.tkt_location, tkt.tkt_division, tkt.tkt_rebate_code,
tkt.tkt_rebate_underp, tkt.tkt_fare_basis, tkt.tkt_tour_code, tkt.tkt_rate_of_exchg, tkt.tkt_consultant,
tkt.tkt_comm_amt
FROM itinerary i
INNER JOIN issued_tkt tkt
ON i.itin_booking_num = tkt.tkt_booking_num
AND i.itin_item_id_num = tkt.tkt_item_id_num
INNER JOIN issued_mop mop ON tkt.tkt_mop_locator = mop.mop_locator
INNER JOIN (SELECT DISTINCT b.itin_booking_num FROM itinerary b WHERE b.itin_date_booked = [today]) a
ON a.itin_booking_num = i.itin_booking_num
Far away is close at hand in the images of elsewhere.
Anon.
February 9, 2007 at 7:53 am
Many thanks for the above - I am running DTS from SQL server 2005 to an old informix database. When I parse the query I get the following message ERROR [42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred. (iclit09b.dll)
Any ideas?
February 9, 2007 at 8:53 am
I don't know Informix but suspect it is probably due to multiple joins using ansi-92 syntax. I think Informix requires the query like this
SELECT i.itin_booking_num, i.itin_revision_num, i.itin_delta_num, i.itin_item_id_num, i.itin_line_num,
i.itin_parent_id, i.itin_service_type, i.itin_operator_code, i.itin_service_info, i.itin_service_class,
i.itin_departure, i.itin_arrival, i.itin_from_city, i.itin_from_city_loc, i.itin_to_city, i.itin_to_city_loc,
i.itin_status, i.itin_date_booked, i.itin_option_date, i.itin_supplier_code, i.itin_pricing_group,
i.itin_fare_basis_1, i.itin_fare_basis_2, i.itin_consultant, i.itin_request_codes, i.itin_request_flags,
i.itin_sale_price, i.itin_number_pax, i.itin_short_desc, i.itin_item_ticketed, i.itin_invoiced_rev,
mop.mop_locator, mop.mop_type, mop.mop_description, mop.mop_amount, mop.mop_tax_cost,
mop.mop_tax_info, mop.mop_xchg_tkt_info, tkt.tkt_booking_num, tkt.tkt_item_id_num, tkt.tkt_invoice_rev,
tkt.tkt_supplier, tkt.tkt_number, tkt.tkt_pax_name, tkt.tkt_routing, tkt.tkt_date, tkt.tkt_fare,
tkt.tkt_nett, tkt.tkt_comm, tkt.tkt_mop_locator, tkt.tkt_location, tkt.tkt_division, tkt.tkt_rebate_code,
tkt.tkt_rebate_underp, tkt.tkt_fare_basis, tkt.tkt_tour_code, tkt.tkt_rate_of_exchg, tkt.tkt_consultant,
tkt.tkt_comm_amt
FROM (((itinerary i
INNER JOIN issued_tkt tkt
(ON i.itin_booking_num = tkt.tkt_booking_num
AND i.itin_item_id_num = tkt.tkt_item_id_num))
INNER JOIN issued_mop mop ON (tkt.tkt_mop_locator = mop.mop_locator))
INNER JOIN (SELECT DISTINCT b.itin_booking_num FROM itinerary b WHERE (b.itin_date_booked = [today])) a
ON (a.itin_booking_num = i.itin_booking_num))
Far away is close at hand in the images of elsewhere.
Anon.
February 9, 2007 at 9:10 am
Again thank you, same results. I have asked an Informix DBA and he has advised that is may be due to teh version of Informix (7.31 UC5) not being able to handle it.
February 9, 2007 at 9:28 am
The only other thing I can suggest is to use your original code, check that each query's syntax is correct and add the first query code to the end of the second like this
and itinerary.itin_booking_num = ALL (select distinct itinerary.itin_booking_num from itinerary where itinerary.itin_date_booked = today)
Other than that not sure can help you further.
Far away is close at hand in the images of elsewhere.
Anon.
February 12, 2007 at 1:51 am
I have ran the above statement and it returns the same error. If I do a simply select statement it returns information so I gather it is due to it cannot handle complex queries. Thanks again.
February 12, 2007 at 8:29 pm
Why not have Informix do an export to, say, a comma or tab delimited file and then DTS that bad boy in?
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply