?
Solved

need help with this select code..

Posted on 2003-03-08
7
Medium Priority
?
584 Views
Last Modified: 2012-08-13
There are four tables in the db like the following (those are the table name and the columns)

Sales Table-     Product_ID, Area_ID, Date, Amount
Product Table-     Product_ID, P_Name
Area Table-     Area_ID, A_Name
Time Table-     Date, Month_ID, Quater

There are 3 item types in the oracle 6i form I created.
1.      I have all the above mentioned column names in check boxes,
2.     I have a place to input the time period    e.g.: _____________ to ____________
3     and a button called Execute

At runtime the user ticks the check boxes (column names) that he wants to merge, inputs the time period (e.g.: 01-JAN-2003 to 21-FEB-2003) and then he clicks on the Execute button.

Can someone please tell me where the code is wrong This code is placed in a WHEN-BUTTON-PRESSED trigger.
What I want is for a user to input at runtime (both column names and the time period) then when the user presses the button to execute the query and outputs the result on another window. Please help.
pri
__________________________________________________________________________________
DECLARE
      v_select VARCHAR2(32000) := 'SELECT';
     
      DATE_ID VARCHAR2(80) := 'SALES_FACT.DATE_ID';
     MONTHI VARCHAR2(80) := 'SALES_FACT.MONTHI';
     QUATER VARCHAR2(80) := 'SALES_FACT.QUATER';
     PRODUCT_ID VARCHAR2(80) := 'SALES_FACT.PRODUCT_ID';
     P_NAME VARCHAR2(80) := 'SALES_FACT.P_NAME';
     AREA_ID VARCHAR2(80) := 'SALES_FACT.AREA_ID';
     A_CATEGORY VARCHAR2(80) := 'SALES_FACT.A_CATEGORY';
     QTY VARCHAR2(80) := 'SALES_FACT.QTY';
     
  connected  boolean;
  errcode          number     :=error_code;
  cursor_name exec_sql.curstype;
  rows_processed pls_integer;
  connection_id exec_sql.conntype;



BEGIN
IF Checkbox_Checked(DATE_ID) THEN           /* DATE CHECKBOXES*/
          v_select := v_select || ' SALES_FACT.DATE_ID,';
     END IF;
     
     IF Checkbox_Checked(MONTHI) THEN
           v_select := v_select || ' TIME_DIMEN.MONTH,';
     END IF;
     
     IF Checkbox_Checked(QUATER) THEN
           v_select := v_select || ' TIME_DIMEN.QUATER,';
     END IF;
     
     IF Checkbox_Checked(PRODUCT_ID) THEN           /* PRODUCT CHECKBOXES*/
           v_select := v_select || 'SALES_FACT.PRODUCT_ID,';
     END IF;
         
     IF Checkbox_Checked(P_NAME) THEN
           v_select := v_select || ' PRODUCT_DIMEN.P_NAME,';
     END IF;
         
     IF Checkbox_Checked(AREA_ID) THEN           /* AREA CHECKBOXES*/
           v_select := v_select || ' SALES_FACT.AREA_ID,';
     END IF;
         
     IF Checkbox_Checked(A_CATEGORY) THEN
           v_select := v_select || ' AREA_DIMEN.A_CATEGORY,';
     END IF;
         
                   
     IF Checkbox_Checked(QTY) THEN           /* QTY CHECKBOXES*/
           v_select := v_select || ' SALES_FACT.QTY,';
     END IF;
         
     IF Checkbox_Checked(NPS) THEN
           v_select := v_select || ' SALES_FACT.NPS,';
     END IF;

     v_select := RTRIM( v_select, ',' );     /* TO REMOVE THE , IN THE LAST SELECT STATEMENT */




connection_id := exec_sql.open_connection('samual/lakecity@viva8');
connected := exec_sql.is_connected(connection_id);
if connected = TRUE then
    message ('Connected  ');
   
else
    message ('NOT connnected');
end if;


cursor_name := EXEC_sql.open_cursor(connection_id);
     
  SQLSTR :=( SELECT         :v_select
                       FROM     Sales, Product, Area, Timei
           WHERE     'Sales.Product_ID= Product.Product_ID'||
           'AND      Sales.Area_ID = Area.Area_ID '||
           'AND          Sales.Date = Time.Date   '||
           'AND          DATE BETWEEN  :D1 AND :D2';

         
exec_SQL.parse(connection_id,cursor_name,SQLSTR,exec_sql.v7);
rows_processed := exec_sql.execute( connection_id,cursor_name);
exec_sql.close_cursor(connection_id,cursor_name);
exec_sql.close_connection(connection_id);


end;


0
Comment
Question by:spri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Expert Comment

by:Datamonkey
ID: 8094082
i don't know forms very well but the way you build SQLSTR doesn't look quite correct to me, 2 things:
1.
it looks to me that you build the v_select variable as a string that lists the columns that you want to use and then rather than concatenating that string with the main select statement, you use it as a bind variable
2.
When building SQLSTR you don't use quotes,

SQLSTR :=( SELECT         :v_selelect                  FROM     Sales, Product, Area, Timei
(etc.)

should that not simply be

SQLSTR := 'SELECT ' || v_select ||
          'FROM Sales, Product, Area, Timei'
(etc.)

it could of course be my lack of forms knowledge that lets me down here... :-)
--dm
0
 

Author Comment

by:spri
ID: 8097280
I coded the following and compiled no errors. But when I run there is an error saying : argument 2 to builtin CREATE_GROUP_FROM_QUERY cannot be null. In the example under ORACLE  REPORT it say that the name, data type, and number of columns in the report must be the same as the record group in the form.  I dont know how I can do this? How to create a record group. Please help

I modified  qry VARCHAR2(2000)         := 'SELECT ename, sal, comm' ||
                               'FROM emp' || to  qry VARCHAR2(2000)         := SQLSTR  I hope that can be done..?
 

DECLARE
             v_select VARCHAR2(32000) := 'SELECT';
             
             DATE_ID VARCHAR2(80) := 'SALES_FACT.DATE_ID';
            MONTHI VARCHAR2(80) := 'SALES_FACT.MONTHI';
            QUATER VARCHAR2(80) := 'SALES_FACT.QUATER';
            PRODUCT_ID VARCHAR2(80) := 'SALES_FACT.PRODUCT_ID';
            P_NAME VARCHAR2(80) := 'SALES_FACT.P_NAME';
            AREA_ID VARCHAR2(80) := 'SALES_FACT.AREA_ID';
            A_CATEGORY VARCHAR2(80) := 'SALES_FACT.A_CATEGORY';
            QTY VARCHAR2(80) := 'SALES_FACT.QTY';

       SQLSTR            VARCHAR2(2000);
       groupname VARCHAR2(30)     := 'RG1';
       paramlistname VARCHAR2(30) := 'PLIST1';
       qname VARCHAR2(30)         := 'Q_EMP';
       qry VARCHAR2(2000)         := SQLSTR ; --(THIS IS THE QUERRY THAT WAS CREATED USING THE  CHECKBOXES.
                                                                                                       -- ACCORDING TO THE EXAMPLE THE qry SHOULD MATCH THE rgid RECORDGROUP.                                                                                    -- BUT I DONT KNOW HOW TO DO THAT)
       rgid                       RECORDGROUP;
       plid                       PARAMLIST;
       n                          NUMBER;
 
 
            
BEGIN

--(TRYING TO GET THE USER INPUTS (CLOMN NAMES) AT RUNTIME TO PASS ON TO THE SELECT STATMENT)----------------------------------------
                      IF Checkbox_Checked(DATE_ID) THEN                                                                   /* DATE CHECKBOXES*/
                         v_select := v_select || ' SALES_FACT.DATE_ID,';
            END IF;
      
            IF Checkbox_Checked(MONTHI) THEN
                   v_select := v_select || ' TIME_DIMEN.MONTH,';
            END IF;
      
            IF Checkbox_Checked(QUATER) THEN
                   v_select := v_select || ' TIME_DIMEN.QUATER,';
            END IF;
            
            IF Checkbox_Checked(PRODUCT_ID) THEN                                                                   /* PRODUCT CHECKBOXES*/
                   v_select := v_select || 'SALES_FACT.PRODUCT_ID,';
            END IF;
            
            IF Checkbox_Checked(P_NAME) THEN
                   v_select := v_select || ' PRODUCT_DIMEN.P_NAME,';
            END IF;
            
            IF Checkbox_Checked(AREA_ID) THEN                                                                               /* AREA CHECKBOXES*/
                   v_select := v_select || ' SALES_FACT.AREA_ID,';
            END IF;
            
            IF Checkbox_Checked(A_CATEGORY) THEN
                   v_select := v_select || ' AREA_DIMEN.A_CATEGORY,';
            END IF;
                  
            IF Checkbox_Checked(QTY) THEN                                                                   /* QTY CHECKBOXES*/
                   v_select := v_select || ' SALES_FACT.QTY,';
            END IF;
            
                  v_select := RTRIM( v_select, ',' );                                          /* THIS IS TO REMOVE THE COMMA IN THE LAST SELECT STATEMENT */



--(TRYING TO CREATE A GROUP,PASS THE SELECT STATEMENT AND THEN GET THE OUTPUT IN ANOTHER WINDOW/REPORT/GRAPHICS)----------------------------------------
 
  SQLSTR :=( 'SELECT'   ||v_select||
                                     'FROM                  Sales, Product, Area, Timei'||
                                     'WHERE          Sales.Product_ID= Product.Product_ID'||
                                     'AND               Sales.Area_ID = Area.Area_ID'||
                                     'AND                Sales.Date = Time.Date'||
                                     'AND                DATE BETWEEN  :D1 AND :D2');
                                                      
 --  Make sure the group does not already exists.
 rgid := FIND_GROUP(groupname);

 --   If the group exists, then delete it.
 IF NOT ID_NULL(rgid) THEN
    DELETE_GROUP(rgid);
 END IF;

  --  Create the group.
 rgid  := CREATE_GROUP_FROM_QUERY(groupname, qry);

  --  Make sure the record group is empty.
 DELETE_GROUP_ROW(rgid, ALL_ROWS);

 --   Retrieve the data.
 n := POPULATE_GROUP_WITH_QUERY(rgid, qry);

 --   Make sure the list does not already exist.
 plid := GET_PARAMETER_LIST(paramlistname);

 --   If the list exists, then delete it.
 IF NOT ID_NULL(plid) THEN
    DESTROY_PARAMETER_LIST(paramlistname);
 END IF;

 plid := CREATE_PARAMETER_LIST(paramlistname);
 ADD_PARAMETER(plid, qname, DATA_PARAMETER, groupname);

 --   Suppress the parameter form.
 ADD_PARAMETER(plid, 'paramform', TEXT_PARAMETER, 'NO');

--    The following command will call Oracle Reports in SYNCHRONOUS mode.
--    To call Oracle Graphics, replace the REPORTS with GRAPHICS.

 RUN_PRODUCT(REPORTS, 'RG', SYNCHRONOUS, RUNTIME, FILESYSTEM, plid, NULL);

 DESTROY_PARAMETER_LIST(plid);
 DELETE_GROUP(rgid);

end;
0
 

Author Comment

by:spri
ID: 8100216
has anybody done any coding for some thing like this question? if so can i please have a look at it?
i'm desperate. i need help..
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Expert Comment

by:rishi_raj
ID: 8101231
Hi,
There are a few points which you may consider.

1. Since you initialize your v_select variable with "Select" and then start your string variable SQLSTR with "Select" the SQL string being formed would start something like "Select Select SALES_FACT.DATE_ID....." and so on, which will result in error. Maybe you should confirm the veracity of your SQL string by checking it through "message" or in the debugger.

2. Although it may not be an error but you need not use  

"
--  Make sure the record group is empty.
DELETE_GROUP_ROW(rgid, ALL_ROWS);
"
as the record group being created by you is a fresh one.

3. Another point

"
--   Retrieve the data.
n := POPULATE_GROUP_WITH_QUERY(rgid, qry);
"

Since you are creating the record group with the builtin "CREATE_GROUP_FROM_QUERY" you should use "POPULATE_GROUP" to populate the group with query data.

Use POPULATE_GROUP_WITH_QUERY if you have created the record group using CREATE_GROUP.

0
 

Expert Comment

by:rishi_raj
ID: 8101239
when passing a record group to a report as a DATA PARAMETER you should make sure that the number of fields selected in the record group and the number of columns being selected in the report query are same. Also make sure that the column names in both the record group query and the report query are the same.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9985209
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10025466
PAQed, with points refunded (75)

Computer101
E-E Admin
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question