Create temporary table through select statement

[Omavlana]

Hi,

I want to create a temporary table and store the logdetails from
a.logdetail column.

select a.logdetail , b.shmacno
case when b.shmacno is null then
select
cast(substring( a.logdetail,1,c harindex('·',a. logdetail)-1) as
char(2)) as ShmCoy,
cast(substring( a.logdetail,cha rindex('·',a.lo gdetail)+1,char index('·',a.log detail,charinde x('·',a.logdeta il)+1)-(charindex('·', a.logdetail)+1) )
as char(10)) as ShmAcno
into ##tblabc
end
from shractivitylog a
left outer join
shrsharemaster b
on a.logkey = b.shmrecid

This statement giving me syntax error. Please help me..

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'case'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'end'.



sample data in a.logdetail

BR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep
2003·1·SGL·SGL· ·01 Jan 1900·0·0·0·0·0. 00·····


·0234578······· ····· ··········


UB··Aqua Duck··Toon Town Central·Punchli ne Place···02 Sep
2003·1·SGL·SGL· ·01 Jan 1900·0·0·0·0·0. 00·····


Regards.

[Shervin Shapourian]

Re: Create temporary table through select statement

1. You need a "," before CASE.
2. Put your subqueries inside parentheses.
3. Your CASE expression is returning multiple values.
4. You can't use a SELECT INTO inside another SELECT statement.

Sorry with all there problems I can't rewrite your script, I'm not
sure what you want to do. Tell us what you are looking for and send
the DDL.

Shervin

kiran@boardroom limited.com (Omavlana) wrote in message news:<b14098ab. 0310091745.3674 a687@posting.go ogle.com>...[color=blue]
> Hi,
>
> I want to create a temporary table and store the logdetails from
> a.logdetail column.
>
> select a.logdetail , b.shmacno
> case when b.shmacno is null then
> select
> cast(substring( a.logdetail,1,c harindex('·',a. logdetail)-1) as
> char(2)) as ShmCoy,
> cast(substring( a.logdetail,cha rindex('·',a.lo gdetail)+1,char index('·',a.log detail,charinde x('·',a.logdeta il)+1)-(charindex('·', a.logdetail)+1) )
> as char(10)) as ShmAcno
> into ##tblabc
> end
> from shractivitylog a
> left outer join
> shrsharemaster b
> on a.logkey = b.shmrecid
>
> This statement giving me syntax error. Please help me..
>
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'case'.
> Server: Msg 156, Level 15, State 1, Line 7
> Incorrect syntax near the keyword 'end'.
>
>
>
> sample data in a.logdetail
>
> BR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep
> 2003·1·SGL·SGL· ·01 Jan 1900·0·0·0·0·0. 00·····
>
>
> ·0234578······· ····· ··········
>
>
> UB··Aqua Duck··Toon Town Central·Punchli ne Place···02 Sep
> 2003·1·SGL·SGL· ·01 Jan 1900·0·0·0·0·0. 00·····
>
>
> Regards.[/color]

[Mauro]

Re: Create temporary table through select statement

Hi,

You can't create a temp table with CASE function; try with this:

select a.logdetail , case when b.shmacno is null then
cast(substring( a.logdetail,1,c harindex('·',a. logdetail)-1) as
char(2))+
cast(substring( a.logdetail,cha rindex('·',a.lo gdetail)+1,char index('·',a.log detail,charinde x('·',a.logdeta il)+1)-(charindex('·', a.logdetail)+1) )
as char(10))
end
into ##tblabc -- optional
from shractivitylog a
left outer join
shrsharemaster b
on a.logkey = b.shmrecid

Bye

kiran@boardroom limited.com (Omavlana) wrote in message news:<b14098ab. 0310091745.3674 a687@posting.go ogle.com>...[color=blue]
> Hi,
>
> I want to create a temporary table and store the logdetails from
> a.logdetail column.
>
> select a.logdetail , b.shmacno
> case when b.shmacno is null then
> select
> cast(substring( a.logdetail,1,c harindex('·',a. logdetail)-1) as
> char(2)) as ShmCoy,
> cast(substring( a.logdetail,cha rindex('·',a.lo gdetail)+1,char index('·',a.log detail,charinde x('·',a.logdeta il)+1)-(charindex('·', a.logdetail)+1) )
> as char(10)) as ShmAcno
> into ##tblabc
> end
> from shractivitylog a
> left outer join
> shrsharemaster b
> on a.logkey = b.shmrecid
>
> This statement giving me syntax error. Please help me..
>
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'case'.
> Server: Msg 156, Level 15, State 1, Line 7
> Incorrect syntax near the keyword 'end'.
>
>
>
> sample data in a.logdetail
>
> BR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep
> 2003·1·SGL·SGL· ·01 Jan 1900·0·0·0·0·0. 00·····
>
>
> ·0234578······· ····· ··········
>
>
> UB··Aqua Duck··Toon Town Central·Punchli ne Place···02 Sep
> 2003·1·SGL·SGL· ·01 Jan 1900·0·0·0·0·0. 00·····
>
>
> Regards.[/color]