Pass Through Query

[jmarr02s]

I am creating a Pass Through Query. Here is my code:

SELECT MDSDBA_CINTAKE. RECVDATE, MDSDBA_CINTAKE. CMPSRC,
Count(MDSDBA_CI NTAKE.CMPSRC) AS CountOfCMPSRC
FROM MDSDBA_CINTAKE
WHERE RECVDATE>=to_da te( '01-09-2005','dd-mm-yyyy')
AND RECVDATE<to_dat e( '01-10-2005','dd-mm-yyyy')
AND CMPSRC IS NOT NULL
GROUP BY CMPSRC;

I get the error: ODBC--call failed.

[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
(#942)

I believe that part of the problem is my date format?

Thanks for your assistance!

jmarr02s

[Gord]

Re: Pass Through Query



On Jan 26, 11:35 am, "jmarr02s" <john.marru...@ illinois.govwro te:

I am creating a Pass Through Query. Here is my code:
>
SELECT MDSDBA_CINTAKE. RECVDATE, MDSDBA_CINTAKE. CMPSRC,
Count(MDSDBA_CI NTAKE.CMPSRC) AS CountOfCMPSRC
FROM MDSDBA_CINTAKE
WHERE RECVDATE>=to_da te( '01-09-2005','dd-mm-yyyy')
AND RECVDATE<to_dat e( '01-10-2005','dd-mm-yyyy')
AND CMPSRC IS NOT NULL
GROUP BY CMPSRC;
>
I get the error: ODBC--call failed.
>
[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
(#942)

If you took the name from an Access linked table then there is likely
no Oracle table called

MDSDBA_CINTAKE

I'll bet that the table is actually named "CINTAKE" and it is owned by
"MDSDBA". Try using

"MDSDBA"."CINTA KE"

instead.

[Tim Marshall]

Re: Pass Through Query

jmarr02s wrote:

I am creating a Pass Through Query. Here is my code:
>
SELECT MDSDBA_CINTAKE. RECVDATE, MDSDBA_CINTAKE. CMPSRC,
Count(MDSDBA_CI NTAKE.CMPSRC) AS CountOfCMPSRC
FROM MDSDBA_CINTAKE
WHERE RECVDATE>=to_da te( '01-09-2005','dd-mm-yyyy')
AND RECVDATE<to_dat e( '01-10-2005','dd-mm-yyyy')
AND CMPSRC IS NOT NULL
GROUP BY CMPSRC;
>
I get the error: ODBC--call failed.
>
[Oracle][ODBC][Ora]ORA-00942: table or view does not exist
(#942)
>
I believe that part of the problem is my date format?

Your to_date syntax is correct. There are, however a couple of error in
your SQL, but the problem throwing the above error is that your ODBC
connection cannot find table/view MDSDBA_CINTAKE. See later in this
post where I say "ATTENTION" for further corrections you need.

Specifying tables in a PTQ can be tricky, depending upon which username
your ODBC DSN points to.

My experience is as follows. My explanation for the behaviour you are
experiencing might not be exactly technically correct, but it should
serve you well.

If the Oracle username that is being used in the DSN is different from
the host name, you need to specify the host name. As an example, my
major Oracle application is host is called "TMA.WORLD" (or just "TMA").
If the Oracle user ID (UID) identified in my DSN is "TMA", I can write:

Select wo_number from f_workorder

However, I have set up a special user name for the people who access my
applications (it has grant select only, so no data changes can be made,
only viewed) called "TMAR". If the UID identified in my DSN is "TMAR",
and I try to run the exact same select statement above, I throw exactly
the same error you have experienced.

What I need to do is prefix the table name with the host name:

Select wo_number from tma.f_workorder

And bingo.

In your case, if your Oracle UID is, for example, "GUMBY", you'd rewrite
your SQL as follows:

SELECT GUMBY.MDSDBA_CI NTAKE.RECVDATE, GUMBY.MDSDBA_CI NTAKE.CMPSRC,
Count(GUMBY.MDS DBA_CINTAKE.CMP SRC) AS CountOfCMPSRC
FROM GUMBY.MDSDBA_CI NTAKE
WHERE RECVDATE>=to_da te('01-09-2005','dd-mm-yyyy')
AND RECVDATE<to_dat e('01-10-2005','dd-mm-yyyy')
AND CMPSRC IS NOT NULL
GROUP BY CMPSRC

ATTENTION!!!! I just noticed something wrong with your SQL. You have a
group by clause but have recvdate and cmpsrc in the select clause. I'm
pretty sure this will cause Oracle to puke on what you give it.
Remember that you need to have each non-aggregate in the select clause
represented in the group by clause. I'm guessing you want to group the
number of CMPSRC by date. I would rewrite what you have as follows.
Note that you don't need to specify the table name if the column names
are all distinct and in this case, with only one table, they are.
Indeed, your where clause does not bother to specify the table alias
like you do in the select clause. You should find the following air SQL
works fine and the count(*) is likely faster than count(cmpsrc):

SELECT
RECVDATE,
Count(*) AS CountOfCMPSRC
FROM
GUMBY.MDSDBA_CI NTAKE
WHERE
RECVDATE>=to_da te('01-09-2005','dd-mm-yyyy')AND
RECVDATE<to_dat e('01-10-2005','dd-mm-yyyy')AND
CMPSRC IS NOT NULL
GROUP BY
RECVDATE

Another recommendation:

If you are doing Access development on an Oracle back end, whether you
are using linked tables (which I rarely do anymore) or PTQ like you're
working on now, you should have SQL Plus installed from your Oracle cd
(or some other third party SQL writing tool such as TOAD by Quest). If
you don't have it installed, do it - it's the same sort of process as
installing the Oracle net client. When your SQL gags, SqlPlus will show
you exactly where in the SQL the problem is, unlike an Access PTQ which
just returns the Oracle error.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me