Post reply

getting where clause for views

  • 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

  • June 22, 2005 at 6:24 am

    #567358

    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 !!!**

  • June 22, 2005 at 6:26 am

    #567361

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

  • June 22, 2005 at 6:28 am

    #567363

    what's your typing speed Remi ?!







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

  • June 22, 2005 at 6:32 am

    #567365

    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

     

     

     

  • June 22, 2005 at 6:37 am

    #567369

    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 !!!**

  • June 22, 2005 at 6:53 am

    #567391

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

  • June 22, 2005 at 6:56 am

    #567397

    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 !!!**

  • June 22, 2005 at 7:28 am

    #567426

    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 .

  • June 22, 2005 at 7:33 am

    #567431

    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 !!!**

  • June 22, 2005 at 7:47 am

    #567445

    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

  • June 22, 2005 at 7:51 am

    #567449

    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

  • June 22, 2005 at 7:52 am

    #567451

    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

    1. June 22, 2005 at 7:53 am

      #567455

      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

    2. June 22, 2005 at 7:53 am

      #567452

      Please delete the last 2 message of the tripple post .

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

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