V_QUERY_STUDENT
Provides support for querying on Student information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| KEYNAME | nvarchar(100) | Last name | |
| FIRSTNAME | nvarchar(50) | First name | |
| MIDDLENAME | nvarchar(50) | Middle name | |
| NICKNAME | nvarchar(50) | Nickname | |
| LOOKUPID | nvarchar(100) | yes | Student ID |
| SUFFIXCODEID_TRANSLATION | nvarchar(100) | yes | Suffix |
| TITLECODEID_TRANSLATION | nvarchar(100) | yes | Title |
| GENDER | nvarchar(7) | yes | Gender |
| BIRTHDATE | UDT_FUZZYDATE | Birth date | |
| DECEASEDDATE | UDT_FUZZYDATE | yes | Deceased date |
| ISINACTIVE | bit | Inactive | |
| DECEASED | bit | yes | Deceased |
| AGE | int | yes | Age |
| NAME | nvarchar(154) | yes | Name |
| WEBADDRESS | UDT_WEBADDRESS | Web address | |
| DONOTMAIL | bit | Do not mail | |
| DONOTEMAIL | bit | Do not email | |
| DONOTPHONE | bit | Do not phone | |
| PICTURE | varbinary | yes | Picture |
| PRIMARYBUSINESS_ID | uniqueidentifier | yes | Primary business ID |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/30/2010 11:35:39 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.8.2022.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_STUDENT AS
select
STUDENT.ID,
CONSTITUENT.ID as [CONSTITUENTID],
CONSTITUENT.KEYNAME,
CONSTITUENT.FIRSTNAME,
CONSTITUENT.MIDDLENAME,
CONSTITUENT.NICKNAME,
CONSTITUENT.LOOKUPID,
(select SUFFIXCODE.DESCRIPTION from dbo.SUFFIXCODE where SUFFIXCODE.ID = CONSTITUENT.SUFFIXCODEID) as [SUFFIXCODEID_TRANSLATION],
(select TITLECODE.DESCRIPTION from dbo.TITLECODE where TITLECODE.ID = CONSTITUENT.TITLECODEID) as [TITLECODEID_TRANSLATION],
CONSTITUENT.GENDER,
CONSTITUENT.BIRTHDATE,
DECEASEDCONSTITUENT.DECEASEDDATE,
CONSTITUENT.ISINACTIVE,
convert(bit, case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end) as DECEASED,
dbo.UFN_CONSTITUENT_GETAGE(CONSTITUENT.ID) as AGE,
CONSTITUENT.NAME,
CONSTITUENT.WEBADDRESS,
CONSTITUENT.DONOTMAIL,
CONSTITUENT.DONOTEMAIL,
CONSTITUENT.DONOTPHONE,
CONSTITUENT.PICTURE,
(select PRIMARYBUSINESS.RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP as PRIMARYBUSINESS where PRIMARYBUSINESS.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and PRIMARYBUSINESS.ISPRIMARYBUSINESS = 1 and CONSTITUENT.ISORGANIZATION = 0) as [PRIMARYBUSINESS_ID],
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
STUDENT.DATEADDED,
STUDENT.DATECHANGED,
STUDENT.TSLONG
from
dbo.STUDENT
inner join dbo.CONSTITUENT on CONSTITUENT.ID = STUDENT.ID
left join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = STUDENT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = STUDENT.CHANGEDBYID