Using QA with Alias
-
Hello
In Access i can build a query that use an alias or more to return calculations values
in a field that not exits on my table.
very simple example :
column sails of my table invoiceslines
column cost of my table invoiceslines
column myprofit
[sails]-[cost])*100/[sails]could i make the same thing in Sql Server QA, because sometimes i have subquerys with alias names that have a lot of lines and instead of typing this lines or make a copy/paste , it will be more simple to use an column field expression that wil not exist in my table, like my example.
Many Thanks
Luis Santos
-
column sails of my table invoiceslines
column cost of my table invoiceslines
column myprofit [sails]-[cost])*100/[sails]
SELECT sails, cost, (sails-cost)*100/sails as myprofit
FROM ....
_____________
Code for TallyGenerator -
Thanks for your reply
but effectively me sample is not complicate but if i use a statment like the following :
select ft.nmdoc,ft.fno,(case when ft.pais=1 then 'NAC' else case when ft.pais=2 then 'UE' else case when ft.pais=3 then 'O.Mercados' end end end) as Pais,
st.faminome as Familia,
Quant=sum(case when fi.etiliquido > 0 then qtt else -qtt end),
sum(fi.etiliquido) as Sails,
Cost=sum(case when fi.etiliquido < 0 then (-fi.ecusto*fi.qtt) else (fi.ecusto*fi.qtt)end),
MyProfit=((sum(fi.etiliquido)-(sum(case when fi.etiliquido < 0 then (-fi.ecusto*fi.qtt) else (fi.ecusto*fi.qtt)end)))*100/(sum(fi.etiliquido)))
from ft (nolock) inner join fi (nolock) on ft.ftstamp=fi.ftstamp
inner join st (nolock) on fi.ref=st.ref
where ft.fdata between #1# and #2# and ft.ndoc in(1,3,8) and fi.epv>0 and ft.nmdoc like 'F%'
group by ft.nmdoc,ft.fno,ft.pais,faminome order by ft.pais
in this example more complex, my question is if i can write "MyProfit=((sails-cost))*100/sails instead of writing :
MyProfit=((sum(fi.etiliquido)-(sum(case when fi.etiliquido < 0 then (-fi.ecusto*fi.qtt) else (fi.ecusto*fi.qtt)end)))*100/(sum(fi.etiliquido)))
Thanks
Luis Santos
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply