Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jay.

Asked: March 28, 2012 - 12:25 pm UTC

Last updated: February 25, 2013 - 10:36 am UTC

Version: 11G

Viewed 10K+ times! This question is

You Asked

I have the following senario of a an address table where each id has at least one address or more. Some address type have more than one sequence number where the highest sequence number indicates the latest modified address.

Table: MyAddr

addr_id addr_type addr_seqno addr_desc
------------------------------------------------------------
12345 1 0 12345 Main Erorr st
12345 1 1 12345 Main Good st
12345 2 0 23456 No st
12345 2 1 23456 Yes st
10101 1 0 1000 Fun st
22222 1 0 3030 No road st
22222 2 0 3131 My st
------------------------------------------------------------


How to create a sqlplus that extract these records and put them in one row taking into consideration pulling the
highest address sequent number and if the id does not content the type of address, insert NULL in the column.

Example output:

addr_id addr_type1 addr_seqno addr_desc1 addr_type2 addr_seqno addr_desc2
--------------------------------------------------------------------------------------------------------------
12345 1 1 12345 Main Good st 2 1 23456 Yes st
10101 1 0 1000 Fun st NULL NULL NULL
22222 1 0 3030 No road st 2 0 3131 My st
--------------------------------------------------------------------------------------------------------------

---- sample scripts table creation and insert records-----------------


create table myaddr
( addr_id number
addr_type number
addr_seqno number
addr_desc varchar2(20)
)

insert into myaddr values
(12345,1,0,'12345 Main Erorr st');
insert into myaddr values
(12345,1,1,'12345 Main Good st');
insert into myaddr values
(12345,2,0,'23456 No st');
insert into myaddr values
(12345,2,1,'23456 Yes st');
insert into myaddr values
(10101,1,0,'1000 Fun st');
insert into myaddr values
(22222,1,0,'3030 No road st');
insert into myaddr values
(22222,2,1,'3131 My st');

-----------------------------------------------------------------

Thanks
bosou

and Tom said...

please use the CODE button so I can read your output in the future.


ops$tkyte%ORA11GR2> select addr_id,
  2         addr_seqno,
  3         max( decode( rn, 1, addr_type ) ) addr_type1,
  4         max( decode( rn, 1, addr_desc ) ) addr_desc1,
  5         max( decode( rn, 2, addr_type ) ) addr_type2,
  6         max( decode( rn, 2, addr_desc ) ) addr_desc2
  7    from (
  8  select myaddr.*,
  9         max(addr_seqno) over (partition by addr_id) max_seqno,
 10        row_number() over (partition by addr_id order by addr_seqno DESC, addr_type) rn
 11    from myaddr
 12         )
 13   where addr_seqno = max_seqno
 14   group by addr_id, addr_seqno
 15   order by addr_id
 16  /

   ADDR_ID ADDR_SEQNO ADDR_TYPE1 ADDR_DESC1           ADDR_TYPE2 ADDR_DESC2
---------- ---------- ---------- -------------------- ---------- --------------------
     10101          0          1 1000 Fun st
     12345          1          1 12345 Main Good st            2 23456 Yes st
     22222          1          2 3131 My st

ops$tkyte%ORA11GR2>



you could also use pivot if you like...

Rating

  (20 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

rebisco, March 28, 2012 - 9:30 pm UTC

Hello Tom,

I have similar scenario as stated above but, in the 10g_R2 version. I am not too sure if the keywords "over" and "partition" are available/usable for such solution in this version.

Thank you for taking the time in answering on most of the questions here. We are so blessed to have you here.

Thank you,
Rebisco

Tom Kyte
March 29, 2012 - 6:59 am UTC

why didn't you try it?????????????????????????

seriously?


analytics were added last century, over and partition by have been part of our SQL for well over a decade. They are the coolest thing to happen to sql since the keyword select was introduced. You need to know them and know them well - you can do "magic" with them.

Rebisco - Your Doco is the answer

Jim, March 28, 2012 - 10:25 pm UTC

Hello Rebisco,

You could have so easily answered that yourself.

1: Look at your manuals, if you haven't got them download them. Have a look at how many of TOm's answers are look in the manual.

2: You could have so easily searched Tom's site or used google

3: Surely you must have a test ennvironment, it is so easy for you to cut and paste this example is as it is really short only has a create table, insert some data and a select.

I am not trying to appear harsh but if you don't even try the most simple things yourself you won't get anywhere.



Rebisco, March 29, 2012 - 9:14 pm UTC

Hello Jim and Tom,

@Jim: Thank you for dropping by and your very useful tip. Indeed, they are usable even at the version I am using.

Anyways, here is my almost similar situation. I just want to get the sum of the maximum value that rn returns instead of displaying null value to the non-matching results as the original poster mentioned.

Table: MyTbl
CREATE TABLE MYTBL
(
  ID VARCHAR2(5),
  GATE VARCHAR2(50),
  QTY NUMBER,
  TXN NUMBER,
  TIMESTAMP VARCHAR2(18),
  RESULT VARCHAR2(40),
  SZ NUMBER,
  ACCQTY NUMBER,
  DEFQTY NUMBER
);

Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('AA','GATE1',5442,700476212,'20110821 034636','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('AA','GATE1',5439,700485347,'20110822 134809','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('BB','GATE1',1404,700480350,'20110821 172614','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('BB','GATE1',1400,700485351,'20110822 134827','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('CC','GATE1',3068,700504830,'20110825 103828','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('CC','GATE1',3077,700503729,'20110825 062849','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('DD','GATE1',1278,700501729,'20110824 234504','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('DD','GATE1',1274,700506200,'20110825 144451','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('EE','GATE1',1338,700517646,'20110827 103130','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('EE','GATE1',1342,700512819,'20110826 154214','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('FF','GATE1',7037,700559780,'20110907 155804','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('FF','GATE1',7022,700574211,'20110909 203259','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('GG','GATE1',5881,700596916,'20110913 011007','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('GG','GATE1',5881,700597447,'20110913 030829','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('HH','GATE1',1347,700578905,'20110910 121119','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('HH','GATE1',1337,700608182,'20110914 142629','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('II','GATE1',1336,700587739,'20110911 174115','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('II','GATE1',1335,700593431,'20110912 134453','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('JJ','GATE1',4931,700603842,'20110913 235426','REJECTED',315,0,10);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('KK','GATE1',2965,700668761,'20110922 202359','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('LL','GATE1',4233,700680675,'20110924 121652','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('LL','GATE1',4215,700686196,'20110925 050318','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('LL','GATE1',4233,700680792,'20110924 123030','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('MM','GATE1',4325,700674897,'20110923 170810','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('MM','GATE1',4275,700688163,'20110925 121637','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('NN','GATE1',21181,700649156,'20110920 062119','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('NN','GATE1',21172,700650805,'20110920 124553','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('OO','GATE1',733,700700595,'20110927 055915','REJECTED',315,0,1);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('PP','GATE1',20450,700697001,'20110926 174608','ACCEPTED',315,0,0);
Insert into MYTBL (ID,GATE,QTY,TXN,TIMESTAMP,RESULT,SZ,ACCQTY,DEFQTY) values ('PP','GATE1',20503,700693872,'20110926 084540','REJECTED',315,0,1);

I tried:
select GATE, count(distinct ID) nooflots,
 sum ( decode(rn, 1, 1)) rejLots
From (
  select a.*,
  max(TIMESTAMP) over (partition by ID) max_ts,
  row_number() over (partition by ID order by TIMESTAMP desc, TXN) rn
  from MYTBL a
)
where TIMESTAMP = max_ts
group by GATE;


Output from the above query:
GATE    NOOFLOTS  REJLOTS
-----   --------  -------
GATE1 16     16


Expected output:
GATE    NOOFLOTS  REJLOTS
-----   --------  -------
GATE1 16    15

I know the reason why my query got the wrong result, it is because of the ID 'LL' it is counted twice instead of just once. I may sound stupid of just following the original solution that Tom posted but, I just don't know how this will be done.

Thank you,
Rebisco
Tom Kyte
March 30, 2012 - 7:07 am UTC

spell out in detail the logic you wish to apply. put it in the form of a specification.


I don't know why people just post things like:

<bunch of data with no explaination>

<my broken query that doesn't work here>

<output i didn't want here>

<expected output>


and expect us to perfectly reverse engineer all of their rules, assumptions, etc from it.


here is how to get your expected output (using my own set of assumptions)


select 'GATE1' gate, 16 nooflots, 15 rejlots from dual;


it works - but it probably wasn't what you were looking for - so, tell us in detail what your data means and what logic we need to apply to it to get your answer every single time - regardless of the values in the table.

Rebisco, March 31, 2012 - 2:04 am UTC

Hello Tom,

Sorry for not giving you the specifications I needed in detail. Anyway, here it is:

MYTBL - is our staging table which contains the lot information that is coming/passing a gate(ie GATE1). Lot can be ACCEPTED or REJECTED as it passes a gate. They are logged base on the Timestamp it occurred (TIMESTAMP) with its transaction id(TXN).

The one I posted above is the current information we got from this specific gate (GATE1). There are still other gates from our staging table aside from GATE1.

Our specification is to retrieve the information from a specific gate. The information must contain the number of lot/s that passes it and the number of lots that have been rejected base on its Timestamp (latest).

I hope I explained it clearly as this gives me a headache for a couple of days now.

Thank you,
Rebisco
Tom Kyte
March 31, 2012 - 8:17 am UTC

The
information must contain the number of lot/s that passes it and the number of
lots that have been rejected base on its Timestamp (latest).

what is a "lot".

I don't see any attribute "lot" - is "id" your "lot"?


remember, we do not understand any of your data, explain the problem as if you were explaining it to your mom.

More info

Jim, April 02, 2012 - 5:53 pm UTC

Hi Rebisco

I have looked at this couple of times while I waited for some jobs to finish and as Tom said you will need to provide more information.

Especially what is your definition of a "lot"?

You said
>Expected output:

>GATE NOOFLOTS REJLOTS
>----- -------- -------
>GATE1 16 15

I don't know how you are getting a count of 15 for rejected lots.

I inserted your data and there are 16 records with REJECTED and I could not see any duplication.

scott@DEV> l
  1  select id,gate,qty,txn 
     from mytbl where result = 'REJECTED'
  2* order by id,gate,txn


scott@DEV> /

ID    GATE          QTY        TXN
----- ------------- ---------- ----------
AA    GATE1         5442       700476212
BB    GATE1         1404       700480350
CC    GATE1         3077       700503729
DD    GATE1         1278       700501729
EE    GATE1         1342       700512819
FF    GATE1         7037       700559780
GG    GATE1         5881       700596916
HH    GATE1         1347       700578905
II    GATE1         1336       700587739
JJ    GATE1         4931       700603842
KK    GATE1         2965       700668761
LL    GATE1         4233       700680792
MM    GATE1         4325       700674897
NN    GATE1        21181       700649156
OO    GATE1          733       700700595
PP    GATE1        20503       700693872
16 rows selected.


I don't think you even need to use analytics, however post some more information and I will be happy to check this out further for you

rebisco, April 02, 2012 - 9:42 pm UTC 

Hello Jim,

Thank you for looking this post again. I am getting so desperate already on how to come up the desired output. Currently, we are using the client side (PHP) to handle the computations which is not a smart move since it involves multiple looping in order to get the desired output.

@Tom, yes "lot" here is represented by the "id" or what we called here as LotId.

By the way, I may have a correction on getting the correct number of rejected lots, it must be retrieve base on the earliest occurrence (TIMESTAMP) and not latest. Meaning, if one lot (ID) have multiple results (REJECTED or ACCEPTED), actual RESULT will be checked back depending on the TIMESTAMP it occurred.

It can be best described on the id 'LL'. Here, 'LL' should not be considered as REJECTED since its first occurrence base on earliest TIMESTAMP is 'ACCEPTED':
<code>
ID GATE QTY   TXN      TIMESTAMP      RESULT    SZ   ACCQTY  DEFQTY
--  ----    ---   ---------  --------------- -------   ---  ------  ------
LL GATE1   4233  700680675  20110924 121652 ACCEPTED  315 0       0
LL GATE1   4233  700680792  20110924 123030 REJECTED  315 0       1
LL GATE1   4215  700686196  20110925 050318 ACCEPTED  315 0       0


To get the correct number of lots and rejected lots, I tried:
SQL> select count(distinct id) numlots from mytbl;

   NUMLOTS
----------
        16


SQL> select count(min_ts) rejLots
  2  from (
  3  select a.*, min(timestamp) over (partition by id) min_ts
  4  from mytbl a
  5  )
  6  where timestamp = min_ts
  7  and result = 'REJECTED';

   REJLOTS
----------
        15


Maybe, there are still another solutions out there that will merge both number of lots (numlots), rejected lots (rejlots) , total quantity(TotalQTY), total size(TotalSZ), total accepted quantity(TotalACCQTY) and total defects quantity(TotalDEFQTY) into one query.

Thank you,
Rebisco</code>
Tom Kyte
April 03, 2012 - 6:47 am UTC 

ops$tkyte%ORA11GR2> select count(distinct id) c,
  2         count( case when result = 'ACCEPTED' then 1 end) a,
  3         count( case when result = 'REJECTED' then 1 end ) r
  4    from (
  5  select t.*, min(timestamp) over (partition by id) min_ts
  6    from mytbl t
  7         )
  8   where timestamp = min_ts;

         C          A          R
---------- ---------- ----------
        16          1         15

Interesting

jim, April 02, 2012 - 10:23 pm UTC

Hi Rebisco

Thanks for the extra info
The classification of a rejected "Lot" is interesting
in your example are you saying

Although Lot LL with Qty 4233 rejected at 12:30, it is not
counted as rejected because it was accepted earlier, is that correct?

I would have thought you would be most concerned with what happened most recently.

For your rejected lots have I got your requirement right
IF result = rejected AND no earlier accepted record
THEN record row as rejected
END IF

If that is the case what else are you matching on?
The day? QTY ?

eg would any of the following result in Rejected candidates

The accepted "lot" entry was not the same day as rejected
ID    GATE    QTY      TXN         TIMESTAMP  RESULT   SZ   ACCQTY  DEFQTY
--  ----    ---   ---------  --------------- -------   ---  ------  ------
LL    GATE1   4233  700680675     20110922 120000 ACCEPTED  315    0       0
LL    GATE1   4233  700680792     20110924 123030 REJECTED  315    0       1
LL    GATE1   4215  700686196     20110925 050318 ACCEPTED  315    0       0


The Quantity was different between Accepted and Rejected
ID    GATE    QTY      TXN         TIMESTAMP RESULT    SZ   ACCQTY  DEFQTY
--  ----    ---   ---------  --------------- -------   ---  ------  ------
LL    GATE1   4200  700680675     20110924 121652 ACCEPTED  315    0       0
LL    GATE1   4233  700680792     20110924 123030 REJECTED  315    0       1
LL    GATE1   4215  700686196     20110925 050318 ACCEPTED  315    0       0


Regards
Jim

rebisco, April 02, 2012 - 11:43 pm UTC

Hello Jim,

Our general computation is something like this:

lotArray t_Array; --This is supposed to be an array of already processed/checked lot IDs

DECLARE
numlots NUMBER := 0;
rejLots NUMBER := 0;
totalRejQty NUMBER := 0;
totalAcceptedSz NUMBER := 0;
totalAcceptedQty NUMBER := 0;
totalDefQty NUMBER := 0;
BEGIN
FOR x in (select * from mytbl order by GATE,TIMESTAMP,ID) LOOP
IF checkID(x.ID) = 0 THEN -- 0 means this LotId is not yet processed
<update the lotArray array with x.ID>
numLots := numLots + 1;

IF x.RESULT = 'REJECTED' THEN
rejLots := rejLots + 1;
totalRejQty := totalRejQty + x.QTY;
totalDefQty := totalDefQty + x.DEFQTY;
END IF;

IF x.RESULT = 'ACCEPTED' THEN
totalAcceptedSz := totalAcceptedSz + x.SZ;
totalAcceptedQty := totalAcceptedQty + x.QTY;
END IF;
<FLUSH the retrieve data using DBMS_OUTPUT.PUT_LINE>
END IF;
END LOOP;
END;

Above is our current dirty process which I converted to PHP. As you can see, only the first LOT information is being processed since it is sorted by TIMESTAMP.

I am just hoping to optimize this process, I know there are still room for improvement for the above processing.

Thank you again,
Rebisco

jim, April 03, 2012 - 12:52 am UTC

Thankyou but it did not answer my questions
and created more

What does checkID(x.ID) do?
If I use the 3 LL records as a sample then from your code
the following process happens

1: LL TXN 700680675 is read
2: CheckID (assume not processed)
3: increment tot number of lots
4: row has Result = ACCEPTED so increment Total accepted
figures

Loop back to 1: Read LL TXN 700680792
Where did LL TXN 700680675 get updated to say
processed?
How do I know what value will be return for
row LL TXN 700680792 so I know if it is excluded or goes
on to have the respective REJECTED ot ACCEPTED totals
updated.

Cheers
Jim



rebisco, April 03, 2012 - 1:44 am UTC

Hello Jim,

Thank you so much for getting me this far.

>What does checkID(x.ID) do?
checkID function will suppose to check the lotArray array if the parameter x.ID is already found in this array. It will return 1 if found, 0 otherwise.


>If I use the 3 LL records as a sample then from your code
the following process happens
>
>1: LL TXN 700680675 is read
>2: CheckID (assume not processed)
>3: increment tot number of lots
>4: row has Result = ACCEPTED so increment Total accepted
figures
>
>Loop back to 1: Read LL TXN 700680792
>Where did LL TXN 700680675 get updated to say
>processed?
LL TXN 700680675 will be saved/appended to lotArray at the first loop so, the 2nd and 3rd loop for lotId LL will be skipped.

>How do I know what value will be return for
row LL TXN 700680792 so I know if it is excluded or goes
on to have the respective REJECTED ot ACCEPTED totals
updated.

LL TXN 700680792 in this case will be skipped since checkID() function will return 1 in the 2nd and 3rd loop for LotId LL.

Thank you,
Rebisco

Does this look right?

A reader, April 03, 2012 - 6:50 pm UTC

Ok how does the following result look?

GATE                 NOOFLOTS    ACCLOTS    REJLOTS
-------------------- ---------- ---------- ----------
GATE1                 16         14        15



Here's how I got it

select  gate, 
        count(distinct id) NOOFLOTS, 
        count( case when result = 'ACCEPTED' then 1 end) ACCLOTS,
  count(case when timestamp = min_timestamp then(case when result = 'REJECTED' then 1 end ) end)  REJLOTS
from    (
         select mytbl.*,  min(timestamp) over (partition by id) min_timestamp
   from mytbl
   )
   group by gate



Thanks a million

rebisco, April 03, 2012 - 8:45 pm UTC 

Hello Reader,

Thanks a million man. I don't know who you are but you just saved me from misery.

Anyways, your solution is 99% close to what I am looking for almost 3weeks already...grrr! You just missed 1 for AccLots column. As from the table(MYTBL) I posted:

Number of Lots (NumLots) : 16
Number of Rejected Lots (RejLots) : 15
Number of Accepted Lots (AccLots) : ?
  - This value should be or must be 1 since, AccLots = numlots - rejlots or AccLots = 16 - 15

I just modified your code a little bit and I came the solution I am looking for. Here it is. Thanks again man!!!

<code>
select  gate, 
  count(distinct id) NOOFLOTS, 
  count(case when timestamp = min_timestamp then(case when result = 'ACCEPTED' then 1 end) end) ACCLOTS,
  count(case when timestamp = min_timestamp then(case when result = 'REJECTED' then 1 end ) end)  REJLOTS
from    (
  select mytbl.*,  min(timestamp) over (partition by id) min_timestamp
  from mytbl
         )
group by gate


I'll take the other needed columns from here (ie TotalSz, TotalDefQty, etc).

To Jim, I owe you man. I cannot have these solutions here without your patience. I salute you!

To Tom, thanks for the pointers. Your website always amazed me on how do you came up on such brilliant and simple solutions.

Thanks again,
Rebisco</code>

Jim, April 03, 2012 - 9:01 pm UTC

Hi Rebisco,

Sorry that was me posting a suggested solution
as "a Reader" forgot to include my name in the review
very glad to have helped.

Make sure you grab the Oracle manuals
and of course buy Tom's books if you don't have them already they really are the most educational on practical and efficient use of Oracle out there.

I bought his 1st one a long time ago (back at oracle
V 8.1.7) and the updated along with new book are brilliant


Regards
Jim


Checkout Tom's solution

Jim, April 03, 2012 - 9:16 pm UTC

Check out Tom's solution, his meets your requirement more accurately because I misunderstood your requirement for
the accepted lot count.

The where clause in Tom's SQL means the CASE statements comparing timestamps in my suggestion are not required.

ie changing the SQL to be more like Tom's
select  gate, 
  count(distinct id) NOOFLOTS, 
  count(case when result = 'ACCEPTED' then 1 end) 
ACCLOTS,
  count(case when result = 'REJECTED' then 1 end)  
REJLOTS
from    (
  select mytbl.*,  min(timestamp) over (partition by id) min_timestamp
  from mytbl
         )
where timestamp = min_timestamp
group by gate



Thanks again

rebisco, April 03, 2012 - 9:28 pm UTC

Hello Jim,

Yes, I already ordered Tom's book "Expert One on One Oracle" last week from Amazon, unfortunately its delivery date is quite longer than I expected. It should be delivered at 30May2012 :(

Thanks again Jim, you really made my day.
I am so glad that I get help from your expertise.

Regards,
Rebisco

rebisco, April 03, 2012 - 9:32 pm UTC

Hello Tom,

Sorry, I missed your follow up at the top of this page.
Thank you so much, you really are a brilliant man.

All my respect to you,
Rebisco

Errors when running inside an anonymous block

rebisco, April 03, 2012 - 11:27 pm UTC

Hello Tom,

Quick question.
Why do oracle do not allow me to run it inside an anonymous block while assigning the individual results into a variable?

declare 
  -- Local variables here
  numLots NUMBER := 0;
  accLots NUMBER := 0;
  rejLots NUMBER := 0;
begin
  -- Test statements here
  select count(distinct id) into numLots,
  count( case when result = 'ACCEPTED' then 1 end) into accLots,
  count( case when result = 'REJECTED' then 1 end ) into rejLots
  from (
      select t.*, min(timestamp) over (partition by id) min_ts
      from mytbl t
       );
  where timestamp = min_ts;

  DBMS_OUTPUT.PUT_LINE(numLots || ',' || accLots  || ',' || rejLots);
end;


Errors I got:
ORA-06550: line 9, column 3:
PL/SQL: ORA-00934: group function is not allowed here
ORA-06650: line 8, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 3:
PLS-00103: Encountered the symbol "WHERE" when expecting one of the following:

begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimit


Thank you,
Rebisco

Ignore my last question

rebisco, April 04, 2012 - 1:08 am UTC

Hello Tom,

Please ignore my last question.
I'm kinda lost for a while. Apologies.

Thanks again,
rebisco

multiple-columns

Ahmad Al-Sallal, April 26, 2012 - 5:50 am UTC

Hi Tom,
I always been asking can we return multiple-columns through subquery within the SELECT Clause???
My Answer is Unfourtunatilly NO, Am i wrong ?
It's will be helpfull for me (especially when i'm doing a data migration) and using a very complex subquery (within SELECT Clause" and need to return two columns from it!!!
Here i have two choises (as far as i know)
1) Duplidate it
2) use With Clause
3*) JOIN
Down an example of what i want
SELECT EMPNO , ENAME , (SELECT DNAME , LOC FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO ) 
FROM EMP

Note: In SOME cases that i faced a JOIN is not one of my options

Thanks alot
Tom Kyte
April 26, 2012 - 8:24 am UTC

Note: In SOME cases that i faced a JOIN is not one of my options


sure it is! if you can do it in a scalar subquery, I can do it in a join.

prove me wrong :) give a "for example"


two approaches for selecting multiple attributes from a scalar subquery:

ops$tkyte%ORA11GR2> select empno, ename, trim( substr( data, 1, 14 ) ) dname, substr( data, 15 ) loc
  2    from (
  3  SELECT EMPNO , ENAME ,
  4         (SELECT rpad( DNAME, 14 ) || LOC FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO ) data
  5    FROM EMP
  6         )
  7  /

     EMPNO ENAME      DNAME          LOC
---------- ---------- -------------- -------------
      7369 SMITH      RESEARCH       DALLAS
      7499 ALLEN      SALES          CHICAGO
      7521 WARD       SALES          CHICAGO
      7566 JONES      RESEARCH       DALLAS
      7654 MARTIN     SALES          CHICAGO
      7698 BLAKE      SALES          CHICAGO
      7782 CLARK      ACCOUNTING     NEW YORK
      7788 SCOTT      RESEARCH       DALLAS
      7839 KING       ACCOUNTING     NEW YORK
      7844 TURNER     SALES          CHICAGO
      7876 ADAMS      RESEARCH       DALLAS
      7900 JAMES      SALES          CHICAGO
      7902 FORD       RESEARCH       DALLAS
      7934 MILLER     ACCOUNTING     NEW YORK

14 rows selected.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace type myType as object( dname varchar2(14), loc varchar2(13) )
  2  /

Type created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select empno, ename, x.data.dname, x.data.loc
  2    from (
  3  select empno, ename,
  4         (select mytype( dname, loc ) from dept where emp.deptno = dept.deptno ) data
  5    from emp
  6         ) x
  7  /

     EMPNO ENAME      DATA.DNAME     DATA.LOC
---------- ---------- -------------- -------------
      7369 SMITH      RESEARCH       DALLAS
      7499 ALLEN      SALES          CHICAGO
      7521 WARD       SALES          CHICAGO
      7566 JONES      RESEARCH       DALLAS
      7654 MARTIN     SALES          CHICAGO
      7698 BLAKE      SALES          CHICAGO
      7782 CLARK      ACCOUNTING     NEW YORK
      7788 SCOTT      RESEARCH       DALLAS
      7839 KING       ACCOUNTING     NEW YORK
      7844 TURNER     SALES          CHICAGO
      7876 ADAMS      RESEARCH       DALLAS
      7900 JAMES      SALES          CHICAGO
      7902 FORD       RESEARCH       DALLAS
      7934 MILLER     ACCOUNTING     NEW YORK

14 rows selected.

Need a better way to write the below sql

pranavmithra, February 21, 2013 - 6:24 pm UTC

Hi tom,

Is there is any better way to write the below sql:

select max(T.DATE1) from
((Select MIN(START_DT) "DATE1" from PS_UC_ASSGN_EMP_VW where UC_STAFFING_BU = 'IN007' and UC_STAFFING_PRJID = 'IND000160' and EMPLID = '071823')
union
(SELECT START_DT "DATE1" FROM PS_PROJECT WHERE BUSINESS_UNIT = 'US261' AND PROJECT_ID = '000000000012450')) T;
Tom Kyte
February 25, 2013 - 10:36 am UTC

start by indenting

select max(T.DATE1) from
(
(Select MIN(START_DT) "DATE1" 
  from PS_UC_ASSGN_EMP_VW 
 where UC_STAFFING_BU = 'IN007' 
   and UC_STAFFING_PRJID = 'IND000160' 
   and EMPLID = '071823'
)
union
(SELECT START_DT "DATE1" 
   FROM PS_PROJECT 
  WHERE BUSINESS_UNIT = 'US261' 
    AND PROJECT_ID = '000000000012450'
)
) T;


so that one can read it - then I might suggest a MAX in the second part of the union:

select max(T.DATE1) from
(
(Select MIN(START_DT) "DATE1" 
  from PS_UC_ASSGN_EMP_VW 
 where UC_STAFFING_BU = 'IN007' 
   and UC_STAFFING_PRJID = 'IND000160' 
   and EMPLID = '071823'
)
union
(SELECT max( START_DT ) "DATE1" 
   FROM PS_PROJECT 
  WHERE BUSINESS_UNIT = 'US261' 
    AND PROJECT_ID = '000000000012450'
)
) T;


if ps_project is a table - an index on business_unit,project_id,start_dt (or project_id,business_unit,start_dt) would be useful, we'd expect an index min/max scan

the union can be union all - the max on the outer part of the query will "distinct" for us.

as the first table queried appears to be a vw, that is about all we can say. You might want to unwind the view text into the query itself and see what bits you can remove from it - undoubtedly it gets a lot more attributes than you actually need.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.