Skip to Content

Product Category Query

Hi Experts

We have categorised stock in A, B, C & D, Finished Stock, Lamps & Blanks and every month we run these queries to get the stock value of the different categories of stock

Have to run four queries to get these values, the first Query Product Category has to be run four times to get the results for the A, B, C & D

Then another query to find the results for Finished Goods

And then Another query for Lamps

And finally, another query to check for Blanks

I was wondering if its possible to combine all these queries to get the results as SubColumns, so When you run the query, it should display

Product Category A

Results

Sub Total

Product Category B

Results

Sub Total

Product Category C

Results

Sub Total

Product Category D

Results

Sub Total

Product Category – Finished Goods

Results

Sub Total

Product Category - Lamps

Results

Sub Total

Product Category - Blanks

Results

Sub Total



Please help me combine these queries

The Queries are below

1. Product Category

Product 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]

2. Product Category - Finished Goods

Product Category – Finished Goods
SELECT 
T0.[ItemCode], 
T0.[ItemName], 
T0.[OnHand], 
T0.[AvgPrice], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE T0.[ItmsGrpCod] <> '287' AND  
T0.[ItmsGrpCod] <> '288' AND  
T0.[OnHand] >= 1 
AND 
T0.[ItmsGrpCod] <> '289' 
ORDER BY T0.[ItemCode]

3. Product Category - Lamps

Product Category – Lamps
SELECT 
T0.[ItemCode], 
T0.[ItemName], 
T0.[OnHand], 
T0.[AvgPrice], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock'
FROM OITM T0 
WHERE T0.[ItmsGrpCod] <> '287' AND  
T0.[ItmsGrpCod] <> '288' AND  
T0.[OnHand] >= 1 AND 
T0.[ItmsGrpCod] = '289' 
ORDER BY T0.[ItemCode]

4. Product Category - Blanks

Product Category – Blanks
SELECT 
T0.[ItemCode] as 'Product Code', 
T0.[ItemName] as 'Product Description',  
T0.[OnHand], 
T0.[AvgPrice], 
(T0.[OnHand] * T0.[AvgPrice]) as 'Value Of Stock' 
FROM OITM T0 
WHERE T0.[ItmsGrpCod] = '287' AND  
T0.[U_Category] IS NULL 
AND  T0.[OnHand] > 0

Thanks

Rahul

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 18, 2017 at 02:35 PM

    Hi Rahul,

    As long as the amount of columns is the same for each query, you can combine them using the UNION ALL function. The syntax is simple:

    query 1 with 5 columns
    UNION ALL
    query 2 with 5 columns
    UNION ALL
    query 3 with 5 columns
    etc.

    You can add a text column at the beginning of each query to describe the Category. So you would get:

    SELECT 'Category A',
    T0.[ItemCode], 
    T0.[ItemName], 
    T0.[OnHand], 
    T0.[AvgPrice],(T0.[OnHand]* T0.[AvgPrice])as'Value Of Stock'
    etc.

    Please note that this syntax does not allow you to use the traditional variables ([%0]) in the normal way. You can still have variables, but we can tell you how, if that is necessary.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded