SQL Modulo Math Problem 1

[pticarl]

I am having a problem with using Mod(%) math in a SQL query with the IMINVLOC and OEORDLIN tables. I want to do a modulus division of OEORDLIN_SQL.qty_ordered by IMINVLOC_SQL.recom_min_ord to check customer order quantities for valid amounts. I have the following SQL Query but it gives me an error.

SELECT dbEORDLIN_SQL.item_no AS "item_no", dbEORDLIN_SQL.qty_ordered AS "qty_ordered", dbo.IMINVLOC_SQL.recom_min_ord AS "recom_min_ord", dbEORDLIN_SQL.qty_ordered % dbo.IMINVLOC_SQL.recom_min_ord AS "calc_qty"
FROM (dbo.IMINVLOC_SQL LEFT OUTER JOIN dbEORDLIN_SQL ON dbo.IMINVLOC_SQL.item_no = dbEORDLIN_SQL.item_no) WHERE dbo.IMINVLOC_SQL.recom_min_ord > 0
ORDER BY dbEORDLIN_SQL.item_no ASC

The error is SQL Server Error #8117 "Operand Data Type Numeric is invalid for Modulo operation".

I have created the same SQL Query in Access and I can run it just fine, but when I try to run the same query checking for calc_qty=0, I receive the same error.

Strangely enough I can create a Crystal Report that does allow me to search for only > 0 calc_qtys but I would prefer to use the above SQL as an event in Event Manager to allow more targetted reporting.

I have visually scanned through the tables and have not seen any data that appears to be corrupted or out of type.

I am running Macola SQL 7.6.1a

Thanks in advance,
pticarlw

 

[dgillz]

What does the % or modulo actually supposed to do? Perhaps there is another way to express this.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[pticarl]

What I am trying to do is determine if our customers are placing orders to our internal box quantities as defined in the IMINVLOC_SQL.recom_min_ord field. By using qty_ordered MOD recom_min_ord I get the remainder of qty_ordered / recom_min_ord, so if the remainder is 0 then I know that qty_ordered is a factor of our box qty, and hence a "valid" order quantity.

Once I can trap the "invalid" quantities, then I will be using Crystal or perhaps Event Manager to notify our CS staff that we need waivers from our customers for these odd quantities.

As a side note I am doing some additional research on this. Modulo should only work on integer based fields. Is the numeric datatype of Macola an int field or is it more likely a float/double or some other non-int datatype? If that is the case I may need to cast/convert the fields in the select statement before I perform the calculation?



Thanks!

 

[dgillz]

That field is not an integer field. It is possible to order .000001 of an item, hence the problem using modulo.

It is also worth pointing out that the IMINVLOC_SQL.recom_min_ord field is not intended for use with OE. This is intended for when YOU order the part, not the customer, and is used with either Purchase Orders, POP Orders, or Shop Floor Control Orders.

There is another field called order multiple which can keep your Orders in multiples of say, 100 or 12 or whatever, but this again does not apply to customer orders -- a customer can order anything.

If you want to do this, I believe Flexibility could easily accomodate the logic to keep them ordering in proper multiples.


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[pticarl]

The datatype of the field makes sense, and explains why the modulo operation has fits. For our purposes we do not sell portions of an item so we will have to workaround that limitiation.

We already are using the order multiple field for this pupose, but we need to balance raw material yields (order multiple) with box quantities (rec_min_order) when computer generating orders via MRP.

I wanted to capture these situations at order entry via Flex but I have not been able to get it installed as of yet. I believe that will be a much better solution.


Thanks!

 

[kscheeler]

You need to use the CAST command to convert those fields to an int.

See the example below.

SELECT dbEORDLIN_SQL.item_no AS "item_no",
dbEORDLIN_SQL.qty_ordered AS "qty_ordered",
dbo.IMINVLOC_SQL.recom_min_ord AS "recom_min_ord",
cast(dbEORDLIN_SQL.qty_ordered as int) % cast(dbo.IMINVLOC_SQL.recom_min_ord as int) AS "calc_qty"
FROM (dbo.IMINVLOC_SQL LEFT OUTER JOIN dbEORDLIN_SQL ON dbo.IMINVLOC_SQL.item_no = dbEORDLIN_SQL.item_no) WHERE dbo.IMINVLOC_SQL.recom_min_ord > 0
ORDER BY dbEORDLIN_SQL.item_no ASC

Kevin Scheeler

 

[dgillz]

Kevin,

Very nice.....a star for you.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[pticarl]

Thanks dgillz and Kevin!

Both your responses were on the money and have been very helpful. Casting the fields to int-types works great.

Now for a followup question if you have the time....

The SQL statement returns ALL of the rows regardless of outcome of the % operation. I can make this work but it would be beter to return only rows where the calc_qty >,= or <> 0. When I add any of these comparisons into the SELECT statement in the WHERE clause I begin to get errors again, div by zero for the most part. I am guessing that it is because how SQL executes statements, that is, the calculated field will always been calculated in a first pass then in a second pass, SQL will perform the exclusions in the WHERE clause etc?

I hope that I am not trying anyone's patience and thanks very much for your help!

 

[kscheeler]

The following script should do what you want. You want to include the calculation in the where clause and also only look for items that have a recommended minimum qty <> 0.

I changed it a little bit. In reading your earlier threads again, you only want items to show if there are orders out there for them. In that case, it would be better to use an inner join from IMINVLOC to OEORDLIN than a left outer join. Also, you want to include the LOC field in the join. Though this would only matter if you had more than 1 location set up in IMINVLOC. I also added the order number and line number as a reference.

SELECT oeordlin_sql.ord_no, oeordlin_sql.line_no, dbEORDLIN_SQL.item_no AS &quot;item_no&quot;, dbEORDLIN_SQL.loc as &quot;loc&quot;,
dbEORDLIN_SQL.qty_ordered AS &quot;qty_ordered&quot;,
dbo.IMINVLOC_SQL.recom_min_ord AS &quot;recom_min_ord&quot;,
cast(dbEORDLIN_SQL.qty_ordered as int) % cast(dbo.IMINVLOC_SQL.recom_min_ord as int) AS &quot;calc_qty&quot;
FROM (dbo.IMINVLOC_SQL INNER JOIN dbEORDLIN_SQL ON dbo.IMINVLOC_SQL.item_no = dbEORDLIN_SQL.item_no and
dbo.IMINVLOC_SQL.loc = dbEORDLIN_SQL.loc)
WHERE dbo.IMINVLOC_SQL.recom_min_ord <> 0 and cast(dbEORDLIN_SQL.qty_ordered as int) % cast(dbo.IMINVLOC_SQL.recom_min_ord as int) <> 0
ORDER BY dbEORDLIN_SQL.item_no ASC

Kevin Scheeler

 

[MacolaHelp]

Have you looked at using the selling to stocking unit of measure in the item master file? Or is it not practical because you want to sell less than box quantities from time to time? You can also restrict decimals for quantity to 0 in the company setup.

 

[pticarl]

Generally we only ship to our negotiated box quantities, but our CS staff does not always cross-reference that information at the order entry stage. As a short term solution I was going to us this report to catch exceptions, until I can get Flex installed. Once I have that I will work on capturing the errors at data entry as opposed to later. We want to retain the capability to have non-box qty orders, but have a reminder for our CS staff that they are entering a non-standard box qty. IMHO it looks like Flex will be a much better idea long-term, but I wanted to get something in place now to help capture errors immediately.


Your suggestion about the 0 decimal places is a good one also. I will investigate it further since it could help us in a few other areas also. Since this is in the company setup I assume that it would be a global change to Macola for all standard quantity fields?

Thanks!

 

[MacolaHelp]

Setting up the sell/stock ratio will actually prevent ordering in less than case lots unless you allowed decimals & you could sell portions of a case. If your case lot is 12 & the customer wanted 6, you could sell .5 of this item. That allows you to be proactive on selling case lots instead of reactive, looking for the exceptions after the fact. Just my 2 cents.

Setting the decimals to 0 in company setup will disallow decimals on data entry for any item number for all the modules. There are still some fields that will display decimals such as unit of measure conversion on stocking ratios.