select to_char(app_n) as app_num, ASGN_CR_LIM_A as climit,
fnal_disp_d as decision_dt,
enty_d as application_dt,
to_char(fnal_disp_d,‘yyyy-mm’) as decdt_yyyy_mm, fnal_disp_d-enty_d as days_to_decision,
case when stat_c=‘NEWACCOUNT’ then ‘Approved’
when stat_c=‘DECLINE’ then ‘Decline’
end as app_decision,
APPCT_FICO_SCOR_N as applicant_fico,
CO_APPCT_FICO_SCOR_N as co_applicant_fico,
ICOME_A as Income, DBT_A, HIGH_LIM_A as High_Limit,
VERF_AST_N, SPND_NEED_N,
RECM_LINE_A, CELL_C, case when cell_c in (‘CELL 10’,’’) then ‘AUTO’
else ‘MANUAL’
end as decision, substr(UBS_card_I,13,4) as UBS_card_I
from bank_owner.ccu_application_info where stat_c in (‘NEWACCOUNT’,‘DECLINE’) and curr_rec_f=‘Y’
and app_n not in (14005325,14007232,14007045) and (substr(to_char(ubs_carD_i),1,6) is null or substR(to_char(ubs_card_i),1,6) in (‘419742’))
and fnal_disp_d between ‘01-JUNE-2017’ and ‘30-JUNE-2017’
if there is a field containing the date of birth in the table ccu_application_info then just add a comma, space and that field name (and optionally a space and an alias name like DOB, DateOfBirth or whatever you want the colmn header name to look like) after the last field in your select clause (the last line above the word from). If applicant demographics (like address, phone, and date of birth) are in a separtate table and all you have is an applicant ID inthe ccu_application_info table then you will need a join statement in the from clause to join to that demographics table on the applicant ID (should be a field in both tables) i.e.
from bank_owner.ccu_application_info
join bank_owner.applicant_demographics
on bank_owner.ccu_application_info.applicantID = bank_owner.applicant_demographics.applicantID.
where …
Hello,
The date of birth in the table ccu_application_info is pulling as NULL as it is a column called DATE_OF_BIRTH_NL which I am not sure if it is related to DOB. There is a date of birth column in the table ccu_customer_info that doesn’t pull has NULL and this is that data I am looking for. Is there anyway to either change the DATE_OF_BIRTH_NL from pulling as NULL or is there a way that I can pull data of birth column from the table ccu_customer_info?
The simple answer is that you will need to add the table that contains the desired information into the query by joining to that table.
HOW this is done, i.e., which column(s) to join with, depends on your data model. Your statement of “…I am not sure if it is related to DOB” cause me to think that you need to become more familiar with your data model. You may need to consult with the database administrator and/or the database documentation to determine what would work best.
Thanks for the reply. I agree I do need to become more familiar with the data model that I am working with if I will continue to work with it, unfortunately there is not a database administrator or any database documentation at the company that I work at for this system. The documentation that I did find was through my research from Google was helpful to a degree. I located this website by Google to see if I could locate the help that I was looking for. The script above was given to me by my manager who doesn’t know what it means but wants to pull DOB as part of the script. The person that created this script is no longer with the company.
You can always see what is in a table by selecting top 100 * from YourTableName (or the Oracle equivalent of top, something like where rownum <= 100 forgot exact syntax).
Change the from clause in your query to:
from bank_owner.ccu_application_info
join bank_owner.ccu_customer_info
on bank_owner.ccu_application_info. = bank_owner.ccu_customer_info.
The join adds the fields in the ccu_customeer_info table, then you can add the date of birth field from that table to the select clause of your query. You may want to alias the table names to make it easier.
Select Cust.DateOfBirth, rest of your App.fields
From bank_owner.ccu_application_info App
join bank_owner.ccu_customer_info Cust
on App. = Cust.
Find your common customer ID by number or alpha number that always matches the customer name.
Thank you.
You are welcome. Just paying it back for all the help I got from this forum when I started using Toad. Just wait until you start using Toad Automation (greatest feature of Toad IMHO), there are tutorial videos online to help you get started.