Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    USA
    Posts
    1

    Unanswered: Help with Oracle SQL

    Hi everybody,

    I have proble to create report,

    I've table wich is contains data for different report types,

    How can I pool out data for each report in one select.

    for example this a mane select.

    SELECT Distinct W_AMC_MAIL_STATUS_MASTER_DATA.AMC_MAIL_MASTER_TXN_ ID,
    W_AMC_MAIL_STATUS_MASTER_DATA.AIRPORT_CODE,
    W_AMC_MAIL_STATUS_MASTER_DATA.REPORTING_DATE,
    W_AMC_MAIL_STATUS_MASTER_DATA.CONDITION_REMARKS,
    W_AMC_MAIL_STATUS_MASTER_DATA.RESOLUTION_COMPLETE_ STATUS,
    W_AMC_MAIL_STATUS_DETAIL_DATA.CONTAINER_SEQ_NO,
    W_AMC_CONTAINERS_REF.CONTAINER_TYPE,
    W_AMC_MAIL_STATUS_DETAIL_DATA.NUMBER_OF_CONTAINERS ,
    W_AMC_MAIL_STATUS_DETAIL_DATA.MAIL_CLASS_CODE,
    W_AMC_MAIL_STATUS_DETAIL_DATA.WEIGHT_IN_LBS ,
    W_AMC_MAIL_STATUS_DETAIL_DATA.PLAN_FAIL_WEIGHT,
    W_AMC_MAIL_STATUS_DETAIL_DATA.PLAN_FAIL_CONTAINERS ,
    (W_AMC_MAIL_STATUS_DETAIL_DATA.PLAN_FAIL_CONTAINER S * W_AMC_CONTAINERS_REF.NUMBER_OF_PIECES) PLAN_FAIL_MAIL_PCS
    FROM W_AMC_MAIL_STATUS_MASTER_DATA,
    W_AMC_MAIL_STATUS_DETAIL_DATA,
    W_AMC_CONTAINERS_REF,
    W_AMC_MAIL_CLASS_REF
    WHERE (W_AMC_MAIL_STATUS_MASTER_DATA.AIRPORT_CODE = W_AMC_MAIL_STATUS_DETAIL_DATA.AIRPORT_CODE)
    AND (W_AMC_MAIL_STATUS_MASTER_DATA.REPORT_TYPE = W_AMC_MAIL_STATUS_DETAIL_DATA.REPORT_TYPE)
    AND (W_AMC_MAIL_CLASS_REF.MAIL_CLASS_CODE = W_AMC_MAIL_STATUS_DETAIL_DATA.MAIL_CLASS_CODE)
    AND (W_AMC_CONTAINERS_REF.AMC_CONTAINER_SEQ_NO = W_AMC_MAIL_STATUS_DETAIL_DATA.CONTAINER_SEQ_NO)
    AND (W_AMC_MAIL_STATUS_MASTER_DATA.REPORTING_DATE = W_AMC_MAIL_STATUS_DETAIL_DATA.REPORTING_DATE)
    AND (W_AMC_MAIL_STATUS_MASTER_DATA.REPORT_TYPE in(2,3,4,5,6))
    ORDER BY REPORTING_DATE
    I need show PLAN_FAIL_WEIGHT for each REPORT_TYPE


    Thank for help.
    Albert.

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Probably I didn't get the problem right, so let's go for a little trial and error.

    If your problem is :
    1. You need to create different reports, all based on the "main" select you posted
    2. You want to avoid typing the FROM ... WHERE ... for each of the reports you need to create

    => then you might want to create a view over your "main" select

    CREATE VIEW YOUR_VIEW_NAME
    AS
    SELECT DISTINCT <the_rest_of_your_main_select>

    Afterwards, you can create your reports as SELECT ... FROM YOUR_VIEW_NAME

    Good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •