Hello All,
I have spent much time trying to learn the correct terminology to find an answer for my problem but I cannot seem to be able to do it. Here is what I am doing and what is happening. Hopefully someone can tell me why this happens and can clue me in on the right terminology to be able to search for this issue on the internet.
I have an Excel workbook that I am loading data into from an IBM Informix Dynamic Server Version 10.00.UC5W5 database using a System DSN set up using the 32 bit Informix 3.34 driver. I have run into a problem where if I add a line to this query I get an error as such
DataSource.Error: ODBC: An error occurred but no ODBC error information was available.
Details:
DataSourceKind=Odbc
DataSourcePath=dsn=apropos
OdbcErrors=[Table]
Ok, here is the main query and yes, it's horrible cause I am teaching myself this on the fly but at least it works
SELECT DISTINCT((CASE WHEN "001" NOT IN (SELECT store_id FROM item_stores WHERE inv_id3 = item_id AND store_id = '001' AND on_hand_qty > '0' ) THEN "" ELSE "001, " END)
|| (CASE WHEN '005' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '005' AND on_hand_qty > '0' ) THEN "" ELSE "005, " END)
|| (CASE WHEN '007' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '007' AND on_hand_qty > '0' ) THEN "" ELSE "007, " END)
|| (CASE WHEN '009' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '009' AND on_hand_qty > '0' ) THEN "" ELSE "009, " END)
|| (CASE WHEN '012' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '012' AND on_hand_qty > '0' ) THEN "" ELSE "012, " END)
|| (CASE WHEN '014' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '014' AND on_hand_qty > '0' ) THEN "" ELSE "014, " END)
|| (CASE WHEN '14' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '14' AND on_hand_qty > '0' ) THEN "" ELSE "14, " END)
|| (CASE WHEN '022' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '022' AND on_hand_qty > '0' ) THEN "" ELSE "022, " END)
|| (CASE WHEN '031' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '031' AND on_hand_qty > '0' ) THEN "" ELSE "031, " END)
|| (CASE WHEN '99' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '99' AND on_hand_qty > '0' ) THEN "" ELSE "99, " END)
|| (CASE WHEN '100' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '100' AND on_hand_qty > '0' ) THEN "" ELSE "100," END)
|| (CASE WHEN '101' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '101' AND on_hand_qty > '0' ) THEN "" ELSE "101, " END)
|| (CASE WHEN '500' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '500' AND on_hand_qty > '0' ) THEN "" ELSE "500, " END)
|| (CASE WHEN '512' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '512' AND on_hand_qty > '0' ) THEN "" ELSE "512, " END)
|| (CASE WHEN '555' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '555' AND on_hand_qty > '0' ) THEN "" ELSE "555, " END)
|| (CASE WHEN '777' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '777' AND on_hand_qty > '0' ) THEN "" ELSE "777, " END)
|| (CASE WHEN '888' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '888' AND on_hand_qty > '0' ) THEN "" ELSE "888, " END)
|| (CASE WHEN '900' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '900' AND on_hand_qty > '0' ) THEN "" ELSE "900, " END)
|| (CASE WHEN '999' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '999' AND on_hand_qty > '0' ) THEN "" ELSE "999, " END)) AS _resides_in
, inv_id3
, CASE
WHEN inv_user3 LIKE '%HOLIDAY%'
THEN "HOLIDAY"
WHEN inv_user3 LIKE '%COC%'
THEN "COC"
WHEN inv_user3 LIKE '%OC%'
THEN "COC"
WHEN inv_user3 LIKE '%CLOSE%'
THEN "COC"
WHEN inv_user3 = 'NC' AND inv_cons_date > (TODAY - 30 UNITS DAY)
THEN "NC30"
WHEN inv_user3 = 'NC' AND inv_cons_date BETWEEN (TODAY - 31 UNITS DAY) AND (TODAY - 60 UNITS DAY)
THEN "NC60"
WHEN inv_user3 = 'NC' AND inv_cons_date BETWEEN (TODAY - 61 UNITS DAY) AND (TODAY - 90 UNITS DAY)
THEN "NC90"
WHEN inv_user3 = 'NC' AND inv_cons_date < (TODAY - 91 UNITS DAY)
THEN "NC??"
WHEN inv_user3 LIKE 'C%' AND inv_cons_date > (TODAY - 30 UNITS DAY)
THEN "NC30"
WHEN inv_user3 LIKE 'C%' AND inv_cons_date BETWEEN (TODAY - 31 UNITS DAY) AND (TODAY - 60 UNITS DAY)
THEN "NC60"
WHEN inv_user3 LIKE 'C%' AND inv_cons_date BETWEEN (TODAY - 61 UNITS DAY) AND (TODAY - 90 UNITS DAY)
THEN "NC90"
WHEN inv_user3 LIKE 'C%' AND inv_cons_date < (TODAY - 91 UNITS DAY)
THEN "CORE"
WHEN inv_user3 LIKE '%ORE%'
THEN "CORE"
WHEN inv_user3 LIKE '%BB%'
THEN "BB"
WHEN inv_user3 LIKE 'V%'
THEN "VDISC"
WHEN inv_user3 LIKE 'SO%'
THEN "SO"
WHEN inv_user3 LIKE '%S/O%'
THEN "SO"
WHEN inv_user3 LIKE '%F%'
THEN "SO"
WHEN inv_user3 LIKE 'SP%'
THEN "SO"
WHEN inv_user3 = 'NS' AND inv_cons_date > (TODAY - 30 UNITS DAY)
THEN "NS30"
WHEN inv_user3 = 'NS' AND inv_cons_date BETWEEN (TODAY - 31 UNITS DAY) AND (TODAY - 60 UNITS DAY)
THEN "NS60"
WHEN inv_user3 = 'NS' AND inv_cons_date BETWEEN (TODAY - 61 UNITS DAY) AND (TODAY - 90 UNITS DAY)
THEN "NS90"
WHEN inv_user3 = 'NS' AND inv_cons_date < (TODAY - 91 UNITS DAY)
THEN "SEASONAL"
WHEN inv_user3 LIKE 'SE%' AND inv_cons_date > (TODAY - 30 UNITS DAY)
THEN "NS30"
WHEN inv_user3 LIKE 'SE%' AND inv_cons_date BETWEEN (TODAY - 31 UNITS DAY) AND (TODAY - 60 UNITS DAY)
THEN "NS60"
WHEN inv_user3 LIKE 'SE%' AND inv_cons_date BETWEEN (TODAY - 61 UNITS DAY) AND (TODAY - 90 UNITS DAY)
THEN "NS90"
WHEN inv_user3 LIKE 'SE%' AND inv_cons_date < (TODAY - 91 UNITS DAY)
THEN "SEASONAL"
ELSE
"WHAT?"
END AS user3update
, inv_user3
, inv_last_sale
, inv_cons_date
, inv_last_pur
, inv_first_purch
, inv_desc
, inv_user2
, inv_user4
, inv_subcategory
, inv_id1 AS VendorID
, inv_id2 AS CLASS
, inv_buyer
, inv_ven_id AS STYLE
, CASE WHEN ("001" NOT IN (SELECT store_id FROM item_stores WHERE inv_id3 = item_id AND store_id = '001' AND on_hand_qty > '0')) THEN "Doesn't Have" ELSE "Has" END AS _1
, CASE WHEN ('012' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '012' AND on_hand_qty > '0')) THEN "Doesn't Have" ELSE "Has" END AS _12
, CASE WHEN ('022' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '022' AND on_hand_qty > '0')) THEN "Doesn't Have" ELSE "Has" END AS _22
, CASE WHEN ('031' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '031' AND on_hand_qty > '0')) THEN "Doesn't Have" ELSE "Has" END AS _31
, CASE WHEN ('999' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '999' AND on_hand_qty > '0')) THEN "Doesn't Have" ELSE "Has" END AS _999
, (inv_tot1+inv_tot2+inv_tot3+inv_tot4+inv_tot5+inv_tot6+inv_tot7+inv_tot8+inv_tot9+inv_tot10+inv_tot11+inv_tot12+inv_tot13+inv_tot14+inv_tot15+inv_tot16+inv_tot17+inv_tot18+inv_tot19+inv_tot20+inv_tot21+inv_tot22+inv_tot23+inv_tot24) AS invbucketqty
FROM inv
WHERE inv_id3 > '100000'
AND (inv.inv_id1 != '683' OR inv.inv_id1 != '977'OR inv.inv_id1 != '541')
AND inv.inv_id3 IN (SELECT inv.inv_id3 FROM inv WHERE ((inv_tot1+inv_tot2+inv_tot3+inv_tot4+inv_tot5+inv_tot6+inv_tot7+inv_tot8+inv_tot9+inv_tot10+inv_tot11+inv_tot12+inv_tot13+inv_tot14+inv_tot15+inv_tot16+inv_tot17+inv_tot18+inv_tot19+inv_tot20+inv_tot21+inv_tot22+inv_tot23+inv_tot24) > 0))
When I try and add this line just before the "FROM inv" line I get the error I mentioned above which is no very informative
, (SELECT MAX(po_detail.pd_rec_date) FROM po_detail WHERE po_detail.pd_id3 = inv.inv_id3 AND po_detail.pd_rec_date IS NOT NULL AND po_detail.pd_status = 'PST') AS _last_recv
Through trial and error I have found that if I remove the very beginning of the query, this section
DISTINCT((CASE WHEN "001" NOT IN (SELECT store_id FROM item_stores WHERE inv_id3 = item_id AND store_id = '001' AND on_hand_qty > '0' ) THEN "" ELSE "001, " END)
|| (CASE WHEN '005' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '005' AND on_hand_qty > '0' ) THEN "" ELSE "005, " END)
|| (CASE WHEN '007' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '007' AND on_hand_qty > '0' ) THEN "" ELSE "007, " END)
|| (CASE WHEN '009' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '009' AND on_hand_qty > '0' ) THEN "" ELSE "009, " END)
|| (CASE WHEN '012' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '012' AND on_hand_qty > '0' ) THEN "" ELSE "012, " END)
|| (CASE WHEN '014' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '014' AND on_hand_qty > '0' ) THEN "" ELSE "014, " END)
|| (CASE WHEN '14' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '14' AND on_hand_qty > '0' ) THEN "" ELSE "14, " END)
|| (CASE WHEN '022' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '022' AND on_hand_qty > '0' ) THEN "" ELSE "022, " END)
|| (CASE WHEN '031' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '031' AND on_hand_qty > '0' ) THEN "" ELSE "031, " END)
|| (CASE WHEN '99' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '99' AND on_hand_qty > '0' ) THEN "" ELSE "99, " END)
|| (CASE WHEN '100' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '100' AND on_hand_qty > '0' ) THEN "" ELSE "100," END)
|| (CASE WHEN '101' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '101' AND on_hand_qty > '0' ) THEN "" ELSE "101, " END)
|| (CASE WHEN '500' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '500' AND on_hand_qty > '0' ) THEN "" ELSE "500, " END)
|| (CASE WHEN '512' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '512' AND on_hand_qty > '0' ) THEN "" ELSE "512, " END)
|| (CASE WHEN '555' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '555' AND on_hand_qty > '0' ) THEN "" ELSE "555, " END)
|| (CASE WHEN '777' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '777' AND on_hand_qty > '0' ) THEN "" ELSE "777, " END)
|| (CASE WHEN '888' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '888' AND on_hand_qty > '0' ) THEN "" ELSE "888, " END)
|| (CASE WHEN '900' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '900' AND on_hand_qty > '0' ) THEN "" ELSE "900, " END)
|| (CASE WHEN '999' NOT IN (SELECT (store_id) FROM item_stores WHERE inv_id3 = item_id AND store_id = '999' AND on_hand_qty > '0' ) THEN "" ELSE "999, " END)) AS _resides_in
It will accept the new line being added to the query and it will retrieve and load data. So what is confusing me is that I can run the full query in Data Ninja against the database using setnet32 as my means of connection no problem. However, I have this problem with Excel/ODBC. So at present, in my uneducated point of view there must be some limit on the number of subselects in a SQL statement that is sent via either the ODBC driver or Excel. However, I cannot find any info on such limitations so I may be utterly wrong. I cannot seem to know the correct search terms to locate help on this topic. I could use some help if anyone has an idea.
------------------------------
Casey Popp
------------------------------
#Informix