MS SQL Server MSEQ - Set a dinamic varable in query

Author

Set a dinamic varable in query

2005-05-13, 7:23 am

Hello!
I wish to use a variable i a query that can change under execute.

Here are some of my rows, i wish to make the f3 colonne

F1 F2 F3(my new colonne)
10 text 10
null text 10
null tesxt 10
11 text 11
null tekxt 11

Hugo Kornelis

2005-05-13, 8:24 pm

On Fri, 13 May 2005 04:48:02 -0700, CJ wrote:

>Hello!
>I wish to use a variable i a query that can change under execute.
>
>Here are some of my rows, i wish to make the f3 colonne
>
>F1 F2 F3(my new colonne)
>10 text 10
>null text 10
>null tesxt 10
>11 text 11
>null tekxt 11

Hi CJ,

If these are all your columns, you are out of luck. A table in SQL
Server has no implicit ordering. As far as SQL Server is concerned, the
table above is exactly the same as this one:

F1 F2 F3(my new colonne)
null text 10
11 text 11
null tesxt 10
10 text 10
null tekxt 11

(Note that I merely has some of the rows switch position).

Your sample data indicates that you want to set F3 equal to F1 if it's
not NULL, or equal to F1 from the "last" of the "previous" rows with F1
not NULL otherwise. But since there is noting in the data to define
"last" or "previous", this becomes an impossible task.

If there is a way to distinguish "previous" and "last" rows from the
data, then feel free to repost. The best way to do that would be to
follow the guidelines in www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

2005-05-18, 3:23 am

I'm not sure that was the right answer to my question. But i tyr again.
I want to a column to desribe a hierarchy .

Here is my table:

F1 F2 F3(my new colonne)
10 text 10
null text 10
null tesxt 10
12 text 12
null tekxt 12
null tete 12
44 rwrw 44
null hhkjh 44
null tertre 44
........

"Hugo Kornelis" wrote:

> On Fri, 13 May 2005 04:48:02 -0700, CJ wrote:
>
>
> Hi CJ,
>
> If these are all your columns, you are out of luck. A table in SQL
> Server has no implicit ordering. As far as SQL Server is concerned, the
> table above is exactly the same as this one:
>
> F1 F2 F3(my new colonne)
> null text 10
> 11 text 11
> null tesxt 10
> 10 text 10
> null tekxt 11
>
> (Note that I merely has some of the rows switch position).
>
> Your sample data indicates that you want to set F3 equal to F1 if it's
> not NULL, or equal to F1 from the "last" of the "previous" rows with F1
> not NULL otherwise. But since there is noting in the data to define
> "last" or "previous", this becomes an impossible task.
>
> If there is a way to distinguish "previous" and "last" rows from the
> data, then feel free to repost. The best way to do that would be to
> follow the guidelines in www.aspfaq.com/5006.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Hugo Kornelis

2005-05-18, 7:24 am

On Wed, 18 May 2005 00:25:15 -0700, CJ wrote:

>I'm not sure that was the right answer to my question. But i tyr again.
>I want to a column to desribe a hierarchy .
>
>Here is my table:
>
>F1 F2 F3(my new colonne)
>10 text 10
>null text 10
>null tesxt 10
>12 text 12
>null tekxt 12
>null tete 12
>44 rwrw 44
>null hhkjh 44
>null tertre 44
>.......

Hi CJ,

Since you only repeated the same question with three extra example rows
tacked on to the end, I'm tempted to repeat my previous reply. But I'll
resist the temptation.

First, you obviously didn't follow the link I sent you. Please, follow
the instructions at www.aspfaq.com/5006 before you post again. If you
post CREATE TABLE and INSERT statements as outlined there, I can copy
and paste them to copy your test data to my test server. That makes
helping you lots easier!

Second, the extra sample rows still don't clarify the logic of your new
column. Take for example this row:
>null tertre 44
Why should the value in F3 for this row be 44? Why not 10, or 12, or any
other value?

If you answer that question, AND you post your table and data as CREATE
TABLE and INSERT statements as outlined in www.aspfaq.com/5006, then I
can try to help you further.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links