Usefull SQL Statements for SQL Server and Visual Manufacturing from Infor

Here is an example of how you can convert OPERATION_BINARY.BITS to text using cast, so that you can have it show up in a report.


SELECT OPERATION_BINARY.SEQUENCE_NO, CAST(CAST(OPERATION_BINARY.BITS AS varbinary(8000)) AS varchar(8000)) AS specs,
OPERATION.RESOURCE_ID, OPERATION.OPERATION_TYPE, OPERATION_TYPE.DESCRIPTION,
OPERATION_BINARY.WORKORDER_TYPE
FROM OPERATION_BINARY INNER JOIN
OPERATION ON OPERATION_BINARY.WORKORDER_BASE_ID = OPERATION.WORKORDER_BASE_ID AND
OPERATION_BINARY.SEQUENCE_NO = OPERATION.SEQUENCE_NO AND
OPERATION_BINARY.WORKORDER_TYPE = OPERATION.WORKORDER_TYPE AND
OPERATION_BINARY.WORKORDER_SUB_ID = OPERATION.WORKORDER_SUB_ID AND
OPERATION_BINARY.WORKORDER_SPLIT_ID = OPERATION.WORKORDER_SPLIT_ID AND
OPERATION_BINARY.WORKORDER_LOT_ID = OPERATION.WORKORDER_LOT_ID INNER JOIN
OPERATION_TYPE ON OPERATION.OPERATION_TYPE = OPERATION_TYPE.ID
WHERE (OPERATION_BINARY.WORKORDER_BASE_ID = '811289')
ORDER BY OPERATION_BINARY.SEQUENCE_NO




Don't forget to change (OPERATION_BINARY.WORKORDER_BASE_ID = 'YourWorkOrder')
to a valid number!


This will allow you to update a Spec in the OPER_TYPE_BINARY column using Update and Replace.
Here I am replacing F19.01 with F19.02 everywhere it appears in the specs area of the database.


SELECT CAST(CAST(BITS AS varbinary(8000)) AS varchar(8000)) AS specs, OPERATION_TYPE_ID
FROM OPER_TYPE_BINARY
WHERE (CAST(CAST(BITS AS varbinary(8000)) AS varchar(8000)) LIKE '%F19.01%')
UPDATE OPER_TYPE_BINARY SET OPERATION_TYPE_ID = REPLACE(OPERATION_TYPE_ID,'F19.01','F19.02')



Use this is a SQL Reporting Services Report to find out how many days late an order was:


=DATEDIFF(Day,Fields!DESIRED_SHIP_DATE.Value,Fields!SHIPPED_DATE.Value)



Get the current date without time (like 5/5/2005) for a SQL criteria:


= CONVERT (varchar(10), GETDATE(), 1)


Go back one day:


= CONVERT (varchar(10),DATEADD(d, - 1, GETDATE()),1)

Comments

uncle rox said…
Hi there,

I am looking for some macros for Visual. I am so glad i bumped into you. We are using 6.5.2 and don't really have an administrator person...i guess thats me. anyway...are you will to talk to me a little bit about some of our needs and possible freelance gig? let me know...
Ed Hammond said…
Here are some of my macros.

http://edhammond.blogspot.com/2009/10/infor-visual-enterprise-macros.html

If you want more custom ones written, email me and I can do some work on the side for you.
Applepie said…
Hey Ed Great work so far mate! I was wondering is their any way to send a SQL query and change the "unit of measure conversions", for a list of parts in one go? Thank you mate!

Popular posts from this blog

Add your Office 2016 KMS Keys to your Volume Activation Server

Solving WinRM Host connection errors in SCVMM - System Center Virtual Machine Manager 2012 R2

Adding Windows 10 to your Server 2012 R2 KMS Server