Conditional ON, for JOIN, in SQL query

1

Good day all
Running latest V13

Please be patient as this is only my third query report !!

My query ( I think) is fine. Its just at the last step where I want to JOIN with tabBin to obtain
current stock quantities, where I am battling. The reason is as follows…

My query…

SELECT qi.parent, qi.idx, qi.item_code, qi.item_name, qi.qty, b.name, bei.item_code, bin.actual_qty
FROM `tabQuotation Item` qi 
LEFT OUTER JOIN `tabBOM` b ON (qi.item_code=b.item AND b.is_default='1') 
LEFT OUTER JOIN `tabBOM Explosion Item` bei ON b.name=bei.parent 
LEFT OUTER JOIN `tabBin` bin ON (bei.item_code=bin.item_code AND bin.warehouse='Stores - AG') ORDER BY qi.parent;

The result of this query…

+--------------------+-----+-----------+------------------+-----------+--------------+
| parent             | idx | item_code | name             | item_code | actual_qty   |
+--------------------+-----+-----------+------------------+-----------+--------------+
| SAL-QTN-2023-00001 |   1 | SKU50004  | NULL             | NULL      |         NULL |
| SAL-QTN-2023-00002 |   1 | SKU50005  | NULL             | NULL      |         NULL |
| SAL-QTN-2023-00003 |   1 | SKU10001  | NULL             | NULL      |         NULL |
| SAL-QTN-2023-00004 |   1 | SKU00001  | BOM-SKU00001-002 | SKU40001  | 12.000000000 |
| SAL-QTN-2023-00004 |   1 | SKU00001  | BOM-SKU00001-002 | SKU50005  |  2.000000000 |
| SAL-QTN-2023-00004 |   1 | SKU00001  | BOM-SKU00001-002 | SKU50004  |  3.000000000 |
| SAL-QTN-2023-00004 |   1 | SKU00001  | BOM-SKU00001-002 | SKU50002  |  6.000000000 |
| SAL-QTN-2023-00005 |   2 | SKU50006  | BOM-SKU50006-001 | SKU40001  | 12.000000000 |
| SAL-QTN-2023-00005 |   2 | SKU50006  | BOM-SKU50006-001 | SKU50002  |  6.000000000 |
| SAL-QTN-2023-00005 |   1 | SKU50004  | NULL             | NULL      |         NULL |
+--------------------+-----+-----------+------------------+-----------+--------------+

I had to take out 2 of the columns to make my table fit.

Essentially what I am trying to do, is get the “actual_qty” for each item_code.
The item_code column of the right ( bei.item_code) has some NULL entries. So when fetching
the “actual_qty”, I want to, in the case of a NUL entry, use the item_code further to the left,
qi.item_code.

I googled a “conditional ON” for a JOIN, and modified my query as below…

SELECT qi.parent, qi.idx, qi.item_code, qi.item_name, qi.qty, b.name, bei.item_code, bin.actual_qty
FROM `tabQuotation Item` qi 
LEFT OUTER JOIN `tabBOM` b ON (qi.item_code=b.item AND b.is_default='1') 
LEFT OUTER JOIN `tabBOM Explosion Item` bei ON b.name=bei.parent 
LEFT OUTER JOIN `tabBin` bin 
ON
(SELECT CASE WHEN (bei.item_code) IS NULL THEN (qi.item_code) ELSE bei.item_code)= (SELECT CASE WHEN bin.item_code
IS NULL THEN bin.item_code ELSE bin.item_code end );

The error is…a syntax error …

syntax to use near ')= (SELECT CASE WHEN bin.item_code
IS NULL THEN bin.item_code ELSE bin.item_c...' at line 7

I would appreciate some guidance on this.

2

You can also LEFT JOIN the Bin table twice. In SELECT section you would use

CASE WHEN IFNULL(bei.item_code, '') = '' THEN BIN_QI.actual_qty ELSE BIN_BEI.actual_qty

:sunglasses:

1 Like
3

Thank you @TurkerTunali so much for your suggestion.

I understand your suggestion. Obviously my translation of your suggestion into code, is flawed
because I am getting a syntax error

I have modified my query to the following …

SELECT qi.parent, qi.idx, qi.item_code, qi.item_name, qi.qty, b.name, bei.item_code, 
CASE WHEN IFNULL(bei.item_code, '') = '' THEN bin_add.actual_qty ELSE bin.actual_qty
FROM `tabQuotation Item` qi 
LEFT OUTER JOIN `tabBOM` b ON (qi.item_code=b.item AND b.is_default='1') 
LEFT OUTER JOIN `tabBOM Explosion Item` bei ON b.name=bei.parent 
LEFT OUTER JOIN `tabBin` bin ON (bei.item_code=bin.item_code AND bin.warehouse='Stores - AG')
LEFT OUTER JOIN `tabBin` bin_add ON (qi.item_code=bin_add.item_code AND bin_add.warehouse='Stores - AG');

Is that the way I should add the “CASE” part of the SELECT ?

4

part should be

CASE WHEN IFNULL(bei.item_code, '') = '' THEN bin_add.actual_qty ELSE bin.actual_qty END
1 Like
5

Bingo !!
Your’e a star @TurkerTunali , thank you.

Here is the last 2 columns of my result…

+-----------+------------------------------------------------------------------------------------------+
| item_code | CASE WHEN IFNULL(bei.item_code, '') = '' THEN bin_add.actual_qty ELSE bin.actual_qty END |
+-----------+------------------------------------------------------------------------------------------+
| NULL      |                                                                              3.000000000 |
| NULL      |                                                                              2.000000000 |
| NULL      |                                                                              0.000000000 |
| SKU40001  |                                                                             12.000000000 |
| SKU50005  |                                                                              2.000000000 |
| SKU50004  |                                                                              3.000000000 |
| SKU50002  |                                                                              6.000000000 |
| SKU40001  |                                                                             12.000000000 |
| SKU50002  |                                                                              6.000000000 |
| NULL      |                                                                              3.000000000 |
+-----------+------------------------------------------------------------------------------------------+

Until now, I have been executing the SQL-queries in command-line on the backend. WHen I
transfer this to my client-side-query report, I assume I have to use the entire
heading in my “fieldname” in my columns definition ?

Thank you again.

Just to complete this post, I shall include my query as it stands…incase someone else can
benefit from this…

type or paste code hereSELECT qi.parent, qi.idx, qi.item_code, qi.item_name, qi.qty, b.name, bei.item_code, 
CASE WHEN IFNULL(bei.item_code, '') = '' THEN bin_add.actual_qty ELSE bin.actual_qty END
FROM `tabQuotation Item` qi 
LEFT OUTER JOIN `tabBOM` b ON (qi.item_code=b.item AND b.is_default='1') 
LEFT OUTER JOIN `tabBOM Explosion Item` bei ON b.name=bei.parent 
LEFT OUTER JOIN `tabBin` bin ON (bei.item_code=bin.item_code AND bin.warehouse='Stores - AG')
LEFT OUTER JOIN `tabBin` bin_add ON (qi.item_code=bin_add.item_code AND bin_add.warehouse='Stores - AG') ORDER BY qi.parent;
6

Cancel my question about the heading … I added a AS in there…

1 Like
7

So here then the final query …

SELECT qi.parent, qi.idx, qi.item_code, qi.item_name, qi.qty, b.name, bei.item_code, 
CASE WHEN IFNULL(bei.item_code, '') = '' THEN bin_add.actual_qty ELSE bin.actual_qty END AS new_name
FROM `tabQuotation Item` qi 
LEFT OUTER JOIN `tabBOM` b ON (qi.item_code=b.item AND b.is_default='1') 
LEFT OUTER JOIN `tabBOM Explosion Item` bei ON b.name=bei.parent 
LEFT OUTER JOIN `tabBin` bin ON (bei.item_code=bin.item_code AND bin.warehouse='Stores - AG')
LEFT OUTER JOIN `tabBin` bin_add ON (qi.item_code=bin_add.item_code AND bin_add.warehouse='Stores - AG') ORDER BY qi.parent;
1 Like
8

So, you are listing BOM Item details with their stock levels, right?

9

Indeed.

Keeping track of BOM items on Quotations. I have in the meantime added the JOIN
to pick up the parent-doc-status.

1 Like