Home » SQL & PL/SQL » SQL & PL/SQL » Slab Level query (merged) (Oracle 11)
Slab Level query (merged) [message #684836] Mon, 06 September 2021 06:51 Go to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

Hello experts

i have query requirement and I have data as like in image attached. please review the data, to elaborate the data i have a column name scale with minimum scale quantity but don't have maximum quantity column in data. Now I want to drive maximum quantity column by query.

suppose my scheme is work as if I have select row1 then it will be work on 1000 till 1999 (where 1999 isn't present in data) while next minimum slab is present which is 2000

so we have slab number where it start but don't have value of to stop .

my question is do we have any facility in query, like a analytical function or any idea where i drive my min-max value on the bases if i only have min value.

any idea/help would be grateful



regards

Anwer
  • Attachment: Untitled1.png
    (Size: 10.66KB, Downloaded 934 times)
Slab Level query [message #684837 is a reply to message #684836] Mon, 06 September 2021 06:52 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

Hello experts

i have query requirement and I have data as like in image attached. please review the data, to elaborate the data i have a column name scale with minimum scale quantity but don't have maximum quantity column in data. Now I want to drive maximum quantity column by query.

suppose my scheme is work as if I have select row1 then it will be work on 1000 till 1999 (where 1999 isn't present in data) while next minimum slab is present which is 2000

so we have slab number where it start but don't have value of to stop .

my question is do we have any facility in query, like a analytical function or any idea where i drive my min-max value on the bases if i only have min value.

any idea/help would be grateful



regards

Anwer
Re: Slab Level query (merged) [message #684838 is a reply to message #684836] Mon, 06 September 2021 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Make sure that lines of code do not exceed 100 characters when you format.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Re: Slab Level query [message #684839 is a reply to message #684837] Mon, 06 September 2021 14:02 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
And just to make sure you understand exactly what Michel was saying, those CREATE and INSERT statements need to be as text that can be copied, not any kind of image. And it needs to be in your post, not at some link destination.
Re: Slab Level query [message #684845 is a reply to message #684839] Mon, 06 September 2021 23:47 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

ok experts , i understand and now i come with table structure, record sets and data to understand my problem


create table scott.tst_slab (
schem_id number,
line_no number,
scale number,
disc number
);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1000, 1, 1000, 100);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1000, 2, 2000, 200);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1000, 3, 3000, 300);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1000, 4, 4000, 400);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1001, 1, 100, 10);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1001, 2, 1500, 100);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1001, 3, 5000, 700);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1002, 1, 10000, 100);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1002, 2, 17000, 2000);
 insert into scott.tst_slab (SCHEM_ID, LINE_NO, SCALE, disc) values ( 1003, 3, 50000, 9000);
 commit;
and the data example is

SCHME_ID	LINE_NO		SCALE		DISC		FROM		TO
------------------------------------------------------------------------------------------------
1001		1		1000		200		1000		1999
1001		2		2000		400		2000		2999
1001		3		3000		600		3000		3999
1001		4		4000		1000		4000		9999999
										
1002		1		100		200		100		499
1002		2		500		400		500		1499
1002		3		1500		600		1500		6999
1002		4		7000		1000		7000		9999999

now my requirement is to drive max number column TO. while FROM and TO are the capping values on SCALE. so you can say when the scale is greater then 1000 and less then 1999 we given them a discount of 200, if the value of scale >= 2000 and lesser then 2999 then we give then a discount of 400 and so on .

i hope i elaborate the requirement , please help and suggest

i m using 11 database with 11.2.0.3


regards




[Updated on: Mon, 06 September 2021 23:51]

Report message to a moderator

Re: Slab Level query [message #684846 is a reply to message #684845] Tue, 07 September 2021 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Assuming that FROM is SCALE and TO is computed as the SCALE-1 of the next LINE_NO in the same SCHEM_ID:
SQL> break on SCHEM_ID skip 1 dup
SQL> select * from tst_slab order by SCHEM_ID, LINE_NO;
  SCHEM_ID    LINE_NO      SCALE       DISC
---------- ---------- ---------- ----------
      1000          1       1000        100
      1000          2       2000        200
      1000          3       3000        300
      1000          4       4000        400

      1001          1        100         10
      1001          2       1500        100
      1001          3       5000        700

      1002          1      10000        100
      1002          2      17000       2000

      1003          3      50000       9000


10 rows selected.

SQL> select SCHEM_ID, LINE_NO, SCALE, disc, SCALE "FROM",
  2         lead(SCALE,1,10000000) over (partition by SCHEM_ID order by LINE_NO) - 1 "TO"
  3  from tst_slab
  4  order by SCHEM_ID, LINE_NO
  5  /
  SCHEM_ID    LINE_NO      SCALE       DISC       FROM         TO
---------- ---------- ---------- ---------- ---------- ----------
      1000          1       1000        100       1000       1999
      1000          2       2000        200       2000       2999
      1000          3       3000        300       3000       3999
      1000          4       4000        400       4000    9999999

      1001          1        100         10        100       1499
      1001          2       1500        100       1500       4999
      1001          3       5000        700       5000    9999999

      1002          1      10000        100      10000      16999
      1002          2      17000       2000      17000    9999999

      1003          3      50000       9000      50000    9999999


10 rows selected.
Re: Slab Level query [message #684847 is a reply to message #684846] Tue, 07 September 2021 03:00 Go to previous message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

yes this solution works

thank you Michel
Previous Topic: Function Finding multiple occurrences of a string
Next Topic: member and collection type
Goto Forum:
  


Current Time: Fri Mar 29 09:09:42 CDT 2024