topic Re: SQL Queries in Application Development and Automation Discussions

SQL Queries

Former Member — Wed, 24 Oct 2018 11:56:01 GMT

Hello Guys ,

May i ask a little bit of your help.

below queries are the process of our Re order level report.

Where our logistics team is calculating the order to be needed.

for example :

Item Available Re_order To be ordered

A 100 150 50

B 250 100 0 (will not show anything)

C 1500 2750 1250

if available (100) is lesser and re order(150) is greater then to be ordered result value should be 50.

and if Available (150) is greater and re order (100) is lesser the to be ordered result value will be Zero.

Below is my SQL code:

SELECT T0.[ItemCode], T0.[ItemName], T1.[OnHand] as 'In Stock', T1.[IsCommited] as 'Committed', T1.[OnOrder] as 'Ordered', T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] as 'Available', T1.[MinOrder] as 'Re-Order', T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] - T1.[MinOrder] as 'To be ordered', T0.[LstEvlPric], T0.[LastPurPrc], T0.[LastPurCur] as 'Currency', T2.[ItmsGrpNam] as 'Supplier' FROM OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] UNION SELECT T0.[ItemCode], T0.[ItemName], T1.[OnHand] as 'In Stock', T1.[IsCommited] as 'Committed', T1.[OnOrder] as 'Ordered', T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] as 'Available', T1.[MinOrder] as 'Re-Order', T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] - T1.[MinOrder] as 'To be ordered', T0.[LstEvlPric], T0.[LastPurPrc], T0.[LastPurCur] as 'Currency', T2.[ItmsGrpNam] as 'Supplier' FROM OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] WHERE T1.[MinOrder] >0 and T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] - T1.[MinOrder] <0

Re: SQL Queries

anindya_bose — Wed, 24 Oct 2018 17:00:54 GMT

You can use a Case statement like below .

SELECT 
ITEM, 
IN_STOCK, 
MIN_STOCK , 
(CASE WHEN  (MIN_STOCK - IN_STOCK) > 0 THEN (MIN_STOCK - IN_STOCK) ELSE 0 END ) AS RE_ORDER
FROM <YOUR_TABLE>

Re: SQL Queries

Former Member — Sun, 28 Oct 2018 05:59:04 GMT

thank you for the query.

Sir, can you help me provide if else condition for this. since case is coming correct but it requires also that

if the condition is 'AVAILABLE' is lesser than re-order then (Re-order minus Available ) as to be Ordered.

but if condition is Availble greater than Re-Order then ( result will be 0) as to be ordered.

Thank you for your time and effort.

appreciate your help

Re: SQL Queries

anindya_bose — Sun, 28 Oct 2018 17:21:44 GMT

(MIN_STOCK - IN_STOCK)>0 THEN (MIN_STOCK - IN_STOCK)

Above statement is basically doing the same .

If my understanding is not correct, can you put a table and explain what exactly you want the query to return .

Cheers

Anindya

Re: SQL Queries

Former Member — Thu, 08 Nov 2018 07:57:36 GMT

Dear Anindya Bose this is our query

Re: SQL Queries

Former Member — Thu, 08 Nov 2018 07:58:11 GMT

this is the sample

Re: SQL Queries

Former Member — Thu, 08 Nov 2018 07:59:09 GMT

actually our Query is showing proper result but the problem is it has something to do with the SQL since it is not showing all the items in which half of our items is not listed

Re: SQL Queries

anindya_bose — Thu, 08 Nov 2018 19:28:05 GMT

If you are missing some rows, then check INNER JOIN and WHERE Clause . I am not aware of the complete requirement here , but you can change INNER to LEFT OUTER JOIN and/or drop the WHERE Clause to understand where you are missing records .

To filter rows , you can use "WHERE [To Be Ordered] > 0 " clause

Regards

Anindya