Need to build the query for incremental data for future

Hello Experts...

Here i want to write a query for my business requirement where...
Suppose for this week i have 10 records after executing a query say.

select * from mtl_system_items_b where organization_id=102;

Later i put these 10 records in excel format so that this data may be exported into other system.

For the next week... some more say 20 records have been added after executing the same previous query...

So , I need to develope one more query which will give the incremental data i.e. any update/additions in ITEMS records that happen in future.

Please help me out in building this query.

Thanks & Regards,
Sami.
samisyedAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
What does your table mtl_system_items_b look like?  Does it have a timestamp on it?
Or primary key?


If it has a timestamp to record the date the record was created you can query using that

select * from mtl_system_items_b where organization_id=102
and create_date > v_incremental_date

set the v_incremental_date variable to whatever point in time you're interested in.

If the table does not have a timestamp you can create one and use a trigger to populate it.
You can also use a primary key and a "sister table" to go along with your process.

When you export your data to excel you add the primary keys you processed to that sister table along with the date you processed them.  Then, when you go to run again, you simply query all data that does NOT have a primary key in the sister table.
0
 
samisyedAuthor Commented:
Hi sdstuber,
Thanks for prompt solution.
We have the timestamp in the tables from which we are trying to fetch the data.
Can you please let us know can process be simplified further as it can be made simple for the client to go ahead.

Thanks & Regards,
Sami.
0
 
samisyedAuthor Commented:
Hi Sdstuber,

We have timestamp in the tables which are being used in the query.
But I dont think this timestamp is being used when the query is run, because we need to consider the date on which this query is run ( and not the date on which the items creation date).
Thats why I suggest is not to use the timestamp which is provided in the tables used.

Can you please provided the way so that the incremental data can be obtained.

Thanks & Regards,
Sami S.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
sdstuberCommented:
you need to store the date of the previous run of the query.

that could be your sister table.  It could be a one column table.  "Query_Run_date"

then your incremental data would be

select * from select * from mtl_system_items_b where organization_id=102
and create_date > (select max(query_run_date) from sister_table)
0
 
sdstuberCommented:
I think you have to combine both the timestamp of the record along with the timestamp of the query run.  

If not the timestamp of the record, then some other criteria that will separate pre-query data from post-query data.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
The query will help you to extract which are created after a specified date.

But if you are intrested in extracting those details which also might have got changed which you have already in excel, then your query should use something like the below :

1)
select * from select * from mtl_system_items_b where organization_id=102
and ( create_date > (select max(query_run_date) from sister_table)
or last_update_date > ( select max(query_run_date) from sister_table ) )

or

2)
select * from select * from mtl_system_items_b
where organization_id=102
and ( create_date > to_date('31-jan-2007 18:30:00','dd-mon-yyyy hh24:mi:ss')
or last_update_date > to_date('31-jan-2007 18:30:00','dd-mon-yyyy hh24:mi:ss') )

instead of the date 31-jan-2007 18:30:00 in the 2) above, put the end time of the last extraction. With this method, no need of another table ( to store the last extracted date ).

Also i would suggest that you should always use some order by in a query which extract some details to excel like application because they will be in order ( to compare and also to verify it will be easy if you have an order by clause though it will take more time for the query to get
executed )

some like...

select * from select * from mtl_system_items_b
where organization_id=102
and ( create_date > to_date('31-jan-2007 18:30:00','dd-mon-yyyy hh24:mi:ss')
or last_update_date > to_date('31-jan-2007 18:30:00','dd-mon-yyyy hh24:mi:ss') )
order by create_date, last_update_date, inventory_item_id

Thanks
0
 
samisyedAuthor Commented:
Hi Experts,

Thanks for your update.

This is to update... as the new requirement came where in which....the complex query is having a collection of tables being used.

Now once the output is being taken into the excel sheet, and again when its run again next week... if the output is having some records updated then only those updated records need to be produced in the excel sheet for that week only.

Here i'm sending the query :
-------------------------------------------------------------------------------------------------------------------------
SELECT msip.segment1 parent_item  --1st column  
--      ,pbe.sort_order    
        ,msip.description parent_description -- Second column abdul.      
      ,apps.pwr_global_pkg.pwr_get_max_rev(pbe.assembly_item_id , mp.organization_id) parent_item_rev  -- 3rd column
      ,msic.segment1 child_item -- 4 th column
      ,msic.description child_description --5th column
      ,apps.pwr_global_pkg.pwr_get_max_rev(pbe.component_item_id, mp.organization_id) child_item_rev  --6th column
      ,apps.pwr_global_pkg.pwr_get_ref_desig(bic.component_sequence_id) Refrence_desig  -- 7th column
      ,bic.effectivity_date effectivity_date_from  -- 8th column
      ,bic.disable_date  effectivity_date_to  -- 9 th column
      ,DECODE(msic.planning_make_buy_code,2,'BUY','MAKE') comp_make_buy  --10 column
FROM pwrbomexpl.pwr_bom_expl pbe
  ,apps.mtl_system_items_b msia
  ,apps.mtl_system_items_b msic
  ,apps.mtl_system_items_b msip
  ,apps.mtl_parameters mp
  ,apps.bom_inventory_components bic
  ,(select inventory_item_id , category_concat_segs from apps.mtl_item_categories_v
        where category_set_id = 4 and organization_id = 86) mica
  ,(select inventory_item_id , category_concat_segs from apps.mtl_item_categories_v
        where category_set_id = 4 and organization_id = 86) micc
  ,(select inventory_item_id , category_concat_segs from apps.mtl_item_categories_v
        where category_set_id = 4 and organization_id = 86) micp
WHERE msia.inventory_item_id = pbe.top_item_id         
  AND msic.organization_id = mp.organization_id      
  AND msic.inventory_item_id = pbe.component_item_id
  AND msic.organization_id = msia.organization_id    
  AND msip.inventory_item_id(+) = pbe.assembly_item_id
  AND msip.organization_id (+)= 86         
  AND pbe.bill_sequence_id = bic.bill_sequence_id  
  AND pbe.component_item_id = bic.component_item_id
  AND msia.segment1 ='SPACUST-01'--'SP612'--'SPASUNM-01' --'SPAMCDT-05'---- ----'--'-'--
  AND mp.organization_code ='GLO'      
  AND msia.inventory_item_id = mica.inventory_item_id(+)
  AND msic.inventory_item_id = micc.inventory_item_id(+)
  AND msip.inventory_item_id = micp.inventory_item_id(+)  
 -- and msip.segment1='417-104254-G'--'SPASUNM-01'
 and bic.disable_date is NULL
ORDER BY decode(length(sort_order),4,1,8,2,12,3,16,4,20,5,24,6),sort_order;
-------------------------------------------------------------------------------------------------------------------------

What I feel like to use the Materialized View or any normal view( based on the above query ) where in which i need to add a date column to know when this last query ran....

Please advice and suggest how do we go ahead if not this way of using views.

Thanks & Regards,
Sami S.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.