"IF" clause in "From" - SQL Server Forums

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 2008 Forums

SQL Server Administration (2008)

"IF" clause in "From"

Author

Topic

Naveed88
Starting Member

19 Posts

Posted - 2009-06-09 : 01:03:36

hi all,

can i have "IF" or "CASE" in "FROM" clase

i want

Select *
From (if(<Condition>) then TableA else TableB )

m i able to do this

thanks for any suggestion

(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer,9867374437-Mumbai.4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-09 : 01:32:10

[code]
IF (<condition>)
select * from tableA
ELSE
select * from tableB
[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 01:59:51

Maybe condition is based on column value?

SELECT CASE WHEN t1.Col2 = 'Y' THEN t1.Col3 ELSE t2.Col3 END AS Col3
FROM TableA AS t1
INNER JOIN TableB AS t2 ON t2.Col1 = t1.Col1

E 12°55'05.63"
N 56°04'39.26"

Naveed88
Starting Member

19 Posts

Posted - 2009-06-09 : 02:30:16

hi peso

you almost get it, but this is not my solution

let me explain more

i have a procedure containing very large query
having same column name so there is no need to write case in select

suppose::
i have two functions which returns same table with different calculation/ logic
1- GetProducts__With__AutoPriceCalculation()
2- GetProducts__Without__AutoPriceCalculation()

and i need

if(@is_Use_Auto_Pricing = true)
Select <a lot of columns> from GetProducts__With__AutoPriceCalculation()
Else
Select <a lot of columns> from GetProducts__Without__AutoPriceCalculation()

BUT my "Select" contains a lot of columns
with some calations and values from inner joined tables

just difference is function name
so i want conditionally select from particular function 1 or 2

thanks all of u

(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer,9867374437-Mumbai.4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 03:06:52

You can do this an a number of ways. Two of the most common are these

-- Method 1
SELECT		*
FROM		Table1 AS t1
INNER JOIN	Table2 AS t2 ON t2.Col1 = t1.Col1
INNER JOIN	(
			SELECT *
			FROM	GetProducts__With__AutoPriceCalculation()
			WHERE	@is_Use_Auto_Pricing = true

			UNION ALL

			SELECT	*
			FROM	GetProducts__Without__AutoPriceCalculation()
			WHERE	@is_Use_Auto_Pricing = false
		) AS d ON d.ColX = t2.ColX

-- Method 2
CREATE TABLE	#Temp
		(
			ColX INT,
			ColY INT
		)

IF @is_Use_Auto_Pricing = true
	INSERT	#Temp
	SELECT	*
	FROM	GetProducts__With__AutoPriceCalculation()
ELSE
	INSERT	#Temp
	SELECT	*
	FROM	GetProducts__Without__AutoPriceCalculation()

SELECT		*
FROM		Table1 AS t1
INNER JOIN	Table2 AS t2 ON t2.Col1 = t1.Col1
INNER JOIN	#Temp AS d ON d.ColX = t2.ColX

DROP TABLE	#Temp

E 12°55'05.63"
N 56°04'39.26"

Naveed88
Starting Member

19 Posts

Posted - 2009-06-09 : 05:32:40

Thanks Peso

both methods are good but still
i don't want to repeat my

"Select < * a lot of columns/calculation/inner_joined_values here> From ...."

for just function name is different

i have made two different procedures for each case

Thanks

(¨`·.·´¨) Always
`·.¸(¨`·.·´¨) Keep
(¨`·.·´¨)¸.·´ Smiling!
`·.¸.·´ & Programming
Regards....
"Deevan" [Naveed Anjum]
Web Developer,9867374437-Mumbai.4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources