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
Hi everyone I have a read only adf table that uses banding. What I want to do is change the text align property of one of it's columns but the task seems not at all trivial. I tried entering a value for the text-align property in the inline style of [More]
   We are meant to be expecting an engineer on the 19th September to set up our BT infinity. On the tracking it is saying we do not need an engineer anymore because they can connect at the exchange. Somehow I don't think this is right. I contacted BT [More]
Hi all, I have nikoia c6-01 and I try to update the phone software to the Belle, but I got an error that connection to the phone lost!, I tried many ways to solve this problem but none of them work! please help if you can...Hi kolahdoozi, Thanks for [More]
Hi,           My JSPs contain a large use of JSTL and other custom Tags.           The problem is that the output contains too many new lines and spaces.           I found out that APACHE Tomcat has a servlet parameter (trimSpaces=true) that solved t [More]
We have upgraded Oracle EBS from 11.5.10.2 to R12.1.3, and we are in the process of configuring the Discoverer 10.1.2 from 11.5.10.2 Tier to R12.1.3 Apps Tier. We have discoverer 10.1.2 configured for 11i, After R12 upgrade we copied the discoverer h [More]
I have an iLife 11 installation DVD bought in the U.S. Can I select the language in which iLife 11 should be installed in on my Mac?It will use the Language that your OS is set to.Read other 2 answers [More]
how to list processes, start processes, and kill processes in a remote system from a server. and how to get configuration information of a remote system. Return information regarding: logical memory configuration, physical and logical disk and partit [More]
All my connections are missing and I cannot create or save new ones. The messagse from the logging page are: oracle.jdevimpl.db.adapter.DefaultContextWrapper - failed to create naming Context for db connections at url: file:/C:/Documents and Settings [More]
Hello Dude, Please give some good book details for Struts , hibernate, Spring . Thank a lot in advance,corlettk wrote: georgemc wrote: I don't know of any e-books on those subjects. Maybe you'll just have to invest actual money in your career, like t [More]
I have two proximity sensors picking up the spoke of a pully wired into my SCXI-1126 module.  The signal jumps around a lot while both are working.  When only one is sensing and the other is not the signal works good without any problems.  It seems l [More]