select * from (select top 6 * from (select top 220 order_id as order_id,fname as fname , lname as lname, order_date as order_date,ord_paid as ord_paid from Cust_Inv ,cust_det where cust_det.Cust_Acc_No=Cust_InV.Cust_Acc_No order by Cust_Inv.INV_No) as tbl1
order by INV_No desc) as tbl2 order by Cust_Inv.INV_No
I get this below error
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'INV_No'. The multi-part identifier "Cust_Inv.INV_No" could not be bound. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,.......................
you were getting that error because that column is not being selected in that query and I you can do this one query itself
select top 6
order_id as order_id,
fname as fname ,
lname as lname,
order_date as order_date,
ord_paid as ord_paid
from Cust_Inv invoices,
cust_det details
where invoices.Cust_Acc_No=details.Cust_Acc_No
order by invoices.INV_No
You are missing INV_No column in SELECT. use the one below:
select * from
(select top 6 * from
(select top 220 order_id as order_id,fname as fname ,
lname as lname, order_date as order_date,
ord_paid as ord_paid from Cust_Inv ,cust_det, INV_No
where cust_det.Cust_Acc_No=Cust_InV.Cust_Acc_No
order by Cust_Inv.INV_No) as tbl1
order by INV_No desc) as tbl2
order by Cust_Inv.INV_No
I get this below error
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'INV_No'. The multi-part identifier "Cust_Inv.INV_No" could not be bound. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,.......................
you cannot use a column from a table which you are using in inner query. That's not gonna work
I tested my query one more time with some test data and it works.
declare @Cust_Inv as
table
(
cust_acc_No varchar(100),
Inv_No varchar(30)
);
declare @cust_det as
table
(
order_id int,
fname varchar(100),
lname varchar(100),
order_date date,
ord_paid date,
cust_acc_No varchar(100)
);
insert into @Cust_Inv values(1,1);
insert into @Cust_Inv values(2,2);
insert into @Cust_Inv values(3,3);
insert into @Cust_Inv values(4,4);
insert into @Cust_Inv values(5,5);
insert into @Cust_Inv values(6,6);
insert into @cust_det values(1,'first name 1','last name 1',GETDATE()-1,GETDATE()-2,1);
insert into @cust_det values(2,'first name 2','last name 2',GETDATE()-3,GETDATE()-4,2);
insert into @cust_det values(3,'first name 3','last name 3',GETDATE()-5,GETDATE()-6,3);
insert into @cust_det values(4,'first name 4','last name 4',GETDATE()-7,GETDATE()-8,4);
insert into @cust_det values(5,'first name 5','last name 5',GETDATE()-9,GETDATE()-10,5);
insert into @cust_det values(6,'first name 6','last name 6',GETDATE()-11,GETDATE()-12,6);
select distinct
top 6
order_id as order_id,
invoices.Inv_No,
fname as fname ,
lname as lname,
order_date as order_date,
ord_paid as ord_paid
from @Cust_Inv invoices,
@cust_det details
where invoices.Cust_Acc_No=details.Cust_Acc_No
order by invoices.INV_No
e trying to achieve last 6 items joining the tables Cust_Inv and cust_det.
for that you need to do this only
select top 6 order_id as order_id,fname as fname ,lname as lname,order_date as order_date,ord_paid as ord_paid
from Cust_Inv ,cust_det
where cust_det.Cust_Acc_No=Cust_InV.Cust_Acc_No
order by Cust_Inv.INV_No desc
pls try and i hope i understood your prob correctly
Regards,
Vimal Syam.V
Don't forget to click "Mark as Answer" on the post that helped you.
pagal721
Member
13 Points
42 Posts
Error: Invalid column name 'INV_No'.
Aug 14, 2010 01:37 PM|LINK
hi,
I get this error when I wrote the below query
select * from (select top 6 * from (select top 220 order_id as order_id,fname as fname , lname as lname, order_date as order_date,ord_paid as ord_paid from Cust_Inv ,cust_det where cust_det.Cust_Acc_No=Cust_InV.Cust_Acc_No order by Cust_Inv.INV_No) as tbl1 order by INV_No desc) as tbl2 order by Cust_Inv.INV_No
I get this below error
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'INV_No'. The multi-part identifier "Cust_Inv.INV_No" could not be bound. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,.......................
plz help!!!
sansan
All-Star
53942 Points
8147 Posts
Re: Error: Invalid column name 'INV_No'.
Aug 14, 2010 02:30 PM|LINK
you were getting that error because that column is not being selected in that query and I you can do this one query itself
select top 6 order_id as order_id, fname as fname , lname as lname, order_date as order_date, ord_paid as ord_paid from Cust_Inv invoices, cust_det details where invoices.Cust_Acc_No=details.Cust_Acc_No order by invoices.INV_Notry that and see if it works.tanatrajan
Participant
1784 Points
370 Posts
Re: Error: Invalid column name 'INV_No'.
Aug 14, 2010 02:39 PM|LINK
You are missing INV_No column in SELECT. use the one below:
pagal721
Member
13 Points
42 Posts
Re: Error: Invalid column name 'INV_No'.
Aug 14, 2010 04:09 PM|LINK
Hi SanSan,
No it doesn't work
sansan
All-Star
53942 Points
8147 Posts
Re: Error: Invalid column name 'INV_No'.
Aug 14, 2010 04:16 PM|LINK
what do you mean by not working, not getting any data or getting some errors??
You are selecting top 220 records and then selecting top 6 sorted by the same order.
which means you are selecting top 6 records and I don't know why you used a nested query there.
I tested the query with some sample data and working fine for me.
can you post the query that you tried.
pagal721
Member
13 Points
42 Posts
Re: Error: Invalid column name 'INV_No'.
Aug 14, 2010 05:46 PM|LINK
i get this error
I get this below error
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'INV_No'. The multi-part identifier "Cust_Inv.INV_No" could not be bound. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,.......................
sansan
All-Star
53942 Points
8147 Posts
Re: Error: Invalid column name 'INV_No'.
Aug 14, 2010 06:01 PM|LINK
check this one.
you cannot use a column from a table which you are using in inner query. That's not gonna work
I tested my query one more time with some test data and it works.
declare @Cust_Inv as table ( cust_acc_No varchar(100), Inv_No varchar(30) ); declare @cust_det as table ( order_id int, fname varchar(100), lname varchar(100), order_date date, ord_paid date, cust_acc_No varchar(100) ); insert into @Cust_Inv values(1,1); insert into @Cust_Inv values(2,2); insert into @Cust_Inv values(3,3); insert into @Cust_Inv values(4,4); insert into @Cust_Inv values(5,5); insert into @Cust_Inv values(6,6); insert into @cust_det values(1,'first name 1','last name 1',GETDATE()-1,GETDATE()-2,1); insert into @cust_det values(2,'first name 2','last name 2',GETDATE()-3,GETDATE()-4,2); insert into @cust_det values(3,'first name 3','last name 3',GETDATE()-5,GETDATE()-6,3); insert into @cust_det values(4,'first name 4','last name 4',GETDATE()-7,GETDATE()-8,4); insert into @cust_det values(5,'first name 5','last name 5',GETDATE()-9,GETDATE()-10,5); insert into @cust_det values(6,'first name 6','last name 6',GETDATE()-11,GETDATE()-12,6); select distinct top 6 order_id as order_id, invoices.Inv_No, fname as fname , lname as lname, order_date as order_date, ord_paid as ord_paid from @Cust_Inv invoices, @cust_det details where invoices.Cust_Acc_No=details.Cust_Acc_No order by invoices.INV_Noand output is this
[URL=http://imagefra.me/][IMG]http://img37.imagefra.me/img/img37/8/8/14/dsanthoshece/f_pfhnfps54gjm_4c8a64c.png[/IMG][/URL]
I don't know why are getting errors if you are using the similar query
Vimal Syam
Member
30 Points
5 Posts
Re: Error: Invalid column name 'INV_No'.
Aug 14, 2010 06:53 PM|LINK
i think, you ar
e trying to achieve last 6 items joining the tables Cust_Inv and cust_det.
for that you need to do this only
select top 6 order_id as order_id,fname as fname ,lname as lname,order_date as order_date,ord_paid as ord_paid
from Cust_Inv ,cust_det
where cust_det.Cust_Acc_No=Cust_InV.Cust_Acc_No
order by Cust_Inv.INV_No desc
pls try and i hope i understood your prob correctly
Vimal Syam.V
Don't forget to click "Mark as Answer" on the post that helped you.