×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help, script on ODBC is not working

Help, script on ODBC is not working

Help, script on ODBC is not working

(OP)
Hi,

I'm attempting to extract data from a Client's pastel partner site and I get the following error "*ERROR* Invalid field size". Can anyone shed some light, I have not had this issue before and have used this script on other clients. This is the script

SELECT
Rtrim(m.ItemCode) AS Item_Code,
Rtrim(IsNull(m.StoreCode, '')) AS Location,
'' AS Date_Added, -- there is no date
Round(CASE
WHEN CURDATE() BETWEEN l.PerStartThis01 AND l.PerEndThis01 THEN m.CostThis01
WHEN CURDATE() BETWEEN l.PerStartThis02 AND l.PerEndThis02 THEN m.CostThis02
WHEN CURDATE() BETWEEN l.PerStartThis03 AND l.PerEndThis03 THEN m.CostThis03
WHEN CURDATE() BETWEEN l.PerStartThis04 AND l.PerEndThis04 THEN m.CostThis04
WHEN CURDATE() BETWEEN l.PerStartThis05 AND l.PerEndThis05 THEN m.CostThis05
WHEN CURDATE() BETWEEN l.PerStartThis06 AND l.PerEndThis06 THEN m.CostThis06
WHEN CURDATE() BETWEEN l.PerStartThis07 AND l.PerEndThis07 THEN m.CostThis07
WHEN CURDATE() BETWEEN l.PerStartThis08 AND l.PerEndThis08 THEN m.CostThis08
WHEN CURDATE() BETWEEN l.PerStartThis09 AND l.PerEndThis09 THEN m.CostThis09
WHEN CURDATE() BETWEEN l.PerStartThis10 AND l.PerEndThis10 THEN m.CostThis10
WHEN CURDATE() BETWEEN l.PerStartThis11 AND l.PerEndThis11 THEN m.CostThis11
WHEN CURDATE() BETWEEN l.PerStartThis12 AND l.PerEndThis12 THEN m.CostThis12
ELSE m.CostThis13
END, 2) AS Inventory_Unit_Cost,
Round(LastPurchAmt, 2) AS Purchase_Unit_Cost,
Round(CASE
WHEN m.SellExcl01 > 0 THEN m.SellExcl01
WHEN m.SellExcl02 > 0 THEN m.SellExcl02
WHEN m.SellExcl03 > 0 THEN m.SellExcl03
WHEN m.SellExcl04 > 0 THEN m.SellExcl04
WHEN m.SellExcl05 > 0 THEN m.SellExcl05
WHEN m.SellExcl06 > 0 THEN m.SellExcl06
WHEN m.SellExcl07 > 0 THEN m.SellExcl07
WHEN m.SellExcl08 > 0 THEN m.SellExcl08
WHEN m.SellExcl09 > 0 THEN m.SellExcl09
WHEN m.SellExcl10 > 0 THEN m.SellExcl10
ELSE '0.00'
END, 2) AS Selling_Price,
CASE
WHEN CURDATE() BETWEEN l.PerStartThis01 AND l.PerEndThis01 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis02 AND l.PerEndThis02 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis03 AND l.PerEndThis03 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis04 AND l.PerEndThis04 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis05 AND l.PerEndThis05 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + m.QtyBuyThis05 + m.QtyAdjustThis05 - m.QtySellThis05 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis06 AND l.PerEndThis06 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + m.QtyBuyThis05 + m.QtyAdjustThis05 - m.QtySellThis05 + m.QtyBuyThis06 + m.QtyAdjustThis06 - m.QtySellThis06 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis07 AND l.PerEndThis07 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + m.QtyBuyThis05 + m.QtyAdjustThis05 - m.QtySellThis05 + m.QtyBuyThis06 + m.QtyAdjustThis06 - m.QtySellThis06 + m.QtyBuyThis07 + m.QtyAdjustThis07 - m.QtySellThis07 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis08 AND l.PerEndThis08 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + m.QtyBuyThis05 + m.QtyAdjustThis05 - m.QtySellThis05 + m.QtyBuyThis06 + m.QtyAdjustThis06 - m.QtySellThis06 + m.QtyBuyThis07 + m.QtyAdjustThis07 - m.QtySellThis07 + m.QtyBuyThis08 + m.QtyAdjustThis08 - m.QtySellThis08 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis09 AND l.PerEndThis09 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + m.QtyBuyThis05 + m.QtyAdjustThis05 - m.QtySellThis05 + m.QtyBuyThis06 + m.QtyAdjustThis06 - m.QtySellThis06 + m.QtyBuyThis07 + m.QtyAdjustThis07 - m.QtySellThis07 + m.QtyBuyThis08 + m.QtyAdjustThis08 - m.QtySellThis08 + m.QtyBuyThis09 + m.QtyAdjustThis09 - m.QtySellThis09 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis10 AND l.PerEndThis10 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + m.QtyBuyThis05 + m.QtyAdjustThis05 - m.QtySellThis05 + m.QtyBuyThis06 + m.QtyAdjustThis06 - m.QtySellThis06 + m.QtyBuyThis07 + m.QtyAdjustThis07 - m.QtySellThis07 + m.QtyBuyThis08 + m.QtyAdjustThis08 - m.QtySellThis08 + m.QtyBuyThis09 + m.QtyAdjustThis09 - m.QtySellThis09 + m.QtyBuyThis10 + m.QtyAdjustThis10 - m.QtySellThis10 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis11 AND l.PerEndThis11 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + m.QtyBuyThis05 + m.QtyAdjustThis05 - m.QtySellThis05 + m.QtyBuyThis06 + m.QtyAdjustThis06 - m.QtySellThis06 + m.QtyBuyThis07 + m.QtyAdjustThis07 - m.QtySellThis07 + m.QtyBuyThis08 + m.QtyAdjustThis08 - m.QtySellThis08 + m.QtyBuyThis09 + m.QtyAdjustThis09 - m.QtySellThis09 + m.QtyBuyThis10 + m.QtyAdjustThis10 - m.QtySellThis10 + m.QtyBuyThis11 + m.QtyAdjustThis11 - m.QtySellThis11 + u.BatchQty
WHEN CURDATE() BETWEEN l.PerStartThis12 AND l.PerEndThis12 THEN m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + m.QtyBuyThis05 + m.QtyAdjustThis05 - m.QtySellThis05 + m.QtyBuyThis06 + m.QtyAdjustThis06 - m.QtySellThis06 + m.QtyBuyThis07 + m.QtyAdjustThis07 - m.QtySellThis07 + m.QtyBuyThis08 + m.QtyAdjustThis08 - m.QtySellThis08 + m.QtyBuyThis09 + m.QtyAdjustThis09 - m.QtySellThis09 + m.QtyBuyThis10 + m.QtyAdjustThis10 - m.QtySellThis10 + m.QtyBuyThis11 + m.QtyAdjustThis11 - m.QtySellThis11 + m.QtyBuyThis12 + m.QtyAdjustThis12 - m.QtySellThis12 + u.BatchQty
ELSE m.OpeningQty + m.QtyBuyLast + m.QtyAdjustLast - m.QtySellLast + m.QtyBuyThis01 + m.QtyAdjustThis01 - m.QtySellThis01 + m.QtyBuyThis02 + m.QtyAdjustThis02 - m.QtySellThis02 + m.QtyBuyThis03 + m.QtyAdjustThis03 - m.QtySellThis03 + m.QtyBuyThis04 + m.QtyAdjustThis04 - m.QtySellThis04 + m.QtyBuyThis05 + m.QtyAdjustThis05 - m.QtySellThis05 + m.QtyBuyThis06 + m.QtyAdjustThis06 - m.QtySellThis06 + m.QtyBuyThis07 + m.QtyAdjustThis07 - m.QtySellThis07 + m.QtyBuyThis08 + m.QtyAdjustThis08 - m.QtySellThis08 + m.QtyBuyThis09 + m.QtyAdjustThis09 - m.QtySellThis09 + m.QtyBuyThis10 + m.QtyAdjustThis10 - m.QtySellThis10 + m.QtyBuyThis11 + m.QtyAdjustThis11 - m.QtySellThis11 + m.QtyBuyThis12 + m.QtyAdjustThis12 - m.QtySellThis12 + m.QtyBuyThis13 + m.QtyAdjustThis13 - m.QtySellThis13 + u.BatchQty
END AS StockOnHand,
CASE
WHEN inv.IncludeOrderedQty = 1 AND u.SalesOrder <> 0 THEN u.SalesOrder * -1
ELSE '0'
END AS Allocated_Stock,
'EX' AS Supply_indicator,
IsNull(p.SupplierCode, '') AS Source_of_Supply,
IsNull(p.SupplierCode, '') AS Vendor_Code,
IsNull(Rtrim(UnitSize),'') AS Purchase_Unit_of_Measure,
'1' AS Purchase_Factor,
' ' AS ABC,
'0' AS Lead_Time,
CASE
When i.Blocked = 1 THEN 'O' -- make Obsolete if inventory record is blocked
ELSE 'S'
END AS Stocking_Indicator,
0 AS Minimum_Stock,
1 AS Minimum_Order_Quantity,
1 AS Order_Multiple,
m.InvGroup AS Group_1 --,
-- ' ' AS Group_2,
-- ' ' AS Group_3,
-- ' ' AS Group_4

FROM MultiStoreTrn m
INNER JOIN Inventory i ON i.ItemCode = m.ItemCode
LEFT OUTER JOIN PreferredSupplier p ON p.ItemCode = m.ItemCode AND p.StoreCode = m.StoreCode
LEFT OUTER JOIN Unposted u ON u.ItemCode = m.ItemCode AND u.StoreCode = m.StoreCode
CROSS JOIN LedgerParameters l
CROSS JOIN InventoryParameters inv
WHERE IsNull(m.StoreCode, '') <> ''

RE: Help, script on ODBC is not working

hi,

Debug time! Blood sweat and tears a possibility.

Pare down the query to a simple Select, using all fields currently accessed. (no case statements!)

Observe what happens.

If you got an error, start eliminating fields one at a time , while running the revised query between each change.

Observe what happens.

If you did not get an error, then start adding the complexities in your case statements

Observe what happens.

It can be a tedious job.

BTW, this is a horrendous poorly designed table that will multiply your sorrows!!!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close