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