SQL-028: Retrieve security question row(s)¶

Retrieves user security question records from EXT_USER_SECURITY_QUESTION joined to code tables for question text, with globalized text via a LEFT JOIN to EXT_USER_SEC_QUESTION_GCODE.

Classification¶

Field	Value
SQL ID	SQL-028
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`clsUserSecurityQuestion`
Criteria property	`RetrieveSQL` / `RetrieveSqlStatement`
Lifecycle method	`BeforeRetrieve`; `GetUserSecurityQuestionsByUserID`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:Culture`	Input	String	5	Culture code for globalized question text.
`:UserSecurityQuestionID`	Input	Decimal	N/A	Primary key (used by `BeforeRetrieve`).
`:UserID`	Input	Decimal	N/A	`EXT_USER_SEQ_NUM` (used by `GetUserSecurityQuestionsByUserID`).

Tables¶

Table	Access	Columns used
EXT_USER_SECURITY_QUESTION	SELECT	All columns
EXT_USER_SEC_QUESTION_CODE	JOIN	`CODE`, `QUESTION_TEXT`, `EXPIRATION_DATE`
EXT_USER_SEC_QUESTION_GCODE	LEFT JOIN	`CODE`, `CULTURE_CODE`, `QUESTION_TEXT`

Sample SQL¶

SELECT
    q.EXT_USER_SEC_QUESTION_SEQ_NUM AS UserSecurityQuestionID,
    q.EXT_USER_SEQ_NUM AS UserID,
    q.EXT_USER_SEC_QUESTION_CODE AS SecurityQuestionCode,
    CASE WHEN g.question_text IS NULL
         THEN cast(c.QUESTION_TEXT AS nvarchar2(200))
         ELSE g.question_text
    END AS Question,
    q.ANSWER_TEXT AS Answer,
    q.CREATE_USER AS CreateUser,
    q.CREATE_DATE AS CreateDate,
    q.UPDATE_USER AS UpdateUser,
    q.UPDATE_DATE AS UpdateDate,
    q.UPDATE_NUM AS UpdateNum,
    c.Expiration_date AS QuestionExpirationDate
FROM EXT_USER_SECURITY_QUESTION q
JOIN ext_user_sec_question_code c ON c.code = q.ext_user_sec_question_code
LEFT JOIN ext_user_sec_question_gcode g
    ON c.code = g.code AND upper(g.culture_code) = :Culture
WHERE q.EXT_USER_SEC_QUESTION_SEQ_NUM = :UserSecurityQuestionID
-- Or: WHERE q.EXT_USER_SEQ_NUM = :UserID (for collection retrieval)

Operations and processes¶

Security question management

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources¶

Business\SAIF.Business.External.Users\clsUserSecurityQuestion.vb