9 Replies Latest reply: Nov 21, 2012 4:04 AM by Manik RSS

    count(*) issue

    Manik
      Hi Gurus,

      :)

      I am trying to follow a post in the forum :

      String matching

      I tried to re-write the query as :
      WITH wordstosearch AS (SELECT 'LOCAL BOPP' txt FROM DUAL),
           wordtbl AS
              (  SELECT TRIM (word) word
                   FROM wordstosearch,
                        XMLTABLE (
                           'ora:tokenize($str, " ")'
                           PASSING CASE
                                      WHEN INSTR (txt, ' ') = 0 THEN txt || ' '
                                      ELSE txt
                                   END AS "str"
                           COLUMNS word VARCHAR2 (100) PATH '.')
               GROUP BY TRIM (word)),
           comments AS
              (  SELECT cmt
                   FROM (SELECT 'BOPP ROLL 175 MM (20 uM) (LOCAL)' cmt FROM DUAL
                         UNION ALL
                         SELECT 'BOPP ROLL 175 MM (20 uM) (PRINTED) (LOCAL)' cmt
                           FROM DUAL
                         UNION ALL
                         SELECT 'LOCAL BOPP' cmt FROM DUAL
                         UNION ALL
                         SELECT 'LOCAL BOPP' FROM DUAL
                         UNION ALL
                         SELECT '(LOCAL) fgafgf (BOPPALONG)' cmt FROM DUAL
                         UNION ALL
                         SELECT 'asdfd BOPP asdafs' FROM DUAL
                         UNION ALL
                         SELECT 'afgaf LOCAL agaf' FROM DUAL
                         UNION ALL
                         SELECT 'sadga hah ' FROM DUAL)
               GROUP BY cmt)
        SELECT cmt
          FROM comments c, wordtbl w
         WHERE INSTR (c.cmt, w.word) > 0
      GROUP BY cmt
        HAVING COUNT (*) = (SELECT COUNT (*) FROM wordtbl);  ---------------  count(*) from wordtbl not working........
      The problem is : select count(*) from wordtbl is not working in this having clause I get no rows selected...But when I use split string logic using regexp (inside wordtbl) I get the result..

      Am I missing anything here??? (May be a silly mistake.. but trying to learn thx in adv!!!!)

      ORACLE VERSION 11g R2

      Cheers,
      Manik.

      Edited by: Manik on Nov 21, 2012 11:05 AM -- Added oracle version
        • 1. Re: count(*) issue
          jeneesh
          Looks like "XML Query XMLTable Returns Wrong Results [ID 1475800.1]" in metalink..

          patch is available..
          • 2. Re: count(*) issue
            Manik
            I was expecting that you will be the one to answer this quick :)

            But, I guess I did not get you correctly.. Please check the below query (Definetely not advisible) in which I brought the with clause query inside the main query and it worked.. You say this is a bug in Oracle???
            WITH wordstosearch AS (SELECT 'LOCAL BOPP' txt FROM DUAL),
                 wordtbl AS
                    (  SELECT TRIM (word) word
                         FROM wordstosearch,
                              XMLTABLE (
                                 'ora:tokenize($str, " ")'
                                 PASSING CASE
                                            WHEN INSTR (txt, ' ') = 0 THEN txt || ' '
                                            ELSE txt
                                         END AS "str"
                                 COLUMNS word VARCHAR2 (100) PATH '.')
                     GROUP BY TRIM (word)),
                 comments AS
                    (  SELECT cmt
                         FROM (SELECT 'BOPP ROLL 175 MM (20 uM) (LOCAL)' cmt FROM DUAL
                               UNION ALL
                               SELECT 'BOPP ROLL 175 MM (20 uM) (PRINTED) (LOCAL)' cmt
                                 FROM DUAL
                               UNION ALL
                               SELECT 'LOCAL BOPP' cmt FROM DUAL
                               UNION ALL
                               SELECT 'LOCAL BOPP' FROM DUAL
                               UNION ALL
                               SELECT '(LOCAL) fgafgf (BOPPALONG)' cmt FROM DUAL
                               UNION ALL
                               SELECT 'asdfd BOPP asdafs' FROM DUAL
                               UNION ALL
                               SELECT 'afgaf LOCAL agaf' FROM DUAL
                               UNION ALL
                               SELECT 'sadga hah ' FROM DUAL)
                     GROUP BY cmt)
              SELECT cmt
                FROM comments c, wordtbl w
               WHERE INSTR (c.cmt, w.word) > 0
            GROUP BY cmt
              HAVING COUNT (*) = (SELECT COUNT (*)
                                    FROM (  SELECT TRIM (word) word
                                              FROM wordstosearch,
                                                   XMLTABLE (
                                                      'ora:tokenize($str, " ")'
                                                      PASSING CASE
                                                                 WHEN INSTR (txt, ' ') = 0 THEN
                                                                    txt || ' '
                                                                 ELSE
                                                                    txt
                                                              END AS "str"
                                                      COLUMNS word VARCHAR2 (100) PATH '.')
                                          GROUP BY TRIM (word)));
            Output:
            CMT
            -------
            BOPP ROLL 175 MM (20 uM) (LOCAL)
            BOPP ROLL 175 MM (20 uM) (PRINTED) (LOCAL)
            (LOCAL) fgafgf (BOPPALONG)
            LOCAL BOPP
            Cheers,
            Manik.
            • 3. Re: count(*) issue
              jeneesh
              I dont have a 11gR2 DB to check now.

              But I do remember, some count(*) queries with WITH Clause and XMLTable giving no results (Count(*) should give 0 as result if no rows are there).

              And there were some work arounds of using NO_XML_QUERY_REWRITE hint, but not sure in this particular case..

              If you check the below query, probably that will give "no rows"
              WITH wordstosearch AS (SELECT 'LOCAL BOPP' txt FROM DUAL),
                   wordtbl AS
                      (  SELECT TRIM (word) word
                           FROM wordstosearch,
                                XMLTABLE (
                                   'ora:tokenize($str, " ")'
                                   PASSING CASE
                                              WHEN INSTR (txt, ' ') = 0 THEN txt || ' '
                                              ELSE txt
                                           END AS "str"
                                   COLUMNS word VARCHAR2 (100) PATH '.')
                       GROUP BY TRIM (word)),
                   comments AS
                      (  SELECT cmt
                           FROM (SELECT 'BOPP ROLL 175 MM (20 uM) (LOCAL)' cmt FROM DUAL
                                 UNION ALL
                                 SELECT 'BOPP ROLL 175 MM (20 uM) (PRINTED) (LOCAL)' cmt
                                   FROM DUAL
                                 UNION ALL
                                 SELECT 'LOCAL BOPP' cmt FROM DUAL
                                 UNION ALL
                                 SELECT 'LOCAL BOPP' FROM DUAL
                                 UNION ALL
                                 SELECT '(LOCAL) fgafgf (BOPPALONG)' cmt FROM DUAL
                                 UNION ALL
                                 SELECT 'asdfd BOPP asdafs' FROM DUAL
                                 UNION ALL
                                 SELECT 'afgaf LOCAL agaf' FROM DUAL
                                 UNION ALL
                                 SELECT 'sadga hah ' FROM DUAL)
                       GROUP BY cmt)
                SELECT cmt,(SELECT COUNT (*) FROM wordtbl) cnt --"this should not actually effect the rows..
                  FROM comments c, wordtbl w
                 WHERE INSTR (c.cmt, w.word) > 0
              GROUP BY cmt
                --HAVING COUNT (*) = (SELECT COUNT (*) FROM wordtbl);"commented"
              {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
              • 4. Re: count(*) issue
                Manik
                yes as u pointed out no rows got selected.

                Cheers,
                Manik.
                • 5. Re: count(*) issue
                  jeneesh
                  Manik wrote:
                  yes as u pointed out no rows got selected.

                  Cheers,
                  Manik.
                  That confirms, it as a Bug..

                  Either you can raise it with ORACLE, or can follow the metalink note provided...
                  :)
                  • 6. Re: count(*) issue
                    Manik
                    :) agreed.. waiting for others to confirm as you dont have oracle 11g. Just waiting to close this thread if someone who has 11g r2 face the same issue.

                    Cheers,
                    Manik.
                    • 7. Re: count(*) issue
                      jeneesh
                      Manik wrote:
                      :) agreed.. waiting for others to confirm as you dont have oracle 11g.
                      yea, cool...

                      :)
                      • 8. Re: count(*) issue
                        padders
                        It is a bug. Try using /*+ INLINE */ hint in wordtbl view or use another method to count (such as analytic COUNT (*) OVER ()).
                        • 9. Re: count(*) issue
                          Manik
                          Thanks! I am closing this thread.. thanks to Jeneesh and Padders.

                          Cheers,
                          Manik.