Joining two fields in a query

I am trying to join two fields in a query in SQL 2000. For example.

Update myTable SET field_1 = @.field_1_value , field_2 = @.field_2_value, field_3 = @.field_1_value + ' x ' + field_2_value

Is this even possible.

I want the user to input values for fields 1 and 2, then in the background combine the two and insert that value in field 3.

Thanks in advance,

Scotty_C

the ' x ' should also be inserted between the values.

|||that'll work! there's just a typo for the @.field_2_value

Update myTable SET field_1 = @.field_1_value , field_2 = @.field_2_value, field_3 = @.field_1_value + ' x ' + @.field_2_value|||

I agree that there was a typo, however, the given SQL Statement was just fabricated for the forum as an example.

Thank you for you input.

When I attempt to execute the Statement I get the Error Message:

"Syntax Error Converting the varChar value ' x ' to a column of datatype int."

The actual SQL Statement being used is this:

UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = @.Width + ' x ' + @.Length
WHERE (SheetSizeID = @.SheetSizeID)

The datatype for the column "Length" is varChar(50)

|||excuse me, it has been a long day, the datatype for the column "Label" is varChar(50) and the dataype for the columns "Length" and "Width" is int.|||

Scotty_C wrote:

I agree that there was a typo, however, the given SQL Statement was just fabricated for the forum as an example.

Thank you for you input.

When I attempt to execute the Statement I get the Error Message:

"Syntax Error Converting the varChar value ' x ' to a column of datatype int."

The actual SQL Statement being used is this:

UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = @.Width + ' x ' + @.Length
WHERE (SheetSizeID = @.SheetSizeID)

The datatype for the column "Length" is varChar(50)

you have to use CAST or CONVERT before concatenating your values...
UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = CAST(@.Width AS varchar(10)) + ' x ' + CAST(@.Length AS varchar(10))
WHERE (SheetSizeID = @.SheetSizeID)

HTH,|||

Yes CryptoKnight,

That works very well, thank you!

Thanks,

Scotty_C

Joining table to itself

In myTable I have
RequiredAmount
RequiredDate
GrantedAmount
GrantedDate
I wish to obtain one view showing the sum per year.
For the required part it would be:
SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
And for the Granted part:
SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)
Now, what I need is a presentation with three columns:
myYear, reqAmount, grantAmount
I'm fooling around with the table joined to itself, but can't seem to
get it right...maybe wrong approach?
Regards /SnedkerRead about Cross-tab reports in sql server help file
Madhivanan
Morten Snedker wrote:
> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker|||use a full outer join (self)..
something like this..
untested..
SELECT COALESCE(A.myYear,B.myYear), COALESCE(A.reqAmount,0),
COALESCE(B.grantAmount,0)
FROM
(SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
) A FULL OUTER JOIN
(SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)) B
ON A.myYear= B.myYear
Hope this helps.
-Omni|||Can you see if this works for you
SELECT
YEAR(tDate) as TransYear,
SUM(CASE WHEN tType = 'R' then tAmt else 0 end) as SumReq,
SUM(CASE WHEN tType = 'G' then tAmt else 0 end) as GraReq
FROM
(
select 'R' as tType,reqAmount as tAmt,ReqDate as tDate FROM Mytable
UNION ALL
select 'G' as tType,GrantAmount as tAmt,GrantDate as tDate FROM Mytable
) x
GROUP BY YEAR(tDate)
- Sha Anand
"Morten Snedker" wrote:

> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker
>|||Hi,
Nice solution.. But Why do you need a case? Can't it be something simple
like this.
select TransYear, sum(GrantAmount) SumGrant, sum(reqAmount) SumReq
from
(select 0 as GrantAmount,reqAmount ,year(ReqDate) as TransYear FROM Mytable
UNION ALL
select GrantAmount,0 as reqAmount, year(GrantDate) as TransYear FROM
Mytable) x
group by TransYear
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If your a consultant you know your clients couldn't give
a whit about how you provide a solution just as long as you
give them one.Perhaps we can help.Check out RAC for easy
solutions to all kinds of data manipulation problems including
crosstabs on sql server.
www.rac4sql.net