Sitemap

Amicuk Programming Answers

Sql query ..need idea to write complex query

2015-10-11   Views:0

Advertisement

Hi there, I have assigned the task to write a sql query to get the output as the below stored proc does. In the proc conditions are given with IF statements. I really dont know how to give all the conditions for the period in a single sql query as I'

Hi there,
I have assigned the task to write a sql query to get the output as the below stored proc does.
In the proc conditions are given with IF statements. I really dont know how to give all the conditions for the period in a single sql query as I'm not much used to sql quries.
Is anyone could help me?
Any suggestions pls . writing complicated query is nightmare. no idea . if possible help me...
create or replace PROCEDURE vpp_station_summary_report (
in_user_id                     IN  VARCHAR2,
in_report_id      IN  NUMBER,
in_time_from                IN      vppstation.avi_status_history.status_eff_date%TYPE,
in_time_to                  IN  vppstation.avi_status_history.status_eff_date%TYPE,
result                               OUT SYS_REFCURSOR)
AS
CURSOR station_loop IS
   SELECT ash.station_id,
          ash.avi_id,
          ash.state_id,
          ash.state_eff_date
   FROM   vppstation.avi_state_history ash
   JOIN   vpproadside.vpp_report_stations
     ON   vpp_report_stations.station_id             = ash.station_id
    AND   vpp_report_stations.vpp_report_seq_number  = in_report_id
   WHERE  ash.state_eff_date BETWEEN in_time_from AND in_time_to
   ORDER BY ash.station_id,
            ash.avi_id,
            ash.state_eff_date,
            ash.ash_id;
-- cursor to find the 'entry state' i.e. the state the AVI was in AT the time of
-- in_time_from
CURSOR entry_state (
          state_station_id vppstation.avi_state_history.station_id%TYPE,
          state_avi_id     vppstation.avi_state_history.avi_id%TYPE,
          state_state_date vppstation.avi_state_history.state_eff_date%TYPE
IS
   SELECT ash.state_id
   FROM   vppstation.avi_state_history ash
   WHERE  ash.station_id = state_station_id
     AND  ash.avi_id = state_avi_id
     AND  ash.state_eff_date < state_state_date
   ORDER BY ash.state_eff_date DESC,
            ash.ash_id DESC;
current_station_id         vppstation.avi_state_history.station_id%TYPE;
current_avi_id             vppstation.avi_state_history.avi_id%TYPE;
current_state_id           vppstation.avi_state_history.state_id%TYPE;
current_state_eff_date     vppstation.avi_state_history.state_eff_date%TYPE;
period_length NUMBER;
next_station_id     vppstation.avi_state_history.station_id%TYPE;
next_avi_id         vppstation.avi_state_history.avi_id%TYPE;
next_state_id       vppstation.avi_state_history.state_id%TYPE;
next_state_eff_date vppstation.avi_state_history.state_eff_date%TYPE;
station_open_total       NUMBER;
station_closed_total     NUMBER;
station_all_report_total NUMBER;
current_station_name vpproadside.vpp_station_summary.station_name%TYPE;
state_open       vppstation.avi_control_state_code.state_id%TYPE;
state_closed     vppstation.avi_control_state_code.state_id%TYPE;
state_all_report vppstation.avi_control_state_code.state_id%TYPE;
BEGIN
SELECT state_id
INTO   state_open
FROM   vppstation.avi_control_state_code
WHERE  state_type = 'E'
AND    state_active_ind = 'A';
SELECT state_id
INTO   state_closed
FROM   vppstation.avi_control_state_code
WHERE  state_type = 'D'
AND    state_active_ind = 'A';
SELECT state_id
INTO   state_all_report
FROM   vppstation.avi_control_state_code
WHERE  state_type = 'S'
AND    state_active_ind = 'A';
current_station_id := -1;
current_avi_id     := -1;
current_state_id   := state_closed;
current_state_eff_date := in_time_from;
station_open_total       := 0.0;
station_closed_total     := 0.0;
station_all_report_total := 0.0;
-- for starters - ensure that there is report data for all requested stations...
INSERT INTO vpproadside.vpp_station_summary
      vpp_report_seq_number,
      station_id,
      station_name,
      ln_number,
      lane_name,
      station_open,
      station_close,
      station_all_report,
      station_total
  SELECT in_report_id,
         vrs.station_id,
         si.station_name,
         l.ln_number,
         l.lane_name,
         0.0,
         0.0,
         0.0,
         0.0
  FROM vpproadside.vpp_report_stations vrs
  LEFT OUTER JOIN  vpproadside.stations_installed si
    ON  si.station_id = vrs.station_id
  LEFT OUTER JOIN vppstation.lane_name l
    ON l.station_id = vrs.station_id
  WHERE vrs.vpp_report_seq_number  = in_report_id;
-- loop over state history and update information for all stations found
OPEN station_loop;
LOOP
  FETCH station_loop
  INTO
        next_station_id,
        next_avi_id,
        next_state_id,
        next_state_eff_date;
  IF station_loop%NOTFOUND THEN
    next_station_id := -1;
    next_avi_id     := -1;
  END IF;
  -- if station/avi has changed take the end of the report period
  IF    (next_station_id <> current_station_id)
     OR (next_avi_id     <> current_avi_id)
  THEN
    period_length := in_time_to - current_state_eff_date;
  ELSE
    -- otherwise the start of the next period marks the end of the current period
    period_length := next_state_eff_date - current_state_eff_date;
  END IF;
  -- if we have a real station id then do some work...
  IF (current_station_id <> -1) THEN
    -- determine which category the period fits to and apply calculation
    IF current_state_id = state_open THEN
      station_open_total := station_open_total + period_length - 1;
    ELSIF current_state_id = state_closed THEN
      station_closed_total := station_closed_total + period_length - 1;
    ELSIF current_state_id = state_all_report THEN
      station_all_report_total := station_all_report_total + period_length - 1;
    ELSE
      RAISE_APPLICATION_ERROR(-20111, 'Error: found unknown state code on avi_state_history - ' || current_state_id );
    END IF;
    -- if the station/avi has changed then commit changes to db
    IF    (next_station_id <> current_station_id)
       OR (next_avi_id     <> current_avi_id)
    THEN
      UPDATE vpproadside.vpp_station_summary
      SET
          station_open       = station_open_total,
          station_close      = station_closed_total,
          station_all_report = station_all_report_total
      WHERE vpp_report_seq_number = in_report_id
      AND   station_id = current_station_id
      AND   ln_number  = current_avi_id;
      -- reset counts
      station_open_total       := 0.0;
      station_closed_total     := 0.0;
      station_all_report_total := 0.0;
    END IF;
  END IF;
  -- if we got past the last record then stop processing
  EXIT WHEN station_loop%NOTFOUND;
  -- if the station/avi is changing, get the state that was 'current' at in_time_from
  IF    (next_station_id <> current_station_id)
     OR (next_avi_id     <> current_avi_id)
  THEN
    current_state_eff_date := in_time_from;
    OPEN entry_state (
           next_station_id,
           next_avi_id,
           in_time_from
    FETCH entry_state
    INTO  current_state_id;
    IF entry_state%NOTFOUND THEN
      current_state_id := state_closed;
    END IF;
    CLOSE entry_state;
    period_length := next_state_eff_date - current_state_eff_date;
    IF current_state_id = state_open THEN
      station_open_total := station_open_total + period_length;
    ELSIF current_state_id = state_closed THEN
      station_closed_total := station_closed_total + period_length;
    ELSIF current_state_id = state_all_report THEN
      station_all_report_total := station_all_report_total + period_length;
    ELSE
        RAISE_APPLICATION_ERROR(-20111, 'Error: found unknown state code on avi_state_history - ' || current_state_id );
    END IF;
  END IF;
  current_state_id       := next_state_id;
  current_state_eff_date := next_state_eff_date;
  current_station_id     := next_station_id;
  current_avi_id         := next_avi_id;
END LOOP;
CLOSE station_loop;
-- update the totals for the percentage calculation
UPDATE vpproadside.vpp_station_summary
SET
       station_total = station_open + station_close+ station_all_report
WHERE   vpp_report_seq_number = in_report_id;
-- 'fix' the totals that are still zero to avoid divide by zero errors...
--       note: all the percentages will still come out as zero since the total
--             was zero
UPDATE vpproadside.vpp_station_summary
SET
       station_total = 1.0
WHERE  vpp_report_seq_number = in_report_id
AND    station_total = 0.0;
OPEN result FOR
SELECT station_name "Site Name",
       lane_name    "Lane Name",
       TO_CHAR((station_open       / station_total) * 100.0, 'FM990.0999') || '%' "Open %",
       TO_CHAR((station_close      / station_total) * 100.0, 'FM990.0999') || '%' "Closed %",
       TO_CHAR((station_all_report / station_total) * 100.0, 'FM990.0999') || '%' "All Report %"
FROM vpproadside.vpp_station_summary
WHERE vpp_report_seq_number = in_report_id
ORDER BY UPPER(station_name),
         UPPER(lane_name);
DELETE FROM vpproadside.vpp_station_summary
WHERE vpp_report_seq_number = in_report_id;
END;Edited by: Indhu Ram on Mar 10, 2010 9:51 AM
Edited by: Indhu Ram on Mar 10, 2010 9:56 AM
Edited by: Indhu Ram on Mar 10, 2010 10:58 AM
Edited by: Indhu Ram on Mar 10, 2010 11:12 AM

The replay answer
Advertisement
Exactly dont know what you are asking for but I can suggest you some tips here
- If you want to check the condition in SQL query then you can use CASE statement into select clause i.e.
SELECT CASE when table1.a=table2.b then table1.c else table2.c END, ... more case..., table columns...
FROM table1, table2
WHERE
<some conditions>
- If you want to achive same functionality (SELECT only, not UPDATE/INSERT/DELETE) then you can convert the part of same procedure into function and can use the same function into your query by passing the parameters.
something like this
SELECT function_name(parameter1, parameter2....) from dual
Hope this will help

Go to See the other 2 answers

Complex SQL Query in BPEL DB Adapter

Category:DefaultRelease time:-0001-11-30Views:130

Hi, Is it possbile to write a complex query in BPEL DB Adapter using "Custom SQL Query"? I would like to write an IF ELSE condition in the DB Adapter similar what is given below.. IF((SELECT COUNT(*) FROM F5898001 WHERE CT58SRCNME = 'CA_TEST' AN[More]

Complex sql query

Category:DefaultRelease time:-0001-11-30Views:130

Hello, My question is: I would like to do more complex sql query ( i need to use GROUP BY, HAVING, ORDER BY). Is possible do it with CMP entity bean, or i have to use BMP entity bean or Session bean? Query return about 20-30 items. Can you recommend[More]

SQLEception using Complex SQL Query with Java Studio Creator2 Build(060120)

Category:DefaultRelease time:-0001-11-30Views:130

I am evaluating Java Studio Creator2 for a WEB base application project that will be making SQL queries to an Oracle Database but I have stumble into a problem using complex SQL queries. I am getting an SQLException "org.apache.jasper.JasperException[More]

Sql query ..need idea to write complex query

Category:DefaultRelease time:2015-10-11Views:130

Hi there, I have assigned the task to write a sql query to get the output as the below stored proc does. In the proc conditions are given with IF statements. I really dont know how to give all the conditions for the period in a single sql query as I'[More]

Obtain rows from a complex sql query

Category:DefaultRelease time:-0001-11-30Views:130

Hi to all, please somebody who can help me, my scenario is as next: One table like next Reservation (table) Boat (string) reservation (date) class (string) room1_cod (int) room1 (int) room2_cod (int) room2 (int) room3_cod (int) room3 (int) In room(x)[More]

How to run a SQL query which is stored in a column using PL/SQL?

Category:DefaultRelease time:2015-10-11Views:130

Hello I have a table A and one of the column values is select * from emp where empno :=xyz; Now I would like to call this SQL query using a cursor in PL/SQL and run this for all the empno's and insert them into a temp table. Can anybody help me in wr[More]

Apex 4.2 Report region sql query with "apex_item.text" renders wrongly

Category:DefaultRelease time:2015-10-11Views:130

In APEX 4.2 page i have created region "Report data". Type="SQl Query". "User Interface"-->"Template"="Reports Region". "Region Source"="select apex_item.text(1, '111') as c1 from dual&qu[More]

Discoverer Report - SQL Query to retrieve list of parameters

Category:DefaultRelease time:2015-10-11Views:130

Hi We have around 100 Discoverer reports and I'm trying to find the list of parameters used in each report. Is there any SQL query that I can use to find the parameters used for each report or should I open the reports one by one to fetch the list of[More]

Not to able add sql query in the item source

Category:DefaultRelease time:2015-10-11Views:130

Hi, I am getting this error when i am trying to add the sql query in the item source ORA-20505: Error in DML: p_rowid=3206911131098131, p_alt_rowid=ID, p_rowid2=, p_alt_rowid2=. ORA-01461: can bind a LONG value only for insert into a LONG column SELE[More]

XSU  and CURSOR expression in the sql query

Category:DefaultRelease time:-0001-11-30Views:130

Platform: oracle 8.X on ibm aix and java client code from windows NT. JDBC DRIVER: JDBC Oracle thin driver version 1.2. when i execute a Sql satement with Cursor expression from the java client code with XSU it returns an XML DOM But if the CURSOR EX[More]

Cartesian join in the SQL query

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have a problem when joining two tables that cannot join directly. As I read (please correct me if I'm wrong), two dims have to join by one fact in order to extract information from them. This way, I created logical source tables with SQL query[More]

Hot
How do I add multiple Apple id's to one account for billing and Icloud use?You don't. You can't combine Apple IDs.Read other 2 answers [More]
Hi Can we use SOUNDEX function in OWB. If yes then how? Regards VibhutiIn Expression operator or in Filter, etc. you can type function name by hand. OlegRead other 2 answers [More]
How do I transfer the whole (not just the new photos, but the whole) of my camera roll on my iPhone 4 (using the latest software) to my (Win7) PC? I've done this before by simply going Computer > Josh's iPhone > Internal Storage > DCIM. Then at w [More]
Greetings! I'm trying to get an HP 4750C wireless printer to work with my older Pismo G3 mac, it's running 10.3.9 and won't be running anything higher. (I've set it up for my wife for internet and mail...) I just purchased this HP printer as our olde [More]
Hi We have posted two FI documents(Vendor invoice) using Network/Activity. one in October and other one in December. When we execute the above report, the document posted during October does not feature in the report though both the documents have be [More]
I'm experimenting with a workflow, so whilst this may seem like an odd thing to want to make work, I have a geniune reason for doing what's described below: I have Canon CR2 RAW files from a 7D, Photoshop CS6 13.01 64bit, with ACR 7.1.0.354, running [More]
I have added a piece of text to a movie I am making. How do I remove it?99 I am strictly an Elements Windows user, but I think that we can meet on common ground between Windows and Mac on this. From your description, it sounds like all the work was d [More]
Am trying to connect my nokia 6300 with my notebook Toshiba P100/P105 Series but it says make sure all the hardware are installed and drivers installed.I have installed Pc suite 6.83 and Toshiba stack which expires after 30days but still its not bein [More]
I purchased an external hard drive not too long ago as my hard drive was reaching capacity. I dumped all of my Iphoto library onto it to free up as much aspace as possible. I upgraded to iLife 06 on my computer shortly thereafter. After freeing up an [More]
Hi, I looked up lpad/rpad in the 10g Complete refernce but can't seem to figure if I can use it in conjunction with dense_rank. When I run "DENSE_RANK () OVER (PARTITION BY 'batch_doc_total' ORDER BY ae.emp_code) AS "batch_skey"," I ge [More]