cancel
Showing results for 
Search instead for 
Did you mean: 

Security on Standalone tables in Universe

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

please suggest how to get it in on universe level

amitrathi239
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Can someone help on  it ..This is really urgent

amitrathi239
Active Contributor
0 Kudos

Hi,

I think object in the first SQL is coming from drived table.Check this table.

  PSD_REPORT_DATA_MG_TB_DERIVED


Amit

Former Member
0 Kudos

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