Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Select into function
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2000
Miscellaneous
Thread ID:
01076328
Message ID:
01076934
Views:
24
Why do you need to return a table from a tax function? It seems to me that all calculations could be be done inside it.
Also you can use calculated columns in consequent calculations by employing derived tables
SELECT *, s.price + disc as netprice
FROM (
	select [lineno],s.item,descrip,qtyord,qtyshp,cost,
	(((s.price*(disc/100))+(s.price*(specialdisc/100))+(s.price*(termdisc/100))+disc2+disc3)*-1) as disc,
	s.price,extprice,icitem.sunmsid
	from pkpro..sotran as s,
		JOIN icitem ON s.item = icitem.item
	where sono = @sono
	) dt1
>I'm trying to select a bunch of information including lineno, and I want to pass lienno into a function but its keeps giving me an incorrect syntax error. Basically,
>
>select [lineno],(select item from tax([lineno])) from table
>
>cant seem to get this to work, I think my syntac might be off.
>
>CREATE PROCEDURE sp_invoicedetail
>@sono as char(10)
>AS
>select [lineno],s.item,descrip,qtyord,qtyshp,cost,
>(((s.price*(disc/100))+(s.price*(specialdisc/100))+(s.price*(termdisc/100))+disc2+disc3)*-1) as disc,
>--DJS tried using the disc name first but didnt work
>s.price + (((s.price*(disc/100))+(s.price*(specialdisc/100))+(s.price*(termdisc/100))+disc2+disc3)*-1) as netprice,
>--(select ((qtyshp1*extprice1)+(qtyshp2*extprice2)+(qtyshp3*extprice3)) from taxitem(@sono,[lineno])) as tax,
>s.price,extprice,icitem.sunmsid
>from pkpro..sotran as s,
>icitem
>where sono = @sono
>and s.item = icitem.item
>GO
--sb--
Previous
Reply
Map
View

Click here to load this message in the networking platform