Home » SQL & PL/SQL » SQL & PL/SQL » Help on a query
| Help on a query [message #624635] |
Wed, 24 September 2014 06:53  |
 |
lovelysethii@gmail.com
Messages: 5
Registered: September 2014
Location: delhi
|
Junior Member |
|
|
Hi Everyone,
i have a requirement where i have to fetch data on weekly basis and provides the data to the business which has been changed since the last week run. i have select statement already ready for this request which is as follows:
SELECT decode(um.status,'Active','N','D') transaction_type, um.status status,
um.createddate create_date,um.modifieddate modified_date,C.XCEN AS Establishment_ID,
UM.FIRSTNAME AS First_Name, UM.LASTNAME AS Last_Name, UM.USERLOGINID AS Employee_Id,
Replace(UM.PHONENUMBER,'+1','') AS Phone, Replace(CELLPHONE,'+1','') AS Intelnet,
UM.EMAILADDR AS Email
FROM SRP.SUPP_SOD_USER_MV UM RIGHT JOIN SRP.SUPP_2400_CONFIG C ON
UM.BUSINESSUNITLEVEL1 = C.CONFIG_TERRITORY
WHERE (((C.CONFIG_TERRITORY) Like '187A%' Or
(C.CONFIG_TERRITORY) Like '5874%'))
first of all i have created temporary table srp.consumeable_data_load2 to hold the record initially and in the next week it will be compared with the above select statement data(latest data), and only those records which are changed as part of Establishment_ID, transaction_type will only be sent to the business and my temporary table will be reloaded after sending this data to the business every week so that it can be compared next week..hope my question is clear. to achieve this i have created a select statement which is as follows:
SELECT decode(um.status,'Active','N','D') transaction_type, um.status status,
um.createddate create_date,um.modifieddate modified_date,C.XCEN AS Establishment_ID,
UM.FIRSTNAME AS First_Name, UM.LASTNAME AS Last_Name, UM.USERLOGINID AS Employee_Id,
Replace(UM.PHONENUMBER,'+1','') AS Phone, Replace(CELLPHONE,'+1','') AS Intelnet,
UM.EMAILADDR AS Email
FROM SRP.SUPP_SOD_USER_MV UM RIGHT JOIN SRP.SUPP_2400_CONFIG C ON
UM.BUSINESSUNITLEVEL1 = C.CONFIG_TERRITORY
WHERE (((C.CONFIG_TERRITORY) Like '187A%' Or
(C.CONFIG_TERRITORY) Like '5874%'))
and (C.XCEN,decode(um.status,'Active','N','D')) in (
SELECT C.XCEN AS Establishment_ID,decode(um.status,'Active','N','D') transaction_type
FROM SRP.SUPP_SOD_USER_MV UM RIGHT JOIN SRP.SUPP_2400_CONFIG C ON
UM.BUSINESSUNITLEVEL1 = C.CONFIG_TERRITORY
WHERE (((C.CONFIG_TERRITORY) Like '187A%' Or
(C.CONFIG_TERRITORY) Like '5874%')))
minus
select Establishment_ID,transaction_type from srp.consumeable_data_load2;
first of all minus clause will extract Establishment_ID and transaction_type which are not present in temporary table and on the basis of these 2 columns i am fetching the whole data. Since i don't want to use the concept of temporary table here so could you please help me with the alternate way of achieiving this functionality..
|
|
|
|
| Re: Help on a query [message #624639 is a reply to message #624635] |
Wed, 24 September 2014 07:21  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:Since i don't want to use the concept of temporary table here so could you please help me with the alternate way of achieiving this functionality..
As you have nothing that let us know what is a new or old row (no date, no flag...), I don't see any other way to do it than to have somewhere what rows have already been reported.
[Updated on: Wed, 24 September 2014 07:22] Report message to a moderator |
|
|
|
| Re: Help on a query [message #624642 is a reply to message #624639] |
Wed, 24 September 2014 07:31 |
|
lovelysethii@gmail.com
Messages: 5
Registered: September 2014
Location: delhi
|
Junior Member |
|
|
Hi Michel,
since our process revolves on 2 fields which are in the select query itself i.e. decode(um.status,'Active','N','D') transaction_type and C.XCEN AS Establishment_ID. we have to capture records from the select statement which are new and those records where transaction type for any Establishment ID has changed since last run.
For example, if on week 1 you have Jacob Woods active for establishment 008281792, then on week 2 you still have Jacob Woods as active in the Supplies system for establishment 008281792, then you would not want to send the same record again with the 'N' transaction type as you have already previously sent the 'N' transaction for Jacob Woods and nothing has changed with regard to Jacob Woods and his support for establishment 008281792.
Similarly, if on week 2 you send the 'D' transaction for Jacob Woods because he has become inactive in the Supplies system for establishment 008281792, then on week 3 you still have Jacob Woods as inactive for establishment 008281792, then you would not want to send the same record again with the 'D' transaction type as you have already previously sent the 'D' transaction for Jacob Woods for establishment 008281792.
hope it makes my request more clear to you now.
|
|
|
|
|
|
|
|
| Re: Help on a query [message #624654 is a reply to message #624645] |
Wed, 24 September 2014 08:06 |
|
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:to check whether they are the new/changed records i have used temporary table
This is what I said, if you have nothing in the data that can give the answer to "old/new" then you have no other way than to store the old in some way in other place.
I'd go for a trigger on the source table(s) that will fill another table with the rowid and pk of the new changes and that your report batch will empty when it ends.
[Updated on: Thu, 25 September 2014 03:07] Report message to a moderator |
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 22:45:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
