Using WHERE clause in selecting data from a inline function (1 Viewer)

[Tiger955]

Hi!

I have an inline table-valued function (with parameters) which if course returns a table.

I can use a query 'SELECT * FROM Myfunction(par1, par2)' which returns ALL records.

I can also use a query 'SELECT * FROM Myfunction(par1, par2) WHERE myfield=1', but I cannot use a query 'SELECT * FROM Myfunction(par1, par2) where myCalculatedResultfield=1'.

'myCalculatedResultfield' exists as fieldname in the result table, but cannot be selected.

There is no error but no result at all.

How do I use a prior calculated field of a function in a WHERE clause for selecting from a result table?

Thanks.
Michael

SQL server 2008 R2

 

[pr2-eugin]

I think you can use a SubQuery,

SELECT tmpQry.* FROM (SELECT yourTableName.*, Myfunction(par1, par2) As newColumn FROM yourTableName) As tmpQry
WHEREtmpQry.newColumn = 1;

Completely air coded.

 

[Tiger955]

Thanks for your fast reply.

Regretfully, the result is still NULL.

By the way, a table with more than one columns cannot be set as 'newcolumn'.
But even when i changed to
SELECT * FROM (SELECT InventurDifferenz as newcolumn from fncBerechnungInventurdifferenz(1, '2014-06-30')) As tmpQry
WHERE tmpQry.newcolumn = 1;

returns NULL records.

 

[pr2-eugin]

Oops ! I just realized you were dealing with SQL server, unfortunately it is not my area of expertise. I am sure someone will be along soon to help you out ! Good luck and sorry.

 

[BlueIshDan]

an inline function

 

[Tiger955]

@BlueIshDan:
thats all you can give me for help?