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 2000 Forums
 Transact-SQL (2000)
 Find and Replace Subselect

Author  Topic 

jonathanNXTG
Starting Member

8 Posts
Posted - 2005-11-22 : 04:11:48
Hello,

I Want to do the following:

SELECT * FROM (SELECT * FROM products WHERE products.ID > 5) AS first

This SQL statement will be filtered with the "WHERE products.name like 'sh%'" WHERE clause

This should result in a VARCHAR containing the following:

SELECT * FROM (SELECT * FROM products WHERE products.ID > 5 AND products.name like 'sh%') AS first WHERE products.name like 'sh%'

Now, adding the additional WHERE clause is already done for a single 'SELECT' and a select containing a 'UNION'. The only problem i have is that every subselect must be found and expanded with the where clause.

Anybody knows how to find and replace subselects using T-SQL?

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-11-22 : 04:42:57
SELECT * FROM (SELECT * FROM products WHERE products.ID > 5) AS first

is equivalent to

SELECT * FROM products WHERE products.ID > 5

Are you using that as a subquery?
What are you trying to do?


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jonathanNXTG
Starting Member

8 Posts
Posted - 2005-11-22 : 05:01:00
quote:
Originally posted by madhivanan

SELECT * FROM (SELECT * FROM products WHERE products.ID > 5) AS first

is equivalent to

SELECT * FROM products WHERE products.ID > 5

Are you using that as a subquery?
What are you trying to do?


Madhivanan

Failing to plan is Planning to fail


Every Select statement must be expanded with a where clause. These 2 params are given to a functions that returns a varchar. The function checks the SELECT and adds the WHERE clauses that fit with the select ( select * from tblProducts where tblOrder.id = 2, will never occure).

Because every select must be manipulated induvidially, I need a function that can give me all the selects and subselects in a select statement, so the following will happen:

Original:
select * from tblProduct UNION select * from(Select * from tblPrice UNION select * from tblStandard) LEFT JOIN tblGivin


Where:
'tblProduct.name like ''a%'',tblPrice.Value > 40,tblStandard.Value > 100,tblGivin.Value < 300'

Result:
select * from tblProduct WHERE tblProduct.name like ''a%'' UNION select * from(Select * from tblPrice WHERE tblPrice.Value > 40 UNION select * from tblStandard tblStandard.value > 100) LEFT JOIN tblGivin WHERE tblGivin.Value < 300

No matter what tables, no matter howmany subselects or unions, every Where-clause that could match a select table should be added to that select.

The good thing is: that work is done for select and union.
But I don't know how to find a subselect and replace it by its modifield select.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-11-22 : 05:23:22
Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jonathanNXTG
Starting Member

8 Posts
Posted - 2005-11-22 : 05:32:40
quote:
Originally posted by madhivanan

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail


Simple, I have a SP for my datagrid.
This sp can be called with a param that filters the resultset.

By working this way I can filter my dataset on fields that are not present in my dataset.
But to make this work, every select within the SP must be updated with the where clause.

To make this work for any SP with any param I need to check if there are subselects. If so, I need to add a possible where clause, if any of the tables of the subselect are submitted in the param.

Filter a dataset on fields it doesn't contains => expand SP with Where clauses
Go to Top of Page

jonathanNXTG
Starting Member

8 Posts
Posted - 2005-11-22 : 06:49:45
What I actually need is a function that can find and replace a subselect in a select statement. Ofcourse the function should call itself when a subselect contains a subselect.


This is the code i use to add a where clause to a select

CREATE FUNCTION dbo.AddFilterConditions
(
@orgSelect VARCHAR(8000), --select * from [table]
@qrySource VARCHAR(8000), --[table].[field] [operator] [value],[table]...,
@endSelect VARCHAR(8000) --[Group By] [...] [Having] [...] [Order By] [...]
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @selectBegin INT
DECLARE @selectEnd INT
DECLARE @select VARCHAR(8000)
DECLARE @return VARCHAR(8000)
SET @selectBegin = 0
SET @selectEnd = 0
SET @return = ''
SET @Orgselect = REPLACE(@Orgselect, CHAR(9), ' ')
SET @Orgselect = REPLACE(@Orgselect, CHAR(10), ' ')
SET @Orgselect = REPLACE(@Orgselect, CHAR(13), ' ')

SET @selectBegin = CHARINDEX('SELECT', @Orgselect, @selectBegin)
SET @selectEnd = CHARINDEX('UNION', @Orgselect, @selectBegin) - @selectBegin
IF @selectEnd < 0
SET @selectEnd = LEN(SUBSTRING(@Orgselect, @selectBegin, LEN(@Orgselect)))
WHILE @selectBegin > 0 AND @selectEnd > 0
BEGIN
SET @select = SUBSTRING(@Orgselect, @selectBegin, @selectEnd)
SET @selectBegin = CHARINDEX('SELECT', @Orgselect, @selectBegin + @selectEnd)
SET @selectEnd = CHARINDEX('UNION', @Orgselect, @selectBegin) - @selectBegin
IF @selectEnd < 0
SET @selectEnd =LEN(SUBSTRING(@Orgselect, @selectBegin, LEN(@Orgselect)))


SET @select = REPLACE(@select, CHAR(13), ' ')
SET @qrySource = REPLACE(@qrySource, CHAR(13), ' ')
SET @endSelect = REPLACE(@endSelect, CHAR(13), ' ')

DECLARE @temp VARCHAR(8000)
DECLARE @qryBuild VARCHAR(8000)
DECLARE @tables VARCHAR(8000)
DECLARE @regex VARCHAR(1000)

SET @qryBuild = ''
SET @tables = ''
--SELECT TABLES
SET @temp = SUBSTRING(@select, CHARINDEX('FROM', @select, 0), LEN(@select))
DECLARE @begin INT
DECLARE @end INT
DECLARE @table VARCHAR(100)
SET @begin = CHARINDEX('tbl', @temp, 0)
SET @end = CHARINDEX(' ', @temp, @begin) - @begin
IF @end < 0
SET @end = LEN(SUBSTRING(@temp, @begin, LEN(@temp)))
WHILE @begin > 0 AND @end > 0
BEGIN
SET @table = SUBSTRING(@temp, @begin, @end)
SET @regex = '[a-zA-Z ]{' + CAST(LEN(@table) as varchar) + '}'
IF dbo.FindRegularExpression(@table, @regex, 0) = 1
SET @tables = @tables + @table + ','
SET @begin = CHARINDEX('tbl', @temp, @begin + @end)
SET @end = CHARINDEX(' ', @temp, @begin) - @begin

IF @end < 0
SET @end = LEN(SUBSTRING(@temp, @begin, LEN(@temp)))
END

--SELECT PARAMS
DECLARE @param VARCHAR(8000)
DECLARE @paramEnd INT
SET @param = ''
SET @begin = CHARINDEX('tbl', @qrySource, 0)
SET @end = CHARINDEX(' ', @qrySource, @begin) - @begin
WHILE @begin > 0 AND @end > 0
BEGIN
SET @table = SUBSTRING(SUBSTRING(@qrySource, @begin, @end), 0, CHARINDEX('.', SUBSTRING(@qrySource, @begin, @end), 0))
SET @paramEnd = CHARINDEX(',', @qrySource, @begin)
IF @paramEnd = 0
SET @param = SUBSTRING(@qrySource, @begin, LEN(@qrySource))
ELSE
SET @param = SUBSTRING(@qrySource, @begin, @paramEnd - 1)

SET @begin = CHARINDEX('tbl', @qrySource, @begin + @end)
SET @end = CHARINDEX(' ', @qrySource, @begin) - @begin

IF CHARINDEX('.', @table, 0) = 0
BEGIN
IF CHARINDEX(@table+',', @tables, 0) > 0
BEGIN
IF LEN(@qryBuild) = 0 AND CHARINDEX('WHERE', @select, 0) = 0
BEGIN
SET @qryBuild = ' WHERE '
SET @qryBuild = @qryBuild + @param
END
ELSE
SET @qryBuild = @qryBuild + ' AND ' + @qryBuild + @param
END
END
END

SET @return = @return + (@select + @qryBuild + @endSelect)
IF @selectEnd + @selectBegin >= LEN(@Orgselect)
SET @return = @return + ' UNION '

END
--SET RETURN PARAM
RETURN @return
END
Go to Top of Page

jonathanNXTG
Starting Member

8 Posts
Posted - 2005-11-22 : 06:52:09
Additional function needed in previous function


CREATE FUNCTION dbo.FindRegularExpression
(
@source varchar(5000),
@regexp varchar(1000),
@ignorecase bit = 0
)
RETURNS bit
AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @objMatches integer
DECLARE @objMatch integer
DECLARE @count integer
DECLARE @results bit

EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END
Go to Top of Page

jonathanNXTG
Starting Member

8 Posts
Posted - 2005-11-22 : 06:52:14
Additional function needed in previous function


CREATE FUNCTION dbo.FindRegularExpression
(
@source varchar(5000),
@regexp varchar(1000),
@ignorecase bit = 0
)
RETURNS bit
AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @objMatches integer
DECLARE @objMatch integer
DECLARE @count integer
DECLARE @results bit

EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END
Go to Top of Page

jonathanNXTG
Starting Member

8 Posts
Posted - 2005-11-22 : 06:52:14
Additional function needed in previous function


CREATE FUNCTION dbo.FindRegularExpression
(
@source varchar(5000),
@regexp varchar(1000),
@ignorecase bit = 0
)
RETURNS bit
AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @objMatches integer
DECLARE @objMatch integer
DECLARE @count integer
DECLARE @results bit

EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-11-22 : 07:40:47
I am not sure about your requirement
Refer this for Dynamic Search
http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-11-23 : 02:54:13
It seems you edited the topic
Can you highlight the changes?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site