On 5/24/2013 1:40 PM, Crystal Reports wrote:
OK but I cannot create a view in the green screen. How to create the view in the green screen?
The easiest way is to do STRSQL and use the command line that pops up
there. When you do that, the 'history' of what you do is stored in an
internal object and there may be separate history for each workstation
you use (I can't remember). The benefit is that you can immediately
execute ANY SQL statement; CREATE VIEW or SELECT FROM or anything else
you can do in dynamic SQL. The downside is that it's not easy to keep
the source. If you want to create a view, you probably don't remember
the exact syntax so you look up the manual and tinker until you get it
just right. Then months pass and you want to create another one. It
might be hard to find the 'working' CREATE VIEW statement, so instead of
pressing F9 to copy the statement and make suitable changes you have to
look up the manual and tinker again. If you had the source, you'd have
a record of what worked and you'd probably put comments with it to note
what you were thinking when you did it. That's STRSQL.
Another way is to store your DDL in a source file. Call it QSQLSRC or
something. Make a PDM user option to do a RUNSQLSTM on it. You can use
most SQL statements except for SELECT, so this works best for DDL like
CREATE VIEW. To do that, you'd CRTSRCPF QSQLSRC and use SEU or RDp to
edit a source member. Inside that source member you'd put your SQL
statement. In this case, it'd be CREATE VIEW mylib/myview... and save
it. Then you'd RUNSQLSTM and specify this source file and member and
that would create the view. The benefit is that you can put comments in
there which you can refer to in the future. The downside is that source
members can only have 10 character names, so it's hard to organise your
scripts with meaningful names.
Another way is to store your DDS in a stream file. You can manipulate
stream files several ways. On the green screen side, you can use WRKLNK
to navigate through the various directories on your system and use
option 2 to edit a file. In my case, I have a directory called 'SQL
Source' and in it I have files like 'AccountsReceivable.sql' Inside
that file, I have DDL like
CREATE TABLE AccountsReceivable...;
CREATE INDEX...;
CREATE VIEW...;
To execute these scripts, I can use RUNSQLSTM (I am on 7.1 and can read
stream files this way) BUT this will process every line of the script,
which may not be desirable if you keep the scripts as sort of a history
of what you've been doing.
So the other way to execute these scripts is to use iNav. On my Windows
box I use Windows Explorer to navigate to my 'SQL Source' directory.
Double clicking opens the file in the iNav SQL script application where
I can choose which statements I want to execute. I can also edit and
save scripts from there. The benefit of using a stream file is that you
can organise your scripts in any way you care to; stream files and
directories can be multiple levels deep. You can copy files via Windows
Explorer for backup or to import them into some other application if you
wish - they're regular text files. You can fill them with comments.
The downside is that you need to map a directory via NetServer and have
most of iNav installed. You also ought to have a little understanding
of the stream file (IFS) file system so you can create directories and
files and understand authority.
Another way to create a view is to write an HLL program (like RPG) which
has the SQL embedded in it. You're probably going to want to use
embedded SQL anyway; this could be one way for you to understand the
details of doing that.
--buck
midrange.com
