Skip to Content
0

Query To Select Pre-Defined Options or Blanks

Jun 20, 2017 at 05:57 AM

48

avatar image

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]
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Nagarajan K Jun 20, 2017 at 10:22 AM
0

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]'= ' ')
Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Nagarajan

I am getting an (SWEI) error

I kind of knew if wont be possible to show blanks as part of the list to select

Change of strategy

How do I change the query to show all category and blanks in the results

but with subtotals to show the total of all categories as well as total of blanks?

Thanks

0

I am not getting such error message. Please try this query again,

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]'= ' ')

0

Hi Nagrajan

Worked this time, dont know why it didnt work the first time

I had tried it a few times

Thanks for that

Much appreciated

0
Balaji Selvaraj Jun 20, 2017 at 09:08 AM
0

Dear Rahul,

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

Regards:

Balaji.S

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thanks for the answer Balaji

It's like I would have to create two queries, one to show results based on selection and second one to show null values

0

Sure its possible with single query.

0
Balaji Selvaraj Jun 21, 2017 at 01:51 PM
0
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
Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Balaji

Would it be possible to display all catogries and the ones blank without selecting from a dropdown?

The query result should show subtotals for each categories in the result

Thanks

0

Hi Bala,

Thanks it works for null.

0