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

Looks like you didn't use the column names in the second SELECT

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