Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] External SQL Scripts

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] External SQL Scripts
 Login/Join
 
Virtuoso
posted
Anyone have experience to relate on using synonyms of External SQL Script files in Oracle?

I'd like to know what features of Oracle's SQL dialect it supports, and which not.

For instance, does it understand --

  • Subquery factoring ("WITH ..." preceding the main SELECT)
  • Nonscalar functions

-- at least sufficiently to identify the answer-set columns, and generate proper queries based on the script?

This message has been edited. Last edited by: Kerry,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
quote:
Subquery factoring

I would suggest trying with a simple example? For subquery factoring, I was able to run a FOCEXEC using this SQL:

ENGINE SQLORA SET DEFAULT_CONNECTION MYORA
SQL SQLORA PREPARE SQLOUT FOR
with generator as (
    select
        rownum id
    from dual
    connect by
        rownum <= 4000
)
select
    /*+ gather_plan_statistics */
    count(*)
from    (
    select
        /*+ no_merge */
        rownum id
    from
        generator,
        generator
    where
        rownum <= 1000
    )
;
END
TABLE FILE SQLOUT
PRINT 
     COUNT___
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END




I do recall a error [(FOC1405) SQL PREPARE ERROR] being thrown using CASE statements that seemed to require them to be wrapped in parentheses as a work-around. For example:

Produces an Error:
CASE WHEN…. THEN…. END AS COLUMN

No Error:
(CASE WHEN…. THEN…. END) AS COLUMN

Hope this helps...


8.0.02M, Oracle 11.2 (AIX), Windows 2008R2, HTML, PDF, Excel
 
Posts: 72 | Registered: November 12, 2012Report This Post
Guru
posted Hide Post
Haven't run into anything that is a problem using ENGINE SQLORA SET DEFAULT_CONNECTION MYORA
SQL SQLORA PREPARE SQLOUT FOR
, other than these few querks;

- Nested CASE statements, might have to put those in line.

- For comments Use -* not -- but /* */ works

- May need to cast some vars

- Don't allow functions to create their own field names, they can quickly exceed the available witdh

There are a few off the top of my head.


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Report This Post
Virtuoso
posted Hide Post
That's useful info, with regard to SQL Pass-Thru.

But my question was about
quote:
using synonyms of External SQL Script files

-- i.e., you store a sql script on the WF server, create a synonym thereof, and use it in TABLE (or GRAPH or MATCH) FILE.


  • If the first word of the script is WITH (rather than SELECT), will the synonym-generator reject it out-of-hand?

  • The feature description prohibits "comments", does that preclude Hint comments?


Unfortunately, at the moment I am not in position to test this.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Guru
posted Hide Post
When you create a synonym for an external SQL script the requirement documented in Managing ORACLE Metdata is

quote:
A Synonym candidate can be any file that contains one (and only one) valid SQL Query and does not contain end-of-statement delimiters (";" or "/") and comments.


A WITH clause preceding the SQL is OK. For example a synonym can be created for this simple query and then used for retrieval:

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   scott.emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   scott.emp e,
       dept_count dc
WHERE  e.deptno = dc.deptno


This simple query works; there is just one SELECT statement and it's last.

This functionality has been enhanced in Release 7.7.05 to support more complex queries.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] External SQL Scripts

Copyright © 1996-2020 Information Builders