Member Avatar for haria_kishore

Hello everyone

I need a solution for my problem. The solution can be a simple PL/SQL block or a trigger

I have a table called T_SBSTTN_VLTG_MSTR with following data

SBSTTN_CD VLTG STTS DT_ENTRY FK_TM_ID
10111111 11 Existing 2/9/2009 19:32 10144
10111111 33 Existing 2/9/2009 19:32 10144
10111111 132 Existing 2/9/2009 19:32 10144
10111112 132 Existing 2/9/2009 19:32 10144
10111112 65 Existing 2/9/2009 19:32 10144

My requirement is that I need to insert the above data (based on SBSTTN_CD) into a new table called T_SBSTTN_RTNG_MSTR

The table would be almost similar but except that VLTG column will be replaced by a column RTNG. The values of RTNG should be populated based on the below methodology

The rating should be 'Higher Value' / 'Next higher value'. Keeping the above table in mind the output would be

SBSTTN_CD RTNG STTS DT_ENTRY FK_TM_ID
10111111 132 / 33 Existing 2/9/2009 19:32 10144
10111111 33 / 11 Existing 2/9/2009 19:32 10144
10111112 132 / 65 Existing 2/9/2009 19:32 10144

  • 2 Contributors
  • 3 Replies
  • 93 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

Following 's request and 's prompt for the SQL, a compact set-based INSERT using Oracle analytic functions (available in 10g) will produce the desired "Higher / Next-higher" pairs. The approach takes each voltage ordered DESC per substation, grabs the next lower value with LEAD, and inserts only rows that have a next-lower partner.

INSERT INTO T_SBSTTN_RTNG_MSTR (SBSTTN_CD, RTNG, STTS, DT_ENTRY, FK_TM_ID)
SELECT SBSTTN_CD,
       TO_CHAR(VLTG) || ' / ' || TO_CHAR(next_vltg) AS RTNG,
       STTS,
       DT_ENTRY,
       FK_TM_ID
FROM (
  SELECT SBSTTN_CD,
         VLTG,
         LEAD(VLTG) OVER (PARTITION BY SBSTTN_CD ORDER BY VLTG DESC) AS next_vltg,
         STTS, DT_ENTRY, FK_TM_ID
  FROM T_SBSTTN_VLTG_MSTR
)
WHERE next_vltg IS NOT NULL;

Notes and alternatives:

  • LEAD(... ORDER BY VLTG DESC) returns the immediate lower voltage for each substation; rows with no lower neighbor are excluded by WHERE next_vltg IS NOT NULL.
  • If LEAD is not available or a plain SQL alternative is preferred, use a self-join that picks the MAX lower value:
INSERT INTO T_SBSTTN_RTNG_MSTR (SBSTTN_CD, RTNG, STTS, DT_ENTRY, FK_TM_ID)
SELECT a.SBSTTN_CD,
       TO_CHAR(a.VLTG) || ' / ' || TO_CHAR(MAX(b.VLTG)) AS RTNG,
       a.STTS, a.DT_ENTRY, a.FK_TM_ID
FROM T_SBSTTN_VLTG_MSTR a
JOIN T_SBSTTN_VLTG_MSTR b
  ON a.SBSTTN_CD = b.SBSTTN_CD
 AND b.VLTG < a.VLTG
GROUP BY a.SBSTTN_CD, a.VLTG, a.STTS, a.DT_ENTRY, a.FK_TM_ID;

Cautions:

  • If multiple rows share the same voltage for a substation, decide whether to deduplicate first or to pick STTS/DT_ENTRY/FK_TM_ID from the higher row (the samples above take them from the higher row).
  • For large tables add an index on (SBSTTN_CD, VLTG) and test the SELECT portion with EXPLAIN PLAN before inserting; run inside a transaction and verify results before commit.

Recommended Answers

All 3 Replies

Member Avatar for debasisdas

what is the query that you are working on ?

Member Avatar for haria_kishore

We are using PL / SQL as the query language and Oracle 10g as database

Member Avatar for debasisdas

i know that .

what is the sql query you are using

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.