How do u do a SELECT at runtime & output the data according to the date range specified?

can u pls help?

We don't do your homework for you.  That's called cheating.  At least attemp to do parts of it.  If you get stuck on something we can give you examples of how to do it.  But don't ask someone else to do your homework for you.

especially this... cuz this is really easy...

This is what I tried..
here you check if the checkbox is checked in the form. If it is checked, the column name is passed to the SQLSTR’s select statement in the EXEC_SQL. If not checked its ignored.
__________________________________________________________________
IF Checkbox_Checked(Sales.Product_ID) THEN           pass Sales.Product_ID to SQLSTR’s select statement   ELSE      ignore  END IF;  
IF Checkbox_Checked(Sales.Area_ID) THEN        pass Sales.Area_ID to SQLSTR’s select statement  
ELSE
      ignore
END IF;  .
.
.
IF Checkbox_Checked(Time.Quater) THEN        pass Time.Quater to  SQLSTR’s select statement   ELSE           ignore
END IF;  END;  __________________________________________________________________


DECLARE
CONNECTION_ID      EXEC_SQL.CONNTYPE;
CURSOR_NAME      EXEC_SQL.CURSTYPE;
SQLSTR          VARCHAR2(2000);



BEGIN
CONNECTION_ID:= exec_sql.open_connection('samual/lakecity@viva8');
CURSOR_NAME := exec_sql.open_cursor(CONNECTION_ID);


SQLSTR :=( SELECT    Sales.Product_ID, Sales.Area_ID, Sales.Date, Sales.Amount,
                Product.Product_ID, Product.P_Name,
                Area.Area_ID, Area.A_Name,
                Time.Date, Time.Month_ID, Time.Quater

FROM                      Sales, Product, Area, Time

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.v8);
rows_processed := exec_sql.execute( CONNECTION_ID,CURSOR_NAME);
     
exec_sql.close_cursor(CONNECTION_ID,CURSOR_NAME);
exec_sql.close_connection(CONNECTION_ID);

END;

__________________________________________________________________

I don’t know how to write the code to pass the chosen columns names from the checkbox checked statements to the SQLSTR in the EXEC_SQL and
EXEC_SQL’s  SQLSTR to accept the parameters I’m sending using the checkbox checked statements. As you can see I am really lost. I have been trying for the last whole week but am not getting anywhere. Due the shear desperation I came here hoping for some help. Pls help me with the code.
Priti

I created one oracle 6i form, data block called  “SALES_FACT”  with columns from 4 tables. All these columns were taken to the form in the form of checkboxes. (The form also has a button).  When a checkbox is clicked it has the value “Y”
I added the below coding in a WHEN-BUTTON-PRESSED trigger and it compiled without any errors.
My question is how do I write the EXEC_SQL’s  SQLSTR..? Can I write it like the one I have shown? Please help?  

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';
     
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;


v_select := RTRIM( v_select, ',' );      

How do you do this part ?


DECLARE
CONNECTION_ID      EXEC_SQL.CONNTYPE;
CURSOR_NAME      EXEC_SQL.CURSTYPE;
SQLSTR          VARCHAR2(2000);

BEGIN
CONNECTION_ID:= exec_sql.open_connection('samual/lakecity@viva8');
CURSOR_NAME := exec_sql.open_cursor(CONNECTION_ID);


SQLSTR :=( SELECT    

FROM                      Sales, Product, Area, Time

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.v8);
rows_processed := exec_sql.execute( CONNECTION_ID,CURSOR_NAME);
exec_sql.close_cursor(CONNECTION_ID,CURSOR_NAME);
exec_sql.close_connection(CONNECTION_ID);

END;

Basically,
just pass the v_select to the sqlstr.  You want the SQLSTR to have the correct select statement.  That would be the one you built in the first part.

ie:
SQLSTR :=( SELECT    :v_select

FROM                      Sales, Product, Area, Time

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';



I can see where some of the code might cause some errors to occur. But that you'll learn to overcome as you get more practice.

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 inputs 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 me.

_________________________________________________________
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;

can any one help me?

prito_81
ok. I feel bad for you.  I think you are using a roundabout logic for your program.
Here is an example program from Oracle.  This program will not be the complete solution to your program.  But it does show you the basics.  Re-write your program and then report back.  The steps you are trying to do is good just using the wrong functions.
This is a bulletin from Metalink:

          PASSING RECORD GROUPS TO ORACLE REPORTS & GRAPHICS


Introduction
============
This bulletin explains the processes involved in creating a record
group in Oracle Forms and then passing it to Oracle Reports and Oracle
Graphics.  This bulletin is divided into three main parts.  The first
part describes the creation of a record group and passing it to Oracle
Reports and Oracle Graphics.  The second part describes how to create
a report that will run with the data based on a record group.  The
third part describes how to create a graph that will run with the data
based on a record group.

For the following sections, the emp table must be available in the current
login of Oracle Forms, Reports, and Graphics.  The emp table is a part of the
standard demo tables.  However, if you do not have access to the emp
table, you can create and populate the emp table as follows:

Name                              Type
-------------------------------    ----
EMPNO                             NUMBER(4)
ENAME                             VARCHAR2(10)
JOB                               VARCHAR2(9)
MGR                               NUMBER(4)
HIREDATE                          DATE
SAL                               NUMBER(7,2)
COMM                              NUMBER(7,2)
DEPTNO                            NUMBER(2)

EMPNO ENAME  JOB       MGR   HIREDATE        SAL      COMM DEPTNO
----- ------ --------- ----- --------- --------- --------- ---------
7839  KING   PRESIDENT       17-NOV-81      5000                  10
7698  BLAKE  MANAGER   7839  01-MAY-81      2850                  30
7782  CLARK  MANAGER   7839  09-JUN-81      2450                  10
7566  JONES  MANAGER   7839  02-APR-81      2975                  20
7654  MARTIN SALESMAN  7698  28-SEP-81      1250      1400        30
7499  ALLEN  SALESMAN  7698  20-FEB-81      1600       300        30
7844  TURNER SALESMAN  7698  08-SEP-81      1500         0        30
7900  JAMES  CLERK     7698  03-DEC-81       950                  30
7521  WARD   SALESMAN  7698  22-FEB-81      1250       500        30
7902  FORD   ANALYST   7566  03-DEC-81      3000                  20
7369  SMITH  CLERK     7902  17-DEC-80       800                  20
7788  SCOTT  ANALYST   7566  09-DEC-82      3000                  20
7876  ADAMS  CLERK     7788  12-JAN-83      1100                  20
7934  MILLER CLERK     7782  23-JAN-82      1300                  10


Oracle Forms
============
The following code will create a record group and call Oracle Reports and/or
Oracle Graphics.  You can implement this code in any trigger.  In this example,
the SELECT statement is built as a fixed string.  You may change the
SELECT statement by changing the WHERE or ORDER BY clause, but the number of
columns, data types, and column names must remain the same.

DECLARE
  groupname VARCHAR2(30)     := 'RG1';
  paramlistname VARCHAR2(30) := 'PLIST1';
  qname VARCHAR2(30)         := 'Q_EMP';
  qry VARCHAR2(2000)         := 'SELECT ename, sal, comm' ||
                                'FROM emp' ||
                                'WHERE sal < 2000';
  rgid                       RECORDGROUP;
  plid                       PARAMLIST;
  n                          NUMBER;

BEGIN
  /*
     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;

Note:
1.  DATA_PARAMETER can only be passed in SYNCHRONOUS mode.  For further
   information, see the RUN_PRODUCT description in the Forms reference manual.

2.  If you are experiencing an error like FRM-40105, FRM-41072, FRM-41076, or
   FRM-41077, check how the SELECT statement was built.


Oracle Reports
==============
As mentioned in the previous section, the name, data type, and number of
columns in the report must be the same as the record group in the form.  After
building the following report, you will be able to run it successfully from
the Designer.  When you use RUN_PRODUCT to run this report, this creates the
report based on the data queried by the record group.

Use the following steps to create the report:

1. Create a Query with the following properties:
     Name             : Q_EMP
     SELECT Statement : SELECT ename, sal, comm FROM emp

2. Create a Tabular Default Layout.

3. Generate and Save the Report as RG.


Oracle Graphics
===============
The same will apply for Oracle Graphics as mentioned in the Oracle Reports
section above.  Use the following steps to create the chart:

1. Create a Query with the following properties:
     Name             : Q_EMP
     SELECT Statement : SELECT ename, sal, comm FROM emp

2. Create a Chart based on the Q_EMP query.

3. Generate and Save the Display as RG.

Thank you lmedfo thank you so much shall do and get back.
Thanks!! :)
Pri

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;

sqlqry needs to be assigned to the qry variable after it is built.
you have qry assigning it prior to the query being built.

i did the change but still the same error.. "argument 2 to builtin CREATE_GROUP_FROM_QUERY cannot be null".
i'm really stuck can someone pls. help me..
___________________________________________________________
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);
       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)----------------------------------------
  qry := 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)
  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;

please help me.

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..  

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..  

sigh...

listen.. your query needs to be valid.. and it isn't...

this i copied from your answer:
I modified  “qry VARCHAR2(2000)         := 'SELECT ename, sal, comm' ||
                              'FROM emp' ||” to  qry VARCHAR2(2000)         := SQLSTR  I hope that can be done..?

if you put your string together, this is your result:
SELECT ename, sal, commFROM emp

you have to put in a space either after comm or before FROM .. that way your query-string will be valid..

hmmm thanks for the help. ..

did it work...? then gimme the points bro..

no pal it still doesnt work. think i had enough with forms!!

swap these two statements.  fix the sql.
qry := 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)
 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');
                                             
Also, look at the from clause.  you have Timei instead of Time.

You initially set your v_select var to 'Select', the you set you8r sqlstr to

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');

It looks to me like you have a syntax problem.  When you pass sqlstr to exec_SQL.parse(connection_id,cursor_name,SQLSTR,exec_sql.v7); you are actually passing "select select...".  Try initializing your v_select to null.

hi priti,
         i looked to the query statement u built ,that is
 SQLSTR :=( 'SELECT'   ||v_select||'FROMSales,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');
what i find that is in the where clause there is no condition to join the time table to any other table.
    u have one form where u just check the check boxes to determine which columns from which tables should be selected,suppose u checked the
boxes for the columns Product_ID, P_Name from the product table.but u see,here u r not giving a value for a where cluse that means u r selecting all the rows from the product table.so suppose u checked all the boxes for all the columns for all the tables,then a select statement will be constructed which will look like the one i mentioned above.here u see there is no where condition for a specific table in the where cluse,so the joining conditions between the tables forming the entire where cluse but there is no joining condition between the time table and any other table.so to make ur select statement correct u have to have relations between all tables.i mean there should be atleaset one primary ,foreign key relationship between time table and any other table so that u can use that relation in the join condition.
       what i feel if u correct ur select statement according to my suggestion ,u may become successfull.

thanks
mehbub

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ/No Refund.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer