Need help converting a CHAR into a Datetime

I have a Database which is having a Counterdate time Column stored in the form of Char(24)

But i need it to be in form of datetime so that i can use the datetime functions on it..When i use the cast or convert inside the function where i am passing this character it gives me error

"Conversion failed when converting datetime from character string."

I am done all permutatiions and combinations for this used

Set @.DE = convert ( datetime, @.ts,121)

Set @.de = cast( @.ts as datetime)

BUT ALWZ give me same error.... also when i copy the whole of the data table into some other database the error doesnt come.. i converts the character into the datetime..

I DONT understand why the Server is behaving wiered..

Hoping to get an answer soon.

With regards

Sharad

Database Developer ,

UIC

Hi Sharad,

Can you post a sample of the (character) data that is causing the issue?

Thanks,

Rob

|||

Sir,

I am just using the performance Logs of the a server and in that the database get automatically generated and hence..

I get the data of datetime column in Char(24) form.. and when i use an function to convert that into a datetime form it gives me error..

take anytime .e.g of the form

"2006-11-15 17:33:22.015"

if this is the time stamp and stored in the char(24) format and I want it to be datetime ...

What any sensible person would do

1. cast it into datetime

2. Convert into datetime..

Both not working ..

With regards

Sharad

|||

PLZ reply if u could Help with this simple STUFF

This CODE I RUN

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print @.de

Output is

:::

2006-10-27 20:01:13.297

Oct 27 2006 8:01PM

But i want output to be exactly same

as 2006-10-27 20:01:13.297

Plz help me with this..

|||

change your statement as below,

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print convert(varchar, @.ds, 121)

Need Help : Constraint

Dear All,
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert LieHi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]
%')
)
GO
John
"Robert Lie" wrote:

> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>

Need Help : Constraint

Dear All,
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert LieHi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]%')
)
GO
John
"Robert Lie" wrote:
> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>

Need Help : Constraint

Dear All,
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert Lie
Hi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]%')
)
GO
John
"Robert Lie" wrote:

> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>
sql

Need Cursor Help

Hello, I'm trying to construct a cursor that will sequentually increment a number and then update a column with the incremented number. My propblem is thatallthe rows in the table are being updated with the base number +1. So all rows are updated with 278301. BUT, what I really want is for only the items with adrscode of 'bill to' to be given an incremented number.

For example, if there are only five rows of 100 with an adrscode = 'bill to' then only five rows will be updated and the value of the custnmbr should be, 278301, 278302, 278303 ....

I could really use some help with this cursor:

Declare @.CustomerName as char (60),
@.seqno as int,
@.BaseSeqno as int
set @.Baseseqno = 278300

declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbr
open c
fetch next from c into @.CustomerName
while @.@.fetch_status=0
begin
set @.seqno = @.BaseSeqno + 1

update NXOFcustomers set custnmbr = @.seqno
Where custnmbr = @.CustomerName

fetch next from c into @.CustomerName
end
close c
deallocate c

Make sure you are dealing with the right data. Is custnmbr same as customername?
Then, you would modify your sequence generation to

set @.seqno = @.seqno + 1

and set the @.seqno to 278300. You wouldnt need the @.Baseseqno at all. Based on your current logic, you will always gett he same number since you are adding 1 to @.BaseSeqno which is 278300.

Finally, I would recommend looking at alternatives to cursors. Read up books on line regarding cursors. There are pros/cons.|||

Thanks Dinakar for helping. I change my code to what is below and thought that I had things correctly from your explaination but the update still updates all the rows with 278301. Yes, customername is the same as custnmbr so I changed the code to reflect this.

Declare @.custnmbr as char (60),
@.seqno as int
set @.seqno = 278300

declare c cursor for select custnmbr from NXOFcustomers Where adrscode = 'BILL TO' order by custnmbr
open c
fetch next from c into @.custnmbr
while @.@.fetch_status=0
begin
set @.seqno = @.seqno + 1

update NXOFcustomers set custnmbr = @.seqno
Where custnmbr = @.custnmbr

fetch next from c into @.custnmbr
end
close c
deallocate c
go

I'm a little confused with sequence generation. Isn't set @.seqno = @.segno + 1 in the correct location.

Thanks again for responding.

|||you might want to change the @.custnmbr from char to varchar. char is a fixed length data type. so it pads spaces if the string is less than the specified length. You might also want to add the condition adrscode='BILL TO' in the update statement, so if you have 2 customers with same name you have a higher chance the correct record can get updated.

Need count and subselects

Hello,
i use mysql 4.0.20 and have the following problem:
I have a column with names and want to extract all names which are lets say 3 times in this column. See

Names
foo
bar
bla
foo
bla
bla
foo

In this case mysql shoud output foo and bla, but how to do this??
I tried some combinations of count and something like subselects, but no success.

Could anybody help me please? It drives me crazy.
Thanks, b52hmm, does mysql support having?

group by names
having count(*) = 3|||yea it does,
i will give it a try, but not sure to get it working.

Thanks, bye, b52|||YES it works! :D :D
SELECT KNAME FROM AUFTRAG LEFT JOIN KUNDE ON KNR=AKNR GROUP by KNAME HAVING COUNT(*) =3

Thank you :) , bye, b52sql

Need a trigger based on a row

Hello, everyone

I need to create a trigger to update a FLAG column by row modification. The table likes,

Col1 FLAG
a 0
b 0
c 0
d 0
e 0

If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.

Anyhelp will be approciated.

Thanks a lot

ZYTIf col1 is a primary key, this is relatively easy. If you don't have a primary key, I don't know how to do it. Can you "fill in the blanks" a bit on your requirements?

-PatP|||Col1 doesn't have to be a primary key, but you do need a primary key. Put this in your trigger:

Update YourTable
set FLAG = 1
from YourTable
inner join Deleted on YourTable.PrimaryKey = Deleted.PrimaryKey
where YourTable.Col1 <> Deleted.Col1|||So what happens when the code that launches the trigger is something like:UPDATE yourtable SET col1 = 'a'All of the rows except for one change. Without a PK, things get ugly really fast from a SQL perspective, and I'd guess that the business rules would go berzerk!

-PatP|||All of the rows except for one change.-PatP

I think that is what he would want. All of the rows except one were modified, so set the flags on all but one record.

But yes, as I mentioned in my post, he is going to need a primary key on the table.

Need a query to transform data to column names...

Hello:
I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GBThis is a multi-part message in MIME format.
--=_NextPart_000_0176_01C3AF6F.30174D50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Try:
select
NameID
, max (case when F = 'A' then Value end) as A
, max (case when F = 'B' then Value end) as B
, max (case when F = 'C' then Value end) as C
, max (case when F = 'D' then Value end) as D
, max (case when F = 'E' then Value end) as E
from
(
select
NameID
, case type 1 then F1 else F2 end as F
, case type 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message
news:Y78vb.67322$1K.40554@.edtnps84...
Hello:
I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GB
--=_NextPart_000_0176_01C3AF6F.30174D50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type 1 =then F1 else F2 end as F
, case type 1 =then V1 else V2 end as Value
from
=T1
cross join
(
select 1 as =type union all
select =2
) as x
) as y
group by
=NameID
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"GB" wrote in message news:Y78vb.67322$1K.40554@.edt=nps84...Hello:I have a table T1 like this:NameID | F1 | V1 | =F2 | V2 =|--- 1 A 49% B =51% 2 C 35% D =65% 3 E 20% B =80% 4 D 10% A 90%I =need a query to transform T1 into a recordset like this:NameID =| A | B | C | D | =E |--- 1 49% 51% 2 = &=nbsp; 35% 65% 3 = 80% &nbs=p; 20% 4 90% &nbs=p; 10%Thanks,GB

---

--=_NextPart_000_0176_01C3AF6F.30174D50--|||This is a multi-part message in MIME format.
--=_NextPart_000_004D_01C3AF59.24E0FAA0
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
Hello, Tom:
Thank you for the reply, but when I try to execute this statement
I'v got the errors:
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '1'.
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'as'.
How to fix the problem?
Thanks,
GB
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:euMUdk5rDHA.1184@.TK2MSFTNGP10.phx.gbl...
Try:
select
NameID
, max (case when F =3D 'A' then Value end) as A
, max (case when F =3D 'B' then Value end) as B
, max (case when F =3D 'C' then Value end) as C
, max (case when F =3D 'D' then Value end) as D
, max (case when F =3D 'E' then Value end) as E
from
(
select
NameID
, case type 1 then F1 else F2 end as F
, case type 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message =news:Y78vb.67322$1K.40554@.edtnps84...
Hello:
I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GB
--=_NextPart_000_004D_01C3AF59.24E0FAA0
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hello, Tom:
Thank you for the reply, but when I try to execute =this statement
I'v got the errors:
Server: Msg 170, Level 15, State 1, Line 12Line =12: Incorrect syntax near '1'.Server: Msg 156, Level 15, State 1, Line 20Incorrect syntax near the keyword 'as'.
How to fix the problem?
Thanks,
GB
"Tom Moreau" = wrote in message news:euMUdk5rDHA.1184=@.TK2MSFTNGP10.phx.gbl...
Try:

select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type =1 then F1 else F2 end as F
, case type =1 then V1 else V2 end as Value
from
=T1
cross join
(
select 1 =as type union all
select =2
) as x
) as y
group by
=NameID
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"GB" wrote in message news:Y78vb.67322$1K.40554@.edt=nps84...Hello:I have a table T1 like this:NameID | F1 | V1 | =F2 | V2 |--- 1 A 49% B =51% 2 C 35% D =65% 3 E 20% B =80% 4 D 10% A 90%I =need a query to transform T1 into a recordset like this:NameID =| A | B | C | D | =E |--- 1 =49% 51% =2 = &=nbsp; 35% 65% =3 = =80% &nbs=p; 20% 4 =90% &nbs=p; 10%Thanks,GB

---

--=_NextPart_000_004D_01C3AF59.24E0FAA0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0064_01C3AF5D.1EB3E030
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
I'v fixed the prblem:
select
NameID
, max (case when F =3D 'A' then Value end) as A
, max (case when F =3D 'B' then Value end) as B
, max (case when F =3D 'C' then Value end) as C
, max (case when F =3D 'D' then Value end) as D
, max (case when F =3D 'E' then Value end) as E
from
(
select
NameID
, (case when type =3D 1 then F1 else F2 end) as F
, (case when type =3D 1 then V1 else V2 end) as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
Thanks,
GB
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:euMUdk5rDHA.1184@.TK2MSFTNGP10.phx.gbl...
Try:
select
NameID
, max (case when F =3D 'A' then Value end) as A
, max (case when F =3D 'B' then Value end) as B
, max (case when F =3D 'C' then Value end) as C
, max (case when F =3D 'D' then Value end) as D
, max (case when F =3D 'E' then Value end) as E
from
(
select
NameID
, case type 1 then F1 else F2 end as F
, case type 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message =news:Y78vb.67322$1K.40554@.edtnps84...
Hello:
I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GB
--=_NextPart_000_0064_01C3AF5D.1EB3E030
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I'v fixed the prblem:
select NameID, max (case when F =3D 'A' then Value end) =as A, max (case when F =3D 'B' then Value end) as B, max (case when F =3D 'C' then Value end) as C, max (case when F =3D 'D' then Value end) as D, max (case when F =3D 'E' then Value end) as Efrom(select NameID, (case when type =3D 1 then F1 else =F2 end) as F, (case when type =3D 1 then V1 else V2 end) as Valuefrom T1cross =join( select 1 as type union all select 2) as x) =as ygroup by NameID
Thanks,
GB
"Tom Moreau" = wrote in message news:euMUdk5rDHA.1184=@.TK2MSFTNGP10.phx.gbl...
Try:

select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type =1 then F1 else F2 end as F
, case type =1 then V1 else V2 end as Value
from
=T1
cross join
(
select 1 =as type union all
select =2
) as x
) as y
group by
=NameID
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"GB" wrote in message news:Y78vb.67322$1K.40554@.edt=nps84...Hello:I have a table T1 like this:NameID | F1 | V1 | =F2 | V2 |--- 1 A 49% B =51% 2 C 35% D =65% 3 E 20% B =80% 4 D 10% A 90%I =need a query to transform T1 into a recordset like this:NameID =| A | B | C | D | =E |--- 1 =49% 51% =2 = &=nbsp; 35% 65% =3 = =80% &nbs=p; 20% 4 =90% &nbs=p; 10%Thanks,GB

---

--=_NextPart_000_0064_01C3AF5D.1EB3E030--|||This is a multi-part message in MIME format.
--=_NextPart_000_0231_01C3AF77.7C2897F0
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: 7bit
Sorry about that. When looks like then after a while ... ;-)
select
NameID
, max (case when F = 'A' then Value end) as A
, max (case when F = 'B' then Value end) as B
, max (case when F = 'C' then Value end) as C
, max (case when F = 'D' then Value end) as D
, max (case when F = 'E' then Value end) as E
from
(
select
NameID
, case type when 1 then F1 else F2 end as F
, case type when 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message
news:sB8vb.67375$1K.38748@.edtnps84...
Hello, Tom:
Thank you for the reply, but when I try to execute this statement
I'v got the errors:
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '1'.
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'as'.
How to fix the problem?
Thanks,
GB
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euMUdk5rDHA.1184@.TK2MSFTNGP10.phx.gbl...
Try:
select
NameID
, max (case when F = 'A' then Value end) as A
, max (case when F = 'B' then Value end) as B
, max (case when F = 'C' then Value end) as C
, max (case when F = 'D' then Value end) as D
, max (case when F = 'E' then Value end) as E
from
(
select
NameID
, case type 1 then F1 else F2 end as F
, case type 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message
news:Y78vb.67322$1K.40554@.edtnps84...
Hello:
I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GB
--=_NextPart_000_0231_01C3AF77.7C2897F0
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Sorry about that. When looks =like then after a while ... ;-)
select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type =when 1 then F1 else F2 end as F
, case type =when 1 then V1 else V2 end as Value
from
=T1
cross join
(
select 1 as =type union all
select =2
) as x
) as y
group by
NameID
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"GB" wrote in message news:sB8vb.67375$1K.38748@.edt=nps84...
Hello, Tom:
Thank you for the reply, but when I try to execute =this statement
I'v got the errors:
Server: Msg 170, Level 15, State 1, Line 12Line =12: Incorrect syntax near '1'.Server: Msg 156, Level 15, State 1, Line 20Incorrect syntax near the keyword 'as'.
How to fix the problem?
Thanks,
GB
"Tom Moreau" = wrote in message news:euMUdk5rDHA.1184=@.TK2MSFTNGP10.phx.gbl...
Try:

select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type =1 then F1 else F2 end as F
, case type =1 then V1 else V2 end as Value
from
=T1
cross join
(
select 1 =as type union all
select =2
) as x
) as y
group by
=NameID
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"GB" wrote in message news:Y78vb.67322$1K.40554@.edt=nps84...Hello:I have a table T1 like this:NameID | F1 | V1 | =F2 | V2 |--- 1 A 49% B =51% 2 C 35% D =65% 3 E 20% B =80% 4 D 10% A 90%I =need a query to transform T1 into a recordset like this:NameID =| A | B | C | D | =E |--- 1 =49% 51% =2 = &=nbsp; 35% 65% =3 = =80% &nbs=p; 20% 4 =90% &nbs=p; 10%Thanks,GB

---

--=_NextPart_000_0231_01C3AF77.7C2897F0--

Need a little help with Join (Beginner)

Im writing this stored procedure and just need to add one more column to the mix. But i cant recall exactly what to do with joins. I have examples, but they all look alittle different. Anyways, i realized that the column i want to add (its highlighted) Needs to have a join. Here is my code. Can anyone explain to me, how i join this with the rest. IT is located in a table called Budget_Reporting_Group and the column is called Budget_Reporting_Group_Description. As you can see, I have another Table called Salesperson_Purchaser, Im pretty sure these two tables are relational by a Salesperson_Purchaser_ID. How would i do the join? If anything, can you display what it would look like (IE yourDatabase.dbo.TableName as Table_Name ON etc etc) I completely forgot the format.

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_Copy] Script Date: 06/28/2007 09:49:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTERPROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY]

(@.Region int=Null)

AS

BEGIN

SELECT

[Item_Ledger_Posting_Datetime] Post_Date

,'SMP' Budget_Type

,'Invoice'as Document_Type

,[Item_Ledger_Document_No] Document_Number

,[Item_Description]+' '+'('+[Item_No]+')' Entry_Description

,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime,'SALESAMP')*-1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL *-1 ) Amount

,-1*[Item_Ledger_Invoiced_Qty] Quantity

,Customer_Name

,'' External_Doc_no

,[Item_Ledger_Sales_Responsible] SR_Code

,[Item_Ledger_Mars_Period_Code] ThePeriod

,[Item_Ledger_Mars_Year] TheYear

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]

INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting

ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code

LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

WHERE Region.Region_Key = @.Region

AND [Item_Ledger_Mars_Year] = 2007

UNIONALL

SELECT

[GL_Entry_Posting_DateTime]

,[Budget_Type_Code]

,[Document_Type]

,[GL_Entry_Document_No]

,[GL_Entry_Description]

,[GL_Entry_Amount]

,0 Quantity

,[User_ID]

,[GL_Entry_External_Document_No]

,[Sales_Responsible]

,[Mars_Period_Code]

,[Mars_Year]

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Tbl_GL_entry] GL_entry

INNERJOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Budget_Accounts] Budget_Accounts

ON Budget_Accounts.[GL_Account_No] = GL_entry.[GL_Account]

INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON GL_entry.[Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting

ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code

WHERE GL_entry.[Mars_Year] = 2007

END

Code Snippet

INNER JOIN Budget_Reporting_Group

ON BUDGET_REPORTING_GROUP.Salesperson_Purchaser_ID = SALESPERSON_PURCHASER.Salesperson_Purchaser_ID

|||

I still keep getting this error:

sg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7

Invalid object name 'Tbl_Budget_Reporting_Group'.

Msg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7

Invalid object name 'Tbl_Budget_Reporting_Group'.

Need a list of Error Code

Hi,

When we configure a transformation to redirect the error record, SSIS gives 2 columns: 'error code' and 'error column'. In one article, I have seen how to get the error description using the 'error code'.

My questions are,

Where I can get list of all possible error codes? 'error column' gives the id of the column. How to get the name of the 'error column'?

Thanks.

Basically you need to know the metadata of the normal output, not that easy, but simon has wrapped this up in a Tx.

http://sqljunkies.com/WebLog/simons/archive/2005/12/15/SSIS_Enhanced_Error_Component.aspx

Some related info -

http://msdn2.microsoft.com/en-us/library/ms345163.aspx

The SSIS error codes are listed in BOL. I also have them on my wiki, because BOL only got them in an update and I wanted them before that, and also you can add notes on the errors on the wiki.

NCHAR vs NVARCHAR

In one of the tables i have a column that will always store a single unicode
character. Now , according to theory i should go with NCHAR(1) . But i would
like to know what is the divantage of using NVARCHAR(1) instead of
NCHAR(1) in this case ? is there going to be any extra overhead ?
Thanks
ManishIf you're always storing 1 character us NCHAR(1).
NVARCHAR(1) will require extra storage space to store the length of the data
(using 4 bytes instead of only 2)
NVARCHAR(1) will allow you to store N'' (empty string) which means it needs
to store length.
"Manish Gaur" <ManishGaur@.discussions.microsoft.com> wrote in message
news:79AEFA64-AB85-4262-9317-204D908C6F57@.microsoft.com...
> In one of the tables i have a column that will always store a single
unicode
> character. Now , according to theory i should go with NCHAR(1) . But i
would
> like to know what is the divantage of using NVARCHAR(1) instead of
> NCHAR(1) in this case ? is there going to be any extra overhead ?
> Thanks
> Manish|||You are going to burn 2 extra bytes per row (per column?) as you will need t
o
store the size of the field, even though it is only 1 in length. Not wise.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Manish Gaur" wrote:

> In one of the tables i have a column that will always store a single unico
de
> character. Now , according to theory i should go with NCHAR(1) . But i wou
ld
> like to know what is the divantage of using NVARCHAR(1) instead of
> NCHAR(1) in this case ? is there going to be any extra overhead ?
> Thanks
> Manish