Challenge to you SQL Experts!!!

The following SQL statement was generated from Crystal Reports. When executed, it generates a syntax error, with no other descriptive error messages. SELECT ship_line_tbl.cust_id, ship_line_tbl.part_num, ship_line_tbl.qty_ord, inventory_tbl.location, inventory_tbl.qty, ship_addr_tbl.sold_name, ship_addr_tbl.ship_name, dms_item_view.dms_item_desc1, dms_item_w_view.dms_list_price, dms_ship_via_view.dms_desc_1, pcktck_rsvd_qty.rsvd_qty FROM srswms_db:srs0232.ship_tbl ship_tbl, srswms_db:srs0232.ship_line_tbl ship_line_tbl, srswms_db:srs0232.ship_addr_tbl ship_addr_tbl, srswms_db:srs0232.dms_item_view dms_item_view, srswms_db:srs0232.dms_item_w_view dms_item_w_view, srswms_db:srs0232.dms_ship_via_view dms_ship_via_view, srswms_db:srs0232.pcktck_rsvd_qty pcktck_rsvd_qty, OUTER srswms_db:srs0232.inventory_tbl inventory_tbl WHERE ship_line_tbl.cust_id = inventory_tbl.cust_id AND ship_line_tbl.part_num = inventory_tbl.part_num AND ship_line_tbl.order_num = 58 AND ship_addr_tbl.order_num = 58 AND dms_item_view.dms_item_num = Trim(ship_line_tbl.cust_id) || "-" || ship_line_tbl.part_num AND dms_item_w_view.dms_whse_code = ship_line_tbl.whse_code AND dms_item_w_view.dms_item_num = dms_item_view.dms_item_num AND dms_ship_via_view.dms_ship_via_code = ship_tbl.ship_via AND pcktck_rsvd_qty.cust_id = ship_line_tbl.cust_id AND pcktck_rsvd_qty.part_num = ship_line_tbl.part_num AND ship_tbl.order_num = 58; If I comment out the two lines in the WHERE clause that start with pcktck_rsvd_qty, the syntax error is eliminated. If I comment out the line in the WHERE clause that has the TRIM function in it, the syntax error is also eliminated, meaning that it isn't the specific syntax of any of the lines in the WHERE clause. I've narrowed this down to the fact that I'm referencing some of the columns a fair number of times. I was receiving this same syntax error, before the "pcktck..." lines were added. This previous syntax error was eliminated by using the order number 58 in the WHERE clause, rather than referencing the order_num column in one of the tables. I don't believe it is the length of the SQL statement, because I used a, b, c, etc. for the ship_tbl, ship_line_tbl, etc. references and I still recieved the syntax error. I cut and pasted this SQL statement into two different applications and they also resulted in a syntax error. Is there a buffer size environment variable, or something else, that I need to increase to allow this SELECT to be accepted? The statement does not appear to be that complex to me. The Informix DB is running on a SCO-Unix server and I'm connecting through ODBC to access the DB from the client. Although I believe that I eliminated the client as a possible problem, because I received the same syntax error from within "dbaccess" on the SCO box. Any help would be greatly appreciated. I've been battling this problem long enough! Joel Sent via Deja.com http://www.deja.com/ Before you buy.

One additional piece of information I just discovered. The cursor always ends up on the 58 in the last line of the SQL statement. I tried changing the 58 to ship_tbl_line.order_num and this eliminated the syntax error. Unfortunately, it doesn't give me the correct results, but the syntax error seems to be related to the 58 at the end of the WHERE clause. I tried moving this last line of the WHERE clause to the beginning, and with the 58 in it, I still get the syntax error, but the cursor is diplayed on the "u" in ship_line_tbl.part_num, in the new last line of the WHERE clause. If I leave the "58" line in the beginning of the WHERE clause, but change the 58 to ship_line_tbl.order_num, the syntax error is eliminated. Any ideas? Joel In article <7vc6fu$m4f$1@nnrp1.deja.com>, jwz1@my-deja.com wrote: > The following SQL statement was generated from Crystal Reports. When > executed, it generates a syntax error, with no other descriptive error > messages. > > SELECT > ship_line_tbl.cust_id, ship_line_tbl.part_num, > ship_line_tbl.qty_ord, > inventory_tbl.location, inventory_tbl.qty, > ship_addr_tbl.sold_name, ship_addr_tbl.ship_name, > dms_item_view.dms_item_desc1, > dms_item_w_view.dms_list_price, > dms_ship_via_view.dms_desc_1, > pcktck_rsvd_qty.rsvd_qty > FROM > srswms_db:srs0232.ship_tbl ship_tbl, > srswms_db:srs0232.ship_line_tbl ship_line_tbl, > srswms_db:srs0232.ship_addr_tbl ship_addr_tbl, > srswms_db:srs0232.dms_item_view dms_item_view, > srswms_db:srs0232.dms_item_w_view dms_item_w_view, > srswms_db:srs0232.dms_ship_via_view dms_ship_via_view, > srswms_db:srs0232.pcktck_rsvd_qty pcktck_rsvd_qty, > OUTER srswms_db:srs0232.inventory_tbl inventory_tbl > WHERE > ship_line_tbl.cust_id = inventory_tbl.cust_id AND > ship_line_tbl.part_num = inventory_tbl.part_num AND > ship_line_tbl.order_num = 58 AND > ship_addr_tbl.order_num = 58 AND > dms_item_view.dms_item_num = Trim(ship_line_tbl.cust_id) || "-" || > ship_line_tbl.part_num AND > dms_item_w_view.dms_whse_code = ship_line_tbl.whse_code AND > dms_item_w_view.dms_item_num = dms_item_view.dms_item_num AND > dms_ship_via_view.dms_ship_via_code = ship_tbl.ship_via AND > pcktck_rsvd_qty.cust_id = ship_line_tbl.cust_id AND > pcktck_rsvd_qty.part_num = ship_line_tbl.part_num AND > ship_tbl.order_num = 58; > > If I comment out the two lines in the WHERE clause that start with > pcktck_rsvd_qty, the syntax error is eliminated. If I comment out the > line in the WHERE clause that has the TRIM function in it, the syntax > error is also eliminated, meaning that it isn't the specific syntax of > any of the lines in the WHERE clause. I've narrowed this down to the > fact that I'm referencing some of the columns a fair number of times. I > was receiving this same syntax error, before the "pcktck..." lines were > added. This previous syntax error was eliminated by using the order > number 58 in the WHERE clause, rather than referencing the order_num > column in one of the tables. > > I don't believe it is the length of the SQL statement, because I used > a, b, c, etc. for the ship_tbl, ship_line_tbl, etc. references and I > still recieved the syntax error. > > I cut and pasted this SQL statement into two different applications and > they also resulted in a syntax error. > > Is there a buffer size environment variable, or something else, that I > need to increase to allow this SELECT to be accepted? The statement > does not appear to be that complex to me. > > The Informix DB is running on a SCO-Unix server and I'm connecting > through ODBC to access the DB from the client. Although I believe that > I eliminated the client as a possible problem, because I received the > same syntax error from within "dbaccess" on the SCO box. > > Any help would be greatly appreciated. I've been battling this problem > long enough! > > Joel > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.