Sitemap

Amicuk Programming Answers

'alias required in SELECT..'  - Why can't I see this?

2015-10-11   Views:0

Advertisement

Please help I'm going mad here! The error message is: ERROR at line 1: ORA-06550: line 113, column 37: PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names ORA-06550: line 113, column 37: PL/SQL: Item ignored ORA-06550:

Please help I'm going mad here!
The error message is:
ERROR at line 1:
ORA-06550: line 113, column 37:
PLS-00402: alias required in SELECT list of cursor to avoid duplicate column
names
ORA-06550: line 113, column 37:
PL/SQL: Item ignored
ORA-06550: line 137, column 13:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 136, column 7:
PL/SQL: SQL Statement ignored
..Yet I disagree.. I cant see any 2 fields called the same.
(FYI the cursor it speaks of is called 'c_check_employee').
And the code.
/* Formatted on 2009/02/27 15:35 (Formatter Plus v4.8.7) */
SET serveroutput ON SIZE 1000000 FORMAT WRAPPED
SET verify OFF
SET feedback OFF
DECLARE
-- Debugging/error handling
-- Work variables
   p_emp_number                     VARCHAR2 (14);
   v_rec_cnt                        NUMBER                          := 0;
   insert_flag                      VARCHAR2 (8);
   l_validate                       BOOLEAN                     DEFAULT FALSE;
   l_obj                            NUMBER;
   l_datetrack_update_mode          VARCHAR2 (30)             := 'CORRECTION';
   l_assignment_sequence            NUMBER;
   l_name_combination_warning       BOOLEAN                         := FALSE;
   l_assign_payroll_warning         BOOLEAN                         := FALSE;
   l_org_now_no_manager_warning     BOOLEAN;
   l_other_manager_warning          BOOLEAN;
   l_spp_delete_warning             BOOLEAN;
   l_entries_changed_warning        VARCHAR2 (30);
   l_tax_district_changed_warning   BOOLEAN;
   l_person_id                      NUMBER;
   l_assignment_id                  NUMBER;
   l_special_ceiling_step_id        NUMBER;
   l_per_effective_end_date         DATE
                                    := TO_DATE ('11-Jul-2049', 'DD-MON-YYYY');
   l_people_group_id                NUMBER;
   l_group_name                     VARCHAR2 (30);
   l_assignment_number              VARCHAR2 (35);
   l_effective_end_date             DATE
                                    := TO_DATE ('11-Jul-2049', 'DD-MON-YYYY');
   l_date                           DATE                           := SYSDATE;
   ip_p_address_id                  per_addresses.address_id%TYPE;
   ip_p_object_version_number       NUMBER;
   ip_p_party_id                    per_addresses.party_id%TYPE;
   l_per_object_version_number      NUMBER;
   l_asg_object_version_number      NUMBER;
   l_full_name                      VARCHAR2 (240);
   l_per_comment_id                 NUMBER;
   l_per_effective_start_date       DATE;
   l_concatenated_segments          VARCHAR2 (12);
   l_soft_coding_keyflex_id         NUMBER;
   l_comment_id                     NUMBER;
   l_no_managers_warning            BOOLEAN;
-- Get employee details info from work table
   CURSOR get_employee_details
   IS
      SELECT std_validate, std_person_id, std_assignment_id, std_hire_date,
             std_business_group_id, std_last_name, std_sex,
             std_date_of_birth, std_email_address, std_employee_number,
             std_first_name, std_marital_status, std_middle_names,
             std_nationality, std_title, std_national_identifier,
             std_address_line1, std_address_line2, std_address_line3,
             std_address_line4, std_post_code, std_telephone_1,
             std_position_id, std_job_id, std_location_id,
             std_organization_id, std_supervisor_id,
             std_default_code_comb_id, std_set_of_books_id, std_payroll_id,
             std_grade_id, std_pay_basis_id, std_assn_ovn
        FROM SU_TEMPLOYEE_DETAILS;
-- checks employee details info from PER_ALL_PEOPLE_F table
   CURSOR c_check_employee (p_emp_number VARCHAR2)
   IS
      SELECT paas.assignment_id, per.person_id, per.business_group_id,
             per.last_name, per.start_date, per.date_of_birth, per.email_address,
            per.employee_number, per.first_name, per.marital_status, per.middle_names,
     per.nationality, per.national_identifier, per.sex, per.title, padd.address_id,
     padd.primary_flag,padd.address_line1, padd.address_line2, padd.address_line3,
             padd.town_or_city, padd.postal_code, padd.telephone_number_1, padd.telephone_number_2,
     padd.telephone_number_3,paas.job_id,   paas.location_id,paas.organization_id, paas.assignment_type, paas.primary_flag, paas.supervisor_id,paas.default_code_comb_id,
paas.set_of_books_id,paas.assignment_number, paas.period_of_service_id,paas.object_version_number
        FROM per_all_people_f per,
             per_all_assignments_f paas,
             per_addresses padd
       WHERE per.employee_number = p_emp_number
         AND per.person_id = padd.person_id
         AND paas.person_id(+) = per.person_id;
   emp_rec                          c_check_employee%ROWTYPE;
-- Cursor retrieves latest Object Version Number from per_assignments_f table..
   CURSOR csr_ovn (cp_person_id IN per_all_people_f.person_id%TYPE)
   IS
      SELECT MAX (paas.object_version_number)
        FROM per_assignments_f paas, per_all_people_f per
       WHERE paas.person_id = per.person_id
         AND per.employee_number = paas.assignment_number
         AND per.person_id = cp_person_id;
BEGIN
-- Process each record in the work table
   FOR v_emp IN get_employee_details
   LOOP
-- determine whether customer already exists
      OPEN c_check_employee (v_emp.std_employee_number);
      FETCH c_check_employee
       INTO emp_rec;
      IF c_check_employee%NOTFOUND
      THEN
         insert_flag := 'I';
      ELSE
         insert_flag := 'X';
      END IF;
      CLOSE c_check_employee;
-- Obtain the most recent Object Version Number..
      OPEN csr_ovn (v_emp.std_person_id);
      FETCH csr_ovn
       INTO l_obj;
      --   IF csr_ovn%NOTFOUND     THEN        RAISE NO_DATA_FOUND;   END IF;
      CLOSE csr_ovn;
-- Create new PER_ALL_PEOPLE_F and PER_ADDRESSES record from
--            info in  table record
      IF insert_flag = 'I'
      THEN
         BEGIN                             -- Importing Employee Procedure --
            --      DBMS_OUTPUT.PUT (CHR (10));
            --      DBMS_OUTPUT.PUT_LINE ('Importing employees....  ');
            BEGIN
               Hr_Employee_Api.create_gb_employee
                  (p_validate                       => l_validate,
                   p_hire_date                      => v_emp.std_hire_date,
                   p_business_group_id              => v_emp.std_business_group_id,
                   p_date_of_birth                  => v_emp.std_date_of_birth,
                   p_email_address                  => v_emp.std_email_address,
                   p_first_name                     => v_emp.std_first_name,
                   p_middle_names                   => v_emp.std_middle_names,
                   p_last_name                      => v_emp.std_last_name,
                   p_sex                            => v_emp.std_sex,
                   p_ni_number                      => v_emp.std_national_identifier,
                   p_employee_number                => v_emp.std_employee_number,
                   p_person_id                      => l_person_id,
                   p_title                          => v_emp.std_title,
                   p_assignment_id                  => l_assignment_id,
                   p_per_object_version_number      => l_per_object_version_number,
                   p_asg_object_version_number      => l_asg_object_version_number,
                   p_per_effective_start_date       => l_per_effective_start_date,
                   p_per_effective_end_date         => l_per_effective_end_date,
                   p_full_name                      => l_full_name,
                   p_per_comment_id                 => l_per_comment_id,
                   p_assignment_sequence            => l_assignment_sequence,
                   p_assignment_number              => l_assignment_number,
                   p_name_combination_warning       => l_name_combination_warning,
                   p_assign_payroll_warning         => l_assign_payroll_warning
               Hr_Person_Address_Api.create_person_address
                  (p_validate                     => l_validate,
                   p_effective_date               => v_emp.std_hire_date,
                   p_pradd_ovlapval_override      => NULL,
                   p_validate_county              => NULL,
                   p_person_id                    => l_person_id,
                   p_primary_flag                 => 'Y',
                   p_style                        => 'GB_GLB',
--                p_date_from                    => v_emp.std_hire_date,
                   p_date_from                    => SYSDATE,
                   p_date_to                      => NULL,
                   p_address_line1                => v_emp.std_address_line1,
                   p_address_line2                => v_emp.std_address_line2,
                   p_address_line3                => v_emp.std_address_line3,
                   p_town_or_city                 => v_emp.std_address_line4,
                   p_postal_code                  => v_emp.std_post_code,
                   p_country                      => v_emp.std_nationality,
                   p_telephone_number_1           => v_emp.std_telephone_1,
                   p_party_id                     => ip_p_party_id,
                   p_address_id                   => ip_p_address_id,
                   p_object_version_number        => l_obj
                                                  --ip_p_object_version_number
               Hr_Assignment_Api.update_emp_asg_criteria
                  (p_validate                          => l_validate,
                   p_effective_date                    =>  l_date,    --   SYSDATE
                   p_datetrack_update_mode             => l_datetrack_update_mode,
                   p_assignment_id                     => l_assignment_id,
                   p_object_version_number             => l_obj,
                   p_organization_id                   => v_emp.std_organization_id,
                   p_location_id                       => v_emp.std_location_id,
                   p_job_id                            => v_emp.std_job_id,
                   p_position_id                       => v_emp.std_position_id,
                   p_special_ceiling_step_id           => l_special_ceiling_step_id,
                   p_effective_start_date              => l_date,
                   p_effective_end_date                => l_effective_end_date,    --IN/OUT
                   p_people_group_id                   => l_people_group_id, --IN/OUT
                   p_group_name                        => l_group_name,  --IN/OUT
                   p_org_now_no_manager_warning        => l_org_now_no_manager_warning,   --IN/OUT
              p_other_manager_warning         => l_other_manager_warning,   --IN/OUT
                   p_spp_delete_warning                => l_spp_delete_warning, --IN/OUT
                   p_entries_changed_warning           => l_entries_changed_warning,  --IN/OUT
                   p_tax_district_changed_warning      => l_tax_district_changed_warning  --IN/OUT
              Hr_Assignment_Api.update_emp_asg
                  (p_validate                    => l_validate,           -- in
                   p_effective_date              => l_date,
                   p_datetrack_update_mode       => 'CORRECTION',
                   p_assignment_id               => l_assignment_id,
                   p_object_version_number       => l_obj,
                   p_supervisor_id               => v_emp.std_supervisor_id, 
                   p_default_code_comb_id        => v_emp.std_default_code_comb_id,
                   p_set_of_books_id             => v_emp.std_set_of_books_id,
                   p_concatenated_segments       => l_concatenated_segments, -- out
                   p_soft_coding_keyflex_id      => l_soft_coding_keyflex_id,     -- out
                   p_comment_id                  => l_comment_id,       -- out
                   p_effective_start_date        => l_date,           -- out
                   p_effective_end_date          => l_effective_end_date,
                   p_no_managers_warning         => l_no_managers_warning,
                   p_other_manager_warning       => l_other_manager_warning
            --     DBMS_OUTPUT.PUT_LINE ('Employee No:'|| v_emp.std_employee_number|| '  imported successfully..' );
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE NO_DATA_FOUND;
                  --        DBMS_OUTPUT.PUT (CHR (10));
                  DBMS_OUTPUT.PUT_LINE (   'Ah, Employee:'
                                        || v_emp.std_employee_number
                                        || ' failed to load.. '
                                        || SQLERRM
            --      DBMS_OUTPUT.PUT (CHR (10));
            END;
         END;
         --        DBMS_OUTPUT.PUT (CHR (10));
         v_rec_cnt := v_rec_cnt + 1;
--         DBMS_OUTPUT.PUT_LINE (   'There were '|| v_rec_cnt|| '  records read in..');
-- End of customer related details
      END IF;
   END LOOP;
   COMMIT;
END;
EXIT;
many thanks..

The replay answer
Advertisement
Check primary_flag .... ;)
Regards.
Satyaki De.
Alex is Winner.... ;)
Edited by: Satyaki_De on Mar 2, 2009 5:33 PM

Go to See the other 5 answers

'alias required in SELECT..'  - Why can't I see this?

Category:DefaultRelease time:2015-10-11Views:130

Please help I'm going mad here! The error message is: ERROR at line 1: ORA-06550: line 113, column 37: PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names ORA-06550: line 113, column 37: PL/SQL: Item ignored ORA-06550:[More]

Alias required in SELECT list of cursors to avoid duplicate column names

Category:DefaultRelease time:-0001-11-30Views:130

I got some error messages while compiling the following. DECLARE      alert_id                              NUMBER;      CURSOR cur_search IS SELECT c.item_description,      d.name modelname,      SUM(b.quentaty),      SUM(b.balence)      FROM      i[More]

PLS-00402: alias required in SELECT list of cursor to avoid duplicate colum

Category:DefaultRelease time:-0001-11-30Views:130

Hello I need a little help with my PLSQL syntax. While the SQL in the select statement run well in a report when I change it to PLSQL 'FOR' statement it needs aliias(es). To that end, I not sure where and how many? Any help is very welcome Regards Pe[More]

Reg logic required for selection-screen.

Category:DefaultRelease time:-0001-11-30Views:130

Hi, i have one requirement on selection screen 2 radio button 1 for service 2 for account Parameter      FILE     LOCALFILE     Filename If the radiobutton ACCOUNT is selected the default name for file will be:      ‘Rev_acc_com_&system_time_stamp&[More]

Drop down required for select-options

Category:DefaultRelease time:-0001-11-30Views:130

drop down required for select-options as displayed in VT01N 'shipment type'.Hi Check out..this example data: i_value       TYPE vrm_values,         wa_value      LIKE LINE OF i_value. PARAMETERS :  p_field   TYPE dd03l-fieldname AS LISTBOX           [More]

Requiring checkbox selection...

Category:DefaultRelease time:-0001-11-30Views:130

I am working on a fairly straightforward form, which is located at http://www.escswa.org/insurance/GBEForm.pdf My supervisor would like to require a selection in the checkboxes of each section. (They would be required to select one of the boxes in th[More]

Warning message is required while selecting expired batch in WM

Category:DefaultRelease time:-0001-11-30Views:130

While picking expired batch in LT01, system is not giving any warning or error message... Batch Management is active...LOBM-VFDAT is part of material classification SELD is active on Warehouse and Storage Type Stock Removal Strategy is H I have check[More]

Requirement Class Selection

Category:DefaultRelease time:-0001-11-30Views:130

Hi We have a two company code say 1000 / 2000. in 1000 co code we produce FG by strategy 10(MTS) where as in co code 2000 we produce material FG by strategy 45 (MTO). now my qestn is : when i create Sales order for material which is in co code 1000 s[More]

Work Center Required on Selection Screen

Category:DefaultRelease time:-0001-11-30Views:130

Dear All, My client has required work center as selection criteria on usage decision screen. Thanks to all.Dear, Try to use the QE01, QE505, here maintain the required operation and work center and give the USage decision. Please check and revert. Th[More]

Requirement to select material and plant ?

Category:DefaultRelease time:-0001-11-30Views:130

Hi, i have below requirement . I have selection screen ..in which material number as selections option and plant as parameters. when ever user executes report i have store the material , plant , stlal(this field is in MAST table ) which are in select[More]

Urgent help on difficult programming  required -remove select statement

Category:DefaultRelease time:-0001-11-30Views:130

loop at zt_wbs.   clear   zt_cobrb.   refresh zt_cobrb. Select settlement rules   select distinct anln1 anln2 from cobrb          into corresponding fields of table zt_cobrb          where objnr = zt_wbs-objnr and                perbz = 'GES'       [More]

Hot
Hi Guys I am replicating a  contract  from  CRM  to R/3 ,  the  CRM contract will replicate  as debit  memo request / contract in r/3  with   u201Cnu201D line items  and these  line items are   populated with  profit centers  based on  plant and prod [More]
hi,         This is pretty urgent.Im not able to creatae delivery for a subcontracting purchase order and for a stock transport order of z document type. Is there any related configurations?I am getting the error "Selection criterium "Delivery c [More]
Hi, I am working a BPEL Build system (ANT) for the SOA Suite. Do we have any documentation on the <customize> activity? I am trying to use it, from the command prompt (not the developer prompt), but I fails to run if it encounters the <bpelc .... [More]
I have a 3 node RAC on Linux redhat. DB version is 11gr2. I want to know the steps to perform each node OS patch upgrade. I want to be sure I did right steps: 1). Node 1, stop crs and do the OS patch upgrade. 2). same steps for Node 2, 3. Is this rig [More]
I have a problem with my actions. on (release) {        //load Graphic Behavior       _root.mylocal.loadMovie("image1.jpg");           //End Behavior in loadmovie  "image1.jpg" I want to assign a variable with two different button. sam [More]
Hi, I have a third party systems which is sending xml files and i want to use HTTP on the sender side and RFC on the receiver side to connect to SAP. I want to know how can i send xml files from the third party system to HTTP adapter on the integrati [More]
Just installed FF 3.6.3 on new W7HomeP PC, exported Bookmarks as HTML from old W2KPC, imported as HTML. Bookmark separators are visible in FF on old PC, but in new installation, bookmarks pane is white background and there are no separators, but the [More]
I recently purchased an airport extreme to replace my older linksys wireless router. had a successful set up but can not get connected to the internet. for some reason, which I am sure is apparent to others, i am missing some needed info to connect t [More]
Please help me find theses pics my son sent    believetk, You've come to the right place for some assistance. You can view all the messages you've sent to your son by going to the messaging app, inbox and or sent box. Please let us know if additional [More]
how can i recover the contact list in my iphone 5It depends on where you backed it up! If it's in your iCloud account, simply go to Settings > iCloud and make sure you've turned Contacts "On". If it's synched to your iTunes on your computer, [More]