Ad Hoc Query


Thank you for your interest in Ad Hoc Query. This course is designed for basic users. Formatting of the output and other features of Ad Hoc will be included in future updates of this course .
Instructions:
     Two open windows are required for your convenience when viewing the link screens within the course. When you click the link from the homepage, this window opens up for the text content of the course. When you click a link within the content of the course, a new window will open up and most likely will cover the first window. Before you click any link, resize this window. Take your mouse pointer to the right hand edge of the active window and drag it to the left(about half way or you decide the size of the window). Then scroll down and look for a link identified in green color. When you click the link, a new window will open up. You do the same thing what you did on the first window (resize it). This time, take your mouse pointer to the left hand edge of the window and drag it right until the two windows are side-by-side. When you click a link page, that page will be displayed on that inactive window. Try it now. Click here to view a sample screen illustration. After you click the word "here", the slide " I'm here on the right window" will be displayed in a new window. Resize this window and place this window to the right hand side of the screen. Next time you click a link, that slide or information screen or illustration screen will be displayed to the right.

    This course is divided into twenty simple steps for quick and easy comprehension of the subject matter. A link to eMail is strategically placed at the end of every steps for the convenient of the student. If a question is borned while reading and comprehending the content of every steps, asking that question is just a click away.

    Her's how you're going to do it... read the course content on the left or right window (depending where you place the window for the course content). Occassionally, you will be instructed to click a link... the link screen illustration or slides or information screen will display on the inactive window.

    Now that you know the basic instructions how to navigate around or within the course, you can begin taking the course at your own pace. You start by reading the information in Step 1 and continue through Step 20.

    Here are the twenty steps involved to complete the basic Ad Hoc Query:


Step 1: Familiarization: Let's begin with a question. What is Ad Hoc Query? Click here for the answer. The answer is displayed on the inactive window. Now, next question... what is SQL? Click here for the answer. Another question... the SQL answer mention about relational database. So, what is a relational database? Click here for the answer. Again, the answer is displayed on the inactive window. The preceeding examples are designed to get you familiar with the process... if you click a link within the content of the course, the linked information or slide will be displayed on the other open window. To verify whether you are looking at the correct slide for that particular step, you take your mouse pointer over the inactive window and click to make the window active without moving your mouse pointer. Then wait for a couple of seconds... a bubble information will display indicating the corresponding slide (i.e. Ad Hoc Slide 6 of 20 for Step 6). Now, let's get back to learning Ad Hoc Query.

    Learning Ad Hoc is very simple provided you are familiar with relational database and the Structured Query Language (SQL). Let's assume you have very little understanding of relational database and SQL. The quickiest way to learn Ad Hoc is to have a sample problem to solve. So, click here and study the slide to the right. Along the way, you will be shown a sample of an actual Ad Hoc Screens with notations on it, database table name, and data element name used within the RSupply database.

   Note: To ensure that you are looking at the correct slide, take your mouse pointer over a screen illustration or picture on the other window and wait for a couple of seconds. A bubble note will pop-up identifying the corresponding slide number of that particular step you are on. For example: The slide on the other window should read "Ad Hoc Slide - Step 1 of 20"... if it does, you are looking at the correct slide. Go ahead move on... make sure to remember the problem. You can now activate the Ad Hoc Query Program. Move on to Step 2.

Any questions? or just want to send feedback... Please click here.


Step 2: Activating The Ad Hoc Query Program: To activate the Ad Hoc Query Program, click here to view and follow the menu path as indicated on the slide being displayed on the other window. Read the notes on the slide. When you click on the Expert Option, a blank Ad Hoc Screen will be displayed. Move on to step 3.

Any questions? or just want to send feedback... Please click here.


Step 3: Building The Ad Hoc Query Parameters: You can start your Ad Hoc Query parameters in two defferent ways: Go ahead click here to view the slide on the other window. From the blank Ad Hoc Screen you simply click either "File" menu option or click on the first icon on the icon menu. When you click on either File or Icon, the Ad Hoc "Query Checklist" will display. Move on to step 4.

Any questions? or just want to send feedback... Please click here.


Step 4: Working On The "Query Checklist": The Query Checklist is your step-by-step guide what to do next. You have to follow the sequence listed on the checklist. Click here to view and again study the Ad Hoc Screen illustraion slide on the other window. The Query Checklist will always be displayed in the background. Every button on the query checklist has its own purpose. As you can see on the slide, only one button is activated (the Tables button). The "Tables" button is the very first step in building your Ad Hoc parameters. Move on to step 5.

Any questions? or just want to send feedback... Please click here.


Step 5: Selecting The "Tables" Button: Click here to view and study the slide to the right. When you click the Tablesbutton, the "Database Tables" pop-up window or dialogue box will be displayed. You notice the pop-up window is consist of two display box. On the left, it says... "Available Items" and to the right, it says... "Selected Items". On the "Available Items" display box, you see all the available database tables ready for your selection. Once the database table is selected, the selected table will move to the right display box called "Selected Items". So, let's move on to the next step.

Any questions? or just want to send feedback... Please click here.


Step 6: Selecting The Correct Database Table or Tables: Click here to view and study the slide on the other window. This is probably the most critical part of the process. You must ensure that you select the correct table or tables. You must select the database table or tables that includes the data element name you want displayed in your output. Data element can be present in various database tables. So, you have to make sure that you select the precise database table. If you select an incorrect database table, chances are, you will not be able to obtain the correct output.

    So, the question is... how do you determine which database table to select? Determining which database table to select is very simple, let's go back to the problem and see what data elements are required to be displayed on the output. Click here to review the problem and focus on the required data elements to be displayed on the output. I highly recommend to take notes (write it down on a piece of paper) so you can search this data element on the cross-reference list you are about to see to the right. Okay, after you wrote those data elements down, click here and study the cross-reference table on the right.

    The cross-reference table to the right consist of two columns. The first column is an alpha sequence of data element names cross-referencing to the RSupply database table names on the second column. Now, as you scroll up or down looking for the correct data element name, you come across a data element that crosses to several database tables (i.e. cog, fsc, etc.). Now, you are face with another problem... which table to select?

    Relax, you have to know the nature of the problem. You have to select the database table that contains the data element depending on what problem or information you want. For example, if you want to display the data element "cog" of an item, item table must be selected because the content of the item table is more permanent compared to the "cog" that is found in active_rqn_tbl or stk_rpt or any other table where the data element "cog" is present. Records in stk_rpt or active_rqn_tbl table are transient. These records are present on those tables only when there was a process that affect those items (i.e. requisitions were process against a certain items). However, if you want to display records pertaining to requisitions... then, active_rqn_tbl table will be selected.

    And another thing, "cog" is not present in item table. Unfortunately, "cog" is written as "cognizance_symbol" in item table. This is another thing that you have to watch out for when selecting a database table or data element name. The data element names you see here is the consolidation of the fourteen most common RSupply database tables. Study the fourteen most common database tables that are displayed to the right. You notice there is a column called (FK) Foriegn Key and (PK) Primary Key. Under those two columns, it says "Yes" or "No". The data element names that has "Yes" under FK column... meaning that these data elements are PK in some other table and can be used to link this table to another table within the RSupply database. We will discuss linking tables in the next step.

    But before moving into the next step, you have an assignment to do. With the list of data element names you wrote down on a piece of paper in paragraph 2 of step 6, select the correct database tables needed to solve the sample problem. Click here to display the alphabetical list of data element names cross-reference to database table names. I want you to select the correct database tables that will be linked to give you the correct output for the given sample problem.

Go ahead select the correct database tables and list the tables you chose on that same piece of paper where you wrote those data element names needed to display in your expected output. After you made your choices, click here to compare our notes. If your answer is exactly like the list displayed on the other window, then move on to the next step. If not, read the previous steps and other related display screens and study some more. Do not move on to the next step until you fully understand why you selected those database tables in your notes or list. If you have any questions, click the link below.

Any questions? or just want to send feedback... Please click here.


Step 7: Linking The Selected Tables: Another critical step. However, this step applies only if you have selected two or more database tables. Majority of your Ad Hoc Query will probably involve two or more database tables. Before you can pick and choose the data element names to be displayed on your expected output, you must link the database tables you selected. Click here to view and study the slide displayed on the other window. As you study the Slide to the right of your screen, you see the "Table Links" pop-up window or dialogue box. This window consists of Table 1 display box and Table 2 display box. Underneath the Table 1 and Table 2 display boxes, there are also columns display boxes. The "Columns" display boxes are where the "FK" and "PK" data element(s) is/are displayed. the "FK" and "PK" data elements are to be used in linking the selected database tables. Go to the next step and study how database tables are linked.

Any questions? or just want to send feedback... Please click here.


Step 8: How Tables Are Linked: Click here to view and study the Slide on the other window. When you click the dropdown menu in Table 1, the three Database Tables you selected earlier (from Step 6) are displayed. You have to make a selection from the list of database tables being displayed. You are free to select any of those tables. After you selected a database table in Table 1, the "PK" or "FK" data element(s) is/are displayed on the Columns display box. Once the "PK" or "FK" data element(s) is/are displayed, you have to select the data element name even if only one data element is displayed. To select the data element name, just click on it. After you selected the data element name, you can move on to select another Database Table in Table 2.     Click the dropdown menu in Table 2 and again the selected database tables will be displayed. Select another database Table to link to database table you selected in Table 1. Again, after you selected a table, the "PK" or "FK" data element(s) of the table selected is/are displayed on the Column(s) display box. Once the "PK" or "FK" data element(s) is/are displayed, you have to select the common data element name (i.e. if you selected the data element name "niin" in Table 1, you have to select the data element name "niin" in Table 2... as you will see in Step 9). Do this process to the remaining database Tables to be linked. If you selected a database Table that do not have a common data element name with the rest of the database Tables you selected, you may not use this Table.

    The easy way to link database Table is to have Table 1 stationary. Select the remaining database Tables in Table 2 box. However, after each Table selection, you have to accept the link. To accept the link, you must click on "Accept" button before selecting another Table. The accepted links will display on the "Active Links" display box. To accept the link, click on "Accept" button (See Step 9.) Click here to see an illustration how it looks like. Go to the next step (Step 9) and study the Slide.

Any questions? or just want to send feedback... Please click here.


Step 9: Accepting The Linked Tables: Click here to view and study the "Slide" on the other window. What you see here is, under Table 1, database Table item is selected and data element name niin is also selected in the "Column" display box. Then, under Table 2, database Table item_loc is selected and then three data element names are displayed on the "Column" display box. The data element name niin is selected because data element name niin is common to both Table 1 and Table 2. After you made the selection, click the "Accept" button. When you click the "Accept" button, the active links will display on the "Active Links" display box (See step 10).

Any questions? or just want to send feedback... Please click here.


Step 10: Linked Tables Verification: Click here to view and study the Slide. On the "Active Links" display box, you have now an active table link. It says, "item.niin = item_loc.niin"... meaning that item database Table and item_loc database Table have been linked using the data element name niin to link the two database Tables.

    Next is to link the remaining database Table, which is the stock_item database Table. Remember you have selected three database Tables based on the sample problem we have. To do this, you click the dropdown menu in Table 2 and select the stock_item database Table... then select the data element name niin to link the stock_item Table to item Table. Click the "Accept" button to accept the link. On the "Active Link" display box, it will say "item.niin = item_loc.niin AND item.niin = stock_item.niin" (See step 11). At this time you have completed the database Table linking process. Go to step 11.

Any questions? or just want to send feedback... Please click here.


Step 11: Completed Linked Tables: Click here to view and study the Slide on the other window. Click here to display an illustration of how the three database Tables were linked. As you can see, "item_loc, item, and stock_item" Tables are linked and appears to be or become one huge database Table. From this huge database Table (it seems), you can now pick and choose the data element names you want displayed on your expected output. Steps 12 and 13 will show you how to select the data element names to be displayed on your output. For now, click the "OK" button to accept the completed database Table links.

Any questions? or just want to send feedback... Please click here.


Step 12: Selecting The Data Element Columns: Click here to view and study the "Slide" displayed on the other window. As you can see, the "Query Checklist" is displayed with all the Query Checklist buttons being highlighted or activated. All buttons are active at this point. This step is all about getting ready to select the data element names to be displayed on your output. So, click the Columns button and go to step 13.

Any questions? or just want to send feedback... Please click here.


Step 13: Selecting and Rearranging The Data Element Columns to be Displayed: Click here to view and study the Slide displayed on the other window. After you click the "Columns" button on the Query Checklist, the "Database Columns" window is displayed. The window consist of two display box. The left display box is labeled "Available Items" and to the right display box is labeled "Selected Items".

    All the data element names from the linked database Tables are now displayed on the "Available Items" display box. Your goal is to select the data element names that you have on your notes from step 6. Click here to see the notes from step 6. As you can see, the first column on the note is the data element to be displayed on the output. The first column to be displayed on the output is "cog". But from which database Table? As you see on the next column on the notes, you use "item" Table for the "cog". However, on the item Table, "cog" is spelled out as "cognizance_symbol". To select the "cog" column, scroll down or up to locate the item that says "item.cognizance_symbol" and double click on it or select the item and click the "Add" button. Selected items will be displayed to the right display box labeled "Selected Items". To remove a selected item, double click on the item or select the item then click on the "Remove" button.

    Again, Click here to view the Slide to the right of your screen. On the "Selected Items" display box, these are all the data element names to be displayed on your output. You can re-sequence or re-arrange the order of the displayed columns by selecting the data element name and click the "Move item up" button or the "Move item down" button. You are free to re-arrange the order of the columns to be displayed in your output.

    After you selected all the data element names or columns or you are satisfied with your column sequence in which to be displayed on your output, you click the "OK" button to accept it. When the "OK" button is clicked, the "Query Checklist" is again visible for the next step. Go to step 14.

Any questions? or just want to send feedback... Please click here.


Step 14: Choosing The Criteria: Click here to view and study the "Slide" displayed on the other window. Read the captions on the slide... then go to step 15.

Any questions? or just want to send feedback... Please click here.


Step 15: Completing The Criteria Selection: Click here to view and study the "Slide" being dispalyed on the other window. Read and understand the captions on the slide... then move on to step 16.

Any questions? or just want to send feedback... Please click here.


Step 16: Verification And Accepting The Criteria: Click here to view and study the "Slide" displayed on the other window. Again, read and understand the captions on the slide. Step 16 completes your criteria selection... based on the given problem. When you click the "OK" button, the Ad Hoc program accepts your criteria. And the "Query Checklist" is again visible for the next step. The next three buttons (Calculations, Sort, and Group) are not applicable to the given problem. The customer did not specify whether the output is to be calculated, sorted, or grouped. The unit price could be totalled by using calculation, or the output could be sorted by any displayed column the customer choose, or the output could be grouped by cognizance symbol. Anyway, for now can run the query since the customer did not specify any calculation, sort, or group. Go to step 17.

Any questions? or just want to send feedback... Please click here.


Step 17: Running The Query: Click here to view and study the "Slide" displayed on the other window. Again, read and understand the captions illustrated on the slide. When you click the "Run" button, the "Query Impact" sceen will display. See the "Query Impact" screen on the next step. Go to step 18.

Any questions? or just want to send feedback... Please click here.


Step 18: Query Impact Screen: Click here to view and study the "Slide" displayed on the other window. At this point, make sure the "Grid" radio button under "Format" on the "Output" block is checked. It should be pre-selected already. If not, click to select the radio button... then click on "OK" button to continue the process. See step 19.

Any questions? or just want to send feedback... Please click here.


Step 19: Retrieving Rows: Click here to view and study the "Slide" displayed on the other window. The Ad Hoc program should start retrieving rows. After a few minutes (depending on the size of the output), you should see your output. See step 20 for the expected output.

Any questions? or just want to send feedback... Please click here.


Step 20: The Output: Click here to view and study the "Slide" on the other window. This is your output. Look at it and study it... by referring it the given problem.

Any questions? or just want to send feedback... Please click here.