VISUAL Did You Know?

If you have any questions about Infor VISUAL, contact us at 800.804.4715. We would be happy to help you!

Do you have VISUAL and want monthly tips and news? Subscribe to the BizTech VISUAL eNewsletter.

Are you looking for an ERP software? Subscribe to our monthly eNewsletter about ERP selection & implementation. Subscribe to the BizTech intERPreter.

Join the VISUAL Manufacturing Group on LinkedIn!

Search This Blog

Monday, June 3, 2013

Did you know that for VISUAL Reports you can extend the limit for Converting Binary to Vchar?

VISUAL includes many Specifications Fields in the “BINARY” tables.  For any of those specifications fields to be used on reports with tools like Crystal Reports or Microsoft Access, the field type needs to be converted from binary to text when the database platform is SQL Server or Oracle.  This can be accomplished with a Database Function or a Database View.  For example, a new Database View can be used to convert the binary data to text with a specific function.  Then the View (not the actual table) can be linked in the application to properly display the text.

Some documentation shows views or conversion scripts that convert the Binary tables in VISUAL using the expression Varchar(8000).

But, if you hit the 8000 character limit you can use Varchar(max) instead.

For example:

CREATE VIEW [dbo].[Select_Op_Binary_to_VChar] AS
SELECT 
CUST_ORDER_ID,
CUST_ORDER_LINE_NO,
BITS_LENGTH,
CAST(CONVERT(varBINARY(max),BITS) AS Varchar(max)) AS txtBITS

FROM
SELECT_OPT_BINARY

WHERE TYPE = 'D' AND
   SEQ_NO = '1'


2 comments:

  1. Make note that VARCHAR(MAX) and VARBINARY(MAX) were introduced in SQL 2005, so users running previous versions of SQL will need to stick with the (8000) designator.

    ReplyDelete