Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

JOINs and CATSEARCH

833964Jan 25 2011 — edited Jan 27 2011
I have 3 tables

CONTRACTOR
- ID
- NAME
- ADDRESS

CONTRACTOR_DOMESTIC_CODE
- ID
- CODE

CONTRACTOR_INTERNATIONAL_CODE
- ID
- CODE

The codes consist of multiple word and there is a UI where a user can enter any one word and search. I need to create a CTXCAT index over the codes to return the results. The user has an option of selecting Domestic, International, Both while performing his search.

For domestic search I tried the following query:

SELECT con.id
FROM contractor con
LEFT OUTER JOIN contractor_domestic_code dom
ON con.id = dom.id
WHERE con.status_delete != 'Y'
AND CATSEARCH(dom.code, 'de*', '') > 0

It throws the following exception

ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation
DRG-10599: column is not indexed
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.

I haven't been able to get around this problem. IN clause works but is very slow. I will also need to add contractor_international_code in the query when required.
This post has been answered by Barbara Boehmer on Jan 25 2011
Jump to Answer

Comments

fsitja
Probably this: 
SQL> with t as (select 'California' state, 'Los Angeles' city, 'AA' categ, 1 nbr from dual
  2  union all select 'California' state, 'Los Angeles' city, 'BB' categ, 2 nbr from dual
  3  union all select 'California' state, 'Los Angeles' city, 'CC' categ, 3 nbr from dual
  4  union all select 'California' state, 'San Diego' city, 'AA' categ, 4 nbr from dual
  5  union all select 'California' state, 'San Diego' city, 'BB' categ, 5 nbr from dual
  6  union all select 'California' state, 'San Diego' city, 'CC' categ, 6 nbr from dual
  7  union all select 'California' state, 'San Francisco' city, 'AA' categ, 7 nbr from dual
  8  union all select 'California' state, 'San Francisco' city, 'BB' categ, 8 nbr from dual
  9  union all select 'California' state, 'San Francisco' city, 'CC' categ, 9 nbr from dual)
 10  select state, 
 11         city,
 12         categ,
 13         sum(nbr) total
 14   from t
 15   group by rollup (state, city, categ)
 16  order by state, city, categ
 17  /

STATE      CITY          CA      TOTAL
---------- ------------- -- ----------
California Los Angeles   AA          1
California Los Angeles   BB          2
California Los Angeles   CC          3
California Los Angeles               6
California San Diego     AA          4
California San Diego     BB          5
California San Diego     CC          6
California San Diego                15
California San Francisco AA          7
California San Francisco BB          8
California San Francisco CC          9

STATE      CITY          CA      TOTAL
---------- ------------- -- ----------
California San Francisco            24
California                          45
                                    45

14 linhas selecionadas.

SQL>
682388
Hi fsitja,

Thank you for your prompt reply but that's not providing the category totals for all of California.

Lou
fsitja
My bad, that would be a cube then 
SQL> with t as (select 'California' state, 'Los Angeles' city, 'AA' categ, 1 nbr from dual
  2  union all select 'California' state, 'Los Angeles' city, 'BB' categ, 2 nbr from dual
  3  union all select 'California' state, 'Los Angeles' city, 'CC' categ, 3 nbr from dual
  4  union all select 'California' state, 'San Diego' city, 'AA' categ, 4 nbr from dual
  5  union all select 'California' state, 'San Diego' city, 'BB' categ, 5 nbr from dual
  6  union all select 'California' state, 'San Diego' city, 'CC' categ, 6 nbr from dual
  7  union all select 'California' state, 'San Francisco' city, 'AA' categ, 7 nbr from dual
  8  union all select 'California' state, 'San Francisco' city, 'BB' categ, 8 nbr from dual
  9  union all select 'California' state, 'San Francisco' city, 'CC' categ, 9 nbr from dual)
 10  select state, 
 11         city,
 12         categ,
 13         sum(nbr) total
 14   from t
 15   group by cube(state, city, categ)
 16  having (grouping(city) = 1 and grouping(state) = 0)
 17  or (grouping(categ) = 0 and grouping(city) = 0 and grouping(state) = 0)
 18  order by state, city, categ
 19  /

STATE      CITY          CA      TOTAL
---------- ------------- -- ----------
California Los Angeles   AA          1
California Los Angeles   BB          2
California Los Angeles   CC          3
California San Diego     AA          4
California San Diego     BB          5
California San Diego     CC          6
California San Francisco AA          7
California San Francisco BB          8
California San Francisco CC          9
California               AA         12
California               BB         15

STATE      CITY          CA      TOTAL
---------- ------------- -- ----------
California               CC         18
California                          45

13 linhas selecionadas.

SQL>
You can play around with the having clause and groupings and select the rows as needed.

Regards,
Francisco.

Edited by: fsitja on Mar 24, 2009 6:11 PM

Edited by: fsitja on Mar 24, 2009 6:14 PM
Frank Kulash
Answer
Hi,
SELECT	  state, city, category
,	  SUM (nbr)			AS nbr
FROM	  table_x
GROUP BY  CUBE (state, city, category)
HAVING	  GROUPING (state)   = 0
ORDER BY  state, city, category;
Does basically what you want.

The numbers aren't quite what you posted.
The "Grand Total" row says "Califoirnia" instead. You can fix that with CASE and GROUPING.
Marked as Answer by 682388 · Sep 27 2020
682388
That's it Frank, thank you!

I can take care of the verbiage with CASE statements.
Aketi Jyuuzou
Frank Kulash wrote
GROUP BY  CUBE (state, city, category)
HAVING    GROUPING (state)   = 0
There is another one ;-)
select state, city,categ,
       sum(nbr) total
 from t
 group by state,cube(city,categ)
order by state, city, categ;
1 person found this helpful
682388
That's even better Aketi!

Now I don't have to filter out totals I don't need.
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 24 2011
Added on Jan 25 2011
#database-key-features, #text
7 comments
4,045 views