5 Replies Latest reply: Dec 27, 2012 9:27 AM by Frank Kulash RSS

    sqlplus query issue:customerID column appear twice

    981311
      I have tried in several ways to display a query where shows customer who have made more than 3 rentals from 01Nov to 31 Dec. However, for some reason the customerID column appear twice.

      ** I'm using 3 tables to get this result:

      select RentalAgreement.membershipcardid as "MCardID",
      customer.customerid,customer.cname,customer.address,
      membershipcard.customerid,invoice.rentc,RentalAgreement.DateAndTimeOfIssue,
      count(RentalAgreement.membershipcardid) as "NOfRents" from RentalAgreement,customer,membershipcard,invoice
      where rentalAgreement.membershipcardid=MembershipCard.MembershipCardID
      and customer.customerid = membershipcard.customerid
      and RentalAgreement.DateAndTimeOfIssue between '01-Nov-2012' and '31-Dec-2012'
      group by rentalAgreement.membershipcardid,MembershipCard.
      customerid,customer.cname,customer.address,invoice.rentc,customer.customerid,RentalAgreement.DateAndTimeOfIssue;


      MCardID CUSTOMERID CNAME ADDRES CUSTOMERID RENTC DATEANDTI
      ---------- ---------- ---------- ------ ---------- ---------- ---------
      NOfRents
      ----------
      107 27 EDuois N151AS 27 60 11-NOV-12
      5

      108 28 WDuois N151AS 28 60 19-NOV-12
      5

      101 22 ALuois N193AS 22 60 22-NOV-12
      5


      MCardID CUSTOMERID CNAME ADDRES CUSTOMERID RENTC DATEANDTI
      ---------- ---------- ---------- ------ ---------- ---------- ---------
      NOfRents
      ----------
      101 22 ALuois N193AS 22 60 21-NOV-12
      5

      101 22 ALuois N193AS 22 60 20-NOV-12
      5

      101 22 ALuois N193AS 22 60 24-NOV-12
      5


      MCardID CUSTOMERID CNAME ADDRES CUSTOMERID RENTC DATEANDTI
      ---------- ---------- ---------- ------ ---------- ---------- ---------
      NOfRents
      ----------
      109 30 ADDuis N151AS 30 60 03-DEC-12
      5
      Thanks for your time.
      Im not sure if this query seems to be logic, I would appreciate any online guide for pl/sql
        • 1. Re: sqlplus query issue:customerID column appear twice
          Frank Kulash
          Hi,

          Welcome to the forum!
          978308 wrote:
          I have tried in several ways to display a query where shows customer who have made more than 3 rentals from 01Nov to 31 Dec. However, for some reason the customerID column appear twice.
          You're SELECTing two columns that are both called customerid. If you format your code, this will be easier to see:
          SELECT        RentalAgreement.membershipcardid          AS "MCardID"
          ,       customer.customerid               -- 1st customerid
          ,       customer.cname,customer.address
          ,       membershipcard.customerid          -- 2nd customerid
          ...
          In the WHERE clause, you're saying you only want to see rows where those two are the same.
          If the query is not being used as a sub-query or a view, then column names don't have to be distinct.
          ** I'm using 3 tables to get this result: ...
          Actually, you have 4 tables in the FROM clause.
          ... from RentalAgreement,customer,membershipcard,invoice
          where rentalAgreement.membershipcardid=MembershipCard.MembershipCardID
          and customer.customerid = membershipcard.customerid
          and RentalAgreement.DateAndTimeOfIssue between '01-Nov-2012' and '31-Dec-2012'
          group by rentalAgreement.membershipcardid, ...
          Again, this is easier to see if you format your code:
          ...
          FROM       RentalAgreement     -- 1st table
          ,       customer          -- 2nd
          ,       membershipcard     -- 3rd          -
          ,       invoice          -- 4th
          WHERE       rentalAgreement.membershipcardid     = MembershipCard.MembershipCardID
          AND       customer.customerid               = membershipcard.customerid
          AND       RentalAgreement.DateAndTimeOfIssue     BETWEEN TO_DATE ( '01-Nov-2012' 
                                                    , 'DD-Mon-YYYY'
                                                  )
                                          AND      TO_DATE ( '31-Dec-2012'
                                                  , 'DD-Mon-YYYY'
                                                  )
          GROUP BY  rentalAgreement.membershipcardid
          ...
          The invoice table is not mentioned in the WHERE clause. That means every row in the invoice table will be joined to every row in the result set formed by joining the other 3 tables. This may cause some rows to be duplicated.

          What is the datataype of DateAndTimeOfIssue? If it's a DATE, then don't compare it to VARCHAR2 values, such as '01-Nov-2012'. Compare DATEs to other DATEs, such as the DATEs returned by the TO_DATE function.
          Im not sure if this query seems to be logic,
          It looks like a step in the right direction, though without seeing some sample data and the results you want from that data, it's impossible to say for sure.
          If you're looking for cutomers that have at least 3 rentals, then I would expect to see the literal 3 somewhere in the code (or maybe the literal 2, since "more than 2" can be substituted for "at least 3"). Perhaps this was only an early draft of the query.
          I would appreciate any online guide for pl/sql
          You don't need any PL/SQL for this problem; plain old SQL is good enough.
          Get a good grasp of SQL first, before trying to use PL/SQL.


           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}

          Edited by: Frank Kulash on Dec 23, 2012 9:35 PM
          • 2. Re: sqlplus query issue:customerID column appear twice
            981311
            Hi
            Thank you for your answer. (I'm suing Oracle you're using (e.g. 11.2.0.1.0).
            sorry my question it wasnt not clear since I didn't attached my tables .
            As you suggested It is not necessary to use plsql, I tried to use the logic of algebra and old sql to solve query number 2: /* query No2
            produce a list of customers who have made more than three scotter rentals in the past 2 months
            show appropiate customer and rental agreement details*/

            However, I found many errors maybe because I my class diagram wasnt properly completed, So I decided to used plsql to get the result.
            It is true my query is incomplete because first of all I wanted to delete duplicate columns then I will work on number of ocurrences.

            If I delete and customer.customerid = membershipcard.customerid , sql will show 500 customers, the repetition is even higher.


            /* create EScotter*/
            create table EScotter
            (
            EScotterID number (5),
            InsuranceID number(5),
            Manufacturer varchar (10),
            ModelName varchar(10),
            Mileage number(5),
            speedLimit number(5),
            drivingRange number(5),
            BatteryReacharged number (3),
            constraint EScotter_pk primary key (EScotterID),
            constraint EScotter_fk foreign key (InsuranceID)references Insurance
            )
            ;
            -- if a BatteryReacharged is required. boolean condition 0 = no 1 = yes. --

            insert into EScotter values (1,401,'honda','honda1',10,30,60,1);
            insert into EScotter values (2,401,'honda','honda2',10,30,60,1);
            insert into EScotter values (3,401,'honda','honda3',10,30,60,1);
            insert into EScotter values (4,401,'suzuki','s1',10,30,60,1);
            insert into EScotter values (5,401,'suzuki','s2',10,30,60,1);

            /* create TrackingDevice*/

            create table TrackingDevice
            (
            TrackingDeviceID varchar(5),
            EScotterID number(5),
            LossTheftEscooter number (3),
            constraint TrackingDevice_pk primary key (TrackingDeviceID),
            constraint TrackingDevice_fk foreign key (EScotterID)references EScotter
            )
            ;
            -- If a Escotter is Loss or Theft boolean condition 0 = no 1 = yes /

            insert into TrackingDevice values ('D1',1,0);
            insert into TrackingDevice values ('D2',2,0);
            insert into TrackingDevice values ('D3',3,0);
            insert into TrackingDevice values ('D4',4,0);
            insert into TrackingDevice values ('D5',5,0);

            /* create RentalStore*/

            create table RentalStore
            (
            RentalStoreID number,
            StoreName varchar(5),
            Location varchar(5),
            constraint RentalStore_pk primary key (RentalStoreID)
            )
            ;

            insert into RentalStore values (100,'Wtrl','City');
            insert into RentalStore values (100,'Wtrlo','City');
            insert into RentalStore values (200,'Archw','North');
            insert into RentalStore values (300,'Acton','WestL');
            insert into RentalStore values (500,'EastH','EastL');

            --------------------------------------------------------***
            /* create Staff table*/
            /* JobType: number 1 for AdminOfficer, 2 for DeskClerk and 3 for Engineer*/


            create table Staff
            (
            StaffID number,
            RentalStoreID number,
            StaffType number,
            constraint Staff_pk primary key (StaffID),
            constraint Staff_fk foreign key (RentalStoreID)references RentalStore
            )
            ;
            ----------------------------------------------------------***

            insert into Staff values (1000,100,1);
            insert into Staff values (1001,100,1);
            insert into Staff values (3005,200,2);
            insert into Staff values (3007,300,2);
            insert into Staff values (3010,500,3);


            --------------------------------------------------------------

            /* create table Customer, dbo= date of birth */
            create table Customer
            (
            CustomerID number(5),
            DBO date not null,
            Passport varchar(9),
            Address varchar (6),
            CreditCarN Number(16),
            CName varchar (10),
            constraint Customer_pk primary key (CustomerID)
            )
            ;

            insert into Customer values(21,to_date('19960725','YYYYMMDD'),'UK2000','N183AS',8883384841,'SLuois');
            insert into Customer values(22,to_date('19920825','YYYYMMDD'),'UK2001','N193AS',988338482,'ALuois');
            insert into Customer values(23,to_date('19910225','YYYYMMDD'),'UK2002','N113AS',6883384843,'BLuois');
            insert into Customer values(24,to_date('19890225','YYYYMMDD'),'UK20003','N143AS',5883384844,'CLuois');
            insert into Customer values(25,to_date('19850815','YYYYMMDD'),'UK2004','N153AS',4883384845,'DLuois');
            insert into Customer values(26,to_date('19850815','YYYYMMDD'),'UK2031','N151AS',4883384845,'wDuois');
            insert into Customer values(27,to_date('19850815','YYYYMMDD'),'UK2021','N151AS',4883384845,'fDuois');
            insert into Customer values(28,to_date('19850815','YYYYMMDD'),'UK2001','N151AS',4883384845,'WDuois');
            insert into Customer values(29,to_date('19850815','YYYYMMDD'),'UK2021','N151AS',4883384845,'QDuois');
            insert into Customer values(30,to_date('19850815','YYYYMMDD'),'UK2111','N151AS',4883384845,'ADDuis');
            /* query No1 */
            select Customer.Customerid,Customer.cname,MembershipCard.MembershipCardID
            from MembershipCard,customer
            where Customer.Customerid=MembershipCard.CustomerID;

            /* create table MembershipCard = customer mememberhsip card to rent a scotter
            RegistrationFee = pay to become a member, DuplicateFee =lost/stolen membership card fee */

            create table MembershipCard
            (
            MembershipCardID number(5),
            CustomerID number(5),
            RegistrationFee number(9,2),
            DuplicateFee number(9,2),
            constraint MembershipCard_pk primary key (MembershipCardID),
            constraint MembershipCard_fk foreign key (CustomerID)references Customer
            )
            ;
            insert into MembershipCard values(100,21,150,0);
            insert into MembershipCard values(101,22,150,0);
            insert into MembershipCard values(102,23,150,0);
            insert into MembershipCard values(103,24,150,60);
            insert into MembershipCard values(104,25,150,60);
            insert into MembershipCard values(105,26,150,60);
            insert into MembershipCard values(106,27,150,60);
            insert into MembershipCard values(107,28,150,60);
            insert into MembershipCard values(108,29,150,60);
            insert into MembershipCard values(109,30,150,60);

            /* create table RentalAgreement = scotter Rental Agreement
            MembershipCardID = customer membership card ID, StaffID= staff involved in the rental agreement,
            DateAndTimeOfIssue = scotter Date And Time Of Issuedate, DueDateAndTimeofReturn = scotter Date And Time Of Issuedate*/

            create table RentalAgreement
            (
            RentalAgreementID number(5),
            EScotterID number(5),
            MembershipCardID number(5),
            StaffID number(5),
            DateAndTimeOfIssue date not null,
            DueDateAndTimeofReturn date not null,
            constraint RentalAgreement_pk primary key (RentalAgreementID),
            constraint RentalAgreement_fk1 foreign key (MembershipCardID)references MembershipCard,
            constraint RentalAgreement_fk2 foreign key (EScotterID)references EScotter,
            constraint RentalAgreement_fk3 foreign key (StaffID)references Staff
            )
            ;
            insert into RentalAgreement values (10001,1,100,1000, TO_DATE('11-Jul-2012 09:21:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('11-Jul-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10002,1,101,1000, TO_DATE('12-Jul-2012 10:11:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('12-Jul-2012 19:01:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10003,3,101,1000, TO_DATE('13-Jul-2012 10:31:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('13-Jul-2012 19:02:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10004,3,103,1000, TO_DATE('14-Jul-2012 11:21:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('14-Jul-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10005,5,104,1000, TO_DATE('15-Jul-2012 11:41:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('16-Jul-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10006,5,105,1000, TO_DATE('16-Sep-2012 11:41:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('16-Sep-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10007,1,106,1000, TO_DATE('20-Sep-2012 11:41:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('20-Sep-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10008,1,102,1000, TO_DATE('11-Nov-2012 11:41:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('11-Nov-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10009,2,101,1000, TO_DATE('19-Nov-2012 11:41:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('19-Nov-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10011,2,101,1000, TO_DATE('20-Nov-2012 11:41:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('20-Nov-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10012,1,101,1000, TO_DATE('21-Nov-2012 11:41:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('21-Nov-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10013,4,101,1000, TO_DATE('22-Nov-2012 11:41:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('22-Nov-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));
            insert into RentalAgreement values (10014,5,101,1000, TO_DATE('24-Nov-2012 11:41:21', 'DD-Mon-YYYY HH24:MI:SS'),TO_DATE('24-Nov-2012 19:10:21', 'DD-Mon-YYYY HH24:MI:SS'));

            /* query No2
            produce a list of customers who have made more than three scotter rentals in the past 2 months
            show appropiate customer and rental agreement details*/
            select RentalAgreement.membershipcardid as "MemberID",
            customer.customerid,
            customer.cname,
            customer.address,
            membershipcard.customerid,
            RentalAgreement.DateAndTimeOfIssue,
            RentalAgreement.EScotterID,
            count(RentalAgreement.membershipcardid) as "NumberOfRent"
            from RentalAgreement,customer,membershipcard
            where rentalAgreement.membershipcardid=MembershipCard.MembershipCardID
            and customer.customerid = membershipcard.customerid
            and RentalAgreement.DateAndTimeOfIssue between '01-Nov-2012' and '31-Dec-2012'
            group by rentalAgreement.membershipcardid,
            MembershipCard.customerid,
            customer.cname,customer.address,
            RentalAgreement.EScotterID,
            customer.customerid,
            RentalAgreement.DateAndTimeOfIssue;

            /* create Staff Invoice*/
            /* InvID =inoviceid, RentalAgreementID= scotter rental id, InDate=invoice day,RentC= rent fee,
            RepC= scotter repair fee, LateRC= late return scotter fee, Disc= discount*/
            create table Invoice (
            InvID number(5),
            RentalAgreementID number(5),
            InDate date not null,
            RentC number,
            RepC number ,
            LateRC number,
            Disc number (5,2),
            Tax number,
            constraint Invoice_pk primary key (InvID),constraint Invoice_fk foreign key (RentalAgreementID)references RentalAgreement);

            insert into Invoice values (201,10001,to_date('20120711','YYYYMMDD'),60,20,0,0.5,0.15);
            insert into Invoice values (202,10002,to_date('20120712','YYYYMMDD'),60,20,0,0.5,0.15);
            insert into Invoice values (203,10003,to_date('20120713','YYYYMMDD'),60,50,0,0.5,0.15);
            insert into Invoice values (204,10004,to_date('20120714','YYYYMMDD'),60,10,0,0.5,0.15);
            insert into Invoice values (205,10005,to_date('20120716','YYYYMMDD'),60,40,200,0.5,0.15);

            Edited by: 978308 on Dec 25, 2012 6:32 AM

            Edited by: 978308 on Dec 25, 2012 6:34 AM
            • 3. Re: sqlplus query issue:customerID column appear twice
              Frank Kulash
              Hi,
              978308 wrote:
              Hi
              Thank you for your answer. (I'm suing Oracle you're using (e.g. 11.2.0.1.0).
              sorry my question it wasnt not clear since I didn't attached my tables .
              As you suggested It is not necessary to use plsql, I tried to use the logic of algebra and old sql to solve query number 2: /* query No2
              produce a list of customers who have made more than three scotter rentals in the past 2 months
              show appropiate customer and rental agreement details*/

              However, I found many errors maybe because I my class diagram wasnt properly completed, So I decided to used plsql to get the result.
              It is true my query is incomplete because first of all I wanted to delete duplicate columns then I will work on number of ocurrences.

              If I delete and customer.customerid = membershipcard.customerid , sql will show 500 customers, the repetition is even higher.
              The condition "customer.customerid = membershipcard.customerid" in the WHERE clause looks right; you don't have to change that part.
              The SELECT clause determines what columns appear in the result set. If you only want customerid to appear in one column, then only include one customerid column in the SELECT clause. There need not be any connection between the SELECT clause and the WHERE clause; it's perfectly okay to use a column in the SELECT clause but not the WHERE clause, and it's perfectly okay to use a column in the WHERE clause but not the SELECT clause.
              ...
              /* query No2
              produce a list of customers who have made more than three scotter rentals in the past 2 months
              show appropiate customer and rental agreement details*/
              Once again, please format your code, and use \
               tags when you post it here to preserve the spacing.  It's hard to read informatted queries like this, and therefore it's hard to debug them.
              See the forum FAQ {message:id=9360002}
              
              select RentalAgreement.membershipcardid as "MemberID",
              customer.customerid,
              customer.cname,
              customer.address,
              membershipcard.customerid,
              RentalAgreement.DateAndTimeOfIssue,
              RentalAgreement.EScotterID,
              count(RentalAgreement.membershipcardid) as "NumberOfRent"
              from RentalAgreement,customer,membershipcard
              where rentalAgreement.membershipcardid=MembershipCard.MembershipCardID
              and customer.customerid = membershipcard.customerid
              and RentalAgreement.DateAndTimeOfIssue between '01-Nov-2012' and '31-Dec-2012'
              Once again, don't compare a DATE (such as RentalAgreement.DateAndTimeOfIssue) to a VARCHAR2 (such as '01-Nov-2012').  You're using TO_DATE correctly in your INSERT statements; use TO_DATE in the query, too.
              group by rentalAgreement.membershipcardid,
              MembershipCard.customerid,
              customer.cname,customer.address,
              RentalAgreement.EScotterID,
              customer.customerid,
              RentalAgreement.DateAndTimeOfIssue;
              "GROUP BY a, b, c, d" means that each row of output will represent a distinct combination of columns a, b, c and d, and aggregate functions will operate on the whole group.  In the query above, that means count(RentalAgreement.membershipcardid) will show the number of rows that had a value for membershipcardid for each combination of the 6 columns listed in the GROUP BY clause, including custiomerid and dateandtimeofissue.  That means that rentals for each customer *and date* will be counted separately.  Look at the output you're getting from query 2:
              Member CUSTOMER CUSTOMER DATEANDTIME ESCOTTER Number
              ID ID CNAME ADDRES ID OFISSUE ID OfRent
              ------ -------- ---------- ------ -------- ----------- -------- ------
              102 23 BLuois N113AS 23 11-Nov-2012 1 1
              101 22 ALuois N193AS 22 21-Nov-2012 1 1
              101 22 ALuois N193AS 22 24-Nov-2012 5 1
              101 22 ALuois N193AS 22 20-Nov-2012 2 1
              101 22 ALuois N193AS 22 22-Nov-2012 4 1
              101 22 ALuois N193AS 22 19-Nov-2012 2 1
              The count is always 1.  That is, customer 22 did have 5 rentals, but only 1 rental per day.
              You haven't posted the results you want from the same data.  I'm guessing you want something like this:
              Member CUSTOMER DATEANDTIME ESCOTTER Number
              ID ID CNAME ADDRES OFISSUE ID OfRent
              ------ -------- ---------- ------ ----------- -------- ------
              101 22 ALuois N193AS 19-Nov-2012 2 5
              101 22 ALuois N193AS 20-Nov-2012 2 5
              101 22 ALuois N193AS 21-Nov-2012 1 5
              101 22 ALuois N193AS 22-Nov-2012 4 5
              101 22 ALuois N193AS 24-Nov-2012 5 5
              showing that that customer 22 had 5 rentals in the period of interest.  That is, you *do* want to combine all rows for a customer when COUNTing, but you *don't* want to combine the rows when displaying.  The aggregate GROUP BY combines the rows, so how can you still get the details (such as date and scooter ID) for each separate rental?  One way is to do a GROUP BY just to get the customerid and COUNT, and use that in an IN subquery (or maybe a join), where the main query does not use GROUP BY.
              
              If this is a school assignment, I don't want to ruin it for you, so I'll use an example with the scott.dept and emp tables.  Say we want to find which departments have mor than 1 CLERK working in them.  One way to do that is:
              SELECT     *
              FROM     scott.dept
              WHERE     deptno     IN (
                        SELECT deptno
                        FROM      scott.emp
                        WHERE      job     = 'CLERK'
                        GROUP BY     deptno
                        HAVING     COUNT (*)     > 1
                        )
              ;
              Output:
              DEPTNO DNAME LOC
              ------ -------------- -------------
              20 RESEARCH DALLAS
              Another, com pletely different way would be to use the analytic COUNT function, not the aggregate function.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
              • 4. PLS-00201: identifier 'REPORT_CURSOR' must be declared
                981311
                Hi than you for your help, I have another question regarding pl/sql/
                I'm using oracle 11.2.0. I'm trying to get a report of all repairs , so I put a condition if :
                3 WHERE REPAIRFEE > 1; -- then It will only display repair fee

                I got an error after exec report_cursor

                SQL> exec report_cursor;

                ERROR at line 1:
                ORA-06550: line 1, column 7:
                PLS-00201: identifier 'REPORT_CURSOR' must be declared
                ORA-06550: line 1, column 7:
                PL/SQL: Statement ignored

                ---------PL/PSQL-----------------------------

                SQL>
                1 set serveroutput on DECLARE CURSOR report_cursor IS
                2 SELECT INVID, REPAIRFEE,INVOICEDA,REPAIRDETAIL FROM INVOICE
                3 WHERE REPAIRFEE > 1;
                4 I_INVID INVOICE.INVID%TYPE;
                5 I_REPAIRFEE INVOICE.REPAIRFEE%TYPE;
                6 I_DATE INVOICE.INVOICEDA%TYPE;
                7 I_DETAIL INVOICE.REPAIRDETAIL%TYPE;
                8 BEGIN
                9 DBMS_OUTPUT.PUT_LINE('ID, Fee and Scooter Repaired Details');
                10 DBMS_OUTPUT.PUT_LINE('======================================');
                11
                12 OPEN report_cursor;
                13 LOOP
                14 FETCH report_cursor INTO I_INVID, I_REPAIRFEE,I_DATE,I_DETAIL;
                15 EXIT WHEN report_cursor%NOTFOUND;
                16 DBMS_OUTPUT.PUT_LINE (I_INVID || '| £ ' || I_REPAIRFEE||'|'||I_DATE||'|'||I_DETAIL);
                17 END LOOP;
                18 DBMS_OUTPUT.PUT_LINE('===================================');
                19 DBMS_OUTPUT.PUT_LINE('TOTAL INVOICE PROCESSED ' || report_cursor%ROWCOUNT);
                20 DBMS_OUTPUT.PUT_LINE('--- END OF REPORT ----');
                21 CLOSE report_cursor;
                22 END;
                23 /



                ***ID, Fee and Scooter Repaired Details***
                ***======================================***
                ***201| £ 60|11-NOV-12|breaks***
                ***202| £ 60|12-NOV-12|wheels***
                ***203| £ 60|15-NOV-12|grip tape***
                ***205| £ 60|30-NOV-12|chain***
                ***===================================***
                ***TOTAL INVOICE PROCESSED 4***
                ***--- END OF REPORT ----***

                PL/SQL procedure successfully completed.

                -------------------------error--------------------------------------------------------
                SQL> exec report_cursor;
                BEGIN report_cursor; END;
                *
                ERROR at line 1:
                ORA-06550: line 1, column 7:
                PLS-00201: identifier 'REPORT_CURSOR' must be declared
                ORA-06550: line 1, column 7:

                ----------------------Invoice Table--------------------------------------------------
                InvID number(5),
                RentalAgreementID number(5),
                RepairFee number,
                RepairDetail varchar (12)
                Invoiceda date,
                constraint Invoice_pk primary key (InvID),constraint Invoice_fk foreign key (RentalAgreementID)references RentalAgreement);

                insert into Invoice values (201,10001,60,'breaks', '11-Nov-2012');
                insert into Invoice values (202,10002,60,'wheels', '12-Nov-2012');
                insert into Invoice values (203,10003,60,'grip tape', '15-Nov-2012');
                insert into Invoice values (204,10004,null,null,null);
                insert into Invoice values (205,10005,60,'chain','30-Nov-2012');

                -----------------------------------------
                Again thank you for your help
                PL/SQL: Statement ignored
                • 5. Re: PLS-00201: identifier 'REPORT_CURSOR' must be declared
                  Frank Kulash
                  Hi,
                  978308 wrote:
                  Hi than you for your help, I have another question regarding pl/sql/
                  When you have a new, separate question, then you should start a new, separate thread. This thread has 4 replies, and is marked as "Answered"; most people who haven't already participated in this thread (and, in this case, that's just you and me) aren't going to start reading it now. You'll get much better response if you start a new thread.
                  I'm using oracle 11.2.0. I'm trying to get a report of all repairs , so I put a condition if :
                  3 WHERE REPAIRFEE > 1; -- then It will only display repair fee

                  I got an error after exec report_cursor

                  SQL> exec report_cursor;

                  ERROR at line 1:
                  ORA-06550: line 1, column 7:
                  PLS-00201: identifier 'REPORT_CURSOR' must be declared
                  ORA-06550: line 1, column 7:
                  PL/SQL: Statement ignored

                  ---------PL/PSQL-----------------------------
                  The line above shows just what the trouble is!
                  What follows is not just PL/SQL code. Like many scripts, this is a mixture of SQL*Plus and PL/SQL (and, unintentionally, pure SQL, too).
                  "SET SERVEROUTPUT ON" is a SQL*Plus command. Like all SQL*Plus commands, it is terminated by the end of the line. In this case, that means "DECLARE CURSOR report_cursor IS" is being taken as part of the SET command. When I run it, I get a SQL*Plus error message "SP2-0158: unknown SET option "declare"".
                  SQL>
                  1 set serveroutput on DECLARE CURSOR report_cursor IS
                  So the first line is ignored. Since you haven't started a PL/SQL block, lines 2-3 are taken as being a pure SQL query.
                  2 SELECT INVID, REPAIRFEE,INVOICEDA,REPAIRDETAIL FROM INVOICE
                  3 WHERE REPAIRFEE > 1;
                  4 I_INVID INVOICE.INVID%TYPE;
                  5 I_REPAIRFEE INVOICE.REPAIRFEE%TYPE;
                  6 I_DATE INVOICE.INVOICEDA%TYPE;
                  7 I_DETAIL INVOICE.REPAIRDETAIL%TYPE;
                  I'm not sure why lines 4-7 don't cause errors, since the DECLARE was ignored.
                  Starting from line 8, the script is being interpreted as PL/SQL: it looks like an anonymous block that doesn't have the optional DECLARE section.
                  8 BEGIN
                  9 DBMS_OUTPUT.PUT_LINE('ID, Fee and Scooter Repaired Details'); ...
                  Please format your code. It will help you as well as others.

                  All you have to do is start the DECLARE statement on a fresh line:
                  set serveroutput on 
                  
                  DECLARE 
                      CURSOR report_cursor IS
                              SELECT  INVID
                          ,         REPAIRFEE
                          ,        INVOICEDA 
                  ...