Calculations stopped working in Report Studio

[CognosTalk]

When i changed the Cognos sql to pass-through and added additional data source, which is the same only uses public synonyms all the reports that i did before stopped working and start complaining about the calculations, like to char or to number.
I am using Oracle and they are valid commands.
Can you kindly tell me what is wrong?
Thank you in advance for your cooperation and support,
Mila.

 

[blom0344]

Post the SQL please. Passthrough will only work for self-contained statements.
It must not reference anything outside of that SQL, such as database prompts, variables, or native formatting that would normally be supplied by the calling application.


Ties Blom

 

[CognosTalk]

Ties,
my query is
Select
INSPECTION_DIM.ID,
INSPECTION_DIM.START_DATE,
INSPECTION_DIM.END_DATE,
INSPECTION_DIM.CURRENT_INDICATOR,
INSPECTION_DIM.SOURCE_ROW_ID,
INSPECTION_DIM.INSPECTION_DATE,
INSPECTION_DIM.PREVIOUS_INSPECTION_DATE,
INSPECTION_DIM.DATE_ASSIGNED,
INSPECTION_DIM.INSPECTION_COMPLETION_DATE,
INSPECTION_DIM.INSPECTION_FILE_CLOSING_DATE,
INSPECTION_DIM.AUDIT_SAMPLE_RESULTS_RECEIVED,
INSPECTION_DIM.REPORT_SENT_TO_DWS_OWNER,
INSPECTION_DIM.REPORT_TARGET_DATE,
INSPECTION_DIM.INSPECTION_REPORT_NUMBER,
INSPECTION_DIM.SUPERVISOR_NAME,
INSPECTION_DIM.PRIMARY_INSPECTOR_NAME,
INSPECTION_DIM.SECONDARY_INSPECTOR_NAME,
INSPECTION_DIM.FACILITY_ID,
INSPECTION_DIM.DWS_NUMBER,
INSPECTION_DIM.SUPERVISOR_LOGIN_NAME,
INSPECTION_DIM.PRIMARY_INSPECTOR_LOGIN_NAME,
INSPECTION_DIM.SECONDARY_INSPECTOR_LOGIN_NAME,
INSPECTION_DIM.INSPECTION_TYPE_DESC,
INSPECTION_DIM.FIELD_INSPECTION_TYPE_DESC,
INSPECTION_DIM.INSPECTION_FILE_STATUS_DESC,
INSPECTION_DIM.MNR_OFFICE,
INSPECTION_DIM.CONSERVATION_AUTHORITY,
INSPECTION_DIM.HEALTH_UNIT,
INSPECTION_DIM.SOURCE_TRANS_DATE,
INSPECTION_DIM.CREATE_DATE,
INSPECTION_DIM.UPDATE_DATE,
INSPECTION_DIM.LOAD_ID,
INSPECTION_DIM.DATE_TEMPLATE_PULLED,
INSPECTION_DIM.PESTICIDE_SAMPLING,
INSPECTION_DIM.TOTAL_QUESTION_RATING,
INSPECTION_DIM.MAXIMUM_QUESTION_RATING,
INSPECTION_DIM.TOTAL_NON_COMPLIANCE_RATING,
INSPECTION_DIM.FINAL_INSPECTION_RATING,
INSPECTION_DIM.PERMIT_TO_TAKE_WATER,
INSPECTION_DIM.CAPACITY_ASSESSMENT,
INSPECTION_DIM.TREATMENT_PROCESSES,
INSPECTION_DIM.DISTRIBUTION_SYSTEM,
INSPECTION_DIM.OPERATIONS_MANUALS,
INSPECTION_DIM.LOGBOOKS,
INSPECTION_DIM.CONTINGENCY_EMERGENCY_PLANNING,
INSPECTION_DIM.CONSUMER_RELATIONS,
INSPECTION_DIM.CERTIFICATION_AND_TRAINING,
INSPECTION_DIM.WATER_QUALITY_MONITORING,
INSPECTION_DIM.REPORTING_CORRECTIVE_ACTIONS,
INSPECTION_DIM.SOURCE,
INSPECTION_DIM.INSPECTION_FISCAL_YEAR,
(select
INSPECTION_DIM.REPORT_TARGET_DATE - max(LOADING_TRANS_DATE)
from
bi_admin_log
where
load_id = (select
max(load_id)
from
bi_admin_log
where
loaded_table in ('DWIS_STAGING_AREA','LWIS_STAGING_AREA','IDS_STAGING_AREA','WWOCS_STAGING_AREA')
)
and loaded_table in ('DWIS_STAGING_AREA','LWIS_STAGING_AREA','IDS_STAGING_AREA','WWOCS_STAGING_AREA')
) as report_due_days
From
"PUBLIC".INSPECTION_DIM

when specifying the sql type to drill-through it works, but my calculations inside the report stopeed working. it complained primarily for the conversions to_char, or to_number. After your respond I went back and changed the sql type to native sql and my calculations started working again.
Now sure what is going on,. hopefully it will continue work for the rest of the reports.
Thank you very much for your respond!