Skip to Content

Query To Select Pre-Defined Options or Blanks

Hello Experts

I have written a simple query which check for product categories and when run the user selects the category he wants to check and it shows the products for the selected category

We have 4 categories Category A; Category B, Category C and Category D

Is It possible to add a selection for products that don’t have a category selected?

When the query is run, it should give the options to either select A, B, C, D or Blanks

Please help

SELECT 
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description', 
T0.[AvgPrice], 
T0.[OnHand], (T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 

FROM OITM T0 
WHERE T0.[U_Category] =[%0]
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jun 20, 2017 at 10:22 AM

    Hi,

    It is not possible to change/add data in where clause. System will show existing records to run the query.

    The option is either you select category by category or all category.

    SELECT 
    T0.[ItemCode]as'Product Code', 
    T0.[ItemName]as'Product Description', 
    T0.[AvgPrice], 
    T0.[OnHand],(T0.[OnHand]* T0.[AvgPrice])as'Value Of Stock'FROM OITM T0 
    WHERE (T0.[U_Category]=[%0] OR '[%0]'= ' ')
    Add comment
    10|10000 characters needed characters exceeded

  • Jun 20, 2017 at 09:08 AM

    Dear Rahul,

    Blank is related to null. Without category what value it shows it selection creteria.

    Regards:

    Balaji.S

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 21, 2017 at 01:51 PM
    Below query will provide the selection of Empty/null criteria or with value
    
    SELECT T0.ItemCode,T0.[ItemCode] as 'Product Code', 
    T0.[ItemName] as 'Product Description', 
    T0.[AvgPrice], 
    T0.[OnHand], (T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock', Isnull(T0.[U_Category],'') Category FROM [dbo].[OITM] T0 
    where  Isnull(T0.[U_Category],'') =[%0] 
    GROUP BY T0.ItemCode,T0.[ItemName] ,T0.[AvgPrice],T0.[OnHand], T0.[U_Category]
    ORDER BY T0.[U_Category]
    
    Regards:
    Balaji.S
    Add comment
    10|10000 characters needed characters exceeded