Re: IF Statement help in SQL

Lucy Chew wrote:

> Hi,
>
> Can anyone please inform on how i can use an IF statement in the
> following code to bring in Addr_Country field but only when the
> address4 is blank (and input into were address4 should b, had it not
> been blank)- if not blank i dont want it to be brought in, i want it
> to be left as address4.
>
> SET SERVEROUT ON
> SET LINESIZE 1000
> SET TRIMSPOOL ON
>
> SPOOL H:\payaddrupdate_mc.prn
>
> DECLARE
>
> CURSOR c_main IS
> SELECT apps.payroll_area
> ,apps.employee_number
> ,apps.address1
> ,apps.address2
> ,apps.address3
> ,apps.address4
> ,apps.ADDR_COUNTRY
> ,apps.postcode
> FROM apps.xxhrs_person_v apps
> where apps.PER_PA_LAST_UPDATE_DATE Between
> TO_DATE('30-OCT-2003', 'DD-MON-YYYY') AND TO_DATE('27-NOV-2003
> 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
> and apps.payroll_area is not null
> and apps.employee_number is not null
> order by apps.payroll_area,apps.employee_number;
>
> BEGIN
>
> DBMS_OUTPUT.ENABLE(10000000);
>
> <<main>>
> FOR r_per IN c_main LOOP
>
> DBMS_OUTPUT.PUT_LINE(RPAD(r_per.payroll_area, 3) ||
> RPAD(r_per.employee_number, 17) || '141' || RPAD(r_per.address1,50));
> DBMS_OUTPUT.PUT_LINE(RPAD(r_per.payroll_area, 3) ||
> RPAD(r_per.employee_number, 17) || '142' || RPAD(r_per.address2,50));
> DBMS_OUTPUT.PUT_LINE(RPAD(r_per.payroll_area, 3) ||
> RPAD(r_per.employee_number, 17) || '143' || RPAD(r_per.address3,50));
> DBMS_OUTPUT.PUT_LINE(RPAD(r_per.payroll_area, 3) ||
> RPAD(r_per.employee_number, 17) || '144' || RPAD(r_per.address4,50));
> DBMS_OUTPUT.PUT_LINE(RPAD(r_per.payroll_area, 3) ||
> RPAD(r_per.employee_number, 17) || '145' ||
> RPAD(r_per.addr_country,50));
> DBMS_OUTPUT.PUT_LINE(RPAD(r_per.payroll_area, 3) ||
> RPAD(r_per.employee_number, 17) || '14A' || RPAD(r_per.postcode,50));
>
> END LOOP main;
>
> END;
> /
>
> SPOOL OFF
>
>
>
> Regards
>
> Lucy Chew

Look up NVL and netiquette - only answered in c.d.o.server

-- 
Regards, Frank van Bortel