• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Review Syntax as cannot locate problem

Hello,
I've writtenthis function but there seems to be an issue with the If statements and I cannot locate what this is for the life of me... If anyone is willing to take a look and review I would greatly appreciate that - the error that comes up is just that it underlines the (END fn_GetRegulateeInfo;) statement and  indicates that it is looking for an If ...:

FUNCTION fn_GetRegulateeInfo(v_DOSSIER_CO_ROLE_CODE IN CHAR)
 RETURN cur_RegulateeInfo IS
 v_regulatee cur_RegulateeInfo;
 v_carrierList integer;
 
  BEGIN
          --Deals with 'G' generators, 'R' receivors
          IF (V_DOSSIER_CO_ROLE_CODE <> 'C' AND V_DOSSIER_CO_ROLE_CODE <> 'F') THEN
         
            OPEN v_regulatee FOR
           
                SELECT DISTINCT CONCAT(CONCAT(dc.company_number, '_'),
                  dc.company_site_number) AS RegulateeID,
                  dc.LICENCE_REG_ID AS RegistrationNumber,
                  co.COMPANY_ENG_NAME AS CompanyName, 'Not Applicable', 'Not Applicable'
                FROM COMPANY co INNER JOIN DOSSIER_COMPANY dc ON co.company_number = dc.company_number
                  INNER JOIN COMPANY_SITE_CONTACT csc ON csc.company_number = dc.company_number
                WHERE dc.company_site_number = v_companySiteNumber
                  AND dc.company_number = v_companyNumber
                  AND dc.DOSSIER_CO_ROLE_CODE = v_DOSSIER_CO_ROLE_CODE
                  AND  dc.dos_hwmd_sys_entered_yr = v_dossierYear
                  AND dc.dossier_identifier = v_dossierID
                  AND dc.dossier_type_code = v_dossierType;
         
          ELSE IF V_DOSSIER_CO_ROLE_CODE = 'F' Then
         
              OPEN v_regulatee FOR
             
              SELECT DISTINCT (CASE wd.company_number
                                WHEN NULL Then
                                  CONCAT(CONCAT(dc.company_number, '_'), dc.company_site_number)
                                ELSE
                                     CONCAT(CONCAT(wd.company_number, '_'), wd.shipping_site_number)
                                END) AS RegulateeID,
                      dc.LICENCE_REG_ID AS RegistrationNumber,
                      co.COMPANY_ENG_NAME AS CompanyName, 'Not Applicable', 'Not Applicable'
              FROM dossier_company dc INNER JOIN dossier_waste dw
                ON dw.dos_hwmd_sys_entered_yr = dc.dos_hwmd_sys_entered_yr
                AND dw.dossier_identifier = dc.dossier_identifier
                AND dw.dossier_type_code = dc.dossier_type_code
                LEFT OUTER JOIN Waste_Destination wd
                ON wd.dos_hwmd_sys_entered_yr = dc.dos_hwmd_sys_entered_yr
                AND wd.dossier_identifier = dc.dossier_identifier
                AND wd.dossier_type_code = dc.dossier_type_code LEFT JOIN company co ON co.company_number = dc.company_number
              WHERE dc.DOSSIER_CO_ROLE_CODE = v_DOSSIER_CO_ROLE_CODE
                AND dc.dos_hwmd_sys_entered_yr = v_dossierYear
                AND dc.dossier_identifier = v_dossierID
                AND dc.dossier_type_code = v_dossierType;
         
           ELSE /*IF V_DOSSIER_CO_ROLE_CODE = 'C' Then*/ --This pertains to ONLY Carriers 'C'
     
                  SELECT Count(*) into v_CarrierList
                  FROM carrier_full_list
                  WHERE parent_company = v_companynumber;
                 

                      IF (v_carrierList > 0) THEN
                 
                          OPEN v_regulatee FOR
                         
                          --need to know if if is the child company number and site that is regulatee id
                          --need to know what to do if the license number is blank
                          --need to know what to do if there are multiple license numbers for same company
                            SELECT DISTINCT CONCAT(CONCAT(cfl.company_number, '_'),
                            cfl.company_site_number) AS RegulateeID,  
                              (SELECT DISTINCT cr.licence_reg_id
                                      FROM company_registration cr
                                      WHERE cr.company_number = cfl.company_number
                                      AND cr.company_site_number = cfl.company_site_number
                                      AND rownum = 1) AS RegistrationNumber,
                            ( SELECT co.COMPANY_ENG_NAME AS CompanyName
                                    FROM company co
                                    where cfl.company_number = co.company_number) AS CompanyName,
                            'Not Applicable', 'Not Applicable'
                            FROM carrier_full_list cfl INNER JOIN company_registration cr ON
                              cfl.company_number = cr.company_number INNER JOIN company co ON
                              co.company_number = cfl.company_number
                            WHERE  cfl.parent_company = v_companyNumber;
                   
                        ELSE
                   
                            OPEN v_regulatee FOR
                           
                                SELECT DISTINCT CONCAT(CONCAT(dc.company_number, '_'),
                                  dc.company_site_number) AS RegulateeID,
                                  dc.LICENCE_REG_ID AS RegistrationNumber,
                                  co.COMPANY_ENG_NAME AS CompanyName, 'Not Applicable', 'Not Applicable'
                                FROM COMPANY co INNER JOIN DOSSIER_COMPANY dc ON co.company_number = dc.company_number
                                INNER JOIN COMPANY_SITE_CONTACT csc ON csc.company_number = dc.company_number
                                WHERE dc.company_site_number = v_companySiteNumber
                                  AND dc.company_number = v_companyNumber
                                  AND dc.DOSSIER_CO_ROLE_CODE = v_DOSSIER_CO_ROLE_CODE
                                  AND  dc.dos_hwmd_sys_entered_yr = v_dossierYear
                                  AND dc.dossier_identifier = v_dossierID
                                  AND dc.dossier_type_code = v_dossierType;
                     
                          END IF;
            END IF;
       
      RETURN v_regulatee;
     
EXCEPTION
  WHEN OTHERS THEN
  v_ErrNumber := SQLCODE;
  v_ErrMsg := SUBSTR(SQLERRM(v_ErrNumber), 1, 200);
   
  sp_LogError('fn_GetNoticeOfficerDetails',v_ErrMsg, v_ErrNumber);
       
END fn_GetRegulateeInfo;
0
Mosquitoe
Asked:
Mosquitoe
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Every IF needs an END IF.  I count 3 IF and only two END IF.

Check out ELSIF instead of ELSE IF:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e25519/controlstatements.htm#LNPLS391
0
 
MosquitoeAuthor Commented:
This was probably the stupidest I have felt in a week...Thanks
0
 
slightwv (䄆 Netminder) Commented:
lol...  We have ALL been there!

Glad to help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now