How to refer to subselect columns in select statement

Hi everybody,

I have been looking around for an answer to this one, but haven't had any luck so far. Hope some of you clever minds out there can help me out on this :-)

I have an sql statement that does updates into another table like this:

insert into result_table

select value1 as 'value 1',

(select number1 from some_other_table where (... linked)) as 'my sub1',

(select number2 from some_third_table where (... again linked)) as 'my sub2'

select 0 as 'zero'

from my_table where ("some clause")

Now, I want to replace the 'zero' column with something useful like the sum of subselect 1 and 2. How can I do that? The only solution I can make work is something like:

insert into result_table

select value1 as 'value 1',

(select number1 from some_other_table where (... linked)) as 'my sub1',

(select number2 from some_third_table where (... again linked)) as 'my sub2',

(

(select number1 from some_other_table where (... linked)) +

(select number2 from some_third_table where (... again linked))

) as 'sub1_plus_sub2'

from my_table where ("some clause")

It works, but in my current project I would end up with tons of duplicate lines. This way I would end up having to maintain select code several places.

Is there a way to refer to a column in the same select statement that you're executing? In the ORDER BY clause you can use things like ORDER BY 1,2 to refer to columns without having to use the entire statement again, but I haven't been able to find anything that seems useful on this subject. I guess what I miss is a way like

field(2).value + field(3).value

Best regards, Jan

In T-SQL you can't do as you guess..

But it is possible to reuse the values. For achive your requirement you have to use the derived table..

Code Snippet

insert into result_table

Select

[Value 1]

,[My Sub1]

,[My Sub2]

,[My Sub1] + [My Sub1] as [Sub1_Pluse_Sub2]

From

(

select

value1 as [Value 1]

,(select number1 from some_other_table where (... linked)) as [My Sub1]

,(select number2 from some_third_table where (... again linked)) as [My Sub2]

from

my_table

where

("some clause")

) as Data

|||

try this instead

INSERT
INTO result_table
SELECT value1 as 'value 1'
, some_other_table.number1 AS 'my sub1'
, some_third_table.number2 AS 'my sub2',
, some_other_table.number1 + some_third_table.number2 as 'sub1_plus_sub2'
FROM my_table INNER JOIN
some_other_table ON (.. linked) INNER JOIN
some_third_table ON (... again linked)
WHERE ("some clause")

How to reapply a trasaction log to a database?

Hi everybody,

I'm in a very awfull situation. Because of a big crash in my MS Sql server, y loose the .mdf file of a database. I have an older .mdf file and the complete transaction log (.ldf file) before the crash. [Note: not a backup file, just the .ldf file]. As far as I know, no truncate has been performed since the date of the mdf file, therefore, all transaction shoud be "stored" inside the log file.

Is there any way to re-apply the transactions stored on the log file to the original database?

I will appreciate a lot any help.

Thanks in advance,

Enrique.

enrique_pinaud@.hotmail.com

I don′t know if there is a a thrid party tool, but restoring for Point in time recovery (like you need) is only supported with builtin tools with a backup.

HTH, jens Suessmeyer.|||Without a backup to start from you won't be able to use any builtin mechanisms to recover with only an old mdf and the current ldf. There are 3rd party tools out there that can extract dml statements out of the log, and may be of some help to you.

How to read DTS log ?

Hi everybody,
Someone to know a program for DTS log reading ?
When I open it with Wordpad there was same confusion.
Thanks a lot.To view package logs

In SQL Server Enterprise Manager, expand Data Transformation Services.

Do one of the following:
Right-click Local Packages (if the Data Transformation Services (DTS) package log was saved to Microsoft SQL Server) and then click Package Logs.

Right-click Meta Data Services Packages (if the package log was saved to SQL Server 2000 Meta Data Services), and then click Package Logs.

Click Local Packages or Meta Data Services Packages, and in the details pane, right-click a package and click Package Logs.