Set declare SQL query NAV - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Set declare SQL query NAV

Author

Topic

dinhson_vn
Starting Member

15 Posts

Posted - 2015-03-26 : 00:48:24

Hi Everybody

I use SQL query in Navision but can't set declare of Company.

I set declare :

declare @CompanyName VARCHAR(30)
Set @CompanyName = 'USA'

When query i'm set ['+@CompanyName+'$Item]
but error Invalid object name 'dbo.'+@CompanyName+'$Item'.

Please help me. Thanks very much.

Regards,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-26 : 03:05:54

'dbo.[' + @CompanyName + '$Item' + ']'.

Just in case there are spaces or other likewise characters in the name.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

dinhson_vn
Starting Member

15 Posts

Posted - 2015-03-26 : 10:02:16

Thanks SwePeso.

But when I query then Error :

DECLARE @COMPANYNAME NVARCHAR(max)
SET @COMPANYNAME='USA'
SELECT * FROM 'dbo.[' + @COMPANYNAME + '$Item' + ']'

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'dbo.['.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 10:11:47

You need to use dynamic sql to do that:


declare @sql nvarchar(4000);
set @sql = 'SELECT * FROM dbo.' + QUOTENAME(@COMPANYNAME + '$Item')
exec sp_executesql @sql

dinhson_vn
Starting Member

15 Posts

Posted - 2015-03-26 : 10:31:35

Thanks Gritton.

DECLARE @COMPANYNAME NVARCHAR(max)
SET @COMPANYNAME='CBVNT'
DECLARE @sql NVARCHAR(MAX)
set @sql='SELECT * FROM '+@COMPANYNAME+'$Item'
EXEC sp_executesql @sql

very good results

But when use AP Aging Report in SQL For Dynamics NAV in [url]http://mibuso.com/blogs/ara3n/2014/07/01/ap-aging-report-in-sql-for-dynamics-nav/[/url] with


DECLARE @AsOfDate dateTime
set @AsOfDate = '2015-03-31'
DECLARE @AgingMethod varchar(10)
set @AgingMethod = 'DocDate'  -- DocDate,TransDate,DueDate
DECLARE @AgingDays int 
set @AgingDays = 30
DECLARE @PrintInVendorCurrency bit
set @PrintInVendorCurrency = 0
DECLARE @Vendor varchar(10)
set @Vendor ='1000'
DECLARE @COMPANY NVARCHAR(max)
SET @COMPANY='USA'
DECLARE @sql NVARCHAR(MAX)

 --Set @sql= 
 Select  [Vendor No_],Name, [Buy-from Vendor No_],BuyFromName,PhoneNo,CurrencyCode,[Purchaser Code],PurchaserName,[Description],[Document No_],[External Document No_],TransDate,DaysLate,BalanceDue,
CASE WHEN DaysLate <= 0 and @AgingMethod = 'DueDate' Then BalanceDue 
     WHEN DaysLate < @AgingDays  and @AgingMethod != 'DueDate' Then BalanceDue 
ELSE 0 End as CurrentAmt,
CASE WHEN DaysLate > 0 AND  DaysLate <= @AgingDays and @AgingMethod = 'DueDate' Then BalanceDue 
     WHEN DaysLate >= @AgingDays + 1 AND  DaysLate <= @AgingDays * 2 and @AgingMethod != 'DueDate' Then BalanceDue 
ELSE 0 End as Column1,
CASE WHEN DaysLate > @AgingDays AND  DaysLate <= @AgingDays * 2 and @AgingMethod = 'DueDate' Then BalanceDue 
     WHEN DaysLate >= @AgingDays * 2 AND  DaysLate <= @AgingDays * 3 and @AgingMethod != 'DueDate' Then BalanceDue 
ELSE 0 End as Column2,
CASE WHEN DaysLate > @AgingDays * 2  AND  DaysLate <= @AgingDays * 3 and @AgingMethod = 'DueDate' Then BalanceDue 
     WHEN DaysLate >= @AgingDays * 3 AND  DaysLate <= @AgingDays * 4 and @AgingMethod != 'DueDate' Then BalanceDue 
ELSE 0 End as Column3,
CASE WHEN DaysLate > @AgingDays * 3  AND  DaysLate <= @AgingDays * 4 and @AgingMethod = 'DueDate' Then BalanceDue 
     WHEN DaysLate >= @AgingDays * 4 AND  DaysLate <= @AgingDays * 5 and @AgingMethod != 'DueDate' Then BalanceDue  
ELSE 0 End as Column4,
CASE WHEN DaysLate > @AgingDays * 4 AND  DaysLate <= @AgingDays * 5 and @AgingMethod = 'DueDate' Then BalanceDue 
     WHEN DaysLate >= @AgingDays * 5  AND  DaysLate <= @AgingDays * 6 and @AgingMethod != 'DueDate' Then BalanceDue 
ELSE 0 End as Column5,
CASE WHEN DaysLate > @AgingDays * 5 AND  DaysLate <= @AgingDays * 6 and @AgingMethod = 'DueDate' Then BalanceDue 
     WHEN DaysLate >= @AgingDays * 6 AND  DaysLate <= @AgingDays * 7 and @AgingMethod != 'DueDate' Then BalanceDue 
ELSE 0 End as Column6,
CASE WHEN DaysLate > @AgingDays * 6 AND  DaysLate <= @AgingDays * 7 and @AgingMethod = 'DueDate' Then BalanceDue 
     WHEN DaysLate >= @AgingDays * 7 AND  DaysLate <= @AgingDays * 8 and @AgingMethod != 'DueDate' Then BalanceDue 
ELSE 0 End as Column7,
CASE WHEN DaysLate > @AgingDays * 7  Then BalanceDue 
     WHEN DaysLate >= @AgingDays * 8 Then BalanceDue 
ELSE 0 End as Column8
From 
(
Select [Vendor No_], [Buy-from Vendor No_],[Document No_], [External Document No_],[Purchaser Code],[Description],
CASE @AgingMethod WHEN 'DueDate' Then [Due Date]
                  WHEN 'DocDate' Then [Document Date]
                  ELSE [Posting Date]
End as TransDate,
CASE @AgingMethod WHEN 'DueDate' Then DATEDIFF(day,[Due Date], @AsOfDate)
                  WHEN 'DocDate' Then DATEDIFF(day,[Document Date], @AsOfDate) 
                  ELSE DATEDIFF(day,[Posting Date], @AsOfDate) 
End as DaysLate,
CASE WHEN @PrintInVendorCurrency = 0 THEN
	(Select Sum([Amount (LCY)]) 
	From ['+@COMPANY+'$Detailed Vendor Ledg_ Entry]
	Where ['+@COMPANY+'$Detailed Vendor Ledg_ Entry].[Posting Date] <= @AsOfDate
	and [Vendor Ledger Entry No_] = VendEntry.[Entry No_] ) 
 ELSE 	
 (Select Sum([Amount]) 
	From ['+@COMPANY+'$Detailed Vendor Ledg_ Entry]
	Where ['+@COMPANY+'$Detailed Vendor Ledg_ Entry].[Posting Date] <= @AsOfDate
	and [Vendor Ledger Entry No_] = VendEntry.[Entry No_] ) 
End As BalanceDue,	
(Select TOP 1 (Name) 
From ['+@COMPANY+'$Vendor]
Where [No_] = VendEntry.[Vendor No_] ) As Name,
(Select TOP 1 (Name) 
From ['+@COMPANY+'$Vendor]
Where [No_] = VendEntry.[Buy-from Vendor No_] ) As BuyFromName,
(Select TOP 1 (Name) 
From ['+@COMPANY+'$Salesperson_Purchaser]
Where [Code] = VendEntry.[Purchaser Code] ) As PurchaserName,
(Select TOP 1 [Phone No_]  
From ['+@COMPANY+'$Vendor]
Where [No_] = VendEntry.[Vendor No_] ) As PhoneNo,
(Select TOP 1 [Currency Code]  
From ['+@COMPANY+'$Vendor]
Where [No_] = VendEntry.[Vendor No_] ) As CurrencyCode
from ['+@COMPANY+'$Vendor Ledger Entry] as VendEntry
where [Posting Date] <= @AsOfDate 
and [Vendor No_] = '1000'
) As Detail
Where BalanceDue <> 0
Order By [Vendor No_],TransDate

I was embarrassed to make it work. Please help me

Regards,
Dinhson

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 10:47:56

you need to write:


set @sql = ' .... '

like this:


declare @sql nvarchar(max);
declare @company varchar(30) = 'foobar';
declare @AgingDays int =1;
declare @AsOfDate datetime = getdate();

set @sql = '
SELECT [Vendor No_]
	,NAME
	,[Buy-from Vendor No_]
	,BuyFromName
	,PhoneNo
	,CurrencyCode
	,[Purchaser Code]
	,PurchaserName
	,[Description]
	,[Document No_]
	,[External Document No_]
	,TransDate
	,DaysLate
	,BalanceDue
	,CASE 
		WHEN DaysLate <= 0
			AND @AgingMethod = ''DueDate''
			THEN BalanceDue
		WHEN DaysLate < ' + cast(@AgingDays as varchar(20)) + '
			AND @AgingMethod != ''DueDate''
			THEN BalanceDue
		ELSE 04
		END AS CurrentAmt
	,CASE 
		WHEN DaysLate > 0
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + '
			AND @AgingMethod = ''DueDate''
			THEN BalanceDue
		WHEN DaysLate >= ' + cast(@AgingDays as varchar(20)) + ' + 1
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 2
			AND @AgingMethod != ''DueDate''
			THEN BalanceDue
		ELSE 0
		END AS Column1
	,CASE 
		WHEN DaysLate > ' + cast(@AgingDays as varchar(20)) + '
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 2
			AND @AgingMethod = ''DueDate''
			THEN BalanceDue
		WHEN DaysLate >= ' + cast(@AgingDays as varchar(20)) + ' * 2
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 3
			AND @AgingMethod != ''DueDate''
			THEN BalanceDue
		ELSE 0
		END AS Column2
	,CASE 
		WHEN DaysLate > ' + cast(@AgingDays as varchar(20)) + ' * 2
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 3
			AND @AgingMethod = ''DueDate''
			THEN BalanceDue
		WHEN DaysLate >= ' + cast(@AgingDays as varchar(20)) + ' * 3
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 4
			AND @AgingMethod != ''DueDate''
			THEN BalanceDue
		ELSE 0
		END AS Column3
	,CASE 
		WHEN DaysLate > ' + cast(@AgingDays as varchar(20)) + ' * 3
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 4
			AND @AgingMethod = ''DueDate''
			THEN BalanceDue
		WHEN DaysLate >= ' + cast(@AgingDays as varchar(20)) + ' * 4
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 5
			AND @AgingMethod != ''DueDate''
			THEN BalanceDue
		ELSE 0
		END AS Column4
	,CASE 
		WHEN DaysLate > ' + cast(@AgingDays as varchar(20)) + ' * 4
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 5
			AND @AgingMethod = ''DueDate''
			THEN BalanceDue
		WHEN DaysLate >= ' + cast(@AgingDays as varchar(20)) + ' * 5
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 6
			AND @AgingMethod != ''DueDate''
			THEN BalanceDue
		ELSE 0
		END AS Column5
	,CASE 
		WHEN DaysLate > ' + cast(@AgingDays as varchar(20)) + ' * 5
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 6
			AND @AgingMethod = ''DueDate''
			THEN BalanceDue
		WHEN DaysLate >= ' + cast(@AgingDays as varchar(20)) + ' * 6
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 7
			AND @AgingMethod != ''DueDate''
			THEN BalanceDue
		ELSE 0
		END AS Column6
	,CASE 
		WHEN DaysLate > ' + cast(@AgingDays as varchar(20)) + ' * 6
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 7
			AND @AgingMethod = ''DueDate''
			THEN BalanceDue
		WHEN DaysLate >= ' + cast(@AgingDays as varchar(20)) + ' * 7
			AND DaysLate <= ' + cast(@AgingDays as varchar(20)) + ' * 8
			AND @AgingMethod != ''DueDate''
			THEN BalanceDue
		ELSE 0
		END AS Column7
	,CASE 
		WHEN DaysLate > ' + cast(@AgingDays as varchar(20)) + ' * 7
			THEN BalanceDue
		WHEN DaysLate >= ' + cast(@AgingDays as varchar(20)) + ' * 8
			THEN BalanceDue
		ELSE 0
		END AS Column8
FROM (
	SELECT [Vendor No_]
		,[Buy-from Vendor No_]
		,[Document No_]
		,[External Document No_]
		,[Purchaser Code]
		,[Description]
		,CASE @AgingMethod
			WHEN ''DueDate''
				THEN [Due Date]
			WHEN ''DocDate''
				THEN [Document Date]
			ELSE [Posting Date]
			END AS TransDate
		,CASE @AgingMethod
			WHEN ''DueDate''
				THEN DATEDIFF(day, [Due Date], ' + cast(@AsOfDate as varchar(5)) + ')
			WHEN ''DocDate''
				THEN DATEDIFF(day, [Document Date], ' + cast(@AsOfDate as varchar(5)) + ')
			ELSE DATEDIFF(day, [Posting Date], ' + cast(@AsOfDate as varchar(5)) + ')
			END AS DaysLate
		,CASE 
			WHEN @PrintInVendorCurrency = 0
				THEN (
						SELECT Sum([Amount (LCY)])
						FROM ['+@COMPANY+'$Detailed Vendor Ledg_ Entry]
						WHERE ['+@COMPANY+'$Detailed Vendor Ledg_ Entry].[Posting Date] <= ' + cast(@AsOfDate as varchar(5)) + '
							AND [Vendor Ledger Entry No_] = VendEntry.[Entry No_]
						)
			ELSE (
					SELECT Sum([Amount])
					FROM ['+@COMPANY+'$Detailed Vendor Ledg_ Entry]
					WHERE ['+@COMPANY+'$Detailed Vendor Ledg_ Entry].[Posting Date] <= ' + cast(@AsOfDate as varchar(5)) + '
						AND [Vendor Ledger Entry No_] = VendEntry.[Entry No_]
					)
			END AS BalanceDue
		,(
			SELECT TOP 1 (NAME)
			FROM ['+@COMPANY+'$Vendor]
			WHERE [No_] = VendEntry.[Vendor No_]
			) AS NAME
		,(
			SELECT TOP 1 (NAME)
			FROM ['+@COMPANY+'$Vendor]
			WHERE [No_] = VendEntry.[Buy-from Vendor No_]
			) AS BuyFromName
		,(
			SELECT TOP 1 (NAME)
			FROM ['+@COMPANY+'$Salesperson_Purchaser]
			WHERE [Code] = VendEntry.[Purchaser Code]
			) AS PurchaserName
		,(
			SELECT TOP 1 [Phone No_]
			FROM ['+@COMPANY+'$Vendor]
			WHERE [No_] = VendEntry.[Vendor No_]
			) AS PhoneNo
		,(
			SELECT TOP 1 [Currency Code]
			FROM ['+@COMPANY+'$Vendor]
			WHERE [No_] = VendEntry.[Vendor No_]
			) AS CurrencyCode
	FROM ['+@COMPANY+'$Vendor Ledger Entry] AS VendEntry
	WHERE [Posting Date] <= ' + cast(@AsOfDate as varchar(5)) + '
		AND [Vendor No_] = ''1000''
	) AS Detail
WHERE BalanceDue <> 0
ORDER BY [Vendor No_]
	,TransDate
'

dinhson_vn
Starting Member

15 Posts

Posted - 2015-03-26 : 11:04:51

Thanks Gbritton.

When add EXEC sp_executesql @sql and Run Executesql then Error

Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@AgingMethod".
Msg 137, Level 15, State 2, Line 116
Must declare the scalar variable "@AgingMethod".
Msg 102, Level 15, State 1, Line 135
Incorrect syntax near '2'.
Msg 102, Level 15, State 1, Line 141
Incorrect syntax near '2'.
Msg 156, Level 15, State 1, Line 149
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 154
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 159
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 164
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 169
Incorrect syntax near the keyword 'AS'.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 11:07:50

Missed @AgingMethod.

Notice what I did for @AsOfDate. e.g. I changed:


WHERE [Posting Date] <= @AsOfDate


WHERE [Posting Date] <= ' + cast(@AsOfDate as varchar(5)) + '

Do the same thing with @AgingMethod

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources