1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ora-01427 error

Discussion in 'SQL PL/SQL' started by UmaJanaa, Jun 22, 2009.

  1. UmaJanaa

    UmaJanaa Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Code (SQL):
    SELECT DISTINCT commonpackage.get_communityname (l1.communitycode) community,
                    (SELECT   COUNT (*)
                         FROM leads
                        WHERE communitycode IN (SELECT communitycode
                                                  FROM communities)
                          AND leaddate BETWEEN '01-Apr-2008' AND '30-Apr-2008'
                     GROUP BY communitycode) leads,
                    (SELECT   COUNT (*)
                         FROM leads l1, leadactivities l2
                        WHERE l2.activityname LIKE '%Tour%'
                          AND l1.communitycode IN (SELECT communitycode
                                                     FROM communities)
                          AND l1.leadid = l2.leadid(+)
                          AND l1.leaddate BETWEEN '01-Apr-2008' AND '30-Apr-2008'
                     GROUP BY l1.communitycode) leadstotours,
                    (SELECT   COUNT (*)
                         FROM leadactivities
                        WHERE activityname LIKE '%Tour%'
                          AND communitycode IN (SELECT communitycode
                                                  FROM communities)
                     GROUP BY communitycode) tours,
                    (SELECT   COUNT (*)
                         FROM leads l1, leadactivities l2
                        WHERE l2.activityname LIKE '%Tour%'
                          AND l1.STATUS = 'Moved-In'
                          AND l1.leadid = l2.leadid(+)
                          AND l1.leaddate BETWEEN '01-Apr-2008' AND '30-Apr-2008'
                          AND l1.communitycode IN (SELECT communitycode
                                                     FROM communities)
                     GROUP BY l1.communitycode) tourstomoveins,
                    (SELECT   COUNT (*)
                         FROM leads l1, leadactivities l2
                        WHERE l1.STATUS = 'Moved-In'
                          AND l1.leadid = l2.leadid(+)
                          AND l1.leaddate BETWEEN '01-Apr-2008' AND '30-Apr-2008'
                          AND l1.communitycode IN (SELECT communitycode
                                                     FROM communities)
                     GROUP BY l1.communitycode) leadstomoveins,
                      TRUNC
                         (  (SELECT   COUNT (*)
                                 FROM leads l1, leadactivities l2
                                WHERE l2.activityname LIKE '%Tour%'
                                  AND l1.communitycode IN (SELECT communitycode
                                                             FROM communities)
                                  AND l1.leadid = l2.leadid(+)
                                  AND l1.leaddate BETWEEN '01-Apr-2008'
                                                      AND '30-Apr-2008'
                             GROUP BY l1.communitycode)
                          / DECODE
                               ((SELECT   COUNT (*)
                                     FROM leads
                                    WHERE communitycode IN (SELECT communitycode
                                                              FROM communities)
                                      AND leaddate BETWEEN '01-Apr-2008'
                                                       AND '30-Apr-2008'
                                 GROUP BY communitycode),
                                0, NULL
                               ),
                          6
                         )
                    * 100 perleadstotours,
                      TRUNC
                         (  (SELECT   COUNT (*)
                                 FROM leads l1, leadactivities l2
                                WHERE l2.activityname LIKE '%Tour%'
                                  AND l1.STATUS = 'Moved-In'
                                  AND l1.leadid = l2.leadid(+)
                                  AND l1.leaddate BETWEEN '01-Apr-2008'
                                                      AND '30-Apr-2008'
                                  AND l1.communitycode IN (SELECT communitycode
                                                             FROM communities)
                             GROUP BY l1.communitycode)
                          / DECODE
                               ((SELECT   COUNT (*)
                                     FROM leads l1, leadactivities l2
                                    WHERE l2.activityname LIKE '%Tour%'
                                      AND l1.communitycode IN (
                                                              SELECT communitycode
                                                                FROM communities)
                                      AND l1.leadid = l2.leadid(+)
                                      AND l1.leaddate BETWEEN '01-Apr-2008'
                                                          AND '30-Apr-2008'
                                 GROUP BY l1.communitycode),
                                0, NULL
                               ),
                          6
                         )
                    * 100 pertourstomoveins,
                      TRUNC
                         (  (SELECT   COUNT (*)
                                 FROM leads l1, leadactivities l2
                                WHERE l2.activityname LIKE '%Tour%'
                                  AND l1.STATUS = 'Moved-In'
                                  AND l1.leadid = l2.leadid(+)
                                  AND l1.leaddate BETWEEN '01-Apr-2008'
                                                      AND '30-Apr-2008'
                                  AND l1.communitycode IN (SELECT communitycode
                                                             FROM communities)
                             GROUP BY l1.communitycode)
                          / DECODE
                               ((SELECT   COUNT (*)
                                     FROM leads
                                    WHERE communitycode IN (SELECT communitycode
                                                              FROM communities)
                                      AND leaddate BETWEEN '01-Apr-2008'
                                                       AND '30-Apr-2008'
                                 GROUP BY communitycode),
                                0, NULL
                               ),
                          6
                         )
                    * 100 perleadstomoveins
               FROM leads l1, leadactivities l2
              WHERE l1.communitycode IN (SELECT communitycode
                                           FROM communities)
                AND l1.leaddate BETWEEN '01-Apr-2008' AND '30-Apr-2008'
                AND l1.STATUS = 'Moved-In'
                AND l1.leadid = l2.leadid(+)
    THIS IS WHAT MY QUERY, WHILE EXECUTING THIS QUERY AM GETTING SINGLE SUBQUERY RETURNS MORE THAN ONE ROW PL/SQL ERROR, AM DYING WITH THIS ERROR FOR PAST ONE WEEK, HOPE AT LEAST THROUGH THIS FORUM I GET THE SOLUTION FOR THIS ISSUE.
     
  2. sashraf

    sashraf Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Hyderbad. India
    Error clearly says depending upon your data one of the sub query is returning more than one row so you can execute all the sub queries separately one after other to check for which query you are getting more than one row.
    Because you are using Group BY clause it is returning more than 1 row which 1 count result for each group
    Once you get that query you can modify it to return single row.

    ~Ashraf
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi UmaJanaa, you need to provide us with

    1. The Create table scripts for your tables leads, communities, leadactivities
    2. Some sample inserts into these tables
    3. The code for the commonpackage.get_communityname and related create table scripts if any
    4. WHAT YOU ARE TRYING TO DO!

    Then and only then can we help in writing the query. On a single glance, this looks like a very unnecessarily complicated query.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or do a homework for changing the SQL so that ,

    1. Each sub query references relevant Main query field.
    2. Avoid using Group by (not needed in sub squery referring main query)

    Also feel free to provide the tes case as suggested by Tyro.