Informix

Expand all | Collapse all

ODBC Limit or Excel Query Limit

  • 1.  ODBC Limit or Excel Query Limit

    Posted 22 days ago
    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
    ------------------------------


  • 2.  RE: ODBC Limit or Excel Query Limit

    Posted 22 days ago
    Casey:

    I suspect that Excel is truncating the query when you add the new clause resulting in a generic syntax error. I also suspect the Data Ninja is also using ODBC or JDBC so it is unlikely that this is an ODBC issue.

    I must say that this is one ugly query. 

    Try exporting the data from Data Ninja to a delimited file and importing it into Excel that way. Or use dbaccess's UNLOAD command to do that.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: ODBC Limit or Excel Query Limit

    Posted 20 days ago
    Hi Casey,

    I would create a view from the complex query, and use the view in Excel to pull the data.

        CREATE VIEW myview (colname1, colname2, ... ) AS SELECT ...

    https://www.ibm.com/docs/en/informix-servers/14.10?topic=statement-naming-view-columns

    https://www.ibm.com/docs/en/informix-servers/14.10?topic=statements-create-view-statement

    Seb

    ------------------------------
    Sebastien FLAESCH
    ------------------------------