>I have an SQL statement that is built depending on which queue the user is coming from. So, let's say for the Open Item queue the criteria below is stored to a variable to use in the SELECT statement.
>
> STORE '(empty(stmt_date) or empty(fund_pos) or empty(h.source_num)) and empty(oob_amount) and empty(first_user)' TO itemtype
>
>So, for the reports, I want to display the reason associated with each of the 4 numbers listed above (if populated), otherwise display 'Not Defined'.
>
>I'm able to pull all the data using:
> SELECT CUSIP, ACCOUNT, FUND, FUND_ACCT, PHONENUM, FUNDNUM, DTOC(PREVRECN) AS Prev_Recn,;
> STR(PASTDUENUM) AS Ovr30_reas,;
> STR(OOB_NUM) AS oob_reason, STR(ACTION_NUM) AS act_taken,;
> STR(SOURCE_NUM) AS source_doc, DTOC(STMT_DATE) AS STMT_DATE, FUND_POS, CMS_POS, RR_AMOUNT,;
> OOB_AMOUNT, DTOC(OOB_DATE) AS OOB_DATE, DTOC(TAKEACT_DT) AS TAKEACT_DT, CONTACT, CONT_NUM, OOB_NOTES, FIRST_USER,;
> DTOC(FIRST_DATE) AS FIRST_DATE, SEC_USER, DTOC(SEC_DATE) AS SEC_DATE, &age_date-PREVRECN AS age;
> FROM holdrecn h;
> WHERE &itemtype
>
>But, as you can tell, it doesn't give me the descriptions...so I'm trying to figure out how to link the descriptions...
>
>SELECT CUSIP, ACCOUNT, FUND, FUND_ACCT, PHONENUM, FUNDNUM, DTOC(PREVRECN) AS Prev_Recn, NVL(past_due.reas_desc,'') AS Ovr30_reas,;
> NVL(oobreason.OOB_desc,'') AS oob_reason, NVL(oobaction.OOB_desc,'') AS act_taken, NVL(sourcedoc.doc_recv,'') AS source_doc, DTOC(STMT_DATE) AS STMT_DATE, FUND_POS, CMS_POS, RR_AMOUNT,;
> OOB_AMOUNT, DTOC(OOB_DATE) AS OOB_DATE, DTOC(TAKEACT_DT) AS TAKEACT_DT, CONTACT, CONT_NUM, OOB_NOTES, FIRST_USER,;
> DTOC(FIRST_DATE) AS FIRST_DATE, SEC_USER, DTOC(SEC_DATE) AS SEC_DATE, &age_date-PREVRECN AS age;
> FROM holdrecn h, oobreason, oobaction, sourcedoc, past_due;
> WHERE &itemtype AND &desctype;
> INTO TABLE m.locdir+'\rept_data';
>
>This of course doesn't work either...For this particular queue, the record can have an empty source_num, empty pastduenum, both, or one of these 2 fields can be populated. I can get what I want going about it another way...less efficient I'm sure...so I was hoping someone could give me a suggestion. Any help is greatly appreciated.
>
>-Missy