Link to home
Create AccountLog in
Avatar of buffygal
buffygalFlag for United States of America

asked on

How do I use an APEX Application Express form to display results from a SELECT query???

The user will enter values in our APEX web form. Then we need to run a SQL statement with what the user put in.  I have the SQL code but don't know how to use it.  

********Please look at the file below for:************************
                                            (1) The web form
                                            (2) The SQL code  

Need to know:
(1)  Where to put this code in the APEX web form?
(2)  How to make the code process what the user put in the form?
 
------------------------------------------------------------------------------------------------------------
Our form looks like this:

Start_Date ____________

Per_Period ____________

Period ____________

[Submit Button]

---------------------------------------------------------------------------------------------------------------
The user will enter these 3 values in the form.
This is an example of an user providing the values:

Start_Date     03/14/08_______

Per_Period    $200.00________

Period              4____________

[Submit Button]

________________________________________________________________

Then they will click the Submit Button.

-----------------------------------------------------------------------------------------------------------------
The SQL command would return this output based on the users selections:

START_DATE                      PER_PERIOD        PERIOD
------------------------------ ---------------------- -----------------------------
14-MAR-2008  00:00          200                          Week 1 of 4
21-MAR-2008  00:00          200                          Week 2 of 4
28-MAR-2008  00:00          200                          Week 3 of 4
04-APR-2008  00:00           200                          Week 4 of 4
Total 800

-----------------------------------------------------------------------------------------------------------------
Thanks very much for your help!!!!


Please-look-at---The-Web-Form---.doc
Avatar of Sean Stuber
Sean Stuber

How does the query in your document relate to the inputs?

You would add a report region to your form with your query.
And reference (I think) the following bind variables in your where clause.

  :p2_start_date_pick_date_mm_dd_yyyy,
  :p2_per_period_text,
  :p2_periods_text

maybe as a where clause on t1, maybe as an adjustment on your criteria with clause.
I'm not sure.  why does the with have a hard coded value?  Is that supposed to be the start_date selected by the user?

with criteria as (select to_date('03/14/08', 'mm/dd/rr')  as start_date,
                         4 as periods,
                         'Week' as period,
                         200 per_period from dual),
periods as (select 'Week' period, 7 days, 0 months from dual
  union all select 'BiWeek', 14, 0 from dual
  union all select 'Month', 0, 1 from dual
  union all select 'ByMonth', 0, 2 from dual
  union all select 'Quarter', 0, 3 from dual
  union all select 'Year', 0 , 12 from dual
),
t1 as (
select add_months(start_date,months*(level-1))+days*(level-1) start_date,
       per_period,
       c.period||' '||level||' of '||c.periods period
  from criteria c join periods p on c.period = p.period
 connect by level <= periods)
select case grouping(start_date)
            when 1 then 'Total'
            else to_char(start_date)
       end start_date,
       sum(per_period) per_period,
       period
from t1
group by rollup ((start_date, period))

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of buffygal

ASKER

Thank you very very much!!!!

U ROCK!!!!!!!!!!!!