getting where clause for views

Question

Post reply

getting where clause for views

Amrita bhatia

Right there with Babe

Points: 718
More actions
June 22, 2005 at 6:03 am

#164131

hi
Is there a way to get the where claus of a view .
For example if there is a view with following definition
"''''''''''''''''''''''''''''''''''''''''
create View v1 as
select A1,A2 from tab1
join
tab2
on tab1.A=tab2.A
where
A1> 10 and A2 > 23
''''''''''''''''''''''''''''''''''''''''
in the above A1 is of table tab1 and A2 of tab2
is there a query that can get where claus only for tab1 or tab2?????
is the above even possible with Sql query or we would need to parse the string but i think that wud lil error prone
we need the actual criteria that is defined in the view so in above case we wud need a1>10 and a2 >23 from the view??
so for any given view we need the where clause for a particular table
thanks
Amrita

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

I'm not sure I understand the question - you have a view V1 where A1 > 10 and A2 > 23......

If you want a where A1 = something...can you not do :

select * from v1 where A1 between 11 and 15....(or whatever your criteria is)..

Have I completely misunderstood the post ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Or do you need to have dynamic numbers for that query?

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

what's your typing speed Remi ?!

**ASCII stupid question, get a stupid ANSI !!!**

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 4

hi

hope this makes it little clear

wat we need is to get all the where clause for a view which involve a particular table

so if there is a view that depends on 2 tables tab1 and tab2 with where clause for both of them , we need only the where clause for the table defined.

thnx

amrita

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Sorry Amrita - but I still understand you to mean that your V1 view already has values filtered on A1 column from one table and A2 column on another table (say table1 and table2)....

Now you need to narrow your results further to get only where A1 = something OR A2 = something ?!

Why don't you post some sample data from your view and what you would like to see in your result set ?!

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

2550 to 365 so that would be about 7 times faster than you .

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

Sorry Remi - that's not a fair comparison:

1) You're online much longer than I am.

2) Your knowledge base is waaay stronger

3) You don't have any work...

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

1 - I'm online 24/7 as I'm on broadband, and so are you as far as I can tell

2 - Ya maybe

3 - Not true... I just built great automation to help me .

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

1. Staying "connected" is not the same as being online and actively participating

2. How modest!

3. How modest!

**ASCII stupid question, get a stupid ANSI !!!**

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 10

hi

to further clarify the issue

let the view be like create view "Alphabetical list of products" AS

SELECT Products.*, Categories.CategoryName

FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

WHERE Products.Discontinued =0 and Products.productId>13 and categories.categryId > 15

so now wen we give parameter as Products, it returns

Products.Discontinued =0 and Products.productId>13

and wen we give parameter as categories, it returns

categories.categryId > 15

hope that clarify the things

Amrita

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 11

hi

to clarify the issue let the view be like

create view "Alphabetical list of products" AS

SELECT Products.*, Categories.CategoryName

FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

WHERE Products.Discontinued =0 and Categories.CategoryID>12 and Products.CategoryID > 15

so we need a query that wen given parameter as Products returns Products.Discontinued =0 and Categories.CategoryID>12

and wen given Products returns

Products.CategoryID > 15

hope that clarify

amrita

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

You need a stored proc for this :

create procedure dbo.GetProducts @MinProdid as int, @MinCatId as int

As

SET NOCOUNT ON

SELECT Products.

, Categories.CategoryName

FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

WHERE Products.Discontinued = 0 and Products.productId>@MinProdid and categories.categryId > @MinCatId

SET NOCOUNT OFF

GO

Amrita bhatia Right there with Babe Points: 718 More actions · Answer 13

hi

to clarify the issue let the view be like

create view "Alphabetical list of products" AS

SELECT Products.*, Categories.CategoryName

FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

WHERE Products.Discontinued =0 and Categories.CategoryID>12 and Products.CategoryID > 15

so we need a query that wen given parameter as Products returns Products.Discontinued =0 and Categories.CategoryID>12

and wen given Products returns

Products.CategoryID > 15

hope that clarify

amrita

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Please delete the last 2 message of the tripple post .