February 2, 2012 at 10:48 am
tbl_indicator
grp_nbr, sect_nbr, indicat
001234 100 p
002345 101 s
tbl_group
grp_id, grp_nbr, sect_nbr, indicat
333 001987 100 a
555 001987 100 p
444 002987 101 s
222 02987 101 y
Here (in tbl_group) grp_id is Primary Key
tbl_order
order_id, grp_id
5000 333
5001 555
5002 555
5003 555
5004 444
5005 444
5006 222
Here (in tbl_order) grp_id is a Foreign Key to grp_id in tbl_group.
In table tbl_indiactor, for one set of grp_nbr and sect_nbr there is an indicat, for the same set of grp_nbr and sect_nbr there is a correct indicat(555,1, 100, p) and a junk indicat(333, 1, 100, a) in table tbl_group, but both these grp_id s(333, 555) are present in table tbl_orders.
and one more thing here is that the junk data(indicat) in group table (222, 02987, 101, y) the grp_nbr has one character length less than the grp_nbr in tbl_indicat. it should use something 'LIKE' operator how can we handle this??
Now i need to update tbl_order table in such a way that the junk grp_id s should be replaced with correct grp_id s
The output should like:
tbl_orders
order_id, grp_id
5000 555
5001 555
5002 555
5003 555
5004 444
5005 444
5006 444
February 2, 2012 at 10:54 am
First, please don't cross post to other forums. People scan all places.
Second, please give DDL and tables so someone can test and provide help. Include data as well:
create table tbl_indicator
( grp_nbr varchar(10)
, sect_nbr varchar(10)
, indicat varchar(10)
)
insert tbl_indicator select '001234', '100', 'p'
insert tbl_indicator select '002345', '101', 's'
In terms of your question, you need to define what is valid and what is junk with rules, not just stating that a row is junk or a row is valid. We don't understand the data or meaning, so you need to explain this clearly. State the relationships, and state what rules you have for determining whether a row is to be returned or not.
Also, state the question. What are you trying to return. You can show results, and please use the code-other formatting for results, but we need to understand what you are trying to return. Don't ask someone to dig in and try to decode your results.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 2, 2012 at 11:04 am
Did the solution I posted for you a couple of days ago not work?
February 2, 2012 at 11:16 am
no..
February 2, 2012 at 11:17 am
create table tbl_indicator
(
grp_nbr varchar(30),
sect_nbr varchar(30),
indicat varchar(30)
)
insert into tbl_indicator select '001234','100','p'
insert into tbl_indicator select '002345','101','s'
create table tbl_group
(
grp_id int primary key,
grp_nbr varchar(30),
sect_nbr varchar(30),
indicat varchar(30)
)
insert into tbl_group select '333','1','100','a'
insert into tbl_group select '555','1','100','p'
insert into tbl_group select '444','2','101','s'
insert into tbl_group select '222','2','101','y'
create table cr.tbl_order
(
order_id int,
grp_id int FOREIGN KEY REFERENCES tbl_group (grp_id)
)
so if i dont find any indicat for a set of grp_nbr and sect_nbr which exists in tbl_grp that which doesnt exist in tbl_indivator then that record is the junk record in tbl_group table..
February 2, 2012 at 11:18 am
Well, as Steve mentioned, can you be a little more clear on your problem? Why didn't my solution work? It did give back the resulting table you were asking for.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply