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)