Amicuk Programming Answers

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

2015-10-11   Views:0


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
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
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
-- 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
      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
-- checks employee details info from PER_ALL_PEOPLE_F table
   CURSOR c_check_employee (p_emp_number VARCHAR2)
      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.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)
      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;
-- Process each record in the work table
   FOR v_emp IN get_employee_details
-- 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
         insert_flag := 'I';
         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'
         BEGIN                             -- Importing Employee Procedure --
            --      DBMS_OUTPUT.PUT (CHR (10));
            --      DBMS_OUTPUT.PUT_LINE ('Importing employees....  ');
                  (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
                  (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
                  (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
                  (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..' );
               WHEN OTHERS
                  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));
         --        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;
many thanks..

The replay answer
Check primary_flag .... ;)
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, 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 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 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]

I have a MacBook Pro and was wondering if the 60W MagSafe Power Adapter protects against power surges without being plugged into an additional surge protector?Nope.Read other 2 answers [More]
I use SAP 6.0 and I am having difficulty with the downloading of excel reports. Here is my issue. I run a report in SAP and save it locally as a xls file.  This seems fine but the reality is the file is not a true xls file When I go to open it using [More]
Hi All, I am working on RFC to JDBC scenario. I have one table in Oracle DB (like mara in sap). I wants to read whole data through JDBC adapter. I am doing following mapping: '1'         -  SELECT 'SELECT'    -  @action access <tablename> -  table ' [More]
OK so many websites and music sites do not work when I travel overseas. I want to rent movies via iTunes to watch on my laptop, but want to make sure that having a 'non-US' IP address won't prevent me from watching rented videos. Does my laptop have [More]
Hi, I'm using an iBook G4 running Mac OS X 10.3.9, and I have this weird problem with my AirPort Express connection to my speakers. Sometimes, but not always, if my speakers are on but my music is not playing, there is a loud static sound coming thro [More]
Hi, not sure this is the right forum category but here you go: I have a holding tree with more than 200 companies, and several layers. Each company is defined by: - a unique ID - a direct parent ID --> the company immediately above in the tree - an u [More]
Hi all. Hoping someone in the know can tell me whether SP06 is still due to be released this Friday? I know it got knocked back a week originally, so I need to re-plan everything for whenever the new date is. Also, are there any pre-release notes yet [More]
I am curious if the eDiscovery feature will search and find emails that use Entrust for encryption. Does anyone know if this is possible?Hi MichaelColley, Only option I'm aware is it supports IRM Encryption or MS tech encryption only. All other encry [More]
hi    what is the solution,when master data is sent to cube with out check marking ignore duplecate data records at infopackage level. thanks in advance.... points will be assignedNo error while loadng the cube. But when you are loading the master da [More]
It seems like my problems with itunes are never ending on my HP laptop with Windows Vista. Originally, itunes screwed up my disc drive, but then I downloaded the 64 bit version of it and that turned out ok. However, whenever I started itunes it says [More]