November 21, 2007 at 5:08 am
Hi all,
belove the script that give me an error when I commit...I got a trouble for the IF condiction...It's possible in this kinfd of function?
TNKS Alen, Italy
ALTER FUNCTION [dbo].[fnGetConselRow] (
@Prezzo int,
@Tasso0 int,
@Skuint
)
RETURNS TABLE
AS
RETURN (
-- Ha il tasso0 = 1
IF @Tasso0 = 1
SELECT TOP 1
*,
@Sku Sku
FROM ePrice2K5.dbo.[consel]
WHERE imp_fin <= @Prezzo AND tasso0 = @Tasso0
ORDER BY imp_fin DESC
-- Non ha il tasso0 = 0
ELSE
SELECT TOP 1
*,
@Sku Sku
FROM ePrice2K5.dbo.[consel]
WHERE imp_fin >= @Prezzo AND tasso0 = @Tasso0
ORDER BY imp_fin DESC
)
November 21, 2007 at 5:24 am
Alen,
So, have you used some other method for this function used in some other table as computed one?:cool: (earlier post here)
Now for the current post,
You could use OR clauses in the query as WHERE filter such as....
SELECT TOP 1 *, @Sku Sku
FROM ePrice2K5.dbo.[consel]
WHERE ( imp_fin <= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 = 1 )
OR ( imp_fin >= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 != 1 )
ORDER BY imp_fin DESC
--Ramesh
November 21, 2007 at 6:17 am
Thanks but was an error in my code...there is an different ORDER BY if Tasso0 = 0
see belove
----------
ALTER FUNCTION [dbo].[fnGetConselRow] (
@Prezzo int,
@Tasso0 int,
@Skuint
)
RETURNS TABLE
AS
RETURN (
-- Ha il tasso0 = 1
IF @Tasso0 = 1
SELECT TOP 1
*,
@Sku Sku
FROM ePrice2K5.dbo.[consel]
WHERE imp_fin <= @Prezzo AND tasso0 = @Tasso0
ORDER BY imp_fin DESC
-- Non ha il tasso0 = 0
ELSE
SELECT TOP 1
*,
@Sku Sku
FROM ePrice2K5.dbo.[consel]
WHERE imp_fin >= @Prezzo AND tasso0 = @Tasso0
ORDER BY imp_fin ASC
)
But IF can be in table function??
TNKS Alen
November 21, 2007 at 7:22 am
Oops...I didn't see that!!!
SELECT TOP 1 *, @Sku Sku
FROM ePrice2K5.dbo.[consel]
WHERE ( imp_fin <= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 = 1 )
OR ( imp_fin >= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 != 1 )
ORDER BY ( CASE WHEN @Tasso0 = 1 THEN imp_fin DESC ELSE imp_fin ASC END )
Note:
If you're using it as a computed column or using it as RBAR, then i strictly suggest you to NOT to use the above solution.
--Ramesh
November 21, 2007 at 7:28 am
Thanks RAmesh...but always have an eror on compile... R U sure??
----
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGetConselRow] (
@Prezzo int,
@Tasso0 int,
@Skuint
)
RETURNS TABLE
AS
RETURN (
SELECT TOP 1 *, @Sku Sku
FROM ePrice2K5.dbo.[consel]
WHERE ( imp_fin <= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 = 1 )
OR ( imp_fin >= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 != 1 )
ORDER BY ( CASE WHEN @Tasso0 = 1 THEN imp_fin DESC ELSE imp_fin ASC END )
)
Error at this line...
ORDER BY ( CASE WHEN @Tasso0 = 1 THEN imp_fin DESC ELSE imp_fin ASC END )
November 21, 2007 at 7:44 am
Alen,
Its my mistake, may be because its too late in the day;)
Solution 1
SELECT TOP 1 *, @Sku Sku
FROM ePrice2K5.dbo.[consel]
WHERE ( imp_fin <= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 = 1 )
OR ( imp_fin >= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 != 1 )
ORDER BY ( CASE WHEN @Tasso0 = 1 THEN imp_fin END ) DESC, ( CASE WHEN @Tasso0 != 1 THEN imp_fin END )
Solution 2
SELECT TOP 1 *, @Sku Sku
FROM ePrice2K5.dbo.[consel]
WHERE ( imp_fin <= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 = 1 )
OR ( imp_fin >= @Prezzo AND tasso0 = @Tasso0 AND @Tasso0 != 1 )
ORDER BY ( CASE WHEN @Tasso0 = 1 THEN -imp_fin ELSE imp_fin END )
I hope both of them are working:::
--Ramesh
November 21, 2007 at 7:56 am
keep in mind that this tactic kills any possibility of using indexes, AND will choke unless the two operands are of the same data type. Meaning - this will not scale well....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 21, 2007 at 8:02 am
Close but no cigar....
There is something of wrong on the order clausole...but I have learn something of new with your example....
THSNKS anyway...
November 21, 2007 at 8:15 am
I do agree with what Matt has to offer. Thats why i earlier pointed out to NOT to use this type of solution. It would surely not scale well.
--Ramesh
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply