OraFAQ Forum: SQL & PL/SQL » Slab Level query (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Slab Level query (merged) (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Slab Level query (merged) [message #684836]

Mon, 06 September 2021 06:51

annu-agi
Messages: 237
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 387 times)

Report message to a moderator

Slab Level query [message #684837 is a reply to message #684836]

Mon, 06 September 2021 06:52

annu-agi
Messages: 237
Registered: July 2005
Location: Karachi

Senior Member

Report message to a moderator

Re: Slab Level query (merged) [message #684838 is a reply to message #684836]

Mon, 06 September 2021 07:37

Michel Cadot
Messages: 68418
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.

Report message to a moderator

Re: Slab Level query [message #684839 is a reply to message #684837]

Mon, 06 September 2021 14:02

EdStevens
Messages: 1375
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.

Report message to a moderator

Re: Slab Level query [message #684845 is a reply to message #684839]

Mon, 06 September 2021 23:47

annu-agi
Messages: 237
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

Michel Cadot
Messages: 68418
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.

Report message to a moderator

Re: Slab Level query [message #684847 is a reply to message #684846]

Tue, 07 September 2021 03:00

annu-agi
Messages: 237
Registered: July 2005
Location: Karachi

Senior Member

yes this solution works

thank you Michel

Report message to a moderator

Previous Topic:	Function Finding multiple occurrences of a string
Next Topic:	member and collection type

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 28 14:14:59 CDT 2023