on 07-02-2015 6:34 AM
Hi All,
I have table on an existing universe named as Report Data which is having column Transref .When I am pulling this column on report .it generates Following type of query :
SELECT
PSD_REPORT_DATA_MG_TB_DERIVED.TRANSACTION_REFERENCE
FROM
PSD_PRODUCT_TYPE_REF,
( SELECT
USER_KEY_SEQ,
USER_ID,
SUPER_USER_FLAG,
ADMIN_USER_FLAG,
PSD_USER_FLAG,
MLAR_USER_FLAG,
RMAR_USER_FLAG,
START_DATE,
ACTIVE_FLAG,
SUBMITTER_FLAG,
TO_DATE(TO_CHAR(LAST_UPDATED,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS LAST_UPDATED,
END_DATE,
FIRST_NAME,
LAST_NAME
FROM IRR_USER_TB
WHERE PSD_USER_FLAG = 'Y' AND ACTIVE_FLAG = 'Y' AND END_DATE IS NULL) IRR_USER_TB_DERIVED,
( SELECT
FIRM_FSA_REFERENCE,
FIRM_S_NAME,
FIRM_NAME,
SUBMITTING_DEPT
FROM IRR_FIRM_REF) IRR_FIRM_REF_DERIVED,
( SELECT PSD_USER_FIRM_TB.USER_KEY,PSD_USER_FIRM_TB.FIRM_FSA_REFERENCE,PSD_USER_FIRM_TB.PRODUCT_ID,TO_DATE(TO_CHAR(PSD_USER_FIRM_TB.LAST_UPDATED,'DD/MM/YYYY'),'DD/MM/YYYY') AS LAST_UPDATED_DATE FROM PSD_USER_FIRM_TB) PSD_USER_FIRM_TB_DERIVED,
( SELECT
FSA_YEAR, FSA_PERIOD, TRANSACTION_REFERENCE,
VERSION, PRODUCT_ID, SOURCE_SYSTEM, nvl(BRAND_ID, 'NA') AS BRAND_ID,
REPORT_IDENTIFIER, SUBMITTING_FIRM, SUBMITTING_DEPARTMENT,
FIRM_FSA_REFERENCE, CANCELLATION, PRINCIPAL_OR_NETWORK_FSA_REF,
ACCT_OPEN_DATE, ADVISED_SALE_FLAG, TYPE_INT_RATE_IND,
MTGE_CHAR1_IND, MTGE_CHAR2_IND, MTGE_CHAR3_IND,
MTGE_CHAR4_IND, MTGE_CHAR5_IND, PROPERTY_POST_CODE,
TYPE_BORROWER_IND, REPAY_METHOD_IND, MTGE_TERM,
LOAN_AMT, PROPERTY_VALUE, INCOME_BASIS_IND,
MAIN_DOB_DATE, REMTGE_PURPOSE_IND, MAIN_EMP_STATUS_IND,
TOTAL_GROSS_INCOME, INCOME_VERIFICATION_FLAG, MAIN_BORROWER_CCJ_VALUE,
SECOND_BORROWER_CCJ_VALUE, MAIN_BORR_IMP_CRED_HIST1_IND, MAIN_BORR_IMP_CRED_HIST2_IND,
MAIN_BORR_IMP_CRED_HIST3_IND, SECOND_BORR_IMP_CRED_HIST1_IND, SECOND_BORR_IMP_CRED_HIST2_IND,
SECOND_BORR_IMP_CRED_HIST3_IND, INITIAL_GROSS_INT_RATE, INCENTIVE_RATE_ENDS_DATE,
ERC_ENDS_DATE, PURCHASE_PRICE, TYPE_DWELLING_IND,
NUM_HABITABLE_ROOMS, NUM_BEDROOMS, NEW_DWELLING_FLAG,
GARAGE_INC_FLAG, MTGE_PROTECTION_PLAN_INC_FLAG, IS_VALID_FLAG,
COMMENT_FLD, ACTIVE_VERSION_FLAG, SENT_FLAG,
READY_TO_SEND_FLAG,
UPDATED_BY,
TO_DATE(TO_CHAR(SENT_DATE,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS SENT_DATE,
TO_DATE(TO_CHAR(ADDED_DATE,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS ADDED_DATE
FROM IRRPSD_OWNER.PSD_REPORT_DATA_MG_TB) PSD_REPORT_DATA_MG_TB_DERIVED
WHERE
( PSD_USER_FIRM_TB_DERIVED.PRODUCT_ID=PSD_REPORT_DATA_MG_TB_DERIVED.PRODUCT_ID and PSD_USER_FIRM_TB_DERIVED.FIRM_FSA_REFERENCE=PSD_REPORT_DATA_MG_TB_DERIVED.SUBMITTING_FIRM )
AND ( IRR_USER_TB_DERIVED.USER_KEY_SEQ=PSD_USER_FIRM_TB_DERIVED.USER_KEY )
AND ( IRR_FIRM_REF_DERIVED.FIRM_FSA_REFERENCE=PSD_USER_FIRM_TB_DERIVED.FIRM_FSA_REFERENCE )
AND ( IRR_USER_TB_DERIVED.USER_ID = @Variable('BOUSER') )
AND ( PSD_PRODUCT_TYPE_REF.PRODUCT_ID=PSD_USER_FIRM_TB_DERIVED.PRODUCT_ID )
************************
However I have another table named as Report Data007 having column like Transref .When I pull this column on report ..It generates following SQL :
Select Transref from Report Data 007
But I need t generate the Above SQL as in case 1 .
Please help me resolving this issue ...How the query in case 1 is created ..
Appreciate your help.
please suggest how to get it in on universe level
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This is standard behaviour, Whenever you will drag an object from derived table in report. Other way to create another nested derived table on this.
Like
select * from @DerivedTable(PSD_REPORT_DATA_MG_TB_DERIVED)
Map the TRANSACTION_REFERENCE universe object in the new derived table and try.
Or create a View in the database and then use in the universe.
Amit
Can someone help on it ..This is really urgent
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
yes..this is derived table where I have written a query
SELECT
FSA_YEAR, FSA_PERIOD, TRANSACTION_REFERENCE,
VERSION, PRODUCT_ID, SOURCE_SYSTEM, nvl(BRAND_ID, 'NA') AS BRAND_ID,
REPORT_IDENTIFIER, SUBMITTING_FIRM, SUBMITTING_DEPARTMENT,
FIRM_FSA_REFERENCE, CANCELLATION, PRINCIPAL_OR_NETWORK_FSA_REF,
ACCT_OPEN_DATE, ADVISED_SALE_FLAG, TYPE_INT_RATE_IND,
MTGE_CHAR1_IND, MTGE_CHAR2_IND, MTGE_CHAR3_IND,
MTGE_CHAR4_IND, MTGE_CHAR5_IND, PROPERTY_POST_CODE,
TYPE_BORROWER_IND, REPAY_METHOD_IND, MTGE_TERM,
LOAN_AMT, PROPERTY_VALUE, INCOME_BASIS_IND,
MAIN_DOB_DATE, REMTGE_PURPOSE_IND, MAIN_EMP_STATUS_IND,
TOTAL_GROSS_INCOME, INCOME_VERIFICATION_FLAG, MAIN_BORROWER_CCJ_VALUE,
SECOND_BORROWER_CCJ_VALUE, MAIN_BORR_IMP_CRED_HIST1_IND, MAIN_BORR_IMP_CRED_HIST2_IND,
MAIN_BORR_IMP_CRED_HIST3_IND, SECOND_BORR_IMP_CRED_HIST1_IND, SECOND_BORR_IMP_CRED_HIST2_IND,
SECOND_BORR_IMP_CRED_HIST3_IND, INITIAL_GROSS_INT_RATE, INCENTIVE_RATE_ENDS_DATE,
ERC_ENDS_DATE, PURCHASE_PRICE, TYPE_DWELLING_IND,
NUM_HABITABLE_ROOMS, NUM_BEDROOMS, NEW_DWELLING_FLAG,
GARAGE_INC_FLAG, MTGE_PROTECTION_PLAN_INC_FLAG, IS_VALID_FLAG,
COMMENT_FLD, ACTIVE_VERSION_FLAG, SENT_FLAG,
READY_TO_SEND_FLAG,
UPDATED_BY,
TO_DATE(TO_CHAR(SENT_DATE,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS SENT_DATE,
TO_DATE(TO_CHAR(ADDED_DATE,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS ADDED_DATE
FROM IRRPSD_OWNER.PSD_REPORT_DATA_MG_TB
but when I run report ..it is generating following sql
Select
SELECT
PSD_REPORT_DATA_MG_TB_DERIVED.TRANSACTION_REFERENCE
from (
SELECT
FSA_YEAR, FSA_PERIOD, TRANSACTION_REFERENCE,
VERSION, PRODUCT_ID, SOURCE_SYSTEM, nvl(BRAND_ID, 'NA') AS BRAND_ID,
REPORT_IDENTIFIER, SUBMITTING_FIRM, SUBMITTING_DEPARTMENT,
FIRM_FSA_REFERENCE, CANCELLATION, PRINCIPAL_OR_NETWORK_FSA_REF,
ACCT_OPEN_DATE, ADVISED_SALE_FLAG, TYPE_INT_RATE_IND,
MTGE_CHAR1_IND, MTGE_CHAR2_IND, MTGE_CHAR3_IND,
MTGE_CHAR4_IND, MTGE_CHAR5_IND, PROPERTY_POST_CODE,
TYPE_BORROWER_IND, REPAY_METHOD_IND, MTGE_TERM,
LOAN_AMT, PROPERTY_VALUE, INCOME_BASIS_IND,
MAIN_DOB_DATE, REMTGE_PURPOSE_IND, MAIN_EMP_STATUS_IND,
TOTAL_GROSS_INCOME, INCOME_VERIFICATION_FLAG, MAIN_BORROWER_CCJ_VALUE,
SECOND_BORROWER_CCJ_VALUE, MAIN_BORR_IMP_CRED_HIST1_IND, MAIN_BORR_IMP_CRED_HIST2_IND,
MAIN_BORR_IMP_CRED_HIST3_IND, SECOND_BORR_IMP_CRED_HIST1_IND, SECOND_BORR_IMP_CRED_HIST2_IND,
SECOND_BORR_IMP_CRED_HIST3_IND, INITIAL_GROSS_INT_RATE, INCENTIVE_RATE_ENDS_DATE,
ERC_ENDS_DATE, PURCHASE_PRICE, TYPE_DWELLING_IND,
NUM_HABITABLE_ROOMS, NUM_BEDROOMS, NEW_DWELLING_FLAG,
GARAGE_INC_FLAG, MTGE_PROTECTION_PLAN_INC_FLAG, IS_VALID_FLAG,
COMMENT_FLD, ACTIVE_VERSION_FLAG, SENT_FLAG,
READY_TO_SEND_FLAG,
UPDATED_BY,
TO_DATE(TO_CHAR(SENT_DATE,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS SENT_DATE,
TO_DATE(TO_CHAR(ADDED_DATE,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS ADDED_DATE
FROM IRRPSD_OWNER.PSD_REPORT_DATA_MG_TB)IRRPSD_OWNER.PSD_REPORT_DATA_MG_TB_DERIVED.
but I need the one generated in case 1
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.