**Dynamic SQL**

Hi
I'm working with SQL 2000 and I have a table with following struncture and I
want to get the result as I described below ,but I don't know how?
PAP (PART1 NUMERIC(3) ,PART2 NUMERIC(3) ,QTY NUMERIC(2))
Primary Key (part1+part2)
part1 part2 qty
-- -- --
100 205 5
100 204 6
101 203 7
205 107 1
107 116 2
Result:
I want to show following result when we select data for part1=100 and
part2=205:
part1 part2 qty finalqty
-- -- -- --
100 205 5 2
I used following code to get the result and it was successfull
select a.par1,a.part2,a.qty,c.qty2
from pap a,pap b,pap c
where a.part2=b.part1
and b.part2=c.part1
but what I really s for,is the number of join
is not static,because if a new row inserted in the table the result would
be different for example:
new row : 116 114 3
then
the result:
part1 part2 qty finalqty
-- -- -- --
100 205 5 3
I want a sql statement to fetch the result witjout dependecy to the special
number of joins.
Thanks alot
new row:The very first question you'll have to answer is how do you know which row
is the "new" row. Once that is clear you can do something like this.
e.g.
create table pap (part1 numeric(3) ,part2 numeric(3) ,qty numeric(2),
primary key (part1,part2)
)
go
insert pap (part1,part2,qty)
select 100, 205, 5
union all select 100, 204, 6
union all select 101, 203, 7
union all select 205, 107, 1
union all select 107, 116, 2
go
--pick the last qty
select *,(select top 1 qty from pap order by part1 desc,part2 desc) lastqty
from pap
where part1=100 and part2=205
go
drop table pap
go
-oj
"M R" <rezvani@.parskhazar.net> wrote in message
news:eCGJAfbZFHA.1412@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm working with SQL 2000 and I have a table with following struncture and
> I
> want to get the result as I described below ,but I don't know how?
> PAP (PART1 NUMERIC(3) ,PART2 NUMERIC(3) ,QTY NUMERIC(2))
> Primary Key (part1+part2)
> part1 part2 qty
> -- -- --
> 100 205 5
> 100 204 6
> 101 203 7
> 205 107 1
> 107 116 2
> Result:
> I want to show following result when we select data for part1=100 and
> part2=205:
> part1 part2 qty finalqty
> -- -- -- --
> 100 205 5 2
> I used following code to get the result and it was successfull
> select a.par1,a.part2,a.qty,c.qty2
> from pap a,pap b,pap c
> where a.part2=b.part1
> and b.part2=c.part1
> but what I really s for,is the number of join
> is not static,because if a new row inserted in the table the result would
> be different for example:
> new row : 116 114 3
> then
> the result:
> part1 part2 qty finalqty
> -- -- -- --
> 100 205 5 3
> I want a sql statement to fetch the result witjout dependecy to the
> special
> number of joins.
> Thanks alot
> new row:
>
>|||Get a copy of TREES & HIERARCHIES IN SQL for details, but for now,
Google up the "Nested set model". Here is how to travel to the leaf
nodes from any point in the tree.
SELECT @.my_part, T1.qty
FROM Tree AS T1, Tree AS T2
WHERE T1.rgt-T1.lft =1 -- leaf node
AND T1.lft BETWEEN T2.lft AND T2.rgt
AND T2.part = @.my_part;|||It doesn't matter to recognize the new record ,because if we have a correct
sql statement it automatically will be fetched in the result,and your sql
statement is not suitable
select *,(select top 1 qty from pap order by part1 desc,part2 desc) lastqty
from pap
where part1=100 and part2=205
cause the last qty is not the qty with the greatest part1 and part2,we
select the qty as a last one by joining the part2 with part1 in this table
and repeat this action to find the ast one.
"oj" <nospam_ojngo@.home.com> wrote in message
news:#CR18acZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> The very first question you'll have to answer is how do you know which row
> is the "new" row. Once that is clear you can do something like this.
> e.g.
> create table pap (part1 numeric(3) ,part2 numeric(3) ,qty numeric(2),
> primary key (part1,part2)
> )
> go
> insert pap (part1,part2,qty)
> select 100, 205, 5
> union all select 100, 204, 6
> union all select 101, 203, 7
> union all select 205, 107, 1
> union all select 107, 116, 2
> go
> --pick the last qty
> select *,(select top 1 qty from pap order by part1 desc,part2 desc)
lastqty
> from pap
> where part1=100 and part2=205
> go
> drop table pap
> go
>
> --
> -oj
>
> "M R" <rezvani@.parskhazar.net> wrote in message
> news:eCGJAfbZFHA.1412@.TK2MSFTNGP12.phx.gbl...
and
would
>|||Well, you didn't mention the child/parent relationship in your post and I
didn't bother reading each value to see the trend. So, what you're after is
the hierachy problem. Y
You want to take a look at the example in book online or use Celko's or
this.
--adding another left join
--if you need to go level deeper.
select p1.*,coalesce(p3.qty,p2.qty,p1.qty) last_qty
from pap p1 left join pap p2 on p1.part2=p2.part1
left join pap p3 on p2.part2=p3.part1
where p1.part1=100 and p1.part2=205
-oj
"M R" <rezvani@.parskhazar.net> wrote in message
news:ulcBPvlZFHA.3732@.TK2MSFTNGP10.phx.gbl...
> It doesn't matter to recognize the new record ,because if we have a
> correct
> sql statement it automatically will be fetched in the result,and your sql
> statement is not suitable
> select *,(select top 1 qty from pap order by part1 desc,part2 desc)
> lastqty
> from pap
> where part1=100 and part2=205
> cause the last qty is not the qty with the greatest part1 and part2,we
> select the qty as a last one by joining the part2 with part1 in this table
> and repeat this action to find the ast one.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:#CR18acZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> lastqty
> and
> would
>