Difference between revisions of "Query"

From MidrangeWiki
Jump to: navigation, search
(Navigation Introduction)
m (spelling)
 
(9 intermediate revisions by one other user not shown)
Line 1: Line 1:
 
[[Category:Database]]
 
[[Category:Database]]
 
[[Category:Query]]
 
[[Category:Query]]
Query/400 is a basic user oriented database report writer.
+
[[Category:Basics 101]]
 +
== Query/400 Summary ==
 +
* It is a basic user oriented database decision support report writer and utility.
 +
* It is an IBM licensed program that comes free with [[OS/400]].
 +
* It is designed to allow non-technical users to create basic reports and online inquiries in a simple, menu driven way.
 +
* It is a tool that is pretty safe to put into the hands of users, so long as we know how to prevent them from creating an *OUTFILE that gets same name as a pre-existing real file in our data base.
  
It is designed to allow non-technical users to create basic reports and online inquiries in a simple, menu driven way.
+
See [[CLP/400 examples]] of [[CLP]] programs that give people access to Query stuff that can go on a Menu, so that they do not have Command Line Access which has some security concerns.
  
It is a tool that is pretty safe to put into the hands of users, so long as we know how to prevent them from creating an *OUTFILE that gets same name as a pre-existing real file in our data base.
+
=== Query Command Reference List ===
  
See [[CLP/400 examples]] of [[CLP]] programs that give people access to Query stuff that can go on a Menu, so that they do not have Command Line Access which has some security concerns.
+
* [[DLTQRY]] Delete a query definition
 +
* [[RUNQRY]] Run a query definition
 +
* [[STRQRY]] Start Query/400
 +
* [[WRKQRY]] Work with Query definitions
  
 
== Query Tips and Techniques ==
 
== Query Tips and Techniques ==
Line 30: Line 38:
  
 
==== Navigating Display ====
 
==== Navigating Display ====
 +
Assuming the query definition is going to a screen, as opposed to a report or work file, there are two places on top where you can key something, and a few commands available.
 +
 +
Upper Left corner is used for specifying some line number you want to jump to, or "B" for Bottom, or "T" for Top again.  You can also scroll through the data.
 +
 +
Upper Right corner is used for specifying some sideways byte count you want to leap to.  You can also use F20 (upper shift F8) to go to the right, or F19 (upper shift F7) to go to the left.
 +
 +
Here is how to learn how to use F21 (upper shift F9) split screen. 
 +
* Use F20 a few times or Upper Right to get somewhat to the right of the starting fields.
 +
* Place cursor just in front of one of the field columns, then F21.  See how the screen got split.  F21 back again.
 +
* Now move cursor just in front of a different field column, then F21.  Notice that where the split occurs depends on where the cursor was sideways.
 +
* Now F19 a bunch of times to get one side back to the beginning.
 +
* Experiment with this a bit.
 +
* You see that by manipulating the sideways placement with Upper Right before the split, and where place the cursor, you can get two sets of columns side by side that were not that way in the original query definition.
 +
 +
===== Commands in Query Display =====
 +
 +
* F3 to exit this entirely
 +
* F12 to return to the selection screen and make a different combination selection.
 +
* F20 move to the right
 +
* F19 move to the left
 +
* F21 split screen (explained above)
  
 
== Query vs. High Level Languages ==
 
== Query vs. High Level Languages ==
Line 41: Line 70:
 
** this gets us a fast dump of the file contents, without needing a query definition
 
** this gets us a fast dump of the file contents, without needing a query definition
 
** use F4 instead of enter ... and we get at selection criteria and other options
 
** use F4 instead of enter ... and we get at selection criteria and other options
 +
 +
When matching files, can join based on same type, not have to match length of field contents.
  
 
=== HLL Advantages vs. Query ===
 
=== HLL Advantages vs. Query ===
  
In terms of general 400 perromance, Query cannot hold a candle to High Level Languages like [[ROG]] and [[SQL]]. Query/400 is a resource hog.
+
In terms of general 400 perfomance, Query cannot hold a candle to High Level Languages like [[RPG]] and [[SQL]]. Query/400 is a resource hog.
 +
 
 +
Query is somewhat limited on numbers of files to join and inter-link. 
  
Query is somewhat limited on numbers of files to join and inter-link.
+
Bogus results if trying to combine files with multiple connections in each.  Query is Ok if only one file is details, and all other joins are master files, but try to combine multiple details, and you can get excess matches.

Latest revision as of 08:07, 29 August 2008

Query/400 Summary

  • It is a basic user oriented database decision support report writer and utility.
  • It is an IBM licensed program that comes free with OS/400.
  • It is designed to allow non-technical users to create basic reports and online inquiries in a simple, menu driven way.
  • It is a tool that is pretty safe to put into the hands of users, so long as we know how to prevent them from creating an *OUTFILE that gets same name as a pre-existing real file in our data base.

See CLP/400 examples of CLP programs that give people access to Query stuff that can go on a Menu, so that they do not have Command Line Access which has some security concerns.

Query Command Reference List

  • DLTQRY Delete a query definition
  • RUNQRY Run a query definition
  • STRQRY Start Query/400
  • WRKQRY Work with Query definitions

Query Tips and Techniques

Here are links to "how to" do various things in Query, in which Query is part of the name of the topic, so that other people can use the same name with SQL or RPG or some other 400 tool to get similar results.

Navigation Introduction

If you are new to Query, some access options may be non-obvious at first. This info is also for end users of Query Definitions, who may be infrequent users of Query, accessing it from a Menu, not neccessarily knowing what is a HLL, Query, something else.

Selection Criteria

After launching a previously defined query definition, or a file dump, you get to a familiar selection screen with top 1/2 being space to specify what to include, and bottom half being a director of field names.

If the bottom 1/2 does not give textual explanations for the fields, use F11 to toggle between how they are listed. Move cursor to bottom 1/2 then scroll to see what field names can be entered in the top 1/2 as part of selection criteria.

Navigating Display

Assuming the query definition is going to a screen, as opposed to a report or work file, there are two places on top where you can key something, and a few commands available.

Upper Left corner is used for specifying some line number you want to jump to, or "B" for Bottom, or "T" for Top again. You can also scroll through the data.

Upper Right corner is used for specifying some sideways byte count you want to leap to. You can also use F20 (upper shift F8) to go to the right, or F19 (upper shift F7) to go to the left.

Here is how to learn how to use F21 (upper shift F9) split screen.

  • Use F20 a few times or Upper Right to get somewhat to the right of the starting fields.
  • Place cursor just in front of one of the field columns, then F21. See how the screen got split. F21 back again.
  • Now move cursor just in front of a different field column, then F21. Notice that where the split occurs depends on where the cursor was sideways.
  • Now F19 a bunch of times to get one side back to the beginning.
  • Experiment with this a bit.
  • You see that by manipulating the sideways placement with Upper Right before the split, and where place the cursor, you can get two sets of columns side by side that were not that way in the original query definition.
Commands in Query Display
  • F3 to exit this entirely
  • F12 to return to the selection screen and make a different combination selection.
  • F20 move to the right
  • F19 move to the left
  • F21 split screen (explained above)

Query vs. High Level Languages

Query Advantages vs. HLL

On short notice, we generally can get a new query/400 written and into operation faster than a new program, if all we have are native 400 editors. Non-programmers can rapidly get at simple lists and inquiries.

From Command Line

  • RUNQRY *N then name of some file
    • this gets us a fast dump of the file contents, without needing a query definition
    • use F4 instead of enter ... and we get at selection criteria and other options

When matching files, can join based on same type, not have to match length of field contents.

HLL Advantages vs. Query

In terms of general 400 perfomance, Query cannot hold a candle to High Level Languages like RPG and SQL. Query/400 is a resource hog.

Query is somewhat limited on numbers of files to join and inter-link.

Bogus results if trying to combine files with multiple connections in each. Query is Ok if only one file is details, and all other joins are master files, but try to combine multiple details, and you can get excess matches.