I have created a report that displays report name and last refresh date, but most of the reports seem to have multiple rows, each with the same refresh date.
I would like to understand why the reports are showing more than once.
The SQL created by the report is as follows:
SELECT
WA_DOCUMENTS.M_DOC_C_NAME,
WA_OBJ_M_DOCATVAR.M_DOCATV_N_REF_F
FROM
WA_DOCUMENTS,
WA_OBJ_M_DOCATVAR,
WA_OBJ_M_CATEG,
OBJ_M_DOCCATEG
WHERE
( OBJ_M_DOCCATEG.M_CATEG_N_ID=WA_OBJ_M_CATEG.M_CATEG_N_ID(+) )
AND ( WA_DOCUMENTS.M_DOC_N_ID=OBJ_M_DOCCATEG.M_DOC_N_ID(+) )
AND ( WA_DOCUMENTS.M_DOC_N_ID=WA_OBJ_M_DOCATVAR.M_DOC_N_ID )
AND (
WA_OBJ_M_CATEG.M_CATEG_C_NAME_FULL = 'WSL DTS 2004'
AND WA_DOCUMENTS.M_DOC_N_PERSISTENT_LAB = 'Persistent'
)
Did you create this with Free Hand Sql? Because if the Objects both are dimensions BO should group it immediately. And therefor shouldn’t produce duplicate rows.
You could add a DISTINCT in your SQL. That would solve your problem.
Your SQL-statement gets it Data from 2 tables (WA_DOCUMENTS AND WA_OBJ_M_DOCATVAR). These tables are joined through 2 other other tables (WA_OBJ_M_CATEG and OBJ_M_DOCCATEG) to get other info for your filter. In one of the tables the keyvalue M_DOC_N_ID isn’t unique so your query returns multiple rows. With a DISTINCT you tell your database to only return non dublicate rows. Hope this clears it up a little.