...
| Code Block | ||
|---|---|---|
| ||
SELECT
p.student_number,
p.personal_title,
p.first_name,
p.middle_name,
p.last_name,
p.suffix,
TRIM(COALESCE(ecd.property_name,'') || ' ' || COALESCE(ecd.street_subaddress,'')) AS postal_address1,
TRIM(COALESCE(ecd.street_number,'') || ' ' || COALESCE(ecd.street_name,'')) AS postal_address2,
ecd.city,
CASE
WHEN ecd.state_province_geo_id IS NULL THEN ecd.state
ELSE sg.geo_name
END AS state,
ecd.postal_code AS postal_state_code,
CASE
WHEN ecd.country_geo_id IS NULL THEN ''
ELSE cg.geo_name
END AS postal_country
FROM
person p
JOIN edu_contact_details ecd ON ecd.party_id = p.party_id AND ecd.role_type_id = 'RESIDENTIAL'
LEFT JOIN geo sg ON sg.geo_type_id = 'STATE' AND sg.geo_id = ecd.state_province_geo_id
LEFT JOIN geo cg ON cg.geo_type_id = 'COUNTRY' AND cg.geo_id = ecd.country_geo_id
WHERE TRUE |
No Data message
| Code Block |
|---|
<noData>
<band height="150" isSplitAllowed="true" >
<textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >
<reportElement
x="11"
y="108"
width="515"
height="38"
key="textField-23"/>
<box></box>
<textElement>
<font/>
</textElement>
<textFieldExpression class="java.lang.String"><![CDATA[$P{whereClauseDyn}]]></textFieldExpression>
</textField>
<staticText>
<reportElement
mode="Opaque"
x="11"
y="42"
width="518"
height="56"
forecolor="#000000"
backcolor="#FFFFFF"
key="staticText-4"/>
<box leftPadding="10" rightPadding="10"> <pen lineWidth="0.0" lineColor="#FFFFFF"/>
<topPen lineWidth="0.75" lineStyle="Solid" lineColor="#CC0033"/>
<leftPen lineWidth="0.75" lineStyle="Solid" lineColor="#CC0033"/>
<bottomPen lineWidth="0.75" lineStyle="Solid" lineColor="#CC0033"/>
<rightPen lineWidth="0.75" lineStyle="Solid" lineColor="#CC0033"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle">
<font/>
</textElement>
<text><![CDATA[The system was unable to find any records that satisfied the report criteria. Please check that there is at least one course enrolment with the status of enrolled before attempting to run the report again. ]]></text>
</staticText>
<staticText>
<reportElement
mode="Opaque"
x="11"
y="12"
width="518"
height="30"
forecolor="#FFFFFF"
backcolor="#FF0033"
key="staticText-5"/>
<box leftPadding="10" rightPadding="10"> <pen lineWidth="0.0" lineColor="#FFFFFF"/>
<topPen lineWidth="0.75" lineStyle="Solid" lineColor="#CC0033"/>
<leftPen lineWidth="0.75" lineStyle="Solid" lineColor="#CC0033"/>
<bottomPen lineWidth="0.75" lineStyle="Solid" lineColor="#CC0033"/>
<rightPen lineWidth="0.75" lineStyle="Solid" lineColor="#CC0033"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle">
<font pdfFontName="Helvetica-Bold" size="14" isBold="true"/>
</textElement>
<text><![CDATA[WARNING !]]></text>
</staticText>
</band>
</noData> |