Home » SQL & PL/SQL » SQL & PL/SQL » SQL HELP (10.1.0.3.0)
SQL HELP [message #584316] Sun, 12 May 2013 04:52 Go to next message
Yuvraaj
Messages: 104
Registered: January 2011
Location: California, USA
Senior Member
Hi All,

Could you please help me in framing a sql:

Here is the test case:

  CREATE TABLE ITEMINFO (UNITID NUMBER, ITEMID NUMBER, ITEMNAME VARCHAR2(90), ITEMNAMElower  var
char2(90), FLAG NUMBER);

 
SQL> DESC ITEMINFO;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 UNITID                                             NUMBER
 ITEMID                                             NUMBER
 ITEMNAME                                           VARCHAR2(90)
 ITEMNAMELOWER                                      VARCHAR2(90)
 FLAG                                               NUMBER


INSERT INTO ITEMINFO VALUES
(101, 500515, 'Item1', 'Item1', 1
);

INSERT INTO ITEMINFO VALUES
(102, 20206, 'Item2', 'Item2', 1
);

INSERT INTO ITEMINFO VALUES
(103, 213679, 'Item3', 'Item3', 1
);

INSERT INTO ITEMINFO VALUES
(104, 175412, 'Item4', 'Item4', 1
);

INSERT INTO ITEMINFO VALUES
(105, 500515, 'Item1', 'Item1', 0
);

INSERT INTO ITEMINFO VALUES
(106, 20206, 'Item2', 'Item2', 0
);

INSERT INTO ITEMINFO VALUES
(107, 213679, 'Item3', 'Item3', 0
);

INSERT INTO ITEMINFO VALUES
(108, 175412, 'Item4', 'Item4', 0
);

INSERT INTO ITEMINFO VALUES
(109, 500515, 'Item1', 'Item1',1
);

INSERT INTO ITEMINFO VALUES
(110, 20206, 'Item2', 'Item2', 0
);

INSERT INTO ITEMINFO VALUES
(111, 213679, 'Item3', 'Item3', 1
);

INSERT INTO ITEMINFO VALUES
(112, 175412, 'Item4', 'Item4', 0
);


INSERT INTO ITEMINFO VALUES
(113, 175413, 'Item5', 'Item5', 0
);

INSERT INTO ITEMINFO VALUES
(114, 175414, 'Item6', 'Item6', 1
);


SQL> select * from iteminfo;
      
    UNITID     ITEMID ITEMNAME      ITEMNAMELOWER      FLAG                                    
---------- ---------- ---------- -- ----------------- -----                                    
       101     500515 Item1         Item1                 1                                    
       102      20206 Item2         Item2                 1                                    
       103     213679 Item3         Item3                 1                                    
       104     175412 Item4         Item4                 1                                    
       105     500515 Item1         Item1                 0                                    
       106      20206 Item2         Item2                 0                                    
       107     213679 Item3         Item3                 0                                    
       108     175412 Item4         Item4                 0                                    
       109     500515 Item1         Item1                 1                                    
       110      20206 Item2         Item2                 0                                    
       111     213679 Item3         Item3                 1                                    
       112     175412 Item4         Item4                 0                                    
       113     175413 Item5         Item5                 0                                    
       114     175414 Item6         Item6                 1         




Need to buffer to client following way:

  
ITEMID ---  ITEMNAMELOWER --- COUNT(UNITID WITH FLAG=1 + FALG=0) ---  COUNT(UNITID WITH FLAG=1);  

500515  - ITEM1    -  3(2+1) - 2
20206   - ITEM2    -  3(1+2) - 1
213679	- ITEM3    -  3(2+2) - 2
175412	- ITEM4    -  3(1+2) - 1
175413  - ITEM5	   -  1(0+1) - 0
175414  - ITEM6    -  1(1+0) - 1

Values in brackets for explanation

ITEMID -  ITEMNAMELOWER - COUNT(UNITID WITH FLAG=1 + FALG=0) -  COUNT(UNITID WITH FLAG=1);  

500515  - ITEM1    -  3 - 2
20206   - ITEM2    -  3 - 1
213679	- ITEM3    -  3 - 2
175412	- ITEM4    -  3 - 1
175413  - ITEM5	   -  1 - 0
175414  - ITEM6    -  1 - 1

I should send out put like below one by one with a loop with order by itemnamelower...
dbms_output.put_line(ITEMID); -- 500515
dbms_output.put_line(ITEMNAMELOWER); -- ITEM1
dbms_output.put_line(aCOUNT); - 3
dbms_output.put_line(bcount); - 2





Thanks in advance...
-YJ
Re: SQL HELP [message #584317 is a reply to message #584316] Sun, 12 May 2013 06:28 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
Hi,


As it seems to me this can be done directly with SQL (therefore no need for PL/SQL) by using COUNT and DENSE_RANK analytic functions.

SELECT  t1.itemid,
        t1.itemnamelower,
        t1.total_flag_count,
        DECODE(flag, 1, t1.per_flag_count, 0) AS flag_one_count
FROM
(
    SELECT  itemid, 
            itemnamelower, 
            COUNT(*) OVER (PARTITION BY itemid ORDER BY itemid) 
                AS total_flag_count,
            COUNT(*) OVER (PARTITION BY itemid, flag ORDER BY itemid) 
                AS per_flag_count,
            DENSE_RANK() OVER (PARTITION BY itemid ORDER BY flag DESC) 
                AS per_flag_rank, 
            flag  
    FROM iteminfo
) t1
GROUP BY    t1.itemid,
            t1.itemnamelower,
            t1.total_flag_count,
            t1.per_flag_count,
            t1.per_flag_rank,
            t1.flag
HAVING t1.per_flag_rank = 1
ORDER BY t1.itemid DESC;



     ITEMID ITEMNAMELO TOTAL_FLAG_COUNT FLAG_ONE_COUNT
----------- ---------- ---------------- --------------
     500515 Item1		      3 	     2
     213679 Item3		      3 	     2
     175414 Item6		      1 	     1
     175413 Item5		      1 	     0
     175412 Item4		      3 	     1
      20206 Item2		      3 	     1

6 rows selected.

SQL> 



Regards,
Dariyoosh

[Updated on: Sun, 12 May 2013 07:02]

Report message to a moderator

Re: SQL HELP [message #584318 is a reply to message #584317] Sun, 12 May 2013 07:16 Go to previous messageGo to next message
Yuvraaj
Messages: 104
Registered: January 2011
Location: California, USA
Senior Member
Awesome...

Thanks you so much Dariyoosh... It worked 100% Very Happy Very Happy Very Happy

Thanks once again!!!

-YJ
Re: SQL HELP [message #584319 is a reply to message #584317] Sun, 12 May 2013 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a big error to use analytic functions here. It is a simple aggregate case:
SQL> select itemid, itemnamelower,
  2         count(*)  TOTAL_FLAG_COUNT,
  3         count(decode(flag, 1, 1)) FLAG_ONE_COUNT
  4  from ITEMINFO
  5  group by itemid, itemnamelower
  6  order by 1 desc
  7  /
    ITEMID ITEMNAMELOWER TOTAL_FLAG_COUNT FLAG_ONE_COUNT
---------- ------------- ---------------- --------------
    500515 Item1                        3              2
    213679 Item3                        3              2
    175414 Item6                        1              1
    175413 Item5                        1              0
    175412 Item4                        3              1
     20206 Item2                        3              1

Regards
Michel
Re: SQL HELP [message #584320 is a reply to message #584319] Sun, 12 May 2013 08:44 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
Thanks for the remark.

I'd forgotten the fact that if the default parameter in DECODE is omitted, then Oracle returns null which likewise will be ignored inside the COUNT. That's why I used DENSE_RANK to separate the cases.

But now, seeing your solution, yes it is pretty useless to apply an analytic solution.

Thanks for pointing this out.


Regards,
Dariyoosh
Re: SQL HELP [message #584321 is a reply to message #584320] Sun, 12 May 2013 10:56 Go to previous message
Michel Cadot
Messages: 58632
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'd forgotten the fact that if the default parameter in DECODE is omitted,


It is possible to also use: decode(flag, 1, 1, to_number(null))

Regards
Michel

Previous Topic: Query help - Alpha characters
Next Topic: What happens in Background
Goto Forum:
  


Current Time: Thu Jul 31 05:43:12 CDT 2014

Total time taken to generate the page: 0.11716 seconds