locked
Filtering in an SQLDatasource RRS feed

  • Question

  • User-430413477 posted

    In a SQLDatasource I hve the following sql

    SELECT
    Locations.Name,
    COUNT(*) AS TotalCars,
    SUM(CASE WHEN StockType = 'NEW' OR StockType = 'DEMO' THEN 1 ELSE 0 END) AS TotalNewAndDemoCars,
    SUM(CASE WHEN StockType = 'USED' THEN 1 ELSE 0 END) AS TotalUsedCars,
    SUM(CASE WHEN StockType = 'NEW' AND Make = 'MAZD' THEN 1 ELSE 0 END) AS NewMazds,
    SUM(CASE WHEN StockType = 'NEW' AND Make = 'MITS' THEN 1 ELSE 0 END) AS NewMits,
    SUM(CASE WHEN StockType = 'NEW' AND Make = 'HYUN' THEN 1 ELSE 0 END) AS NewHyun
    
    FROM InventoryDataLive INNER JOIN Locations ON InventoryDataLive.Company = Locations.LocationsID
    
    GROUP BY Locations.Name

    This works fine but now I want to use a filter expression to filter the results by price of the cars included in the above results, like all cars > 25,000 should be the only cars in the counts.

    But the price is not in the SQL.

    How would I do the filtering??


     

    Monday, August 19, 2013 12:24 PM

All replies

  • User-1360095595 posted

    You can add a where clause to your sql statement with a parameter. Then specify the parameter in the selectparameters. Now you can handle the selecting event and in there you can specify the value if the parameter to filter by. 

    Monday, August 19, 2013 12:34 PM
  • User-1716253493 posted

    You can't filter your results using filter expression, because your query results no price column.

    SelectCommand="SELECT a,b,c FROM tbl"
    FilterExpression="a='{0}'"

    Filter expression will filter your query results.

    Maybe you can pass selectparameter, and use it in where statement

    Monday, August 19, 2013 9:43 PM
  • User-430413477 posted

    Then instead of filtering I do a normal where clause.

    Then the Retail field etc does not need to be in the grouped query results

     

    SELECT
    Locations.Name,
    COUNT(*) AS TotalCars,
    Locations.LocationsID
    
    FROM            InventoryDataLive INNER JOIN
                             Locations ON InventoryDataLive.Company = Locations.LocationsID
    WHERE        (InventoryDataLive.Retail >= @BeginRetail) AND (InventoryDataLive.Retail <= @EndRetail) AND (InventoryDataLive.Age >= @BeginAge) AND
                             (InventoryDataLive.Age <= @EndAge)
    GROUP BY Locations.Name, Locations.LocationsID
    ORDER BY Locations.Name

    Tuesday, August 20, 2013 9:14 AM