I am attempting a minus operation between two tables with the same select criteria, using SQL passthru. So I should get 0 records returned.

It works fine in an SQL client, but fails in the WF console with the following message:
(FOC14069) SYNTAX ERROR ON LINE 6 AT 'SELECT' -- Semi-colon or END expected

Here is the code that I am using:

-SET &ECHO = ALL;

SQL
select
 field1,
 field2
from d_budget

minus

select
 field1,
 field2
from d_budget
;

TABLE FILE SQLOUT
PRINT *
END
  

If I use the intersect or unions operations, the code works fine.

Any ideas on why the minus operation would be failing?

Thanks,
Sean

Never used minus, assuming it is a valid SQL function, then it's possible it is not one covered in the FOCUS SQL conversion when pulling information.

Thanks for the response Leah.

I did find other posts that referred to minus, but for some reason it is not working for me. Possibly it's a regression issue...

If there are conversion restrictions, hopefully someone can enlighten me on that, and how I can remedy any restrictions if possible.

Thanks,
Sean

The code you listed isn't doing SQL Passthru. You need to specify the database engine. For instance:

SQL SQLMSS <== for SQL Server
SELECT *
FROM ....;
END

If you just say SQL with no engine, you are going through the SQL Translator.

Try it with the engine statement and see if you get the same error.

If you do, try retrieving the data separately and use the MATCH command OLD-NOT-NEW to get your set.

Ginny,

Interesting info....can you kindly inform me of the distinction between SQL passthru and the SQL translator. I didn't realize there were 2 different types.

When I add the database engine SQLORA, I get the following error:

(FOC1400) SQLCODE IS 942 (HEX: 000003AE)
(FOC1409) SQL TABLE NOT FOUND. (FOR TABLE: CHECK SEGNAME AND TABLE)
: ORA-00942: table or view does not exist

I have previously used the syntax SQL SQLORA, but with 7.6.2, I think I had to strip it out everywhere. I was puzzled why I had to do this.

As for the MATCH command, thanks for reminding me about that. I will use that as my backup plan if nobody can help me figure out the MINUS issue.

Thanks alot Ginny!
Sean

Sean, have you tried specifying db-name.owner-name.table-name instead of just table-name?

Sean,

Francis is correct. For passthru, you must specify the 'real' table name, not the name of the master, etc. A quick way to find it is look in the access file of the master if you have one. Otherwise, do as Francis suggested and use the two-part or three-part name.

Now to answer your other question.

The SQL Translator is the part of WebFOCUS that takes TABLE requests, or in your case SQL without the engine, and converts it to SQL. EDASERVE or the reporting server was originally designed to speak only SQL. Any SQL that comes out of the SQL Translator is ANSI-standard 2 SQL, i.e. plain vanilla SQL supported by all RDBMS engines. There are things that you can write in your TABLE code that would not be converted to SQL and therefore not passed to the backend. This is called 'turning optimization off'. This does not make TABLE requests bad. After awhile, you get used to what goes through and not. And there are trace commands that allow you to see the generated SQL before you run your program.

SQL Passthru says to WebFOCUS, please take these commands exactly as they are and send them to the RDBMS engine, in your case Oracle. What that buys you is being able to use custom SQL commands specific to the engine you are using. You are not limited to ANSI-standard SQL. And this is what you want to do in this case.

Ginny, Francis,

I assume with Oracle I would use schema-name.table-name. I've never seen syntax using db-name in Oracle (is that perhaps DB2-specific?).

I did try schema-name.table-name, with and without SQLORA, and received the same errors unfortunately.

I also added the line
SQL SQLORA SET SERVER schema-name
and it didn't work.

The interseting thing is that UNION and INTERSECT both work fine with the translator and passthru, but MINUS does not.

Ginny, thanks very much for the clarification b/w translator and passthru! I am familiar with and have used the tracing capabilities, which are very useful. But I didn't realize the distinction between the translator and passthru.

Perhaps I'm missing some small piece of syntax, but the fact that certain operators work and one doesn't seems to indicate otherwise.

Thanks,
Sean

Sean,

If UNION and INTERSECT work but MINUS doesn't, it must be due to something else.

Do you use Toad or some other SQL client? Have you tried running the SQL query there?

I must say I have never used the MINUS query command before.

Look for this document in IBI's document library: "Interface to ORACLE User's Manual
Version 6 Release 5.0". I found this in the doc:

"SQL Datatype LONG - Variable-length character string (such as text) up
to 255 characters (FOCUS limit), although the
Oracle Version 6 limit is 65,535 characters and the
Oracle7 limit is 2 gigabytes. Only one LONG
column may be described per table. Some stated
Oracle restrictions specify that LONG columns
cannot be: indexed, used in WHERE or GROUP
BY or ORDER BY or CONNECT BY or
DISTINCT clauses, referenced by functions such
as SUBSTR, used in the SELECT list of nested
queries, used in expressions, used with UNION or
INTERSECT or MINUS, or used in distributed
queries."

I don't know if this has anything to do with your issue.

Francis,

I use Golden, and the query does work fine there.

For another app, I used PL/SQL to do what I'm trying now, and it worked great. I am verifying that the target data derived via Data Migrator jobs is accurate, and it works like a charm to find data discrepancies.

But the problem for the app for which I am trying to do the same thing is that the data source is SAP, and they don't want to give me direct access to the Oracle tables for security reasons. So I have to attempt the same data verification in WebFocus somehow. I find SQL very easy to construct, so that is my favoured option. However, it looks like I might have to use WF MATCH, as Ginny suggested.

I'll have a look at the doc though to see if I am missing something.

Thanks for your assistance!
Sean

I would say that you probably don't have the "passthru" piece working quite right yet. The gist of passthru is that nothing in the query is parsed - it is passed directly to the RDBMS engine. So if it works in whatever SQL tool you're using, it will also work using SQL passthru. If you're getting an error back either 1)passsthru is NOT being invoked or 2)you've got something set up wrong with the adapter. And #2 is not likely since other things seem to be working OK.

Darin,

Aha, it is working after all with passthru.

Here's my current syntax:

 -SET &ECHO = ALL;

SQL SQLORA SET SERVER MRS_XFI

SQL SQLORA
select
 FM_AREA,
 BUDDOC_NUM
from d_buddoc

minus

select
 FM_AREA,
 BUDDOC_NUM
from d_buddoc
;
END

TABLE
ON TABLE HOLD AS H_BUDDOC
END
 

I was getting this message, but it is in fact accurate, because I am expecting 0 records. I just need to add processing for 0 records:

0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
0 ERROR AT OR NEAR LINE 17 IN PROCEDURE __WCFEX FOCEXEC *
(FOC001) THE NAME OF THE FILE OR THE WORD 'FILE' IS MISSING
BYPASSING TO END OF COMMAND

Thanks very much for prodding me on to a second pass of my code. I may also have not quite had the code right previously as you suggested, but I think I did and was just misinterpreting the output.

Thanks again to you, Ginny, Francis, and Leah!

Sean