D
Dave Y
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
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