Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    35

    Unanswered: odd sql behavior its very urgent

    im running the following sql and get numbers instead of column names
    select * FROM (SELECT ttype as type
    , bucket_size
    , first_bucket_end
    , total
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 0)) WHEN 1 THEN 0 ELSE 1 END) bucket1
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 1)) WHEN 1 THEN 0 ELSE 1 END) - sum(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 0)) WHEN 1 THEN 0 ELSE 1 END) bucket2
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 2)) WHEN 1 THEN 0 ELSE 1 END) - sum(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 1)) WHEN 1 THEN 0 ELSE 1 END) bucket3
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 3)) WHEN 1 THEN 0 ELSE 1 END) - sum(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 2)) WHEN 1 THEN 0 ELSE 1 END) bucket4
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 4)) WHEN 1 THEN 0 ELSE 1 END) - sum(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 3)) WHEN 1 THEN 0 ELSE 1 END) bucket5
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 5)) WHEN 1 THEN 0 ELSE 1 END) - sum(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 4)) WHEN 1 THEN 0 ELSE 1 END) bucket6
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 6)) WHEN 1 THEN 0 ELSE 1 END) - sum(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 5)) WHEN 1 THEN 0 ELSE 1 END) bucket7
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 7)) WHEN 1 THEN 0 ELSE 1 END) - sum(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 6)) WHEN 1 THEN 0 ELSE 1 END) bucket8
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 8)) WHEN 1 THEN 0 ELSE 1 END) - sum(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 7)) WHEN 1 THEN 0 ELSE 1 END) bucket9
    , SUM(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 9)) WHEN 1 THEN 0 ELSE 1 END) - sum(CASE sign((em_result_Value)-(FIRST_BUCKET_END + BUCKET_SIZE * 8)) WHEN 1 THEN 0 ELSE 1 END) bucket10
    ,session_id
    , em_date_time
    , em_location_id
    , em_script_id
    , em_organization_id
    , em_transaction_id
    FROM ( SELECT 1 ttype
    , BUCKET_SIZE
    , TRUNCATE(CEIL((BUCKET_SIZE + mmin) / BUCKET_SIZE) * BUCKET_SIZE,-1) as FIRST_BUCKET_END
    , total
    , em1.session_id
    , em1.em_date_time
    , em1.em_location_id
    , em1.em_script_id
    , em1.em_organization_id
    , em1.em_transaction_id
    , em_result_Value
    FROM event_meter em1,
    ( SELECT em_tmp.*
    , CASE (mmax - mmin)/10.0 WHEN 0 THEN CASE (mmax * 2)/10.0 WHEN 0 THEN 4096 ELSE CASE TRUNCATE(POWER(2.0, CEIL(log2( (mmax * 2)/10.0))),-1) WHEN 0 THEN 4096
    ELSE TRUNCATE(POWER(2.0, CEIL(log2( (mmax * 2)/10.0))),-1)
    END
    END
    ELSE CASE TRUNCATE(POWER(2.0, CEIL(log2( (mmax -mmin)/10.0))),-1) WHEN 0 THEN 4096
    ELSE TRUNCATE(POWER(2.0, CEIL(log2( (mmax - mmin)/10.0))),-1)
    END
    END as BUCKET_SIZE
    FROM
    ( SELECT MAX(em_result_value) mmax
    , min(em_result_value) mmin
    , count(*) total
    ,session_id
    -- ,TRUNC(em_date_time - -16/24) + -16/24 date_time needs 2 b replace with em_date_time
    ,em_date_time
    , em_location_id
    , em_script_id
    , em_organization_id
    , em_transaction_id
    FROM event_meter
    WHERE session_id = 243
    AND em_date_time >= timestamp('2003-05-01-08.00.00.000000')
    AND em_date_time < timestamp('2003-05-02-08.00.00.000000')
    AND em_status_id = 0
    GROUP BY session_id
    , em_date_time
    , em_location_id
    , em_script_id
    , em_organization_id
    , em_transaction_id
    ) as em_tmp
    ) as em2
    WHERE em1.session_id = 243
    AND em1.em_date_time >= timestamp('2003-05-01-08.00.00.000000')
    AND em1.em_date_time < timestamp('2003-05-01-08.00.00.000000')
    AND em1.em_status_id = 0
    AND em1.session_id = em2.session_id
    AND em1.em_date_time >= em2.em_date_time
    AND em1.em_date_time < em2.em_date_time + decimal(1,8,0)
    AND em1.em_transaction_id = em2.em_transaction_id
    AND em1.em_location_id = em2.em_location_id
    AND em1.em_script_id = em2.em_script_id
    AND em1.em_organization_id = em2.em_organization_id
    ) as em
    GROUP BY session_id
    , em_date_time
    , em_location_id
    , em_script_id
    , em_organization_id
    , em_transaction_id
    , ttype
    , BUCKET_SIZE
    , FIRST_BUCKET_END
    , total
    UNION ALL
    SELECT 2
    , EMB_BUCKET_SIZE
    , EMB_FIRST_BUCKET_END
    , COALESCE(EMB_BUCKET_01_PASS_COUNT, 0) + COALESCE(EMB_BUCKET_02_PASS_COUNT, 0) + COALESCE(EMB_BUCKET_03_PASS_COUNT, 0) +
    COALESCE(EMB_BUCKET_04_PASS_COUNT, 0) + COALESCE(EMB_BUCKET_05_PASS_COUNT, 0) + COALESCE(EMB_BUCKET_06_PASS_COUNT, 0) +
    COALESCE(EMB_BUCKET_07_PASS_COUNT, 0) + COALESCE(EMB_BUCKET_08_PASS_COUNT, 0) + COALESCE(EMB_BUCKET_09_PASS_COUNT, 0) +
    COALESCE(EMB_BUCKET_10_PASS_COUNT, 0),
    COALESCE(EMB_BUCKET_01_PASS_COUNT, 0), COALESCE(EMB_BUCKET_02_PASS_COUNT, 0), COALESCE(EMB_BUCKET_03_PASS_COUNT, 0),
    COALESCE(EMB_BUCKET_04_PASS_COUNT, 0), COALESCE(EMB_BUCKET_05_PASS_COUNT, 0), COALESCE(EMB_BUCKET_06_PASS_COUNT, 0),
    COALESCE(EMB_BUCKET_07_PASS_COUNT, 0), COALESCE(EMB_BUCKET_08_PASS_COUNT, 0), COALESCE(EMB_BUCKET_09_PASS_COUNT, 0),
    COALESCE(EMB_BUCKET_10_PASS_COUNT, 0)
    , emb_session_id
    , emb_date_time
    , emb_location_id
    , emb_script_id
    , emb_organization_id
    , emb_transaction_id
    FROM event_meter_buckets
    WHERE emb_session_id = 243
    AND emb_date_time >= timestamp('2003-05-01-08.00.00.000000')
    AND emb_date_time < timestamp('2003-05-02-08.00.00.000000')
    ) emb

    can anyone explain this ? its very urgent
    10x

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Looks like you didn't use the column names in the second SELECT

  3. #3
    Join Date
    Sep 2004
    Posts
    35

    10x

    yes the alias for the columns names in the 2 select needs 2 b the same as the 1 select

Posting Permissions

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