Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Oracle
I have 3 tables that I need to query from. I am trying to get a monthly breakdown of costs. I have a rough outline of some of the columns in the table below.
 
cost_type - this lists the type of costs and the category id
cat_type
cat_id
 
recur_cost
recur_id
lease_id
property_id
amount_expense
cat_id
salesTax
description
date_start
date_end
etc...
 
adj_cost
adjust_id
lease_id
property_id
amount_expense
cat_id
salesTax
description
due_date
etc...
 
please note that although these tables have some similar columns they dont have the same number of columns
 
I am trying to do a select against the tables where the lease_id and property_id are the parameters...because the calculations are dif. this is confusing me
 
i.e if its a recurring cost I have to take the amount_expense and divide it by 12 to get monthly, where as the adjustment table needs no calculation.
 
So for when I do a select using table.property_id and table.lease_id I am getting either way too many results or it is jumbling them together. If a cost has a recur_id, then it shouldn't have an adj_id, but for some reason it join them even tho they should be dif. rows...
 
also, the cost_cat table is being joined on the cat_id, but if it's an adjustment then it would need to join on that, otherwise it would join on the recur_cost.cat_id
 
I need the column names to stay amount_expense, sales_tx, ls_id etc when I get the results because I will copying this into that I will be using to create a
 
I am not in front of my work computer or I could copy my sql code, but its pretty much a mess right now.
 
ANY IDEAS? I believe the issue is also when I call in lease_id and property_id, I have to say WHERE (recur_cost.lease_id= ANDrecur_cost.property_id =) OR (adj_cost.lease_id= AND adj_cost.property_id =)
and it is connecting the results...
 
Basically..there is more to the query, but these are the majors...
a SELECT statement from the two table
Case
WHEN
recur_cost
THEN
amount_expense/12
ELSE
adj_cost.amount_expense
END
as montly_cost,
recur_id.recur_id,
adj_cost.adj_id,
 
cost_cat.cat_type as cost_category
(here it is given the cat_id from which ever table the current row is and returning the string meaning of what that category id means),
Case
WHEN
recur_cost
THEN
recur_cost.description
ELSE
adj_cost.description
END
as description,
recur_id.recur_id,
adj_cost.adj_id,
JOIN
cost_cat on cat_id(from whichever table the current row is)
WHERE (recur_cost.lease_id= AND recur_cost.property_id =) OR (adj_cost.lease_id= AND adj_cost.property_id =)
Posted 24-Jul-12 15:02pm
Edited 24-Jul-12 15:25pm
76.9K
v2
Comments
digimanus at 30-Jul-12 6:40am
   
please show full query or at least the complete WHERE clause. Who says this shoen CASE is the problem?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 24 Jul 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid