Technology

How to use CAST function in the definition of Linked Tables in NAV

Premanath Kumarage · Author
1 min read
A close-up of keyboard keys labelled dot com, dot net and dot org

(SELECT DATEDIFF(hour, GETDATE(), getutcdate()) AS EXPR1), CustomTableName.ScanTime) AS EXPR1) AS ScanTime, DisplayLine1

Example 1

The original SQL script for the Linked Table

USE [NavisionTEST]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW [dbo].[Cronus$UC_CustomTableName]

AS

SELECT DeviceId,

(SELECT DATEADD(hour,

(SELECT DATEDIFF(hour, GETDATE(), getutcdate()) AS EXPR1), CustomTableName.ScanTime) AS EXPR1) AS ScanTime, DisplayLine1

FROM [2ndDATABASE].pfo.dbo.metric_CustomTableName AS CustomTableName

WHERE (DisplayLine1 LIKE '%toner%') OR

(DisplayLine1 LIKE '%cartridge%') ;

Example 2

The same SQL script used to define the Linked Table, being modified with the CAST function

USE [NavisionTEST]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW [dbo].[Cronus$UC_CustomTableName]

AS

SELECT DeviceId,

(SELECT DATEADD(hour,

(SELECT DATEDIFF(hour, GETDATE(), getutcdate()) AS EXPR1), CustomTableName.ScanTime) AS EXPR1) AS ScanTime,

CAST(DisplayLine1 AS NVARCHAR(250)) AS DisplayLine1

FROM 2ndDATABASE.pfo.dbo.metric_CustomTableName AS CustomTableName

WHERE (DisplayLine1 LIKE '%toner%') OR

(DisplayLine1 LIKE '%cartridge%')

Written by

Premanath Kumarage

Author

All insights →

Start here

Every piece here started as a client problem. Tell us yours.

A working session, not a sales call. Bring the process your current system fights you on, and we will tell you plainly what it would take to fix it.

Newsletter

One email a month. No filler.

Stay ahead with Microsoft Business Applications.
Insights, implementation advice and practical ideas to help your business get more from Microsoft technology.

By subscribing, you acknowledge MicroCloud 360’s Privacy Policy.