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
Sadly I spent hours on this issue at a client yesterday with no resolution. Not using UFR but just the Canon PPD I can print fine from all stations (both 10.5 and 10.6). The client needs to be sending department ID's along with the print jobs for acc [More]
I can't remove icons from the dock, they don't move at all when I try to drag and drop them off the dock any help out there?Restart and see if that solves your issue.Read other 2 answers [More]
Hi Everyone! I am having a problem opening my daughters 1st year DVD. Last summer, our iMac died, and we replaced the hard drive. We were able to salvage all the files, and put them onto an external. Now, I am trying to open one of those salvaged fil [More]
Hi, I have done all the config and customisation required for the Payment Card Processing in CRM 4.0. I was wondering if I have to make some changes on the R/3 4.6 c side? Right now the authorisation is happening in CRM. Once the document goes to R/3 [More]
After spending some time working with my WRT54G and my Westel 2200 dsl modem, I got it all working. I have my Westel set to PPPoE, and my switch set to Auto Config - DHCP. All the DHCP is handled by my modem, and is disabled on my router. Due to all [More]
???? Can I not download and install it - even without a remote (I have a keyspan remote, too bad that couldn't interface with it). Cool feature, but I'm usually farther away from my 30" display & Mac Pro than my laptop, so it's kind of wierd it's [More]
Hi guys, how does this FM works? C14Z_WAIT_ON_COMMIT thanks  a lot!Hi, This is for explicit Commit. Refer This: Re: How to solve this problem - Urgent Regards, Shiva KumarRead other 2 answers [More]
While I was installing iTunes a message poped up. Error writing to file: C:\Program Files (x86)\Common Files\Apple\Apple Application Support\WebKit.resources\inspector\Images\applicationCache.png. Verify that you hace access to that directory. (i can [More]
How to set up e mail account on 5C? be specific on what to place wherebe specific on what to place where Is that a request or instruction ? http://manuals.info.apple.com/MANUALS/1000/MA1565/en_US/iphone_user_guide.pdf User guide and http://www.apple. [More]
Hi guys... I have a (what I think is) a little problem but after being the whole night awake I can´t think anymore... I have a button which sends you to frame 10 and in frame 10 I suppose to intialize the class I imported: import Payment; getpremium. [More]