This discussion is archived
11 Replies Latest reply: Nov 20, 2012 6:45 AM by S10390 RSS

select query

949210 Newbie
Currently Being Moderated
select * from emp; ---- if no rows are returned or if count is less than 5
select * from dept
I want to implement just this
please suggest some ideas to implement this without using plsql collections
thank you
  • 1. Re: select query
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    what is it that you are trying to achieve? please rephrase your question
  • 2. Re: select query
    Manik Expert
    Currently Being Moderated
    :)
    SELECT *
      FROM dept
     WHERE 5 > (SELECT COUNT (*) FROM emp);
    Cheers,
    Manik.
  • 3. Re: select query
    971895 Journeyer
    Currently Being Moderated
    pls explain in detial..why are going with collection.. we can use case...
  • 4. Re: select query
    908002 Expert
    Currently Being Moderated
    select * from emp
    ---- if no rows are returned or if count is less than 5

    select * from dept
    where (select count(*) from emp) < 5
  • 5. Re: select query
    949210 Newbie
    Currently Being Moderated
    ok this is my first query
    SELECT REG_ID,NAME,PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM
    FROM(SELECT ROWNUM,REG_ID,NAME,NVL(SUBSTR(CAT_TYPES,1,INSTR(CAT_TYPES, ',', 1, 8)-1),CAT_TYPES) AS PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM FROM MV_CAT_SEG_REG_PROD
    where CATSEARCH(cat_types,'Solar-water-heater-system*',NULL)>0 and rownum<11 order by act_status desc)
    and the following is my second query
    SELECT REG_ID,NAME,PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM
    FROM(SELECT ROWNUM,REG_ID,NAME,NVL(SUBSTR(CAT_TYPES,1,INSTR(CAT_TYPES, ',', 1, 8)-1),CAT_TYPES) AS PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM FROM MV_CSRP_SEG
    where CATSEARCH(cat_types,'Solar-water-heater-system*',NULL)>0 and rownum<11 order by act_status desc)
    I want the first query to fire first
    if it does not return any row(or returns less than 5 rows)
    then execute the second query (if the first query returns rows dont execute the second at all)
  • 6. Re: select query
    908002 Expert
    Currently Being Moderated
    Seems you are using pl.sql for this. in this case
    As column_names and column)count is same use the following
    SELECT REG_ID,NAME,PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM
    FROM(SELECT ROWNUM,REG_ID,NAME,NVL(SUBSTR(CAT_TYPES,1,INSTR(CAT_TYPES, ',', 1, 8)-1),CAT_TYPES) AS PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM FROM MV_CAT_SEG_REG_PROD
    where CATSEARCH(cat_types,'Solar-water-heater-system*',NULL)>0 and rownum<11 order by act_status desc)
    union all
    select a.* from (SELECT REG_ID,NAME,PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM
    FROM(SELECT ROWNUM,REG_ID,NAME,NVL(SUBSTR(CAT_TYPES,1,INSTR(CAT_TYPES, ',', 1, 8)-1),CAT_TYPES) AS PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM FROM MV_CSRP_SEG
    where CATSEARCH(cat_types,'Solar-water-heater-system*',NULL)>0 and rownum<11 order by act_status desc)) a
    where 5> (SELECT count(*) 
    FROM(SELECT ROWNUM,REG_ID,NAME,NVL(SUBSTR(CAT_TYPES,1,INSTR(CAT_TYPES, ',', 1, 8)-1),CAT_TYPES) AS PRODUCTS,ADDRESS1,LOWER(ADDRESS2) AS ADDRESS2,CONTACT_NAME,UPLOAD_THUMBNAILS,ACT_STATUS,IAM FROM MV_CAT_SEG_REG_PROD
    where CATSEARCH(cat_types,'Solar-water-heater-system*',NULL)>0 and rownum<11 order by act_status desc))
    OR u can use pl/sql also

    get the first query into cursor

    if cursorvariable%rowcount <=5 then
    execute second query
    end if;

    Edited by: Kiran on Nov 20, 2012 2:05 AM
  • 7. Re: select query
    949210 Newbie
    Currently Being Moderated
    ok so while executing the second query
    first query will be executed twice (time taken for two executions one for the main query and one for count)
    plus time taken to execute the second query itself
    :
    am i right?
  • 8. Re: select query
    908002 Expert
    Currently Being Moderated
    Yes, if you dont want that.. use plsql solution i have provided.
  • 9. Re: select query
    949210 Newbie
    Currently Being Moderated
    Kiran wrote:
    Yes, if you dont want that.. use plsql solution i have provided.
    where is the plsql solution you have provided??
  • 10. Re: select query
    S10390 Journeyer
    Currently Being Moderated
    Here it is...

    Open the cursor for first query

    if cursorvariable%rowcount <=5 then

    execute second query

    end if;

    Close Cursor;
  • 11. Re: select query
    Solomon Yakobson Guru
    Currently Being Moderated
    Santhosh wrote:
    Here it is...

    Open the cursor for first query

    if cursorvariable%rowcount <=5 then

    execute second query

    end if;

    Close Cursor;
    ??? Go back to reading concepts. Cursor attribute %ROWCOUNT shows number of rows fetched* from cursor since last time it was open. Cursor doesn't know how many rows SELECT statement returns. In order to find out how many rows SELECT statement returns you either need to fetch all rows or add something like count(*) over() and fetch first row.

    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points