SQL Error: No column name was specified for column 1 of 'qry'.

[former_member268870]

Experts,

I need to add the previous Cash Account balance into a report and now get's an error "No column name was specified for column 1 of 'qry'."

Any help would be greatly appreciated,

Marli

WITH qry as ( 
	SELECT getdate(), cast(convert(varchar(10), getdate(), 110) as datetime) AS GetDate
	      ,T1.[Account]
		  ,t2.AcctName
		  ,T1.[RefDate] RefDate
		  ,SUM(T1.[Debit])De
		  ,SUM(T1.[Credit])Cr
	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%'
	WHERE T1.[RefDate] < CONVERT(date,GETDATE() -7)
	GROUP BY
		   T1.[Account]
		  ,T1.[RefDate]
		  ,t2.AcctName
		  ) 


	SELECT 'TEST' as company
		  ,c.Account AS Accountcode
		  ,c.AcctName
		  ,sum(de) - sum(cr) AS Closing
	FROM   qry c
	GROUP BY c.Account, c.AcctName
	ORDER BY 
	  case
		when CHARINDEX('Clearing', c.AcctName) > 1 then 'z'+ c.AcctName
		else c.AcctName
	  end

[kothandaraman_nagarajan · Accepted solution]

Hi,

For with query as statement, i think above query is missing one part,

WITH query_name1 AS (
     SELECT ...
     )
   , query_name2 AS (
     SELECT ...
       FROM query_name1
        ...
     )
SELECT ...

Check query again. Also refer your previously posted thread for same query.

Regards,

Nagarajan

[Former Member]

Hi Marli,

WITH qry as(
SELECT getdate() AS [MySystemDateField], /* column 1 needs a column identifier */
cast(convert(varchar(10), getdate(),110)as datetime) AS GetDate, /* column 2 has an identifier */
....

Best Regards,

Dave