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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
HELP WITH SQL CODE
 Login/Join
 
Silver Member
posted
I need to learn SQL for a new system we are installing. I'm the Focus programer and would like to start using SQL code when I need to do queries against our Oracle tables instead of Focus code. I've dug up the following code from a report coded for us 3 years ago but I cannot get it to work. I just want to start with simple SQL code and work up from there.

The error I receive when I run the following code is: A VALUE IS MISSING FOR SCHEMA


-SET &SRVR='bapm';
-SET &SCHEMA='WFOWNER';

SET SQLENGINE=SQLORA
SQL SET SERVER &SRVR
-RUN
-*

select acct_nbr
, activity_date
, product_code
, product_name
, acct_name
, zip_code

FROM
&SCHEMA.WORK_ORDER_DETAIL wod
WHERE (wod.ACTIVITY_DATE = '20060211') ;

-RUN
-EXIT

Any help is appreciated. Thanks,
Jim
 
Posts: 43 | Location: San Jose, California. | Registered: July 18, 2005Report This Post
Expert
posted Hide Post
Not an Oracle SQLer but this should work for you and is the basic method of using SQL within WebFOCUS. I tend to use uppercase syntactical words for clarity.

You don't state how long you have been WF coding but hopefully you should be able to follow the rest after the semi-colon terminating the SQL.

-SET &SRVR='bapm';
-SET &SCHEMA='WFOWNER';

SET SQLENGINE=SQLORA
SQL SET SERVER &SRVR
-RUN
-*
SQL
SELECT acct_nbr
, activity_date
, product_code
, product_name
, acct_name
, zip_code

FROM
&SCHEMA.WORK_ORDER_DETAIL wod 
WHERE (wod.ACTIVITY_DATE = '20060211')
;
TABLEF FILE SQLOUT
PRINT *
END
-RUN


As for SQL syntax generally, I suggest you grab a book on the subject and have a look through it. Try some examples applied to your data sources and progress from there. I've picked some prior version books up from end of season sales in the States for a couple of dollars. Well worth the investment!!

Good luck

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Guru
posted Hide Post
You may want to try using some of the following code:


SET SQLENGINE=SQLORA
SQL SQLORA SET ORANUMBER DECIMAL
-RUN

SQL SET SERVER &DB_NAME
-RUN

SQL SET CONNECTION_ATTRIBUTES &DB_NAME/&DB_USER,&DB_PASSWORD
-RUN


I do this when ever I do a SQL-passthrough.
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report This Post
Platinum Member
posted Hide Post
Jim,

This is a generic FOCUS Dialogue Manager (DM) issue here - it has nothing to do with SQL. When you put that period immediately after &SCHEMA, you're telling DM to construct an indexed variable (you can look that up in the manuals); but the remaining code is not set up to create an index - hence the error.

There are various methods to make this work. My favorite is to simply put three periods after &SCHEMA:

etc etc etc
&SCHEMA...WORK_ORDER_DETAIL wod
etc etc etc

EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
Silver Member
posted Hide Post
Thanks everyone for your input. My example was not a good one because it was trying to pull data from different tables and that is why it
blew up. This is the code I used to get a simple query to work:

ENGINE SQLORA SET DEFAULT_CONNECTION bapm
SQL SQLORA PREPARE SQLOUT FOR
SELECT acct_nbr
,job_nbr
,activity_date
FROM
WFOWNER.WORK_ORDER_DETAIL wod
WHERE (wod.ACCT_NBR = '000022400') ;
END

TABLE FILE SQLOUT
PRINT *
END

Now I am on my way to getting familiar with SQL
coding. Any other suggestions or recommendations are most welcome.

Thanks much,
Jim
 
Posts: 43 | Location: San Jose, California. | Registered: July 18, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders