Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: select statement problem

    guys, i have this query and i have attached the result that I get.
    The problem is that it is displaying the an unwanted line which is the first line and the 2nd and 3rd line is supposed to be combined as a summary but it is not.

    ( SELECT DISTINCT trunc(delivered_date) AS delivered_date,
    ( SELECT COUNT(*)
    FROM trap_stage_ppb a
    WHERE a.cash_type = '1'
    AND a.units_state = '0'
    AND a.units >= '0'
    AND a.cash_type=b.cash_type
    AND a.units_state=b.units_state
    ) AS ppb_valuecard_quantity_count,
    ( SELECT SUM(units)
    FROM trap_stage_ppb a
    WHERE a.cash_type = '1'
    AND a.units_state = '0'
    AND a.units >= '0'
    AND a.cash_type=b.cash_type
    AND a.units_state=b.units_state
    ) AS ppb_valuecard_value_sum,
    ( SELECT COUNT(*)
    FROM trap_stage_ppb a
    WHERE a.cash_type = '2'
    AND a.units_state = '0'
    AND a.units >= '0'
    AND a.cash_type=b.cash_type
    AND a.units_state=b.units_state
    ) AS ppb_oprs_quantity_count,
    ( SELECT SUM(units)
    FROM trap_stage_ppb a
    WHERE a.cash_type = '2'
    AND a.units_state = '0'
    AND a.units >= '0'
    AND a.cash_type=b.cash_type
    AND a.units_state=b.units_state
    ) AS ppb_oprs_value_sum,
    ( SELECT TRUNC(sysdate) FROM dual
    ) AS current_date
    FROM trap_stage_ppb b)

    --> have attached the data, result and the query in the attachment... pls view and correct me if im wrong..
    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    It's quite unclear what data you expect to retrieve. You should describe what exactly you wanna do (select). Anyway try following select a tried to rewrite it different way.
    Code:
    select  delivered_date, 
    	sum(ppb_valuecard_quantity_count) as ppb_valuecard_quantity_count,
    	sum(ppb_valuecard_value_sum) as ppb_valuecard_value_sum,	
    	sum(ppb_oprs_quantity_count) as ppb_oprs_quantity_count,
    	sum(ppb_oprs_value_sum) as ppb_oprs_value_sum,
    	current_date
    from
    (	
    select trunc(delivered_date) AS delivered_date,
    case when cash_type = '1'
    	AND units_state = '0'
    	AND units >= '0' then
    	1
    	else 
    	0
    	end AS ppb_valuecard_quantity_count,
    case when cash_type = '1'
    	AND units_state = '0'
    	AND units >= '0' then
    	units
    	else
    	0
    	end AS ppb_valuecard_value_sum,
    case when cash_type = '2'
    	AND units_state = '0'
    	AND units >= '0' then
    	1
    	else
    	0
    	end AS ppb_oprs_quantity_count,
    case when cash_type = '2'
    	AND units_state = '0'
    	AND units >= '0' then
    	units
    	else
    	0
    	end AS ppb_oprs_value_sum,
    TRUNC(sysdate) AS current_date
    )
    group by delivered_date, current_date

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    okies..
    the way u wrote is giving me the result..

    i need ut help in incorporating the same group by into my code
    could you pls help ?

    thanks

Posting Permissions

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