Report displaying more information than expected.

Dear All

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'
  )

Thanks and Regards

[used bbc CODE formatting - Andreas]


bobjkb :india: (BOB member since 2004-03-19)

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.


jobjoris :netherlands: (BOB member since 2004-06-30)

Thanks for your prompt reply

But I couldnt get you where exactly I have to add this Distinct

Thanks and Regrads


bobjkb :india: (BOB member since 2004-03-19)

Well, like:

SELECT DISTINCT
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' 
)

Good luck!


jobjoris :netherlands: (BOB member since 2004-06-30)

Thank you very much for your prompt reply … I shall test this and then get back to you .

Thanks and Regards


bobjkb :india: (BOB member since 2004-03-19)

Hi Jobjoris

Hey its working but can you please tell me why this is happening here

Thanks and Regards


bobjkb :india: (BOB member since 2004-03-19)

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.


jobjoris :netherlands: (BOB member since 2004-06-30)

Thank you very much for your help

regards


bobjkb :india: (BOB member since 2004-03-19)