A simple function with a special variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

A simple function with a special variable

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 04:17:13

[code]CREATE FUNCTION testing([edit]@act smallint[/edit], @startdate datetime, @enddate datetime, @startcode varchar(30), @endcode varchar(30))
RETURNS TABLE
AS
RETURN
if @act = 1
BEGIN
SELECT h.sales_rep, sp.sales_desc, h.do_no, h.trx_type, h.debtor, c.cm_name, h.forex_code,
h.forex_rate, t.st_code, s.st_desc, t.total_price, t.quantity, t.qtt_out
FROM (sale_rep sp JOIN qtn_head h on sp.sales_code=h.sales_rep)
JOIN qtn_trx t on h.trx_type=t.trx_type and h.do_no=t.trnx_ref
LEFT JOIN st_mast s on t.st_code=s.st_code
LEFT JOIN customer c on h.debtor=c.cm_cust_no
WHERE h.trx_type = 'SO' and
(h.do_date >= @startdate and h.do_date < @enddate) and
(t.st_code >= @startcode and t.st_code <= @endcode)
END
ELSE
SELECT h.sales_rep, sp.sales_desc, h.do_no, h.trx_type, h.debtor, c.cm_name, h.forex_code,
h.forex_rate, t.st_code, s.st_desc, t.total_price, t.quantity, t.qtt_out
FROM (sale_rep sp JOIN qtn_head h on sp.sales_code=h.sales_rep)
JOIN qtn_trx t on h.trx_type=t.trx_type and h.do_no=t.trnx_ref
LEFT JOIN st_mast s on t.st_code=s.st_code
LEFT JOIN customer c on h.debtor=c.cm_cust_no
WHERE h.trx_type = 'SO' and
(h.do_date >= @startdate and h.do_date < @enddate) and
(t.st_code >= @startcode and t.st_code <= @endcode)
END
[/code]
Is there any method to shorten the function without using dynamic sql?

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-03 : 04:24:41

i have been staring at it for minutes and i can't find any different between the 2 query in the IF block. Can you highlight the difference ?

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 04:27:20

Woops mistake!!

CREATE FUNCTION testing([edit]@act smallint[/edit], @startdate datetime, @enddate datetime, @startcode varchar(30), @endcode varchar(30))
RETURNS TABLE
AS 
RETURN
	if @act = 1
	BEGIN
	SELECT	h.sales_rep, sp.sales_desc, h.do_no, h.trx_type, h.debtor, c.cm_name, h.forex_code, 
			h.forex_rate, t.st_code, s.st_desc, t.total_price, t.quantity, t.qtt_out
	FROM	(sale_rep sp JOIN qtn_head h on sp.sales_code=h.sales_rep)
						 JOIN qtn_trx t on h.trx_type=t.trx_type and h.do_no=t.trnx_ref
						LEFT JOIN st_mast s on t.st_code=s.st_code
						LEFT JOIN customer c on h.debtor=c.cm_cust_no 
	WHERE	h.trx_type = 'SO' and
			(h.do_date >= @startdate and h.do_date < @enddate) and
			(t.st_code >= @startcode and t.st_code <= @endcode)
	END
	ELSE
	SELECT	h.sales_rep, sp.sales_desc, h.do_no, h.trx_type, h.debtor, c.cm_name, h.forex_code, 
			h.forex_rate, t.st_code, s.st_desc, t.total_price, t.quantity, t.qtt_out
	FROM	(sale_rep sp JOIN qtn_head h on sp.sales_code=h.sales_rep)
						 JOIN qtn_trx t on h.trx_type=t.trx_type and h.do_no=t.trnx_ref
						LEFT JOIN st_mast s on t.st_code=s.st_code
						LEFT JOIN customer c on h.debtor=c.cm_cust_no 
	WHERE	h.trx_type = 'SO' and
			(h.do_date >= @startdate and h.do_date < @enddate) and
			(t.st_code >= @startcode and t.st_code <= @endcode)
	END

i was trying the function...and i know that function wouldn't work

AMITOFO+AMEN+YA ALLAH Hope the query works

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 04:29:08

Yes indeed you can shorten the function because there are two equal selects in IF and ELSE or maybe I'm blind.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 04:30:24

quote:
Originally posted by webfred

Yes indeed you can shorten the function because there are two equal selects in IF and ELSE or maybe I'm blind.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Oh damn - too late again.

No, you're never too old to Yak'n'Roll if you're too young to die.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 04:31:17

quote:
Originally posted by webfred

quote:
Originally posted by webfred

Yes indeed you can shorten the function because there are two equal selects in IF and ELSE or maybe I'm blind.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Oh damn - too late again.

No, you're never too old to Yak'n'Roll if you're too young to die.

not really...i think is the time gap between our country which causes the problem

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-03 : 04:33:46

quote:
Originally posted by webfred

quote:
Originally posted by webfred

Yes indeed you can shorten the function because there are two equal selects in IF and ELSE or maybe I'm blind.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Oh damn - too late again.

No, you're never too old to Yak'n'Roll if you're too young to die.

I am much nearer to waterduck than you

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-03 : 04:35:25

I like jokers like you both

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-03 : 04:36:59

your last post on 07/03/2009 : 04:27:20, does each query runs fine by itself ? because in the 2nd SELECT query, you reference column s.st_desc, but the table is not in the query (you have strike it off).

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 04:37:21

<--- problematic joker...only ask but not able to answer

AMITOFO+AMEN+YA ALLAH Hope the query works

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 04:44:41

>"< sorry sorry...mistake again...anyway give up using function....can't see a way to slot in if-else statement or is there a way to meet the requirement


if @action aka @act is 1
   left join st_mast
else
   don't join st_mast

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-03 : 04:52:39

if your 2 query different is one of the table join, you can do something like this to combine it


SELECT <column list>, somecol = coalesce(t1.somecol, t2.somecol)
FROM   <some tables join>
       LEFT JOIN table1 t1 ON  @flag = 1
                           AND <some other condition>
       LEFT JOIN table1 t2 ON  @flag <> 1
                           AND <some other condition>
WHERE  <conditions>


SELECT <column list>
FROM   <some tables join>
       LEFT JOIN table1 t1 ON  @flag = 1
                           AND <some other condition>
WHERE  <conditions>

EDIT in blue

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 04:58:02

erm...my condition are when a parameter is receiving Number 1, the whole query should join an extra table or few table but when a parameter is receiving Number 2, the query will not join that table

AMITOFO+AMEN+YA ALLAH Hope the query works

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 04:59:56

woops slow

AMITOFO+AMEN+YA ALLAH Hope the query works

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 05:02:49

[code]CREATE FUNCTION Testing(@act smallint, @startdate datetime, @enddate datetime, @startcode varchar(30), @endcode varchar(30))
RETURNS TABLE
AS
RETURN
SELECT h.sales_rep, sp.sales_desc, h.do_no, h.trx_type, h.debtor, c.cm_name, h.forex_code,
h.forex_rate, t.st_code, s.st_desc, t.total_price, t.quantity, t.qtt_out
FROM (sale_rep sp JOIN ord_head h on sp.sales_code=h.sales_rep)
JOIN ord_trx t on h.trx_type=t.trx_type and h.do_no=t.trnx_ref
RIGHT JOIN st_mast s on t.st_code=s.st_code and @act = 1
LEFT JOIN customer c on h.debtor=c.cm_cust_no
WHERE h.trx_type = 'SO' and
(h.do_date >= @startdate and h.do_date <= @enddate) and
(t.st_code >= @startcode and t.st_code <= @endcode)
[/code]
Done thx to mr.tan...best in singapore and some say jb xD

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-03 : 05:07:37

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 05:31:58

[code]CREATE FUNCTION Testing(@act smallint, @startdate datetime, @enddate datetime, @startcode varchar(30), @endcode varchar(30))
RETURNS TABLE
AS
RETURN
SELECT h.sales_rep, sp.sales_desc, h.do_no, h.trx_type, h.debtor, c.cm_name, h.forex_code,
h.forex_rate, t.st_code, s.st_desc, t.total_price, t.quantity, t.qtt_out
FROM (sale_rep sp JOIN ord_head h on sp.sales_code=h.sales_rep)
JOIN ord_trx t on h.trx_type=t.trx_type and h.do_no=t.trnx_ref
RIGHT JOIN st_mast s on t.st_code=s.st_code and @act = 1
LEFT JOIN customer c on h.debtor=c.cm_cust_no
WHERE h.trx_type = 'SO' and
(h.do_date >= @startdate and h.do_date <= @enddate) and
(t.st_code >= @startcode and t.st_code <= @endcode)
[/code]will show the result is @act is 1...but it won't show any result if @act is 0 T.T

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-03 : 05:33:44

why right join ?

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 05:40:35

it is because the report got 2 senario
1. when i wan to have the active stock only so oni those which appear in st_mast can be select out. Even they don't exist in transaction
2. when i wan to have all the stock, they will select all even they not exist in st_mast

st_mast = stock table
ord_head & ord_trx = transaction table

AMITOFO+AMEN+YA ALLAH Hope the query works

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-03 : 05:49:52

you mean you have situation where st_code exists in ord_trx but not in st_mast ?

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-03 : 05:51:46

yup, this might occur during...'undertable?'
or when the st_code is moved away from st_mast to another table

AMITOFO+AMEN+YA ALLAH Hope the query works

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources