Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL in Excel Query
Hello,
I currently run an existing Oracle SQL query that I have on a daily basis and then export the data into an Excel spreadsheet. I would like to automate this process by providing the user of the spreadsheet an ODBC connection to our Oracle DB and then run a query within Excel so that the user could get the data heerself. This way I won't need to do it everyday. I'm having trouble getting the query to run in Excel; I receive the following error: [Microsoft ODBC Driver][Invalid parameter number]. I don't know what this means. I know that I have the correct ODBC driver because I have other queries in Excel and Access that I run. Is there a way I can just copy and paste my existing Oracle SQL query into Excel? I have provided the SQL code below: SELECT yy.* FROM (SELECT zz.taxid, zz.Branch, COUNT(zz.tranamt)AS COUNT, SUM (zz.tranamt)AS Tranamt, zz.fundtypcd, zz.mjaccttypcd, zz.origpostdate FROM(SELECT a.acctnbr, a.mjaccttypcd, c.taxid, b.rtxntypcd, g.amt AS Tranamt /*b.tranamt*/, b.origpostdate, f.orgname AS Branch, g.fundtypcd FROM acct a, rtxn b, pers c, rtxnstathist d, ntwknode e, org f, rtxnfundtyp g WHERE a.acctnbr = b.acctnbr AND b.acctnbr = d.acctnbr AND b.rtxnnbr = d.rtxnnbr AND b.acctnbr = g.acctnbr AND b.rtxnnbr = g.rtxnnbr AND d.origntwknodenbr = e.ntwknodenbr AND e.locorgnbr = f.orgnbr AND a.taxrptforpersnbr = c.persnbr AND a.mjaccttypcd IN('CK', 'SAV') AND b.currrtxnstatcd = 'C' AND g.fundtypcd = 'CHK' AND b.rtxntypcd IN ('DEP','XDEP','DDEP','NA', 'BDR', 'DTRN', 'LSDP', 'OPD', 'R TD') AND b.origpostdate = TO_DATE(SYSDATE-1) UNION SELECT a.acctnbr, a.mjaccttypcd, d.taxid, b.rtxntypcd, h.amt AS Tranamt /*b.tranamt*/, b.origpostdate, g.orgname AS Branch, h.fundtypcd FROM acct a, rtxn b, org c, orgtaxid d, rtxnstathist e, ntwknode f, org g, rtxnfundtyp h WHERE a.acctnbr = b.acctnbr AND b.acctnbr = e.acctnbr AND b.rtxnnbr = e.rtxnnbr AND b.acctnbr = h.acctnbr AND b.rtxnnbr = h.rtxnnbr AND e.origntwknodenbr = f.ntwknodenbr AND f.locorgnbr = g.orgnbr AND a.taxrptfororgnbr = c.orgnbr AND c.orgnbr = d.orgnbr AND a.mjaccttypcd = 'CK' AND b.currrtxnstatcd = 'C' AND h.fundtypcd IN('CHK', 'SAV') AND b.rtxntypcd IN ('DEP','XDEP','DDEP', 'NA', 'BDR', 'DTRN', 'LSDP', 'OPD', ' RTD') AND b.origpostdate = TO_DATE(SYSDATE-1)) zz GROUP BY zz.taxid, zz.Branch, zz.fundtypcd, zz.mjaccttypcd, zz.origpostdate) yy WHERE yy.tranamt = 5000 ORDER BY 2 If anyone can advise me on how to automate this process I would greatly appreciate it. Thank you in advance. Dave Y |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Query from microsoft query- Excel 2007 | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |