11 Replies Latest reply: Nov 20, 2012 8:45 AM by S10390 RSS

    select query

    949210
      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
          what is it that you are trying to achieve? please rephrase your question
          • 2. Re: select query
            Manik
            :)
            SELECT *
              FROM dept
             WHERE 5 > (SELECT COUNT (*) FROM emp);
            Cheers,
            Manik.
            • 3. Re: select query
              971895
              pls explain in detial..why are going with collection.. we can use case...
              • 4. Re: select query
                908002
                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
                  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
                    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
                      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
                        Yes, if you dont want that.. use plsql solution i have provided.
                        • 9. Re: select query
                          949210
                          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
                            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
                              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.