May 9, 2006 at 4:12 pm
I have a table that has a dollar amount field (AMOUNT). My requirement is to select one row of data that shows AMOUNT twice; once will represent a revenue amount, and the other will represent a cost amount. So I have to select the field twice, but the information in each one is going to be different.
Any help is greatly appreciated.
May 9, 2006 at 5:07 pm
so are the differences from calculations or are there different entries for the two?
If they are calculations, your query would look something like:
select
AMOUNT AS REVENUE, --with appropriate calculations
AMOUNT AS COST --again with calculations
from MYTABLE
May 10, 2006 at 1:11 am
Hi,
could you possibly include the relevant parts of your database schema, it might help with understanding what you are trying to achieve.
If you have a single AMOUNT column in your table which may contain values with different meanings, then presumably you have another column which holds the context for the AMOUNT. If this is the case then you are trying to perform a pivot operation.
To show the AMOUNT column twice in a single row with different contexts, you would either need to have two copies of the table with different aliases and selection criteria, or use a searched CASE statement in your SELECT query in the form:
CASE WHEN context='Revenue' THEN AMOUNT ELSE NULL END AS REVENUE,
CASE WHEN context='Income' THEN AMOUNT ELSE NULL END AS INCOME
David
If it ain't broke, don't fix it...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply