no column name specified for a

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

SQL Server 2008 Forums

Transact-SQL (2008)

no column name specified for a

Author

Topic

Ujwal231
Starting Member

2 Posts

Posted - 2010-09-22 : 01:32:43

SELECT b.trans_id, a.trans_date,CAST(a.trans_miti_yy as varchar)+ '/'
+ CAST(a.trans_miti_mm as varchar)+ '/'+ CAST(a.trans_miti_dd as varchar)
, a.voucher_type , a.voucher_no, b.ledger_name, a.dr as debit, a.cr as credit
, a.narration, a.sales_person
from
(
SELECT t.trans_id,([voucher_type] + '-' + [voucher_no]) as voucher
, t.trans_date, cast(t.trans_miti_dd as varchar),CAST(t.trans_miti_mm as varchar)
,CAST(t.trans_miti_yy as varchar) , t.voucher_type, t.voucher_no, t.dr, t.cr
, t.narration, t.sales_person
FROM ledger
INNER JOIN
(
SELECT * FROM transactions WHERE fiscal_id=1 AND ledger_id=25 and cr>0
and trans_date BETWEEN '2004/7/16' and '2005/7/15' and dr_cnt =1 and cr_cnt>1
) AS t
ON ledger.ledger_id = t.ledger_id
)AS a
INNER JOIN
(
SELECT t.trans_id, ([voucher_type] +'-' + [voucher_no]) AS voucher, ledger.ledger_name
FROM ledger
INNER JOIN
(
SELECT * FROM transactions WHERE fiscal_id=1 AND ledger_id<>25
AND dr>0 AND ([voucher_type] + '-' + [voucher_no])
In (
SELECT voucher_type +'-' + voucher_no FROM transactions WHERE fiscal_id=1 AND ledger_id=25
and cr>0 and trans_date BETWEEN '2004/7/16'
and '2005/7/15' and dr_cnt=1 and cr_cnt>1)
) AS t ON ledger.ledger_id = t.ledger_id
) as b on b.voucher=a.voucher

Ujwal231
Starting Member

2 Posts

Posted - 2010-09-22 : 01:40:37

i get no column name specified for a error in this query. need help

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 03:34:41

note the highlighted part

The outermost SELECT was not able to refernce a.trans_miti_yy as it has been casted to varchar in subquery a but no alias was provide.



SELECT b.trans_id
      , a.trans_date
	  , CAST(a.trans_miti_yy as varchar)+ '/' + CAST(a.trans_miti_mm as varchar)+ '/'+ CAST(a.trans_miti_dd as varchar) AS [trans_miti_date]      , a.voucher_type , a.voucher_no, b.ledger_name, a.dr as debit, a.cr as credit
      , a.narration
      , a.sales_person 
from
(
	SELECT t.trans_id,([voucher_type] + '-' + [voucher_no]) as voucher
    , t.trans_date, 
    cast(t.trans_miti_dd as varchar) AS [trans_miti_dd],
    CAST(t.trans_miti_mm as varchar) AS [trans_miti_mm],
    CAST(t.trans_miti_yy as varchar) AS [trans_miti_yy],
    t.voucher_type, t.voucher_no, t.dr, t.cr
    , t.narration, t.sales_person
    FROM ledger 
    INNER JOIN
             (
                 SELECT * FROM transactions WHERE fiscal_id=1 AND ledger_id=25 and cr>0 
                  and trans_date BETWEEN '2004/7/16' and '2005/7/15' and dr_cnt =1 and cr_cnt>1
             ) AS t 
            ON ledger.ledger_id = t.ledger_id
)AS a
INNER JOIN
(
     SELECT t.trans_id, ([voucher_type] +'-' + [voucher_no]) AS voucher, ledger.ledger_name 
     FROM ledger 
     INNER JOIN 
              (
                   SELECT * FROM transactions WHERE fiscal_id=1 AND ledger_id<>25 
                   AND dr>0 AND ([voucher_type] + '-' + [voucher_no])
                   In (
                             SELECT voucher_type +'-' + voucher_no FROM transactions WHERE fiscal_id=1 AND ledger_id=25
                             and cr>0 and trans_date BETWEEN '2004/7/16' 
                             and '2005/7/15' and dr_cnt=1 and cr_cnt>1)
             ) AS t 
             ON ledger.ledger_id = t.ledger_id
) as b 
ON b.voucher=a.voucher

Kristen
Test

22859 Posts

Posted - 2010-09-22 : 04:23:45

Not sure why you are CASTing twice?

You are casting
cast(t.trans_miti_dd as varchar)
in the inner query, and then re-casting it as varchar in the outer query.

Just select it as native type in the inner query.

... or move the concatenation for [trans_miti_date] from the outer query to the inner query.

It would be better to store dates as a DATE or DATETYIME datatype, rather than as 3 separate fields - you'd be able to just return the date from this query (formatted appropriately, or preferably format it in the application layer), you'd also be able to compare dates and perform other date computations and comparisons.

Either way, you should add a size attribute to VARCHAR - e.g. VARCHAR(20) - to prevent the default size being used when not appropriate.

You should not use

SELECT * FROM transactions ...

list the specific columns you need.

Note that

trans_date BETWEEN '2004/7/16' and '2005/7/15'

will not select any value where trans_date is '2005/7/15' AND has a non-midnight time value.

It would be more efficient to compare the individual columns voucher_no and voucher_type, rather than concatenating them and comparing them (which will prevent SQL Server using any indexes on the underlying columns)

trans_date >= '2004/7/16' and trans_date b[]<[/b '2005/7/1b[]6[/b'

It is recommended that you use string dates in the format yyyymmdd - 8 digits, no hyphens nor punctuation. They will be unambiguously parsed by SQL Server - whereas any other format is prone to ambiguous interpretation depending on the Server settings and the Country and Language settings for the currently logged in user.

I haven't looked at the syntax carefully, but I think you could just JOIN the tables, rather than using nested sub-selects, which would be more efficient, and probably easier to read; here's my guess of what you are after:



SELECT	T2.trans_id
	, T1.trans_date
	, CAST(T1.trans_miti_yy as varchar(10)) + '/'
		+ CAST(T1.trans_miti_mm as varchar(10)) + '/'
		+ CAST(T1.trans_miti_dd as varchar(10)) AS [trans_miti_date]
	, L1.voucher_type
	, L1.voucher_no
	, L1.ledger_name
	, T1.dr AS debit
	, T1.cr AS credit
	, T1.narration
	, T1.sales_person 
FROM	ledger AS L1
	INNER JOIN transactions AS T1
		 ON T1.ledger_id = L1.ledger_id
		AND T1.fiscal_id=1
		AND T1.ledger_id=25
		AND T1.cr > 0
		AND T1.trans_date >= '20040716'
		AND T1.trans_date <  '20050716'
		AND T1.dr_cnt = 1
		AND T1.cr_cnt > 1
	INNER JOIN ledger AS L2
		 ON ... not sure how this joins to L1 / T1? oor perhaps it joins to T2? or maybe not needed at all. Guessing:
		AND L2.voucher_type = L1.voucher_type
		AND L2.voucher_no = L1.voucher_no
	INNER JOIN transactions AS T2
		 ON T2.ledger_id = L2.ledger_id
		AND T2.voucher_type = T1.voucher_type
		AND T2.voucher_no = T1.voucher_no
		AND T2.fiscal_id=1
		AND T2.ledger_id <> 25
		AND T2.dr > 0
		AND EXISTS
		(
			SELECT	*
			FROM	transactions AS T2b
			WHERE	    T2b.voucher_type = T2.voucher_type
				AND T2b.voucher_no = T2.voucher_no
				AND T2b.fiscal_id = 1
				AND T2b.ledger_id = 25
				AND T2b.cr > 0
				AND T2b.trans_date >= '20040716' 
				AND T2b.trans_date <  '20050716'
				AND T2b.dr_cnt=1
				AND T2b.cr_cnt>1
		)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources