Assigning result of Select

Question

Post reply

Assigning result of Select

gh.abtahi

SSC Enthusiast

Points: 185
More actions
December 9, 2010 at 2:19 am

#230411

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
----------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 1

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

sandippani SSC Eights! Points: 957 More actions · Answer 2

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

Lowell SSC Guru Points: 323518 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gh.abtahi SSC Enthusiast Points: 185 More actions · Answer 4

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

gh.abtahi SSC Enthusiast Points: 185 More actions · Answer 5

gh.abtahi (12/10/2010)
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

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.