Skip to Content

SQL Query changes when tested in a SQL View

Experts,

I have a query that works in SQL, but as soon as I enter this into SQL Views and execute, I get an error "Multi-Part Identifier could not be bound".

When I look over the query, it changed. Some selects now have an "AS Table_1" added where it was not before. "Select 'Test' as Company" now changed to "SELECT TOP (100) PERCENT 'TEST' AS company". Why does this query that worked outside of the View not work in the View?

Your help would be greatly appreciated.

Marli

Query that works before pasting in a new View:

WITH qry as 
(
	SELECT T1.[Account]
		  ,t2.AcctName
		  ,T1.[RefDate] RefDate
		  ,SUM(T1.[Debit])De
		  ,SUM(T1.[Credit])Cr
		  ,T2.ActCurr
	FROM   OJDT T0
		   INNER JOIN JDT1 T1
				ON  T0.TransId = T1.TransId
		   INNER JOIN oact t2
				ON  t1.Account = t2.AcctCode
					AND t2.Finanse = 'Y'
					AND t2.FrozenFor = 'N'
					AND not t2.AcctName like '%Petty Cash%'
	GROUP BY
		   T1.[Account]
		  ,T1.[RefDate]
		  ,t2.AcctName
		  ,T2.ActCurr
),
rates as
(
--select 1 as value


	SELECT 
		--getdate(), 
		cast(convert(varchar(10), getdate(), 110) as datetime) AS 'Today',
		T1.[Currency], 
		T1.[Rate], 
		T0.[CurrName], 
		T1.[RateDate]
FROM OCRN T0  RIGHT OUTER JOIN ORTT T1 
ON T0.[CurrCode] = T1.[Currency]


WHERE T1.[RateDate] = CONVERT(date,GETDATE())
),
usdrate as
(
-- For non-US companies:
--SELECT  cast(convert(varchar(10), getdate(), 110) as datetime) AS 'Today',
--T1.[Currency], T1.[Rate], T0.[CurrName], T1.[RateDate]
--FROM OCRN T0  RIGHT OUTER JOIN ORTT T1 
--ON T0.[CurrCode] = T1.[Currency]
--WHERE T1.[RateDate] = CONVERT(date,GETDATE())
--and T1.Currency = '$'


-- For US companies:
select 1 as Rate
)


	SELECT 'TEST' as company
		  ,c.Account AS Accountcode
		  ,c.AcctName
		  ,sum(de) - sum(cr) AS Closing
		  ,c.ActCurr as AccountCurrency
		  ,rates.Rate
		  ,case c.ActCurr
			when '##' then 1
			else rates.Rate
		  end as DisplayRate
		   ,case c.ActCurr
			when '##' then (sum(de) - sum(cr))
			else (sum(de) - sum(cr)) * rates.Rate
		  end as ValueInSysCurrency
		  --, (sum(de) - sum(cr)) * rates.Rate as 'ValueInSysCurrency'
		  ----, if(true,1, 2) as 'ValueInSysCurrency'
		  , (select Rate from usdrate) as USDRate
		  ,case c.ActCurr
			when '##' then (sum(de) - sum(cr)) / (select Rate from usdrate)
			else (sum(de) - sum(cr)) * rates.Rate / (select Rate from usdrate)
		  end asValueInUSD




	FROM   qry c left outer join rates
	on c.ActCurr = rates.Currency
	GROUP BY c.Account, c.AcctName, c.ActCurr, rates.Rate
	ORDER BY 
	  case
		when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName
		else c.AcctName
	  end
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 23 at 03:50 PM

    Hi Marli,

    SQL Server Views do not typically have an ORDER BY clause (they are considered to be datasets that are ordered in an executing SELECT .. FROM MyView statement.) The TOP (100) PERCENT is a workaround to allow ORDER BY clauses in Views. Remove the ORDER BY clause if you do not specifically need it in the view and the editor will not insert TOP (100) PERCENT.

    The other part refers to not allowing duplicate or ambiguous table aliases, which I admit I am not fully sure about! You define 'rates' as a subquery and then join it later in your output statement "INNER JOIN rates." Instead use WITH allrates as ( ... ) and in the output statement, INNER JOIN allrates as rates.

    Kind regards,

    Dave

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 23 at 03:28 PM

    Hi,

    Try this,

    WITH qry AS (SELECT T1.Account, t2.AcctName, T1.RefDate, SUM(T1.Debit) AS De, SUM(T1.Credit) AS Cr, t2.ActCurr FROM dbo.OJDT AS T0 INNER JOIN dbo.JDT1 AS T1 ON T0.TransId = T1.TransId INNER JOIN dbo.OACT AS t2 ON T1.Account = t2.AcctCode AND t2.Finanse = 'Y' AND t2.FrozenFor = 'N' AND NOT (t2.AcctName LIKE '%Petty Cash%') GROUP BY T1.Account, T1.RefDate, t2.AcctName, t2.ActCurr), rates AS (SELECT CAST(CONVERT(varchar(10), GETDATE(), 110) AS datetime) AS 'Today', T1.Currency, T1.Rate, T0.CurrName, T1.RateDate FROM dbo.OCRN AS T0 RIGHT OUTER JOIN dbo.ORTT AS T1 ON T0.CurrCode = T1.Currency WHERE (T1.RateDate = CONVERT(date, GETDATE()))), usdrate AS (SELECT 1 AS Rate) SELECT TOP (100) PERCENT 'TEST' AS company, c.Account AS Accountcode, c.AcctName, SUM(c.De) - SUM(c.Cr) AS Closing, c.ActCurr AS AccountCurrency, rates_1.Rate, (SELECT Rate FROM usdrate AS usdrate_1) AS USDRate FROM qry AS c LEFT OUTER JOIN rates AS rates_1 ON c.ActCurr = rates_1.Currency GROUP BY c.Account, c.AcctName, c.ActCurr, rates_1.Rate ORDER BY CASE WHEN CHARINDEX('Clearing', c.AcctName) > 1 THEN 'z' + c.AcctName ELSE c.AcctName END

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded