How to recursively work on two tables

system · July 8, 2011, 8:47am

I have two tables where I need to goup on ID in TABLEA…Take first row of Groupped records and check in TABLEB rows …I need to check if three fields satisfies has value Y if it then I need to pick that record orelse Need to check until the end of records… If I dont find then I need to take second record of the same group of TABLEA and perform the same on Table B… I am wondering how should I proceed??

Example : TABLEA is groupped on ID…
TABLEA
GID,ID,NAME,AGE
1,66,A,20
1,69,B,22
2,70,D,30
2,73,E,39
2,77,F,38
3,80,G,40

TABLEB
ID,Field1,Field2,Field3
66,Y,Y,N
69,Y,Y,Y
70,Y,Y,Y
73,Y,Y,N
80,Y,Y,Y

Output :
GID,ID,NAME,AGE,Field1,Field2,Field3
1,69,B,22,Y,Y,Y
2,70,D,30,Y,Y,Y
3,80,G,40,Y,Y,Y

So I need to pick the rigt record from TABLEB based on TABLEA… Also in some cases I dont find all YYY in field1.Field2,Field3 then I need to take where there are more Ys…

Any idea how can I acheive it.
Thanks
RAJEEV

rajeev_mdm (BOB member since 2011-01-16)

system · July 8, 2011, 9:42am

If I have understood correctly, here grouping suggests ordering identical records & not aggregation.
Join both the tables on the ID and then filter in only those records where value is Y and output the desired columns from TABLE A & B.

kdspvp (BOB member since 2007-10-15)

system · July 8, 2011, 10:21am

Hi,
There will be a situation where there are not all fields (Field1,Field2,Field3) has Y then I need to pick other one and that too next record of TABLEA if first record of TABLEA doesnt satsfies…

So need to take one record and see if it find the rgiht records in TABLEB …If not then need to take next record from TABLEA and try to find in TABLEB…
HOw to do this requierment?
Thanks
Rajeev

rajeev_mdm (BOB member since 2011-01-16)

system · July 8, 2011, 11:17am

I think by performing simple join your problem will be resolved. Say 10 is an identical value for 3 records in Table A. On joining with Table B it will select only those records for 10 where ID is matching as well as the three fields (Field1, Field2, Field3) having value as said Y.

Select A.* from Table A, Table B
Where a.id=b.id
And (f1=Y and F2=Y and F3=Y)

It should work, or I am not getting your query properly.

kdspvp (BOB member since 2007-10-15)

system · July 8, 2011, 1:10pm

as long as your field1/2/3’s dont have different weighting, you could add the 3 ascii(‘Y’) values together, sort descending on the id+result. Do a gen_row_by_group() on your id, and pick the group sequence #1.

you could probably just concatenate the fields together and sort(d) on that. It would work in this situation, but if you have more field values (or diff weights) you would then have to do a translation and some math.

jlynn73 (BOB member since 2009-10-27)

system · July 8, 2011, 8:19pm

Hi All,

Thanks for your inputs and I think I didnt made it clear abt my requirements :

I have a Table A

ID,PRDID,NAME,INERNAL,BRANDED,PREFERRED
213,1045,AAA,Y,Y,Y
213,1528,AAA,Y,N,N
227,1524,BBB,Y,Y,Y
227,1744,BB,N,N,N
227,1967,BBB,N,YN

TABLEB
PRDID,REGION,PRICE
1045,EAST,0.0
1528,EAST,1.0
1528,NORTH,1.0
1524,WEST,0.0
1744,WEST,1.0
1744,EAST,1.0
1744,EAST,0.0
1967,WEST,0.0
1967,WEST,12.0
1967,EAST,12.0

These are the Tables: Now I have few conditions to pick from Table A… Like Choose from a group (all IDs are groupped) last three feilds are Y,
If cant find a recor with all Y then choose preferred is Y and Branded is N… Then Choose that PRDID and get the price from TABLEB…

Suppose if we take the first record of first group from TABLEA…
Condi : IF all three last fieds are Y then take that and go in to TABLEB and get price. But price is 0 hence go back to TABLEB and get second record of that group and get back to TABLEB and take the price…

Also I need to pass the different regions along with there prices for that PRDID…

SO can any one let me know how can I get this requirement?

Thanks
Rajeev.

rajeev_mdm (BOB member since 2011-01-16)

system · July 8, 2011, 8:53pm

Oops
I think you are trying to approach the problem in the buisness vision.

Now you added a lot other conditions.

Would you give Table-A, Table-B and Table-Output with all the possible conditions in it?

ganeshxp (BOB member since 2008-07-17)

system · July 8, 2011, 9:13pm

Hi,

Now i joinned the two tables as below:

ID,PRDID,NAME,INERNAL,BRANDED,PREFERRED,REGION,PRICE
213,1045,AAA,Y,Y,Y
213,1528,AAA,Y,N,N
227,1524,BBB,Y,Y,Y
227,1744,BB,N,N,N
227,1967,BBB,N,YN

TABLEB
PRDID,REGION,PRICE
1045,EAST,0.0
1528,EAST,1.0
1528,NORTH,1.0
1524,WEST,0.0
1744,WEST,1.0
1744,EAST,1.0
1744,EAST,0.0
1967,WEST,0.0
1967,WEST,12.0
1967,EAST,12.0

ID,PRDID,NAME,INERNAL,BRANDED,PREFERRED,REGION,PRICE
213,1045,AAA,Y,Y,Y,EAST,0.0
213,1528,AAA,Y,N,N,EAST,1.0
213,1528,AAA,Y,N,N,NORTH,1.0
213,1528,AAA,Y,N,N,WEST,0.0
227,1524,BBB,Y,Y,Y,WEST,0.0
227,1744,BB,N,N,N,WEST,1.0
227,1744,BB,N,N,N,EAST,1.0
227,1744,BB,N,N,N,EAST,0.0
227,1967,BBB,N,YN,WEST,0.0
227,1967,BBB,N,YN,WEST,12.0
227,1967,BBB,N,YN,EAST,12.0

Now how can I extract the right record for all regions with right price?

Regards,
Rajeev

rajeev_mdm (BOB member since 2011-01-16)

system · July 8, 2011, 10:59pm

Just add a condition where (price > 0.0)
Is that your expected set of records by the way?

I requested for a DATA_SET_1, DATA_SET_2 and a EXPECTED_RESULT_SET

I am more confused here. Wondering how a WEST Record come up for 1528

ganeshxp (BOB member since 2008-07-17)

system · July 9, 2011, 6:53am

Hi,
Please find the tables…

ID,PRDID,NAME,INERNAL,BRANDED,PREFERRED,REGION,PRICE
213,1045,AAA,Y,Y,Y
213,1528,AAA,Y,N,N
227,1524,BBB,Y,Y,Y
227,1744,BB,N,N,N
227,1967,BBB,N,YN

TABLEB
PRDID,REGION,PRICE
1045,EAST,0.0
1528,EAST,1.0
1528,NORTH,1.0
1524,WEST,0.0
1744,WEST,1.0
1744,EAST,1.0
1744,EAST,0.0
1967,WEST,0.0
1967,WEST,12.0
1967,EAST,12.0

I dont know wheather by joinning I am getting closer or making more complex…

ID,PRDID,NAME,INERNAL,BRANDED,PREFERRED,REGION,PRICE
213,1045,AAA,Y,Y,Y,EAST,0.0
213,1528,AAA,Y,N,N,EAST,1.0
213,1528,AAA,Y,N,N,NORTH,1.0
227,1524,BBB,Y,Y,Y,WEST,0.0
227,1744,BB,N,N,N,WEST,1.0
227,1744,BB,N,N,N,EAST,1.0
227,1744,BB,N,N,N,EAST,0.0
227,1967,BBB,N,YN,WEST,0.0
227,1967,BBB,N,YN,WEST,12.0
227,1967,BBB,N,YN,EAST,12.0

Please find the conditions and the tabels along with output expected…
Conditions:

if the only record returned is a Branded line (where Branded = Y) then use; otherwise ignore Branded lines
Where there is a line where all three flag fields are Y (line 1 in the example below), match this line first in the table B,
If first selected record from the group has no price value in the TABLEB then take second record from the group of tableA and get the price from the TABLEB. If you dont find price for all fields having Y and Branded having Y then take the record which has all NNN and try to get the price from TABLEB

TABLEA
PRDID,ID,Name,Branded,Prefered,Internal
20044,1001191,AAA,Y,Y,Y
20044,1010246,AAA,Y,N.N
20287, 1005666,NNN,Y,N,N
20287, 1012251,NNN,Y,N,N
20287, 1012821,NNN,N,N,N
20289, 1012824,MMM,Y,N,N

TABLEB
ID,REGION,PRICE,QUANTITY
1001191,W,1.8,28
1001191,E,1.9,28
1010246,NULL,0,0
1005666,W,0.0
1012251,S,2.3,1
1012251,E,2.3,1
1012821,W,0,0
1012824,S,1.2,1

OUTPUT
PRDID,ID,NAME,REGION,PRICE,QUANTITY
20044,1001191,AAA,W,1.8,2.8
20044,1001191,AAA,E,1.9,28
20287, 1012251,NNN,S,2.3,1
20287, 1012251,NNN,E,2.3,1
20289, 1012824,MMM,S,1.2,1

At the end I need to create XML…

Please adive how to achieve above…
Thanks
Rajeev

rajeev_mdm (BOB member since 2011-01-16)

system · July 11, 2011, 1:32am

Hi

Basically I guess you have a rank value based on the Internal/Branded/Preferred

So before you begin, you need to assign a virtual rank to your data.
Like,
1 = Y,Y,Y
2 = ,Y,
3 = Y,,
4 = ,,Y

On TABLE_B it is pretty simple straight forward. TABLE_B.AMOUNT !=0

So join
SELECT TABLE_A., TABLE_B.
FROM TABLE_A A, TABLE_B B
WHERE
(A.ID=B.ID)
AND (B.AMOUNT !=0)
–AND(
– (A.INTERNAL = ‘Y’ AND A.BRANDED = ‘Y’ AND A.PREFERRED = ‘Y’)
– OR
– (A.BRANDED = ‘Y’)
– OR
– (A.INTERNAL = ‘Y’)
– OR
– (A.PREFERRED = ‘Y’)
– )

I initially wrote the commented lines as conditions. But when I figured out the record id - 1012251 in your output, I figured that the rank exist on my commented conditions

So based on the rank my query may not work. But this should be solved in multiple levels of query and not a direct query

ganeshxp (BOB member since 2008-07-17)