[closed]Report SQL

[BIsrik]

Hi,

I have three tables A, B and C used in a report. Table A and Table B are inner joined. Table B and Table C are outer joined. I am selecting one column from each table. When i see the Native sql of the report, the entire contents(or columns) of Table A and B are selected and then the table C is joined to that set. When i see the cognos sql it just shows the 3 columns and 3 table in join.

I am not able to understand why the entire columns of Table A and B are getting selected in the native sql.

Srik

[bdybldr]

Srik,
I haven't seen this before.  Can you post the native and Cognos SQL so we can get a better understanding?

Thanks.

[BIsrik]

This is how the sql goes...

Native SQL

select distinct  /*+   */ "D1"."C0" "Number", "D1"."C260" "Outage_Title", C."CAUSE_CODE" "Cause_Code"
from  (
select  /*+   */ A."NUMBER "C0", A."CATEGORY" "C1", A."OPEN_TIME" "C2", A."OPENED_BY" "C3", A."PRIORITY_CODE" "C4", A."SEVERITY_CODE" "C5", A."UPDATE_TIME" "C6", A."ASSIGNMENT" "C7", A."REFERRAL_TIME" "C8", A."REFERRED_TO" "C9", A."ALERT_TIME" "C10", A."STATUS" "C11", A."CLOSE_TIME" "C12", A."CLOSED_BY" "C13", A."ELAPSED_TIME" "C14", A."VENDOR" "C15", A."REFERENCE_NO" "C16", A."CONTACT_TIME" "C17", A."REFERRAL_TO_CONTACT" "C18", A."ONSITE_TIME" "C19", A."CONTACT_TO_RESPOND" "C20", A."REPAIR_TIME" "C21", A."ONSITE_TO_REPAIR" "C22", A."BACKUP_START" "C23", A."BACKUP_TIME" "C24", A."BACKUP_END" "C25", A."DOWNTIME" "C26", A."CAUSE_CODE" "C27", A."RESOLUTION_CODE" "C28", A."LOGICAL_NAME" "C29", A."GROUPPRGN" "C30", A."JOB_NAME" "C31", A."LOCATION" "C32", A."VERSION" "C33", A."TYPE" "C34", A."ABEND_CODE" "C35", A."MODEL" "C36", A."ACTION" "C37", A."RESOLUTION" "C38", A."AFFECTED" "C39", A."KEY_WORDS" "C40", A."XREFERENCE" "C41", A."ALERT1" "C42", A."ALERT2" "C43", A."ALERT3" "C44", A."DEADLINE" "C45", A."REASSIGNED" "C46", A."ID" "C47", A."LOOKUP_TIME" "C48", A."TOTAL_PAGES" "C49", A."FLAG" "C50", A."DOWNTIME_END" "C51", A."DOWNTIME_START" "C52", A."ASSIGNEE_NAME" "C53", A."RESPOND_TIME" "C54", A."CONTACT_NAME" "C55", A."SECONDS" "C56", A."CALLER_ID" "C57", A."CONTACT_PHONE" "C58", A."UPDATE_ACTION" "C59", A."ACTOR" "C60", A."FORMATPRGN" "C61", A."COUNTPRGN" "C62", A."RESPOND_TO_ONSITE" "C63", A."NETWORK_NAME" "C64", A."FINAL_CLOSE" "C65", A."OPEN_GROUP" "C66", A."ALERT_STATUS" "C67", A."DEADLINE_GROUP" "C68", A."DEADLINE_ALERT" "C69", A."PENDING_DATE" "C70", A."REFERRAL_COUNT" "C71", A."PENDING_REASON" "C72", A." _OPENED_BY_NAME" "C252", A." _UPDATED_BY_NAME" "C253", A." _CLOSED_BY_NAME" "C254", B."NUMBERPRGN" "C255", B." _REOPENED_BY_NAME" "C256", B." _ASSIGN_MANAGER" "C257", B." _SECONDARY_ASSIGNMENT_DESC" "C258", B." _OUTAGE_FLAG" "C259", B." _OUTAGE_TITLE" "C260", B." _OUTAGE_STATUS" "C261", B." _OUTAGE_START_DATE" "C262", B." _OUTAGE_STOP_DATE" "C263", B." _OUTAGE_START_TIME" "C264", B." _OUTAGE_STOP_TIME" "C265", B.CAUSE_CODE" "C266", B." _OUTAGE_FORCE_NOTIFICATION" "C267", B." _OUTAGE_DESC" "C268", B." _OUTAGE_RESOLUTION" "C269", B." _ALERT_LVL_1" "C270", B." _ALERT_LVL_2" "C271", B." _ALERT_LVL_3" "C272", B." _DLALERT_LVL" "C273", B." _OUTAGE_RPT_CATEGORY" "C274", B." _OUTAGE_RPT_SUBCATEGORY" "C275", B." _OUTAGE_LAYER" "C276", B." _OUTAGE_FOLLOWUP_TIME" "C277", B." _OUTAGE_RPT_TIME" "C278", B." _OUTAGE_AIP_EVENT" "C279", B." _OUTAGE_REOCCURENCE" "C280", B." _OUTAGE_REPEAT_NUMBER" "C281", B." _OUTAGE_OWNER1" "C282", B." _OUTAGE_OWNER2" "C283", B." _OUTAGE_NOTIFY_OWNER" "C284", B." _OUTAGE_LAST_UPDT_BY" "C285", B." _OUTAGE_LAST_UPDT_TIME" "C286", B." _OUTAGE_CATEGORY" "C287", B." _OUTAGE_SUBCATEGORY" "C288", B." _OUTAGE_PRODUCT_TYPE" "C289", B." _OUTAGE_PROBLEM_TYPE" "C290", B." _OUTAGE_CLOSE_IND" "C291", B." _OUTAGE_PAGE_TIME" "C292", B." _OUTAGE_CLOSE_PAGE_TIME" "C293"
from  A INNER JOIN  B on A."NUMBER"=B."NUMBER" ) "D1" LEFT OUTER JOIN C on "D1"."C266"=C."CAUSE_CODE"

Cognos SQL

select distinct
       A.NUMBERPRGN  ,
       B. _OUTAGE_TITLE  as  Outage_Title,
       C.CAUSE_CODE  as  Cause_Code
from
       (
            A
            join
            B
            on (A.NUMBER = B.NUMBER)
       )
        left outer join
        C
        on (C.CAUSE_CODE = B.CAUSE_CODE)

Srik

[sir_jeroen]

Hi,

I have three tables A, B and C used in a report. Table A and Table B are inner joined. Table B and Table C are outer joined. I am selecting one column from each table. When i see the Native sql of the report, the entire contents(or columns) of Table A and B are selected and then the table C is joined to that set. When i see the cognos sql it just shows the 3 columns and 3 table in join.

This is quite normal.. I see this all the time. You could prevent this to happen by selecting only those columns from a table that you're gonna use. You must do this in the database query subject. In a lot of cases only a couple of tablecolumns are used for querying. When the database table has a lot of cols it could be handy to select only the needed cols.

[BIsrik]

i could not get what u meant by "selecting only those columns from a table that you're gonna use. You must do this in the database query subject." I am selecting only one column from each table. Then why in native sql it selects all the columns of Table A and B.

Could u plz tell me in more detail...

Srik

[sir_jeroen]

If your table has 20 cols and you're only gonna use 10 cols you could specify in your database query subject only cols 1 to 10 instead of cols 1 to 20. Thus... not using select * from table but select col1...col10 from table. .

[BIsrik]

if u suggesting to edit the datasource query subject from select * to select col1, col2....col10 in FM, then the rest of the 10 columns will not be available to the report authors.

If there is any requirement for the col11 then i need to edit the sql again in FM and publish the package...I feel this would not be a feasable solution since every report has its own selecting columns.

But i am not able to understand if the in the datasource QS sql is select * and in the report i select few columns then why is all the columns getting selected.

Srik