Hi,

I have tried to embed an Oracle SQL query in a WF procedure but it returns no output. It is somewhat complex but I read that inline queries work as long as the WHERE statements are all equivalency tests, which they are. Here it is:

Select Description, Beginning_balance,Realized,unRealized_pl, UNRELAIZED_OCI,
purchases,issuances,sales,Settlements,activity,
transfer_in,transfer_out, net_transfers,
ending_balance
from (
select d.Description, c.disclosure_group_seq_id,c.disclosure_line_seq_id,d.sortby_order,
Sum(Beginning_balance) Beginning_balance,Sum(Realized) Realized,Sum(unRealized_pl) unRealized_pl,
Sum( UNRELAIZED_OCI) UNRELAIZED_OCI, sum(purchases) purchases, sum(issuances) issuances,sum(sales)
sales,sum(Settlements) Settlements,sum(activity) activity, sum(transfer_in)
transfer_in,sum(transfer_out) transfer_out,sum(transfer_in)+sum(transfer_out) net_transfers,
sum(ending_balance) ending_balance
from bkt_category_disclose_xref a,
rollforward_output b,
disclosure_item_xref c,
disclosure_line d,
disclosure_table e
Where a.master_bucket_seq_id = b.master_bucket_seq_id
and a.master_portfolio_seQ_id = b.master_portfolio_seq_id
and a.master_category_seq_id = b.master_category_seq_id
and b.enterprise_system_seq_id = 2
and a.bkt_cat_pfolio_xref_Seq_id = c.bkt_cat_pfolio_xref_seq_id
and c.disclosure_line_seq_id = d.disclosure_line_seq_id
and c.disclosure_table_seq_id = e.disclosure_table_Seq_id
and e.table_name like'2.1%'
group by d.Description, c.disclosure_group_seq_id,
c.disclosure_line_seq_id,d.sortby_order) a
order by sortby_order

Your WebFOCUS program should look something like this:

SET SQLENGINE = SQLORA
SQL SET SERVER server-name
-RUN

SQL
Select Description, Beginning_balance,Realized,unRealized_pl, UNRELAIZED_OCI,
purchases,issuances,sales,Settlements,activity,
transfer_in,transfer_out, net_transfers,
ending_balance
from (
select d.Description, c.disclosure_group_seq_id,c.disclosure_line_seq_id,d.sortby_order,
Sum(Beginning_balance) Beginning_balance,Sum(Realized) Realized,Sum(unRealized_pl) unRealized_pl,
Sum( UNRELAIZED_OCI) UNRELAIZED_OCI, sum(purchases) purchases, sum(issuances) issuances,sum(sales)
sales,sum(Settlements) Settlements,sum(activity) activity, sum(transfer_in)
transfer_in,sum(transfer_out) transfer_out,sum(transfer_in)+sum(transfer_out) net_transfers,
sum(ending_balance) ending_balance
from bkt_category_disclose_xref a,
rollforward_output b,
disclosure_item_xref c,
disclosure_line d,
disclosure_table e
Where a.master_bucket_seq_id = b.master_bucket_seq_id
and a.master_portfolio_seQ_id = b.master_portfolio_seq_id
and a.master_category_seq_id = b.master_category_seq_id
and b.enterprise_system_seq_id = 2
and a.bkt_cat_pfolio_xref_Seq_id = c.bkt_cat_pfolio_xref_seq_id
and c.disclosure_line_seq_id = d.disclosure_line_seq_id
and c.disclosure_table_seq_id = e.disclosure_table_Seq_id
and e.table_name like'2.1%'
group by d.Description, c.disclosure_group_seq_id,
c.disclosure_line_seq_id,d.sortby_order) a
order by sortby_order

TABLE ON TABLE HOLD AS HOLD01 FORMAT ALPHA
END
-RUN

TABLE FILE HOLD01
PRINT *
END
-RUN

Is this your first attempt at running SQL via WebFOCUS? Have you tried a simpler SQl request?

By the way, we would appreciate you adding your environment details to your forum profile (accessible here: Member Profile

Francis,

Thanks very much, especially for the quick response. Actually the query is giving me an error saying SQL TABLE NOT FOUND. (for table check segname and table) erroneous token: disclosure_table
foc145 sql prepare error
The description cannot be found for sqlout (Foc1405).

And to answer your question, I am on a project where another area developed these SQL requests. I was hoping I could simply embed and run them!

And I will update my profile.

Thanks again.

Mike

Are you setting the SQL Engine and the server?

SET SQLENGINE = SQLORA
SQL SET SERVER server-name 
-RUN

Is there a WF data adapter pointing to the DB?

Also, you may have to qualify the table/view names: database-name.table-owner-name.table-name (e.g. MSCRM.dbo.time)

Instead of just SQL at the beginning of the statement, you could use SQL SQLORA. This forces a more exact passthru. If you just say SQL, it is sending SQL, but is still relying on WF's MFD's and SQL parser. Using SQL SQLORA, bypasses all of that and sends the request straight to Oracle.

Also, don't forget the ; at the end of your sequel statement.

If you have not tried using the SQL Report Wizard in DevStudio, give that a try. It may take care of some missing syntax and give you a good idea how to form your SQL-based fexes in the future.

When I look at the .acx file it says:

TABLENAME=FAS157.DISCLOSURE_TABLE...

So maybe if I preface all the table names with FAS157. WF will find them???

I put back the SQLORA after the SQL and there is a semicolon at the end!

Thanks to both of you guys.

Mike

That was it - needed the tablename preface. Sigh.

Darrin,

I'm very interested in your posting regarding a more exact passthru - I'd like to find out more about coding SQL SQLORA instead of SQL. I can't seem to find the documentation on this.

All I've found is "How to Invoke a Stored Procedure" in the Adapter Administration for UNIX, Windows, OpenVMS... manual. Since I'm not calling a stored procedure I'd like to see the documentation for a simple WF passthru request.

I've found this:

"SQLORA - Is the target RDBMS. You can omit this value if you previously issued the SET SQLENGINE command", which seems to suggest that the two methods are equivalent.

Thanks very much,

Francis, I hope this can be helpful to you...
ENGINE SQLORA END SESSION;
-RUN
SQL SQLORA SET SERVER &&XSERVER ;
END
-RUN

SQL SQLORA EX mystored.procedure;
END
-IF &FOCERRNUM EQ '1671' OR '1405' OR '1400' GOTO ERR_RTN;
-IF &RETCODE NE 0 GOTO ERR_RTN;
-RUN
.
.
.


hope this helps.... Ira
AIX 533 WF 538

Ira, thanks for your input, but I'm not running a stored procedure. I'm looking for documentation on the difference between setting the SQL engine in one command than then running a SQL call versus running a SQL call with the engine specified in one command.

Thanks,

Francis,

Darin gave the answer above. SQL with the engine supplied is a passthru request. SQL without the engine must have a master and access file to determine which adapter to use. Then, unless they've changed it, the request is translated to FOCUS then back to SQL. The SQL must be ansi-standard. With passthru, you can use rdbms-specific sql constructs.

I'm not sure where the documentation would be. I used to support EDA for IBI in the 90's and became fairly well-versed in this stuff. But the manuals have all been rewritten and I just don't know where you'd find the info. You might try an iWay data administration manual.

Do you have your SQL ending with a semi-colon? I used to get caught on that. Make sure your SQL ends with a ;

Quoting Darin, "Instead of just SQL at the beginning of the statement, you could use SQL SQLORA. This forces a more exact passthru. If you just say SQL, it is sending SQL, but is still relying on WF's MFD's and SQL parser. Using SQL SQLORA, bypasses all of that and sends the request straight to Oracle."

This seems to suggest there is a difference between

SET SQLENGINE=SQLMSS
SQL SELECT * FROM TestTable1
END

and

SQL SQLMSS SELECT * FROM TestTable1
END

Or am I reading his comment incorrectly?

Neither of these two requests required a Master and Access file.

I don't know where the doc for this is, either. But there isn't much more to it than what Ginny explained.

We used to create our sequel statements beginning witht he standard SQL. In creating a new ETL procedure, we were given an SQL query by a vendor with the "This is how you get the data you need" with no further explanation. We tried pulling it apart to see how it worked but found that it contained some SQLServer-specific code. We just couldn't get it to work without removing some of the statement, but it worked fine using the native SQL tool. By using SQL SQLMSS instead of just SQL, it does a true passthru and all of the SQL query is passed directly to SQL Server and worked great.

We have since gone back and changed most of our SQL queries to use this method, so we don't run into the issue of sometimes it works and sometimes it doesn't. It just always does. Don't know if there are any drawbacks - anyone have input on that? It also removes the requirement to manage MFD/ACX files for those tables that are never accessed except via SQL. I guess the one drawback to that would be that you've got to know what the file layout is and just code heads down. No GUIs or Wizards or anything else are available to show field names/formats/etc. But you can also cut and paste from a native SQL tool with no issues.

Found this case on tech support. It gave a documentation reference that seems to indicate that there is no difference in the two statements referenced by Francis.

However, if the SQLENGINE has NOT been set, then passthru would not be invoked and the query would be passed to the SQL translator. The statement that neither requires an MFD would be the key. If there is no MFD, but the query works, it is using direct passthru.

I just got in the habit of always specifying the ENGINE on the SQL line so it is immediately recognizable as a passthru request, in case the SET ENGINE does not immediately precede it.

Small snippet of SQLPassthru. We always set the default connection as we have multiple databases that Focus Oracle Adapter needs to choose between.

The following is selecting from Oracle DUAL which is public and is returning the database name, Server Host, date and time.

ENGINE SQLORA SET DEFAULT_CONNECTION &ORA_NAME
SQL SQLORA
SELECT SUBSTR(SYS_CONTEXT('USERENV','DB_NAME'),1,8) viname
,SUBSTR(SYS_CONTEXT('USERENV','SERVER_HOST'),1,11) vihost
,to_char(sysdate,'HHMIPM') vitime
,to_char(sysdate,'YYYYMONDD') vidate
from dual;
TABLE FILE SQLOUT
PRINT *
END

We usually send the output from SQLOUT to Hold files for additional processing, but can print/format from this point.

Often add to strip time portion off the date fields in oracle.
SQL SQLORA SET DATETIME OFF

A manual on our documentation CD (5.3.3) is the WebFocus Relational Interface Manual (4.35). It provides great detail on Passthru, environment commands and transaction control.