"SQL Command not properly ended" in pass through query

[enuss]

Hello,

I am writing a pass-through query for an Oracle database and am having trouble with some simple SQL. Even after all of the research I have done, I am clueless as to why I am getting this error. Below is the code. Does anyone know what's going on?

SELECT EWORK.EFOLDER.EFOLDERNAME, METASTORM.GI.TRACKER.V3.SAPPTYPE, METASTORM.GI.TRACKER.V3.SPLANID, METASTORM.GI.TRACKER.V3.SGROUPNAME, METASTORM.GI.TRACKER.V3.DEFFECTIVEDATE
FROM METASTORM.GI.TRACKER.V3, EWORK.EFOLDER;


Thanks,

Erich

 

[SantaMufasa]

I...am having trouble with some simple SQL

My crystal ball is still in the shop, so I need you tell us if you are receiving 1) a syntax error, 2) a run-time error or 3) a logic error (Oracle is not producing the results you want). If either 1 or 2, please please copy and paste the diagnostics that Oracle throws in response to your problem.


If 3, then it is because you are asking for a cartesian product of the two tables, "METASTORM.GI.TRACKER.V3" and "EWORK.EFOLDER". A cartesian product results when every row in your first table partners up with every row in your second table. Thus, if there are 10 rows in Table A and 20 rows in Table B, your result set contains 200 rows. This occurs when you have no "WHERE" clause that instructs Oracle how to join rows from the two (or more) tables.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[enuss]

Ok, I am getting the ORA-00933 error. "SQL command not properly ended (#933)"

I believe you are probably right about the join. This query does require a join, however, when I previously had the join in there, it was giving me the same error.

Does this look right:

SELECT EWORK.EFOLDER.EFOLDERNAME, METASTORM.GI.TRACKER.V3.SAPPTYPE, METASTORM.GI.TRACKER.V3.SPLANID, METASTORM.GI.TRACKER.V3.SGROUPNAME, METASTORM.GI.TRACKER.V3.DEFFECTIVEDATE
FROM METASTORM.GI.TRACKER.V3, EWORK.EFOLDER
WHERE METASTORM.GI.TRACKER.V3.EFOLDERID = EWORK.EFOLDER.EFOLDERID;

This is giving me the same error.

Thanks for the help!

Erich

 

[BJCooperIT]

This makes it easier to read:

select ee.EFOLDERNAME,
       mgtv3.SAPPTYPE,
       mgtv3.SPLANID,
       mgtv3.SGROUPNAME,
       mgtv3.DEFFECTIVEDATE
from   METASTORM.GI.TRACKER.V3 mgtv3,
       EWORK.EFOLDER ee
where  mgtv3.EFOLDERID = ee.EFOLDERID

If a passthru query is a text string, like in dynamic sql. have you tried it without the ';'?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Frankly, Erich, I have never seen the level of qualification that you are using:

"...FROM A.B.C.D..."

What are the results of this command (in SQL*Plus):

describe METASTORM.GI.TRACKER.V3

Also, for ease of reference, you needn't fully qualify columns if they reside in only one of your two tables.

If a column resides in both tables, then I recommend your using an alias for the tables. In your case, [ a)presuming that your "A.B.C.D" reference actually works and b) that you need to qualify your colunns due to existence in both tables] I'd recode in this fashion:

SELECT y.EFOLDERNAME
      ,x.SAPPTYPE
      ,x.SPLANID
      ,x.SGROUPNAME
      ,x.DEFFECTIVEDATE
  FROM METASTORM.GI.TRACKER.V3 x
      ,EWORK.EFOLDER y
 WHERE x.EFOLDERID = y.EFOLDERID;

Let us know your reactions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[enuss]

Thanks for making it look nicer. By the way, how do you get it inside that code box?

Yeah, I've tried it without the semicolon. I am writing the pass-through query in Access. The database that it is accessing is an Oracle database, which requires that I use Oracle SQL as opposed to Jet SQL.

Any more ideas?

Erich

 

[enuss]

I understand your reaction to my naming convention, but according to some information I found online, Oracle SQL uses periods (.) to represent underscores (_). Have you heard this?

It doesn't seem to like the "DESCRIBE" command. I have to be honest that I am not sure what version of Oracle that our database is on. I will try to find that out.

So with the code you are showing me above, you are basically re-representing the tables as y and x? I'm glad you showed me how to do that, since I'm used to Jet SQL, which is much different.

Erich

 

[enuss]

The version of Oracle that our database is on is 9i.

 

[BJCooperIT]

The box comes from using TGML (see link above Submit Post button). I agree with Dave, I have never seen qualification like that, but then again, I have never done a pass-through query from Access. Try the describe Dave asked for and we can start from there.

 

[BJCooperIT]

No, the normal qualification is: SchemaName.TableName

 

[BJCooperIT]

What Dave and I showed you was how to use an alias (his X and Y / my MGTV3 and EE) to give a table a simple name.

 

[SantaMufasa]

Barb, I must run to a wedding presently, so I'll head out and turn this issue over to your capable hands.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

I'm baaa-aack.

...according to some information I found online, Oracle SQL uses periods (.) to represent underscores (_). Have you heard this?

Never heard of such. Could you post the URL that asserted this?

It doesn't seem to like the "DESCRIBE" command.

What is the "It" to which you refer? The only Oracle environment that supports the "DESCRIBE" command is SQL*Plus. Can you please connect to SQL*Plus and



a) run the DESCRIBE command (until you find a table-name reference that works for your table, then (while still in SQL*Plus)
b) run the SELECT statement that I posted, above, replacing the bogus table reference with the reference (spelling/qualification) that worked in the successful DESCRIBE command, above.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[enuss]

I think part of the confusion with this is that I am working in Microsoft Access, attempting to write a passthrough query in Oracle SQL WITHOUT having access to the oracle server through any other querying application. This means that I cannot use SQL *Plus to use the Describe function.

What is interesting about this is that when I remove the METASTORM.GI.TRACKER.V3 table and run a select statement on the EWORK.EFOLDER table it works fine. Unfortunately I couldn't find that website that I referenced that told me to replace (_) underscores with (.) periods.

Should I move this thread to the Jet SQL forum?

Erich

 

[enuss]

I figured out what my problem was. Basically, the Oracle tables are named "METASTORM.GI_TRACKER_V3" and "EWORK.EFOLDER" but Access cannot represent the periods in its table viewer. Because we have these tables linked to the Access database, they appear as "METASTORM_GI_TRACKER_V3", as a result of Access's inability to use the periods to represent the table. When I changed the table name to the above listed name, it worked. Thanks for your work everybody. Sorry to be so confusing...I'm not very aversed in Oracle SQL.

Erich

 

[SantaMufasa]

I'm glad you got this resolved, Erich.

Best wishes in your Oracle and Access endeavors.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]