Home » SQL & PL/SQL » SQL & PL/SQL » Complex select query with criteria (Oracle 10g)
Complex select query with criteria [message #580342] Sat, 23 March 2013 01:41 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

i have a complex requirement to be resolved, i have one table of quantities from where i want to show or select the data based on 2 criterias.
1) if the ps_qty is greater than 1000 then there should be two lines , like the qty should not be displayed greater than 1000, instead it should be lesser than 1000.
2)THe weight should not be more than 50,000, it should be less than 50000 if its more it should be displayed as 2 lines.


THe following is the test case.

CREATE TABLE OW_STAG_SHIP (PS_CODE VARCHAR2(12),PS_DESC VARCHAR2(30),PS_QTY NUMBER,PS_WT NUMBER);

INSERT INTO OW_STAG_SHIP VALUES ('A','AAA',400,30000); -- this will be displayed as its because qty and wt are ok

INSERT INTO OW_STAG_SHIP VALUES ('B','BBB',1100,4000); --Need to be displaed in two lines as qty is more than 1000

INSERT INTO OW_STAG_SHIP VALUES ('C','CCC',2500,6000); --Need to be displayed in three lines as qty is more

INSERT INTO OW_STAG_SHIP VALUES ('D','DDD',600,60000); --Need to be displaed in two lines as wt is more.

select * from ow_stag_ship

PS_CODE	PS_DESC	PS_QTY	PS_WT
A	AAA	400	30000
B	BBB	1100	4000
C	CCC	2500	6000
D	DDD	600	60000


Output what i want is as below


PS_CODE	PS_DESC	PS_QTY	PS_WT
A	AAA	 400	30000
B	BBB	1000	2000
B	BBB	 100	2000
C	CCC	1000	2000
C	CCC	1000	2000
C	CCC	 500	2000
D	DDD	 500   50000
D	DDD	 100   10000

[Updated on: Sat, 23 March 2013 01:51] by Moderator

Report message to a moderator

Re: Complex select query with criteria [message #580344 is a reply to message #580342] Sat, 23 March 2013 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why
D	DDD	 500   50000
D	DDD	 100   10000

and not
D	DDD	 300   50000
D	DDD	 300   10000

or
D	DDD	 600   50000
D	DDD	   0   10000

Regards
Michel
Re: Complex select query with criteria [message #580346 is a reply to message #580342] Sat, 23 March 2013 03:19 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
arif_md2009 wrote on Sat, 23 March 2013 07:41
. . .1) if the ps_qty is greater than 1000 then there should be two lines , like the qty should not be displayed greater than 1000, instead it should be lesser than 1000 . . .


Yet, according to the required output sample you provided, it seems that this criterion is not satisfied.
Quote:

PS_CODE PS_DESC PS_QTY PS_WT
A AAA 400 30000
B BBB 1000 2000
B BBB 100 2000
C CCC 1000 2000
C CCC 1000 2000
C CCC 500 2000

D DDD 500 50000
D DDD 100 10000


For PS_CODE = C you jave PS_QTY = 2500 and you have three lines not two lines (which is normal 2500 cannot be divided by 2 if we assume that each quotient is less than 1000)

As a result, you have to give a more detailed and clearer specification about how to split rows.


Regards,
Dariyoosh
Re: Complex select query with criteria [message #580347 is a reply to message #580344] Sat, 23 March 2013 03:24 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks Michel for the prompt response,actually its my mistake of not defining the problem clearly , there is one table ow_stag_ship which has number of records with one thing in common ps_desc what i gave in the problem initially is the summation of all this qty by ps_desc , what exactly i want is i will take the summation of this qty's and wt and apply those criteria on summary and insert it back into another table with serial numbers or batch_no alloted to each one in detail, that is we take this table , summ the qty's and wt apply the crieria and insert back it on different table ow_batch_pos.

--First the initial records

drop table ow_stag_ship

create table ow_stag_ship (BTCH_PS_NO NUMBER,PS_CODE VARCHAR2(12),PS_DESC VARCHAR2(30),PS_QTY NUMBER,PS_WT NUMBER);

INSERT iNTO OW_STAG_SHIP VALUES (0,'A','AAA',400,30000);

INSERT INTO OW_STAG_SHIP VALUES (0,'B','BBB',600,3000);

INSERT INTO OW_STAG_SHIP VALUES (0,'BX','BBB',500,500);

INSERT INTO OW_STAG_SHIP VALUES (0,'C','CCC',1000,2000);

INSERT INTO OW_STAG_SHIP VALUES (0,'CX','CCC',1000,2000);

INSERT INTO OW_STAG_SHIP VALUES (0,'CY','CCC',500,500);

INSERT INTO OW_STAG_SHIP VALUES (0,'D','DDD',500,50000);

INSERT INTO OW_STAG_SHIP VALUES (0,'DX','DDD',100,10000);

SELECT * FROM OW_STAG_SHIP;

BTCH_PS_NO	PS_CODE	PS_DESC	PS_QTY	PS_WT
0	          A	  AAA	  400	30000
0	          B	  BBB	  600	 3000
0	          BX	  BBB	  500	  500
0	           C	  CCC	 1000	 2000
0	          CX	  CCC	 1000	 2000
0	          CY	  CCC	  500	  500
0	          D	  DDD	  500	50000
0	          DX	  DDD	  100	10000



--Now i am taking summary to apply criterias like qty should not be more than 1000 and weight should not be more than 50,000,Its like identifying how splits it can have

SELECT PS_DESC,SUM(PS_QTY),SUM(PS_WT) FROM OW_STAG_SHIP
GROUP BY PS_DESC;


PS_DESC	SUM(PS_QTY)	SUM(PS_WT)
BBB	   1100	          3500  -- needs two splits
AAA	    400	         30000  -- no splits
CCC	   2500	          4500  -- three splits required since 2500 will be 1000,1000 and 500
DDD	    600          60000  --can have two splits since 60000 can be 50,000 and 10,000 

--now based on the logic i am assigning the batch_nos, it basically done manually by inserting IT into new table ow_batch_pos with batch numbers as serial numbers .

CREATE TABLE  OW_BATCH_POS
(
  BTCH_NO  NUMBER,
  BTCH_PS_CODE  VARCHAR2(12),
  BTCH_PS_DESC  VARCHAR2(20),
  BTCH_PS_QTY  NUMBER(12),
  BTCH_PS_WT    NUMBER
);

--the above tabeE will have the following data

BTCH_PS_NO	BTCH_PS_CODE	BTCH_PS_DESC	PS_QTY	PS_WT
1	          A	          AAA	          400	30000
2	          B	          BBB	          600	 3000
2	          BX	          BBB	          500	  500
3	           C	          CCC	          1000	 2000
4	          CX	          CCC	          1000	 2000
5	          CY	          CCC	           500	  500
6	          D	          DDD	           500	50000
7	          DX	          DDD	           100	10000

[Updated on: Sat, 23 March 2013 04:06] by Moderator

Report message to a moderator

Re: Complex select query with criteria [message #580349 is a reply to message #580347] Sat, 23 March 2013 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now I understand nothing.

Quote:
-the above tabeE will have the following data


You already have it: it is in the ow_stag_ship table.

Starting from the first post, answer my previous question.

Regards
Michel
Re: Complex select query with criteria [message #580350 is a reply to message #580349] Sat, 23 March 2013 04:17 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

michel kindly ignore my first post as i said i made a mistake in posting the first thread, to keep things much simpler, i recreated the test scenarios, with data in second post,there are 8 inserts in ow_stag_ship table and based on this data i am applying two criteria's , one is qty should not be more than 1000 and wt should not be more than 50000,and i am splitting them manually by taking their summary and at present working manually to assign batch number or serial numbers and then inserting into new table ow_batch_pos with serial number or batch_no based on this criterias.
a)Read the data from ow_stag_ship
b)identify the splits based on two criterias qty < 1000 and wt < 50,000
c)Assign the batch_no or serial number based on two criterias
d)Insert the data with batch or serial numbers into ow_batch_pos.

Thanks and Regards

Arif
Re: Complex select query with criteria [message #580351 is a reply to message #580347] Sat, 23 March 2013 04:22 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
arif_md2009 wrote on Sat, 23 March 2013 09:24

. . .

PS_DESC SUM(PS_QTY) SUM(PS_WT)
BBB 1100 3500 -- needs two splits
AAA 400 30000 -- no splits
CCC 2500 4500 -- three splits required since 2500 will be 1000,1000 and 500
DDD 600 60000 --can have two splits since 60000 can be 50,000 and 10,000

. . .



Based on the following sample for quantity using hierarchical query, you should be able to write a query for your tables. Here for quantity, as you specified, no quantity should be greater than 1000.

WITH tmptab AS
(
    SELECT 'code-01' AS code, 2000 AS quantity FROM DUAL UNION ALL
    SELECT 'code-02' AS code, 1000 AS quantity FROM DUAL UNION ALL
    SELECT 'code-03' AS code, 900  AS quantity FROM DUAL UNION ALL
    SELECT 'code-04' AS code, 3500 AS quantity FROM DUAL UNION ALL
    SELECT 'code-05' AS code, 5000 AS quantity FROM DUAL UNION ALL
    SELECT 'code-06' AS code, 4000 AS quantity FROM DUAL UNION ALL
    SELECT 'code-07' AS code, 300  AS quantity FROM DUAL UNION ALL
    SELECT 'code-08' AS code, 1200 AS quantity FROM DUAL
)
SELECT  code, 
        quantity,
        CASE
            WHEN (LEVEL * 1000) - quantity <= 0 THEN
                1000
            ELSE
                MOD(quantity, 1000)
        END AS splittedResult
FROM tmptab
CONNECT BY  PRIOR SYS_GUID() IS NOT NULL AND
            PRIOR code = code AND
            LEVEL <= CASE 
                        WHEN MOD(quantity, 1000) = 0 THEN
                            (quantity / 1000)
                        ELSE
                            (quantity / 1000) + 1
                        END


Which splits the rows in the following way (the thrird column SPLITTEDRESULT is what you're looking for)

CODE	  QUANTITY SPLITTEDRESULT
------- ---------- --------------
code-01       2000	     1000
code-01       2000	     1000
code-02       1000	     1000
code-03        900	      900
code-04       3500	     1000
code-04       3500	     1000
code-04       3500	     1000
code-04       3500	      500
code-05       5000	     1000
code-05       5000	     1000
code-05       5000	     1000
code-05       5000	     1000
code-05       5000	     1000
code-06       4000	     1000
code-06       4000	     1000
code-06       4000	     1000
code-06       4000	     1000
code-07        300	      300
code-08       1200	     1000
code-08       1200	      200

20 rows selected.

SQL> 




Regards,
Dariyoosh
Re: Complex select query with criteria [message #580352 is a reply to message #580351] Sat, 23 March 2013 04:59 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks dariyoosh , first thing i need to assign the batch numbers automatically on number of records along with splits and i need to consider the weight also and then insert into ow_batch_pos, can you please demonstrate on my second test case .
Re: Complex select query with criteria [message #580353 is a reply to message #580349] Sat, 23 March 2013 05:12 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

i am reposting it again, i have two table one from which data should be read ow_stag_ship and the other ow_batch_pos where the data to be inserted along with batch numbers based on criterias that qty should be less than 1000 and weight ps_wt less than 50,000 per ps_desc.Meaning I need to give the batch_no or serial number as per condition one ps_desc can have ps_qty < 1000 and ps_wt < 50000.


drop table ow_stag_ship

create table ow_stag_ship (BTCH_PS_NO NUMBER,PS_CODE VARCHAR2(12),PS_DESC VARCHAR2(30),PS_QTY NUMBER,PS_WT NUMBER);
INSERT iNTO OW_STAG_SHIP VALUES (0,'A','AAA',400,30000);
INSERT INTO OW_STAG_SHIP VALUES (0,'B','BBB',600,3000);
INSERT INTO OW_STAG_SHIP VALUES (0,'BX','BBB',500,500);
INSERT INTO OW_STAG_SHIP VALUES (0,'C','CCC',1000,2000);
INSERT INTO OW_STAG_SHIP VALUES (0,'CX','CCC',1000,2000);
INSERT INTO OW_STAG_SHIP VALUES (0,'CY','CCC',500,500);
INSERT INTO OW_STAG_SHIP VALUES (0,'D','DDD',500,50000);
INSERT INTO OW_STAG_SHIP VALUES (0,'DX','DDD',100,10000);

SELECT * FROM OW_STAG_SHIP;

BTCH_PS_NO	PS_CODE	PS_DESC	PS_QTY	PS_WT
0	          A	  AAA	  400	30000
0	          B	  BBB	  600	 3000
0	          BX	  BBB	  500	  500
0	           C	  CCC	 1000	 2000
0	          CX	  CCC	 1000	 2000
0	          CY	  CCC	  500	  500
0	          D	  DDD	  500	50000
0	          DX	  DDD	  100	10000

--With criterias the records will be inserted into ow_batch_pos wth proper batching done or btch_ps_no allocated

BTCH_PS_NO	btch_PS_CODE	PS_DESC	PS_QTY	PS_WT
1	          A	          AAA	  400	30000
2	          B	          BBB	  600	 3000
3	          BX	          BBB	  500	  500
4	           C	          CCC	 1000	 2000
5	          CX	          CCC	 1000	 2000
6	          CY	          CCC	  500	  500
7	          D	          DDD	  500	50000
8	          DX	          DDD	  100	10000





[Updated on: Sat, 23 March 2013 05:12]

Report message to a moderator

Re: Complex select query with criteria [message #580354 is a reply to message #580353] Sat, 23 March 2013 05:37 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
arif_md2009 wrote on Sat, 23 March 2013 11:12
. . . Meaning I need to give the batch_no or serial number as per condition one ps_desc can have ps_qty < 1000 and ps_wt < 50000 . . .


If the number assignment is based on the weight and quantity criteria to be satisfied simultaneously, I think there is a problem with your problem concept

Consider the followings:
BTCH_PS_NO	PS_CODE	PS_DESC	PS_QTY	PS_WT
0	           C	  CCC	 2500	 2000

This, according to your rule should split in the following way
BTCH_PS_NO	PS_CODE	PS_DESC	PS_QTY	PS_WT
0	           C	  CCC	 1000	 2000
0	           C	  CCC	 1000	 2000
0	           C	  CCC	  500	 2000


But what do you do if you have, forr example something this
BTCH_PS_NO	PS_CODE	PS_DESC	PS_QTY	PS_WT
0	           C	  CCC	 5000	 80000


You have to split into five rows for quantity which is > 1000 but you have to split only into two rows for weight which is > 50000.

So, here you have a problem.


Regards,
Dariyoosh
Re: Complex select query with criteria [message #580356 is a reply to message #580354] Sat, 23 March 2013 05:47 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks dariyoosh , the case what you presented is not possible as there will not be a condition like that, where both the conditions will come.

Quote:

But what do you do if you have, forr example something this

BTCH_PS_NO PS_CODE PS_DESC PS_QTY PS_WT
0 C CCC 5000 80000


You have to split into five rows for quantity which is > 1000 but you have to split only into two rows for weight which is > 50000.




Chances of this cases to come are impossible but to handle this issue , we will go with "OR" condition to tackle this case , and if there are 5 rows then weight should be splitted evenly among 5 rows.
Re: Complex select query with criteria [message #580357 is a reply to message #580356] Sat, 23 March 2013 05:55 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
arif_md2009 wrote on Sat, 23 March 2013 11:47
. . . Chances of this cases to come are impossible but to handle this issue , we will go with "OR" condition to tackle this case , and if there are 5 rows then weight should be splitted evenly among 5 rows . . .


Huh ?? So based on the above underlined rule what will be the output for the following?

BTCH_PS_NO   PS_CODE   PS_DESC   PS_QTY    PS_WT
0              C         CCC      5000     80000



Regards,
Dariyoosh
Re: Complex select query with criteria [message #580360 is a reply to message #580357] Sat, 23 March 2013 06:25 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Quote:

Huh ?? So based on the above underlined rule what will be the output for the following?

BTCH_PS_NO PS_CODE PS_DESC PS_QTY PS_WT
0 C CCC 5000 80000


Very nice point and only way to resolve this issue is by setting priorities , for example the above case priority goes for 5000 or no of more batches , the output will be

BTCH_PS_NO PS_CODE PS_DESC PS_QTY PS_WT
0 C CCC 1000 20000
0 C CCC 1000 20000
0 C CCC 1000 20000
0 C CCC 1000 10000
0 C CCC 1000 10000

lets see another example where wt is more than qty

BTCH_PS_NO PS_CODE PS_DESC PS_QTY PS_WT
0 C CCC 3000 80000

BTCH_PS_NO PS_CODE PS_DESC PS_QTY PS_WT
0 C CCC 1000 50000
0 C CCC 1000 20000
0 C CCC 1000 10000

i hope this satisfies.





Re: Complex select query with criteria [message #580361 is a reply to message #580360] Sat, 23 March 2013 06:36 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
The problem is, that you just manually split rows so that they fulfill your requirement. This is not how you're going to solve this problem

Take your time, instead of reposting different scenarios, try to define a precise and clear rule of splitting rows. Whatever the rule is, I think you will need some kind of hierarchical query to generate extra rows for those quantities/weights to be splitted. The sample solution that I provided, gives you an idea (although it was just for quantities). Based on that, and based on your clear/precise rules (which I still believe that they have not yet been defined properly) you should be able to adapt my code sample to your problem requirement.

Again, I stick to what I said before, instead of searching how to split rows, I think there is a modelling and concept problem within what you want to do.

Regards,
Dariyoosh
Re: Complex select query with criteria [message #580362 is a reply to message #580361] Sat, 23 March 2013 06:45 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks very much dariyoosh , yeah you are right actually there should be some rules for obtaining this results which at present are not defined.thanks again for the great help you provided , i will sure use the sample of hierarchical query that you gave me earlier, but some of the concepts are still hard for me to understand like the below ones,i understand the basics of hierarchical queries like what is root,node,level and connect by but where i can get more examples to read on hierarchy queries.Kindly advice.


CONNECT BY  PRIOR SYS_GUID() IS NOT NULL AND
            PRIOR code = code AND
            LEVEL <= CASE 
                        WHEN MOD(quantity, 1000) = 0 THEN
                            (quantity / 1000)
                        ELSE
                            (quantity / 1000) + 1
                        END



Re: Complex select query with criteria [message #580363 is a reply to message #580362] Sat, 23 March 2013 07:07 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
arif_md2009 wrote on Sat, 23 March 2013 12:45
. . . but some of the concepts are still hard for me to understand like the below ones ...

CONNECT BY  PRIOR SYS_GUID() IS NOT NULL AND
            PRIOR code = code AND
            LEVEL <= CASE 
                        WHEN MOD(quantity, 1000) = 0 THEN
                            (quantity / 1000)
                        ELSE
                            (quantity / 1000) + 1
                        END





- PRIOR SYS_GUID() IS NOT NULL will get rid of cycles

- PRIOR code = code
simply means how/based on what code values do you link each level to the precedent level in the hierarchy (each row in the current level has the same code value of its parent)

- The CASE statement is simply a division used to split rows which indicates the number of rows that have to be generated based on the value of the quantity. If the quantity is divisible by 1000 (for example when quantity = 1000, 2000, 8000, 14000, etc.) then you return (quantity / 1000) otherwise (quantity / 1000) + 1.


arif_md2009 wrote on Sat, 23 March 2013 12:45

. . .
,i understand the basics of hierarchical queries like what is root,node,level and connect by but where i can get more examples to read on hierarchy queries . . .


For more information, you can read

- Oracle online doc: Hierarchical Queries in order to have both description and examples.

- SYS_GUID

If you search on orafaq, I'm sure you will find more articles/threads about hierarchical queries


Regards,
Dariyoosh

[Updated on: Sat, 23 March 2013 07:10]

Report message to a moderator

Re: Complex select query with criteria [message #580364 is a reply to message #580363] Sat, 23 March 2013 07:21 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks very much dariyoosh, appreciate your good help.
Previous Topic: Query Error
Next Topic: Get count on group and pct of total count for each group
Goto Forum:
  


Current Time: Wed Aug 20 17:58:14 CDT 2014

Total time taken to generate the page: 0.06196 seconds