Some SQL help please!

[Drazen D. Babic]

Hi there

can anyone help with this SQL, no matter where I put the line
order_details and purchase_order_details, it does not make ANY result, why?

Only the inventory_transactions has results...

select p1.ID as ID, p1.Product_Name AS Name,  
(select sum(quantity) from order_details od where od.product_id = ID AND  od.status_id =4 GROUP BY od.product_id) quantity_on_back_order,
(select sum(quantity) from inventory_transactions it1 where it1.product_id = ID AND it1.transaction_type =1 GROUP BY it1.product_id) quantity_purchased,
(select sum(quantity) from inventory_transactions it1 where it1.product_id = ID AND it1.transaction_type =2 GROUP BY it1.product_id) quantity_sold,
(select sum(quantity) from inventory_transactions it1 where it1.product_id = ID AND it1.transaction_type =3 GROUP BY it1.product_id) quantity_on_hold,
(select sum(quantity) from purchase_order_details po1 where po1.product_id = ID AND po1.posted_to_inventory =1  GROUP BY po1.product_id) quantity_on_order,
p1.target_level AS target_level
from products p1
GROUP BY ID

Many thanks!

[Fabio Lenzarini]

Ho Drazen

try this:

select
         p1.ID           as ID
       , p1.Product_Name AS Name
       , (
                  select

                           sum(quantity)
                  from
                           order_details od
                  where

                           od.product_id    = p1.ID
                           AND od.status_id = 4
                  GROUP BY
                           od.product_id
         )
         quantity_on_back_order
       , (
                  select

                           sum(quantity)
                  from
                           inventory_transactions it1
                  where

                           it1.product_id           = p1.ID

                           AND it1.transaction_type =1
                  GROUP BY
                           it1.product_id
         )
         quantity_purchased
       , (
                  select

                           sum(quantity)
                  from
                           inventory_transactions it1
                  where

                           it1.product_id           = p1.ID

                           AND it1.transaction_type =2
                  GROUP BY
                           it1.product_id
         )
         quantity_sold
       , (
                  select

                           sum(quantity)
                  from
                           inventory_transactions it1
                  where

                           it1.product_id           = p1.ID

                           AND it1.transaction_type =3
                  GROUP BY
                           it1.product_id
         )
         quantity_on_hold
       , (
                  select
                           sum(quantity)
                  from
                           purchase_order_details po1
                  where

                           po1.product_id              = p1.ID

                           AND po1.posted_to_inventory =1
                  GROUP BY
                           po1.product_id
         )
         quantity_on_order

       , p1.target_level AS target_level

from
         products p1
GROUP BY
         ID

ciao

Fabio

[Drazen D. Babic]

Hi Andrew,

could you please advice about the Jam way of doing this query?

The closest I got is below, but that is showing partial data, not full data as above SQL
(for example, it is not showing Products with no values in other tables):

# def get_records(item):
#     res, inventory = [], product = [], ''

#     product = item.task.products.copy()
#     #product.set_where(id=41)
#     #product.set_order_by('id')
#     #product.open(fields=['id', 'product_name', 'target_level'])
#     product.open(fields=['id', 'product_name', 'target_level'],
#         #funcs={'quantity': 'sum'},
#         group_by=['id'], order_by=['id'])

   
#     for p in product:  
#         allocated_inventory = 0
#         target_level = 0
#         product_id = product.id.value
#         #product_name = product.product_name.display_text
#         target_level += product.target_level.value
#         print(product_id)

#         order_details = item.task.order_details.copy()
#         order_details.set_fields('product_id', 'status_id', 'quantity')
#         #order_details.set_where(product_id=product_id, status_id=4);
#         order_details.set_where(product_id=product_id);
       
#         order_details.open(fields=['product_id', 'quantity', 'status_id'],
#             funcs={'quantity': 'sum'},
#             group_by=['product_id'], order_by=['product_id'])
#         #print(order_details.status_id.value)
#         for i in order_details:
#             quantity = 0;
#             transaction_type = 0
#             quantity_sold = 0
#             quantity_purchased = 0
#             quantity_on_hold = 0
#             quantity_on_hand = 0
#             quantity_on_order = 0
#             quantity_on_back_order = 0
           
#             #quantity += order_details.quantity.value
#             #print()

#             inv_transactions = item.task.inventory_transactions.copy()            
#             inv_transactions.set_fields('product_id', 'transaction_type', 'quantity')
#             inv_transactions.set_where(product_id=product_id, transaction_type__in=[1,2,3])
#             inv_transactions.open(fields=['product_id', 'transaction_type', 'quantity'],
#                 funcs={'quantity': 'sum'},
#                 group_by=['transaction_type'], order_by=['product_id'])
#             #inv_transactions.open(fields=['product_id', 'transaction_type', 'quantity'],
#             #    funcs={'quantity': 'sum'},
#             #    group_by=['product_id'], order_by=['product_id'])

#             for i in inv_transactions:
#                 #allocated_inventory += inv_transactions.quantity.value
#                 if inv_transactions.transaction_type.value == 1:
#                     quantity_purchased += inv_transactions.quantity.value
#                 if inv_transactions.transaction_type.value == 2:
#                     quantity_sold += inv_transactions.quantity.value
#                 if inv_transactions.transaction_type.value == 3:
#                     quantity_on_hold += inv_transactions.quantity.value
#                 quantity_on_hand = quantity_purchased - quantity_sold
               
#                 purchase_order_details = item.task.purchase_order_details.copy()            
#                 purchase_order_details.set_fields('product_id', 'posted_to_inventory', 'quantity')
#                 purchase_order_details.set_where(product_id=product_id, posted_to_inventory=0)
#                 purchase_order_details.open(fields=['product_id', 'quantity'],
#                     funcs={'id': 'count'},
#                     group_by=['product_id'], order_by=['product_id'])
#                 for i in purchase_order_details:
#                     quantity_on_order = purchase_order_details.quantity.value
           
#             res.append(
#                 {
#                     'product_name': i.product_id.display_text,
#                     'target_level': target_level,
#                     'quantity_on_hold': quantity_on_hold,
#                     'quantity_on_hand': quantity_on_hand,
#                     'quantity_purchased': quantity_purchased,
#                     'quantity_sold': quantity_sold,
#                     'quantity_on_order': quantity_on_order
#                 }
#             )
#             print(res)
       
#     return res

Thanks

[Andrew Yushev]

Hi, Drazen

I think you can create a connection by using the connect method of the task 

https://jam-py.com/docs/refs/server/task/m_connect.html

and execute all the query you need.

Regards

ср, 15 февр. 2023 г. в 09:33, Drazen D. Babic <bab...@gmail.com>:

--
You received this message because you are subscribed to the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jam-py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jam-py/35576e06-40f5-4ec9-a451-b115b39d9785n%40googlegroups.com.

[app]

Hi all, 

I tried to optimise this SQL with ChatGPT :)

But the results are wrong, hence I do not think ChatGPT actually knows SQL :)

Here is the SQL:

SELECT
  p1.ID AS ID,
  p1.Product_Name AS Name,
  COALESCE(SUM(CASE WHEN od.status_id = 4 THEN od.quantity ELSE 0 END), 0) AS quantity_on_back_order,
  COALESCE(SUM(CASE WHEN it1.transaction_type = 1 THEN it1.quantity ELSE 0 END), 0) AS quantity_purchased,
  COALESCE(SUM(CASE WHEN it1.transaction_type = 2 THEN it1.quantity ELSE 0 END), 0) AS quantity_sold,
  COALESCE(SUM(CASE WHEN it1.transaction_type = 3 THEN it1.quantity ELSE 0 END), 0) AS quantity_on_hold,
  COALESCE(SUM(CASE WHEN po1.posted_to_inventory = 0 THEN po1.quantity ELSE 0 END), 0) AS quantity_on_order,
  p1.target_level AS target_level
FROM
  products p1
  LEFT JOIN order_details od ON p1.ID = od.product_id
  LEFT JOIN inventory_transactions it1 ON p1.ID = it1.product_id
  LEFT JOIN purchase_order_details po1 ON p1.ID = po1.product_id
GROUP BY
  p1.ID, p1.Product_Name, p1.target_level;

And the explanation from ChatGPT:

"Here's how this query is optimized:

1. Replaced subqueries with LEFT JOINs: Instead of using subqueries to calculate the quantities for each product, we use LEFT JOINs to join the relevant tables and calculate the quantities using conditional aggregation (CASE statements). This reduces the number of subqueries and can improve query performance.

2. Removed unnecessary GROUP BY columns: We only need to group by the product ID, product name, and target level columns, so we remove the "GROUP BY od.product_id" and "GROUP BY po1.product_id" clauses.

3. Used COALESCE to handle null values: If there are no matching rows in a table for a given product, the SUM function will return NULL. To handle this, we use the COALESCE function to replace NULL with zero.

I hope this helps!"

The result provided is wrong, because the Quantities are multiplied by X,

depending on the LEFT join.

So there you go ChatGPT :)