December 9, 2010 at 2:19 am
I Use This Codes But Got Error Too
{---My Code
USe Mg
Go
Declare @CDate nvarchar(max);
Declare @VDate nvarchar(max);
Set @CDate = Select substring(DateOfPayBill,3,5) AS Date from Tvarizi
Set @VDate = Select substring(Recievedate,3,5) AS Date from Tvarizi
Select *
From [year]
Left outer join TVarizi On TVarizi.SabtDate=[year].Date
Left outer join TCash On TCash.SabtDate = [year].Date
Where [Year].Date BETWEEN '87/01' AND '88/30'
----}
{----Error
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Select'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'Select'.
----Error}
That Mg is My DB Name and Tvarizi,Tcash and Year is my Table
please Help me it is urgent(it is my E-mail:gh.abtahi@gmail.com
Thank you
----------
December 9, 2010 at 2:39 am
Hi,
Just try this way
Select @CDate = Select substring(DateOfPayBill,3,5) AS Date from Tvarizi
Select @VDate = Select substring(Recievedate,3,5) AS Date from Tvarizi
December 9, 2010 at 7:04 am
Declare @CDate nvarchar(max);
Declare @VDate nvarchar(max);
Set @CDate = Select substring(DateOfPayBill,3,5) AS Date from Tvarizi
Set @VDate = Select substring(Recievedate,3,5) AS Date from Tvarizi
It is throwing error because your "Select substring(DateOfPayBill,3,5) AS Date from Tvarizi" and "Select substring(Recievedate,3,5) AS Date from Tvarizi" are returning mutiple values and using Set, you can't assign value if the query return mutiple result set.
There are two ways to solve it
Option 1:
Write your Select statement used in set and filter the result set to get one value
Option 2:
Select @CDate =substring(DateOfPayBill,3,5) AS Date from Tvarizi
But in option 2 @CDate will store the result of last record, return by the querry
December 9, 2010 at 7:38 am
avoid using varchar(max) unless you need it...since you are doing substrings, you know you can use something smaller.
if there is more than one row in the table, you cannot guarantee what dates will be used.
also, you can do both asignments in a single select:
Declare @CDate nvarchar(5);
Declare @VDate nvarchar(5);
Select TOP 1 --get ONE row if there is more than one
@CDate = substring(DateOfPayBill,3,5),
@VDate = substring(Recievedate,3,5)
from Tvarizi
Lowell
December 10, 2010 at 4:07 am
HI
Thank you for all answers.They are Great.
Dear Junglee_George I know Your Code are work(because I use them a lot).
Even I need This Codes And Get Error Too
{--Code
Select *,Tvarizi.[money],Tvarizi.BillNum,Tvarizi.DateOfPayBill,Tcash.[Money],Tcash.BillNum,Tcash.Recievedate
From [year]
Left outer join TVarizi On TVarizi.substring(DateOfPayBill,3,5) =[year].Date
Left outer join TCash On TCash.SabtDate = [year].Date
Where [Year].Date BETWEEN '87/01' AND '88/30'
---Code}
{--Error
Msg 4121, Level 16, State 1, Line 10
Cannot find either column "TVarizi" or the user-defined function or aggregate "TVarizi.substring", or the name is ambiguous.
--Error}
It Is right because i have not Tvarizi Column in Year Table.
It is Code of My Tables:
http://www.4shared.com/file/uPJSF3EX/CodeOfTables.html
I need To Get These Columns By Left Inner Join :
[year].Month,[Year].[Year],[Year].[Money].Tvarizi.[money],Tvarizi.BillNum,Tvarizi.DateOfPayBill,Tcash.[Money],Tcash.BillNum,Tcash.Recievedate
I have another question that Why when Use this Code I get error?
Select *
From [year]
Left outer join TVarizi On TVarizi.substring(DateOfPayBill,3,5) =[year].Date
Left outer join TCash On TCash.SabtDate = [year].Date
Where [Year].Date BETWEEN '87/01' AND '88/30'
{---Error
Msg 4121, Level 16, State 1, Line 10
Cannot find either column "TVarizi" or the user-defined function or aggregate "TVarizi.substring", or the name is ambiguous.
---}
Like above .I think It is belonging to this part
*****
TVarizi.substring(DateOfPayBill,3,5)
*****
Thank you
December 11, 2010 at 6:32 am
gh.abtahi (12/10/2010)
HIThank you for all answers.They are Great.
Dear Junglee_George I know Your Code are work(because I use them a lot).
Even I need This Codes And Get Error Too
{--Code
Select *,Tvarizi.[money],Tvarizi.BillNum,Tvarizi.DateOfPayBill,Tcash.[Money],Tcash.BillNum,Tcash.Recievedate
From [year]
Left outer join TVarizi On TVarizi.substring(DateOfPayBill,3,5) =[year].Date
Left outer join TCash On TCash.SabtDate = [year].Date
Where [Year].Date BETWEEN '87/01' AND '88/30'
---Code}
{--Error
Msg 4121, Level 16, State 1, Line 10
Cannot find either column "TVarizi" or the user-defined function or aggregate "TVarizi.substring", or the name is ambiguous.
--Error}
It Is right because i have not Tvarizi Column in Year Table.
It is Code of My Tables:
http://www.4shared.com/file/uPJSF3EX/CodeOfTables.html
I need To Get These Columns By Left Inner Join :
[year].Month,[Year].[Year],[Year].[Money].Tvarizi.[money],Tvarizi.BillNum,Tvarizi.DateOfPayBill,Tcash.[Money],Tcash.BillNum,Tcash.Recievedate
I have another question that Why when Use this Code I get error?
Select *
From [year]
Left outer join TVarizi On TVarizi.substring(DateOfPayBill,3,5) =[year].Date
Left outer join TCash On TCash.SabtDate = [year].Date
Where [Year].Date BETWEEN '87/01' AND '88/30'
{---Error
Msg 4121, Level 16, State 1, Line 10
Cannot find either column "TVarizi" or the user-defined function or aggregate "TVarizi.substring", or the name is ambiguous.
---}
Like above .I think It is belonging to this part
*****
TVarizi.substring(DateOfPayBill,3,5)
*****
Thank you
Thank you Men
I solve my Problem by Wrote this code and get my result
Select [year].[month],[year].[year],[year].[money],Tvarizi.[money],Tvarizi.BillNum,Tvarizi.DateOfPayBill,Tcash.[Money],Tcash.BillNum,Tcash.Recievedate
From [year]
Left outer join TVarizi On substring( TVarizi.DateOfPayBill,3,5)=[year].Date
Left outer join TCash On substring( TCash.Recievedate,3,5) = [year].Date
Where [Year].Date BETWEEN '87/01' AND '89/30'
Only one thing I Have some fields in some records that the value of them is NULL.How Can I Put Dashes(------) instead Of Null Values.
Thank you so so so much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply