pears.NetGetVacancySiteDetails [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetGetVacancySiteDetails ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetGetVacancySiteDetails" IS 
{create FUNCTION pears."NetGetVacancySiteDetails"( IN pVacancyID char(20) DEFAULT null )
RETURNS LONG VARCHAR
DETERMINISTIC
BEGIN
	DECLARE siteAddress LONG VARCHAR;

    DECLARE siteName char(50);
    DECLARE addr1 char(40);
    DECLARE addr2 char(40);
    DECLARE addr3 char(40);
    DECLARE town char(30);
    DECLARE postcode char(20);

    Declare companyID char(20);

    SELECT v.siteName, v.addr1, v.addr2, v.addr3, v.town, v.postcode
    INTO siteName, addr1, addr2, addr3, town, postcode
    FROM vacancy as v
    WHERE v.vacancyID = pVacancyID;

    SET siteAddress = string(
        sitename,
        (IF isnull(addr1,'') != '' THEN (IF Sitename != '' THEN ', ' END IF)+addr1 END IF ),
        (IF isnull(addr2,'') != '' THEN ', ' + addr2 END IF ),
        (IF isnull(addr3,'') != '' THEN ', ' +addr3 END IF ),
        (IF isnull(Town,'') != '' THEN ', ' + town END IF ),
        (IF isnull(postcode,'') != '' THEN ', ' + postcode END IF )
    );

    IF siteAddress IS NULL THEN
        SELECT e.companyID
        INTO companyID
        FROM vacancy as v
            key join employment as e
        WHERE v.vacancyID = pVacancyID;

        SET siteAddress = GetCompanyAddressOnLine(companyID);
    END IF;

	RETURN siteAddress;
END

/* DOC
If the site address is blank then address of the company is used.

2019-06-12 ET Created function for IW-890
*/
}
</code>