Mosquitoe
asked on
pl/sql function syntax help
Hello,
Can anybody tell me why I get an error on the If?
FUNCTION fn_GetExporterRegulateeCon tact
RETURN cur_RegulateeContact IS
v_regulateeContact cur_RegulateeContact;
v_dossier_co_contact_name varchar2(60);
BEGIN
SELECT DOSSIER_CO_CONTACT_NAME into v_dossier_co_contact_name
FROM DOSSIER_COMPANY
WHERE DOS_HWMD_SYS_ENTERED_YR = v_dossieryear
AND DOSSIER_IDENTIFIER = v_dossierid
AND DOSSIER_TYPE_CODE = v_dossiertype
AND DOSSIER_CO_ROLE_CODE = 'G'
AND COMPANY_SITE_NUMBER = v_companySiteNumber
AND company_number = v_companyNumber;
OPEN v_regulateeContact FOR
IF (v_dossier_co_contact_name IS NOT NULL) OR (v_dossier_co_contact_name <> ' ') THEN
SELECT DISTINCT cs.contact_name AS FirstName, 'N/A' AS LastName,
cs.phone_number AS PhoneNumberText, cs.fax_number as FaxText, cs.email_address AS EmailText
FROM DOSSIER_COMPANY dc INNER JOIN COMPANY_SITE_CONTACT cs
ON dc.company_number = cs.company_number
AND dc.company_site_number = cs.company_site_number
WHERE cs.contact_name = v_dossier_co_contact_name
AND dc.dos_hwmd_sys_entered_yr = v_dossierYear
AND dc.dossier_identifier = v_dossierID
AND dc.dossier_type_code = v_dossierType
AND dc.DOSSIER_CO_ROLE_CODE = 'G';
ELSE
SELECT DISTINCT cs.contact_name AS FirstName, 'N/A' AS LastName,
cs.phone_number AS PhoneNumberText, cs.fax_number as FaxText, cs.email_address AS EmailText
FROM DOSSIER_COMPANY dc INNER JOIN COMPANY_SITE_CONTACT cs
ON dc.company_number = cs.company_number
AND dc.company_site_number = cs.company_site_number
WHERE cs.contact_name = v_dossier_co_contact_name
AND dc.dos_hwmd_sys_entered_yr = v_dossierYear
AND dc.dossier_identifier = v_dossierID
AND dc.dossier_type_code = v_dossierType
AND dc.DOSSIER_CO_ROLE_CODE = 'G';
END IF;
RETURN v_regulateeContact;
END fn_GetExporterRegulateeCon tact;
Can anybody tell me why I get an error on the If?
FUNCTION fn_GetExporterRegulateeCon
RETURN cur_RegulateeContact IS
v_regulateeContact cur_RegulateeContact;
v_dossier_co_contact_name varchar2(60);
BEGIN
SELECT DOSSIER_CO_CONTACT_NAME into v_dossier_co_contact_name
FROM DOSSIER_COMPANY
WHERE DOS_HWMD_SYS_ENTERED_YR = v_dossieryear
AND DOSSIER_IDENTIFIER = v_dossierid
AND DOSSIER_TYPE_CODE = v_dossiertype
AND DOSSIER_CO_ROLE_CODE = 'G'
AND COMPANY_SITE_NUMBER = v_companySiteNumber
AND company_number = v_companyNumber;
OPEN v_regulateeContact FOR
IF (v_dossier_co_contact_name
SELECT DISTINCT cs.contact_name AS FirstName, 'N/A' AS LastName,
cs.phone_number AS PhoneNumberText, cs.fax_number as FaxText, cs.email_address AS EmailText
FROM DOSSIER_COMPANY dc INNER JOIN COMPANY_SITE_CONTACT cs
ON dc.company_number = cs.company_number
AND dc.company_site_number = cs.company_site_number
WHERE cs.contact_name = v_dossier_co_contact_name
AND dc.dos_hwmd_sys_entered_yr
AND dc.dossier_identifier = v_dossierID
AND dc.dossier_type_code = v_dossierType
AND dc.DOSSIER_CO_ROLE_CODE = 'G';
ELSE
SELECT DISTINCT cs.contact_name AS FirstName, 'N/A' AS LastName,
cs.phone_number AS PhoneNumberText, cs.fax_number as FaxText, cs.email_address AS EmailText
FROM DOSSIER_COMPANY dc INNER JOIN COMPANY_SITE_CONTACT cs
ON dc.company_number = cs.company_number
AND dc.company_site_number = cs.company_site_number
WHERE cs.contact_name = v_dossier_co_contact_name
AND dc.dos_hwmd_sys_entered_yr
AND dc.dossier_identifier = v_dossierID
AND dc.dossier_type_code = v_dossierType
AND dc.DOSSIER_CO_ROLE_CODE = 'G';
END IF;
RETURN v_regulateeContact;
END fn_GetExporterRegulateeCon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
IF (v_dossier_co_contact_name
But I just realised that they are not consistent, may be one space or tw spaces or more - is there a better way that I can do that in one statement?