April 4, 2008 at 11:02 am
need help in writing DYNAMIC query
select C1,C2,C3 from VMR , B_VMR where VMR.KEY1 = B_VMR.KEYI, VMR_KEY2=B_VMR2
AND VMR_REF.KEYFIELD1 <> VMR_REF.KEYFIELD1 OR VMR_REF.KEYFIELD2 <> VMR_REF.KEYFILED2
--------------------
where VMR, B_VMR are two tables where the columns need to be compared..........(they change dynamically for every iteration takes place and are present in two tables)
VMR.KEY1(columns in 1st table),B_KEY1(second table) are the columns ,they are present in two other tables.................I GOT THEM into temp tables @VMRKEY,@B_VMRKEY
VME_REF.KEYFIELD1,VMR_KEYFIELDS2 are column names and also we get these columns from XYZ table( These are columns present in BVMR and VMR tables.....both have same columns)we need to compare for changes
C1,C2,C3 are also columns need to display and shd get from XYZ TABLE
XYZ TABLE LOOKS LIKE.............
Objname column name
VMR --- TABLE NAME
VMR.BVMR_Ref.LOEVM ---VMR_REF.KEYFIELD1
VMR.BVMR_Ref.TELF1 ---VMR_REF.KEYFIELD2
VMR.BVMR_Ref.XCPDK ---VMR_REF.KEYFIELD3 These columns LOEVM,TELF1,XCPDK need to be
VMR.LIFNR ------- - C1 extracted from table and use for comparing
VMR.PSON1 ---------C2
VMR.TELF1 ---------C3
B_VMR --SECOND TABLE NAME
we need to get these columns from this table and as we have related tablenames in temp table wee need to compare...........this shd be done for every iteration............the columns may change in XYZ table for every iteration as we have diff tables we have diff columns..............we need to extract _ref and C1,C2.... Dynamically and place inthe query for comparision............
hope i provided sufficient information
Thanks
April 4, 2008 at 11:24 am
I got completely lost when I tried to read this.
Are you saying that you have two tables, that they have the same columns as each other, but that the data stored in the colums is different in different cases?
Like in one case, a column might have sales data in it, but in another case, the same column might have inventory data?
I think the table definitions and some sample data will help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2008 at 11:46 am
thanks for ur reply...
we have two tables VMR AND B_VMR
B_VMR is original table
VMR is updated table
and we need to compare these two tables( not particular after every iteration tables change)
and the columns need to compare are from XYZ table.........(table change resp)
we need to get the columns C1,C2,C3 and _ref column names......dynamically from XYZ table.....
i did not understand how to extract column names from XYZ as we have them like _ref.LIFR some thing like that.......we need to get LIFR (column name) as many as we have in table and shd place in above query place as fields for comparing................and also columns C1,C2......from XYX the number of columns change rep to tables
April 4, 2008 at 12:26 pm
I don't know what you mean by "after every iteration table changes".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2008 at 12:34 pm
the comparison of columns is not for particular tables.................
for every ObjectID it need to get the columns and compare...........
if we have ObjectID like 1,2,3
it loops 3 times and every time it need to get table and columns resp and compare them
for ObjectID = 1 it will get two tables and resp columns in XYZ and compare
then ODjectID =2 it will get some other tables ................columns cahge in XYZ
i wrote a cursor for this to get the tables to be compared each time and for XYZ table for every OBject ID
for the every iteration with resp to ObjectID it will have two tables to be compared and a XYZ table with the resp columns.............we need to extract the columns as i said C1,c2 from XYZ .....as many as we have in that particular XYZ table for that iteration(OBjectID=1)
April 4, 2008 at 1:45 pm
I guess I'd have to see the database to figure out what you've got. I'm not following you on this. I don't think I'll be able to help. Hopefully someone else on this page will be able to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply