Nested query using case statements on case statements

is this oracle? zone is selected as PL/SQL!

Actually it is Green Plum - and I am not that familiar with which form of SQL it uses,  could you help me with the inclusion of the case statements, via multi-nested query statement.

All I know is that the main portion of my query is functional I just need to add the additional columns based  on the case statements.

It is late here, I will pick this up in the morning, any assistance you can offer is greatly appreciated.

I dont get what you are asking :)
add this to existing query

,
case when (`cpsaleslabor` <> 0 or `internalsaleslabor` <> 0 or `warrantysaleslabor` <> 0) then 'Labor'
when (`cpsalesparts` <> 0 or `internalsalesparts` <> 0 or `warrantysalesparts` <> 0) then 'Parts'
when (`cpsalessublet` <> 0 or `internalsalessublet` <> 0 or `warrantysalessublet` <> 0) then 'Sublet'
when (`cpsalesmisc` <> 0 or `internalsalesmisc` <> 0 or `warrantysalesmisc` <> 0) then 'Misc'
when (`cpsaleslube` <> 0 or `internalsaleslube` <> 0 or `warrantysaleslube` <> 0) then 'Lube'
else ''
end  as SalesType, 
case when (`cpsaleslabor` <> 0 or `cpsalesparts` <> 0 or `cpsalessublet` <> 0 or `cpsalesmisc` <> 0 or `cpsaleslube` <> 0) then 'Customer'
when (`internalsaleslabor` <> 0 or `internalsalesparts` <> 0 or `internalsalessublet` <> 0 or `internalsalesmisc` <> 0 or `internalsaleslube` <> 0) then 'Internal'
when (`warrantysaleslabor` <> 0 or `warrantysalesparts` <> 0 or `warrantysalessublet` <> 0 or `warrantysalesmisc` <> 0 or `warrantysaleslube` <> 0) then 'Warranty'
else ''
end as Labor type 

Select allOpen in new window

just before "from" in the main query

oops... I see you need new cases based on existing columns...

need a wrapper select to use it as is...

or, it will be a huge code like

case when (`cpsaleslabor` <> 0 or `internalsaleslabor` <> 0 or `warrantysaleslabor` <> 0) then 'Labor'

Select allOpen in new window

>>>

case when (f.customer_labor_sale_local <> 0 or (case when f.saletype = 'Labor' and f.labortype = 'Internal' then f.internalsaleslabor else 0 end) <> 0 or (case when f.saletype = 'Labor' and f.labortype = 'Warranty' then f.warrantysaleslabor else 0 end) <> 0) then 'Labor'

Select allOpen in new window

this is just for your 1st line :)

lots of copy paste...

or I see another method...

you have a query like

select ...
from ...
limit 10

Select allOpen in new window

+ some transformations...

what I am thinking is

select * from (
select ...
  >>>> remove some lines
  >>>> add exisiting transformations
from ...
limit 10
) x

Select allOpen in new window

+ use your new transformations as is...

let me re-phrase it:

>>>> remove some lines
I mean remove lines like

            , f.customer_labor_sale_local as cpsaleslabor
            , f.customer_parts_sale_local as cpsalesparts
            ...
            , (f.warranty_misc_cost_local + f.warranty_shop_cost_local) as warrantycostmisc
            , (warranty_local_tax_local + warranty_state_tax_local + warranty_supplement_sale_tax_local) as warrantytax

Select allOpen in new window

from your query, and put existing transformation here...

old query

select
              dsd.department_id
            , dsd.department_name
			, f.ro_id
            , f.vin
            , f.dim_ro_close_dealer_date_key
            , (de.employee_first_name || ' ' || de.employee_last_name) as serviceadvisorname
            , dd.year_month_start_dt
            , f.advisor_id
            , (f.internal_labor_sale_local + f.warranty_labor_sale_local + f.customer_labor_sale_local) as saleslabor
            , (f.internal_parts_sale_local + f.warranty_parts_sale_local + f.customer_parts_sale_local) as salesparts
            , (f.internal_sublet_sale_local + f.warranty_sublet_sale_local + f.customer_sublet_sale_local) as salessublet
            , (f.internal_lube_sale_local + f.warranty_lube_sale_local + f.customer_lube_sale_local) as salelube
            , (f.internal_misc_sale_local + f.warranty_misc_sale_local + f.customer_misc_sale_local + f.internal_shop_sale_local + f.warranty_shop_sale_local + f.customer_shop_sale_local) as misc_sale
            , (f.internal_labor_cost_local + f.warranty_labor_cost_local + f.customer_labor_cost_local) as costlabor
            , (f.internal_parts_cost_local + f.warranty_parts_cost_local + f.customer_parts_cost_local) as costparts
            , (f.internal_sublet_cost_local + f.warranty_sublet_cost_local + f.customer_sublet_cost_local) as costsublet
            , (f.internal_lube_cost_local + f.customer_lube_cost_local + f.warranty_lube_cost_local) as costlube
            , (f.internal_misc_cost_local + f.warranty_misc_cost_local + f.customer_misc_cost_local + f.internal_shop_cost_local + f.warranty_shop_cost_local + f.customer_shop_cost_local) as misc_cost
            , f.hours_sold
            , f.hours_actual
            , (f.internal_local_tax_local + f.warranty_local_tax_local + f.customer_local_tax_local) as total_local_tax
            , (f.internal_state_tax_local + f.warranty_state_tax_local + f.customer_state_tax_local) as total_state_tax
            , (f.internal_local_tax_local + f.warranty_local_tax_local + f.customer_local_tax_local +
                        f.internal_state_tax_local + f.warranty_state_tax_local + f.customer_state_tax_local +
                        f.internal_supplement_sale_tax_local + f.warranty_supplement_sale_tax_local + f.customer_supplement_sale_tax_local
              ) as totaltax
            , f.customer_labor_sale_local as cpsaleslabor
            , f.customer_parts_sale_local as cpsalesparts
            , f.customer_sublet_sale_local as cpsalessublet
            , f.customer_lube_sale_local as cpsaleslube
            , (f.customer_misc_sale_local + f.customer_shop_sale_local) as cpsalesmisc
            , f.customer_labor_cost_local as cpcostlabor
            , f.customer_parts_cost_local as cpcostparts
            , f.customer_sublet_cost_local as cpcostsublet
            , f.customer_lube_cost_local as cpcostlube
            , (f.customer_misc_cost_local + f.customer_shop_cost_local ) as cpcostmisc
            , (f.customer_local_tax_local + f.customer_state_tax_local + f.customer_supplement_sale_tax_local) as cptax
            , f.internal_labor_sale_local  as internalsaleslabor
            , f.internal_parts_sale_local as internalsalesparts
            , f.internal_sublet_sale_local as internalsalessublet
            , f.internal_lube_sale_local as internalsaleslube
            , (f.internal_misc_sale_local + f.internal_shop_sale_local) as internalsalesmisc
            , f.internal_labor_cost_local as internalcostlabor
            , f.internal_parts_cost_local as internalcostparts
            , f.internal_sublet_cost_local as internalcostsublet
            , f.internal_lube_cost_local as internalcostlube
            , (f.internal_misc_cost_local + f.internal_shop_cost_local) as internalcostmisc
            , (f.internal_local_tax_local + f.internal_state_tax_local + f.internal_supplement_sale_tax_local ) as internaltax
            , f.warranty_labor_sale_local as warrantysaleslabor
            , f.warranty_parts_sale_local as warrantysalesparts
            , f.warranty_sublet_sale_local  as warrantysalessublet
            , f.warranty_lube_sale_local as warrantysaleslube
            , (f.warranty_misc_sale_local + f.warranty_shop_sale_local ) as warrantysalesmisc
            , f.warranty_labor_cost_local as warrantycostlabor
            , f.warranty_parts_cost_local as warrantycostparts
            , f.warranty_sublet_cost_local as warrantycostsublet
            , f.warranty_lube_cost_local as warrantycostlube
            , (f.warranty_misc_cost_local + f.warranty_shop_cost_local) as warrantycostmisc
            , (warranty_local_tax_local + warranty_state_tax_local + warranty_supplement_sale_tax_local) as warrantytax
            , dd.dim_year_month_key
            , f.last_modified_date
  from fact_repair_order_job_events f
 inner join dim_dates dd
    on f.dim_ro_open_dealer_date_key = dd.dim_date_key
 inner join dim_store_departments dsd
    on f.dim_store_department_key = dsd.dim_store_department_key
 inner join xref_store_to_departments xstd
    on xstd.dim_store_department_key = dsd.dim_store_department_key
 inner join dim_stores ds
    on xstd.dim_store_key = ds.dim_store_key
 inner join dim_employees de
    on f.dim_advisor_key = de.dim_employee_key
 where f.dim_ro_open_dealer_date_key between to_char(to_date('20170729','yyyymmdd'),'J')::int and to_char(to_date('20170730','yyyymmdd'),'J')::int
   and ds.store_id  in ('S100037224', 'S100026211', 'S100034821')
 limit 10

Select allOpen in new window

new query will be like

select * from (
select
              dsd.department_id
            , dsd.department_name
			, f.ro_id
            , f.vin
            , f.dim_ro_close_dealer_date_key
            , (de.employee_first_name || ' ' || de.employee_last_name) as serviceadvisorname
            , dd.year_month_start_dt
            , f.advisor_id
            , (f.internal_labor_sale_local + f.warranty_labor_sale_local + f.customer_labor_sale_local) as saleslabor
            , (f.internal_parts_sale_local + f.warranty_parts_sale_local + f.customer_parts_sale_local) as salesparts
            , (f.internal_sublet_sale_local + f.warranty_sublet_sale_local + f.customer_sublet_sale_local) as salessublet
            , (f.internal_lube_sale_local + f.warranty_lube_sale_local + f.customer_lube_sale_local) as salelube
            , (f.internal_misc_sale_local + f.warranty_misc_sale_local + f.customer_misc_sale_local + f.internal_shop_sale_local + f.warranty_shop_sale_local + f.customer_shop_sale_local) as misc_sale
            , (f.internal_labor_cost_local + f.warranty_labor_cost_local + f.customer_labor_cost_local) as costlabor
            , (f.internal_parts_cost_local + f.warranty_parts_cost_local + f.customer_parts_cost_local) as costparts
            , (f.internal_sublet_cost_local + f.warranty_sublet_cost_local + f.customer_sublet_cost_local) as costsublet
            , (f.internal_lube_cost_local + f.customer_lube_cost_local + f.warranty_lube_cost_local) as costlube
            , (f.internal_misc_cost_local + f.warranty_misc_cost_local + f.customer_misc_cost_local + f.internal_shop_cost_local + f.warranty_shop_cost_local + f.customer_shop_cost_local) as misc_cost
            , f.hours_sold
            , f.hours_actual
            , (f.internal_local_tax_local + f.warranty_local_tax_local + f.customer_local_tax_local) as total_local_tax
            , (f.internal_state_tax_local + f.warranty_state_tax_local + f.customer_state_tax_local) as total_state_tax
            , (f.internal_local_tax_local + f.warranty_local_tax_local + f.customer_local_tax_local +
                        f.internal_state_tax_local + f.warranty_state_tax_local + f.customer_state_tax_local +
                        f.internal_supplement_sale_tax_local + f.warranty_supplement_sale_tax_local + f.customer_supplement_sale_tax_local
              ) as totaltax
      , (case when f.saletype = 'Labor' and f.labortype = 'Customer' then f.cpsaleslabor else 0 end) as cpsaleslabor
      , (case when f.saletype = 'Parts' and f.labortype = 'Customer' then f.cpsalesparts else 0 end) as cpsalesparts
      , (case when f.saletype = 'Sublet' and f.labortype = 'Customer' then f.cpsalessublet else 0 end) as cpsalessublet
      , (case when f.saletype = 'Lube' and f.labortype = 'Customer' then f.cpsaleslube else 0 end) as cpsaleslube
      , (case when f.saletype = 'Misc' and f.labortype = 'Customer' then f.cpsalesmisc else 0 end) as cpsalesmisc
      , (case when f.saletype = 'Labor' and f.labortype = 'Customer' then f.cpcostlabor else 0 end) as cpcostlabor
      , (case when f.saletype = 'Parts' and f.labortype = 'Customer' then f.cpcostparts else 0 end) as cpcostparts
      , (case when 	= 'Sublet' and f.labortype = 'Customer' then f.cpcostsublet else 0 end) as cpcostsublet
      , (case when f.saletype = 'Lube' and f.labortype = 'Customer' then f.cpcostlube else 0 end) as cpcostlube
      , (case when f.saletype = 'Misc' and f.labortype = 'Customer' then f.cpcostmisc else 0 end) as cpcostmisc
      , (case when f.labortype = 'Customer' then f.cptax else 0 end) as cptax
      , (case when f.saletype = 'Labor' and f.labortype = 'Internal' then f.internalsaleslabor else 0 end) as internalsaleslabor
      , (case when f.saletype = 'Parts' and f.labortype = 'Internal' then f.internalsalesparts else 0 end) as internalsalesparts
      , (case when f.saletype = 'Sublet' and f.labortype = 'Internal' then f.internalsalessublet else 0 end) as internalsalessublet
      , (case when f.saletype = 'Lube' and f.labortype = 'Internal' then f.internalsaleslube else 0 end) as internalsaleslube
      , (case when f.saletype = 'Misc' and f.labortype = 'Internal' then f.internalsalesmisc else 0 end) as internalsalesmisc
      , (case when f.saletype = 'Labor' and f.labortype = 'Internal' then f.internalcostlabor else 0 end) as internalcostlabor
      , (case when f.saletype = 'Parts' and f.labortype = 'Internal' then f.internalcostparts else 0 end) as internalcostparts
      , (case when f.saletype = 'Sublet' and f.labortype = 'Internal' then f.internalcostsublet else 0 end) as internalcostsublet
      , (case when f.saletype = 'Lube' and f.labortype = 'Internal' then f.internalcostlube else 0 end) as internalcostlube
      , (case when f.saletype = 'Misc' and f.labortype = 'Internal' then f.internalcostmisc else 0 end) as internalcostmisc
      , (case when f.labortype = 'Internal' then f.internaltax else 0 end) as internaltax
      , (case when f.saletype = 'Labor' and f.labortype = 'Warranty' then f.warrantysaleslabor else 0 end) as warrantysaleslabor
      , (case when f.saletype = 'Parts' and f.labortype = 'Warranty' then f.warrantysalesparts else 0 end) as warrantysalesparts
      , (case when f.saletype = 'Sublet' and f.labortype = 'Warranty' then f.warrantysalessublet else 0 end) as warrantysalessublet
      , (case when f.saletype = 'Lube' and f.labortype = 'Warranty' then f.warrantysaleslube else 0 end) as warrantysaleslube
      , (case when f.saletype = 'Misc' and f.labortype = 'Warranty' then f.warrantysalesmisc else 0 end) as warrantysalesmisc
      , (case when f.saletype = 'Labor' and f.labortype = 'Warranty' then f.warrantycostlabor else 0 end) as warrantycostlabor
      , (case when f.saletype = 'Parts' and f.labortype = 'Warranty' then f.warrantycostparts else 0 end) as warrantycostparts
      , (case when f.saletype = 'Sublet' and f.labortype = 'Warranty' then f.warrantycostsublet else 0 end) as warrantycostsublet
      , (case when f.saletype = 'Lube' and f.labortype = 'Warranty' then f.warrantycostlube else 0 end) as warrantycostlube
      , (case when f.saletype = 'Misc' and f.labortype = 'Warranty' then f.warrantycostmisc else 0 end) as warrantycostmisc
      , (case when f.labortype = 'Warranty' then f.warrantytax else 0 end) as warrantytax
            , dd.dim_year_month_key
            , f.last_modified_date
  from fact_repair_order_job_events f
 inner join dim_dates dd
    on f.dim_ro_open_dealer_date_key = dd.dim_date_key
 inner join dim_store_departments dsd
    on f.dim_store_department_key = dsd.dim_store_department_key
 inner join xref_store_to_departments xstd
    on xstd.dim_store_department_key = dsd.dim_store_department_key
 inner join dim_stores ds
    on xstd.dim_store_key = ds.dim_store_key
 inner join dim_employees de
    on f.dim_advisor_key = de.dim_employee_key
 where f.dim_ro_open_dealer_date_key between to_char(to_date('20170729','yyyymmdd'),'J')::int and to_char(to_date('20170730','yyyymmdd'),'J')::int
   and ds.store_id  in ('S100037224', 'S100026211', 'S100034821')
 limit 10
) x;

Select allOpen in new window

and you can use your new cases "as is" with new query :) hopefully...

your old transformation is put into query and wrapped with another select, so you can use your new cases with those column names...

hope you can follow...

* I am just guessing by looking at what you have by the way... try this if it makes sense to you

thanks for your efforts, I am no longer on this project.