Solved

Need to build the query for incremental data for future

Posted on 2007-11-16
9
708 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:samisyed
  • 3
  • 3
9 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 20297689
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
 

Author Comment

by:samisyed
ID: 20298115
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
 

Author Comment

by:samisyed
ID: 20298467
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 20298492
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 20298501
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20311105
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
 

Author Comment

by:samisyed
ID: 20318525
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now