Creating a fix query from an ORACLE data source

[Former Member]

I was taught that using ORACLE tables in a fixed query, you must treat it like it is SQL. So I am writing this code like it is SQL. This code works as long as I keep any calculations out of the equation. "LEFT([WRKCTR],3) AS DEPT" throws an error.

***

Fatal Error

A SQL Error has occurred on query, ORA-00936: missing expression .

***

SELECT PLANT,

PAYDAY,

SHIFTID AS SHIFT,

MAT AS MATERIAL,

MATDESC AS DESCRIPTION,

'' AS DIA,

<b>LEFT([WRKCTR],3) AS DEPT</b>

FROM CIMUSER.ZXMII_LBSRPT

WHERE PAYDAY BETWEEN [SD] AND [ED]

ORDER BY PAYDAY

Why am I getting this error and how can I prevent this from happening again.

Thanks!!

[Former Member · Accepted solution]

Oracle probably doesn't understand LEFT, instead use the Oracle PL/SQL LTRIM function.

[Former Member · Accepted solution]

I am unable to create anything in fixed query that uses the ORACLE data source.

I can do this...

SELECT *

FROM CIMUSER.ZXMII_LBSRPT

but forget about doing something like this...

SELECT DATEPART(m,CIMUSER.ZXMII_LBSRPT.PAYDAY) AS tDATE, SUBSTR(WRKCTR,1,3) AS DEPT

FROM CIMUSER.ZXMII_LBSRPT

I get this error...

Fatal Error

A SQL Error has occurred on query, ORA-00904: invalid column name .

What sucks is I can do what I need to do in MS Access, but in xMII it is not allowing me to do anything. What needs to be done to allow this?

[Former Member · Accepted solution]

Do you have an Oracle Client (like TOAD etc.) ?

Try running the Query there .

That would be a check whether your syntax has any errors.

If the statement works via the client then I guarantee it'll work with the Query Template too.

[Former Member · Accepted solution]

Yes, as Udi states, any Oracle code that runs OK in an Oracle Client will run ok in the Query Template Editor.

I forgot that you are probably linking the Oracle tables to Access and running the queries from there. This is using Access functions like <b>LEFT</b> which are then transformed at runtime to the Oracle syntax.

See if you can get your hands on a "real" Oracle Client tool or use an Oracle reference to run the query through the Query Template.

This might be a good reference for you:

http://www.oracle.com/pls/db92/db92.sql_keywords?remark=homepage

you can also find error messages here.

[Former Member]

Hmmm, who taught you?

It should be SQL syntax (not necessarily Microsoft SQL Server syntax).

You should be able to copy/paste any valid PL/SQL query into the Query Template and substitute in any params or SD and ED.

I don't know that the person trained you meant you had to use Microsoft SQL Server T-SQL commands.

Does that help?

[Former Member]

It was our other instructor that taught us. I guess I misunderstood when he taught us. He is not an SQL guy so maybe some of it was lost in translation.