January 4, 2010 at 2:13 am
Hi all,
I need some help.
I need to create a temporary table from 2 tables.
ex :
BEGIN TRAN
set nocount on
create table testv
( tsv_orderid char (16) null,
tsv_group smallint null,
tsv_ord_ref_reorder char (16) null,
tsv_group_ref_reorder smallint null )
create table testOH
( tsh_orderid char (16) null,
tsh_group smallint null,
tsh_ord_ref_reorder char (16) null,
tsh_group_ref_reorder smallint null )
create table #result1
( res_orderid char (16) null,
res_group smallint null,
res_ord_ref_reorder char (16) null,
res_group_ref_reorder smallint null)
--
insert into testv values (
'XX20000816000224',1, NULL, NULL)
insert into testv values (
'XX20000816000225',1, 'XX20000816000224',1)
insert into testv values (
'XX20000816000226',1, NULL, NULL)
insert into testv values (
'XX20000816000227',1, 'XX20000816000226', 1)
insert into testv values (
'XX20000816000228',1, 'XX20000816000227', 1)
insert into testv values (
'XX20000816000229',1, 'XX20000816000228', 1)
insert into testOH values (
'XX20000816000233',1, 'XX20000816000229', 1)
insert into testv values (
'XX20000816000235',1, null, null)
insert into testv values (
'XX20000816000236',1, 'XX20000816000235', 1)
select * from testv
select * from testOH
rollback
what I want to get is the below result :
from for exemple the key : XX20000816000228
#result1 table :
res_orderid res_group res_ord_ref_reorder res_group_ref_reorder
---------------- --------- ------------------- ---------------------
XX20000816000226 1 NULL NULL--coming from testv
XX20000816000227 1 XX20000816000226 1--coming from testv
XX20000816000228 1 XX20000816000227 1--coming from testv
XX20000816000229 1 XX20000816000228 1--coming from testv
XX20000816000233 1 XX20000816000229 1--coming from testOH
Thanks for your help
January 4, 2010 at 9:32 pm
Hi,
What you try so far, is any statement you build to achieve?
ok, try in this way
/*create the table with additional column*/
create table #testv
(
slno int identity(1,1),
tsv_orderid char (16) null,
tsv_group smallint null,
tsv_ord_ref_reorder char (16) null,
tsv_group_ref_reorder smallint null,
Grouporder int null
)
/*insert the values in sequance of the reorder/ ref orignal order*/
insert into #testv values ('XX20000816000224',1, NULL, NULL,NULL)
insert into #testv values ('XX20000816000225',1, 'XX20000816000224',1,NULL)
insert into #testv values ('XX20000816000226',1, NULL, NULL,NULL)
insert into #testv values ('XX20000816000227',1, 'XX20000816000226', 1,NULL)
insert into #testv values ('XX20000816000228',1, 'XX20000816000227', 1,NULL)
insert into #testv values ('XX20000816000229',1, 'XX20000816000228', 1,NULL)
insert into #testv values ('XX20000816000235',1, null, null,NULL)
insert into #testv values ('XX20000816000236',1, 'XX20000816000235', 1,NULL)
/*Update the group value with referance of fresh order*/
declare @run int
set @run = ''
update #testv
set @run = Grouporder = (case when tsv_ord_ref_reorder is null then slno else @run end)
/*Search value*/
declare @search varchar(16)
set @search = 'XX20000816000228'
select * from #testv
where Grouporder in( select Grouporder from #testv where tsv_orderid = @search)
January 5, 2010 at 12:18 am
Thanks for the reply.
what I need to do is :
I have 2 tables.
the current table and one historical table.
I read the first table, populate a temporary table with the result of the 'current table' following a selection parameter tsv_orderid and we have a link with tsv_ord_ref_reorder.
To know when there is the first record, tsv_ord_ref_reorder = NULL.
in my example, record 'XX20000816000229' has a link on the table testOH.
And I need to get in my temporary table the below result :
res_orderid res_group res_ord_ref_reorder res_group_ref_reorder
---------------- --------- ------------------- ---------------------
XX20000816000226 1 NULL NULL --coming from testv
XX20000816000227 1 XX20000816000226 1 --coming from testv
XX20000816000228 1 XX20000816000227 1 --coming from testv
XX20000816000229 1 XX20000816000228 1 --coming from testv
XX20000816000233 1 XX20000816000229 1 --coming from testOH
thanks for your quick reply.
January 5, 2010 at 12:29 am
Hi,
The first table testv and the search result is more over matches by the above I mentioned, and how the second table testOH related to the tsv_orderid, this is my confusion. My assumption is testv. tsv_orderid is matches with testOH. tsv_orderid or what the way its related?
January 5, 2010 at 2:09 am
Thanks for your quick reply.
I try to explain how finding link between the both tables.
there are 2 tables.
a current table testv ( live table ) and historical table testOH.
the link to find record in table testv is : ‘tsv_orderid’ and ‘tsv_ord_ref_reorder’
link to find record in table testOH (historical table) is .
‘tsh_ord_ref_order’.
I find from ‘tsv_orderid’ and for each ‘tsv_ord_ref_reorder’
insert into testv values ('XX20000816000224',1, NULL, NULL)
insert into testv values ('XX20000816000225',1, 'XX20000816000224',1)
insert into testOH values ('XX20000816000230',1, 'XX20000816000225',1)
'XX20000816000224' is the first record as ‘tsv_ord_ref_reorder’ = NULL
to find if second record exists, select * from testv where tsv_ord_ref_reorder = tsv_orderid (‘tsv_orderid’ coming from first record).
if exists, insert into the temporary table
if not exists in table testv, read testOH to check if record exists.
IF EXISTS (SELECT * FROM testOH where tsh_ord_ref_reorder = @l_tsv_orderid) @l_tsv_orderid coming from testv table.
if exists in table testOH, insert into temporary table.
if NOT, END.
ex : table testv – ‘tsv_orderid’
'XX20000816000224' is the first record as ‘tsv_ord_ref_reorder’ = NULL
'XX20000816000225' is the second record as ‘tsv_ord_ref_reorder’ = 'XX20000816000224'
'XX20000816000230' is the third record as ‘tsh_ord_ref_reorder’ = 'XX20000816000225'
and we find something in table testOH as a link exists between table testv and testOH (historical table)
example 1 :
from a value asked by external program ex : 'XX20000816000224' I need to buid a temporary table :
and the result will be :
'XX20000816000224',1, NULL, NULLcoming from testv
'XX20000816000225',1, 'XX20000816000224',1coming from testv
'XX20000816000230',1, 'XX20000816000225',1coming from testOH
example 2 :
from a value asked by external program ex : 'XX20000816000225' I need to buid a temporary table
and the result also will be :
'XX20000816000224',1, NULL, NULLcoming from testv
'XX20000816000225',1, 'XX20000816000224',1coming from testv
'XX20000816000230',1, 'XX20000816000225',1coming from testOH
I hope there is clear enough.
thanks a lot for your help
January 5, 2010 at 2:57 am
Hi,
Ensure that the OH table having the unique values for each history and just build this as procedure than pass the search value.
create table #testOH
(
tsh_orderid char (16) null,
tsh_group smallint null,
tsh_ord_ref_reorder char (16) null,
tsh_group_ref_reorder smallint null
)
insert into #testOH values ('XX20000816000230',1, 'XX20000816000225', 1)
insert into #testOH values ('XX20000816000233',1, 'XX20000816000229', 1)
insert into #testOH values ('XX20000816000234',1, 'XX20000816000236', 1)
declare @search varchar(16)
set @search = 'XX20000816000229'
select tsv_orderid,tsv_group,tsv_ord_ref_reorder,tsv_group_ref_reorder
into #FINAL
from #testv
where Grouporder in( select Grouporder from #testv where tsv_orderid = @search)
insert into #FINAL
select * from #testOH
where tsh_ord_ref_reorder in (select tsv_orderid from #FINAL)
select * from #FINAL
January 5, 2010 at 4:10 am
Thanks for the reply but it is not what I'm waiting.
January 5, 2010 at 6:53 am
This makes use of recursive logic
Initially create a function and a procedure:
CREATE FUNCTION GetLowestOrderID (@OrderID char(16))
RETURNS char(16)
AS
BEGIN
DECLARE @LowerMostOrderID char(16)
SELECT @LowerMostOrderID = tsv_orderid FROM testsv A WHERE tsv_ord_ref_reorder = @OrderID
IF @LowerMostOrderID IS NULL
SELECT @LowerMostOrderID = tsh_orderid FROM testOH A WHERE tsh_ord_ref_reorder = @OrderID
IF @LowerMostOrderID IS NOT NULL
SELECT @LowerMostOrderID = GetLowestOrderID(@LowerMostOrderID)
ELSE
SELECT @LowerMostOrderID = @OrderID
RETURN @LowerMostOrderID
END
CREATE PROCEDURE AddRecord(@OrderID char(16))
AS
BEGIN
DECLARE @res_ord_ref_reorder char(16)
SELECT @res_ord_ref_reorder = isnull(tsv_ord_ref_reorder,'top') FROM testsv WHERE tsv_orderid = @OrderID
IF @res_ord_ref_reorder IS NOT NULL
BEGIN
SELECT @res_ord_ref_reorder = nullif(@res_ord_ref_reorder,'top')
INSERT #result SELECT @OrderID,tsv_group,@res_ord_ref_reorder,tsv_group_ref_reorder
FROM testsv WHERE tsv_orderid = @OrderID
END
ELSE
BEGIN
SELECT @res_ord_ref_reorder = tsh_ord_ref_reorder FROM testOH WHERE tsh_orderid = @OrderID
INSERT #result SELECT @OrderID,tsh_group,@res_ord_ref_reorder,tsh_group_ref_reorder
FROM testOH WHERE tsh_orderid = @OrderID
END
IF @res_ord_ref_reorder IS NOT NULL
EXEC AddRecord @res_ord_ref_reorder
RETURN
END
Then execute them as follows
create table #result
( res_orderid char (16) null,
res_group smallint null,
res_ord_ref_reorder char (16) null,
res_group_ref_reorder smallint null)
SELECT @LowestOrderID = GetLowestOrderID(@OrderID)
EXEC AddRecord @LowestOrderID
January 5, 2010 at 7:57 am
a big Thank you sunny.
it is what I need.
January 5, 2010 at 8:07 am
That's really great to hear 🙂
You're welcome 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply