HELP WITH SQL CODE

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, 2005

Tony A

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, 2004

Ignored post by Tony A posted

Show Post

reFOCUSing

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, 2004

Ignored post by reFOCUSing posted

Show Post

EricH

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, 2003

Ignored post by EricH posted

Show Post

Jim Dale

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, 2005

Ignored post by Jim Dale posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

HELP WITH SQL CODE

Focal Point | Privacy Statement | Terms of Service