Home » SQL & PL/SQL » SQL & PL/SQL » Need to compare two sqls
 Need to compare two sqls [message #402882] |
Tue, 12 May 2009 23:49  |
yd_261076
Messages: 19
Registered: February 2007
|
Junior Member |
|
|
Hi,
I need to write an sql auery which would compare two strings. These two string would be sql queries in itself. I need to check if the source query has undergone any logical change. Following is an example.
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
---- This is the source query, I need to compare
SELECT cup.strBrandcd strBrand, Wf_Com_Reports_Pkg.wf_get_area_fnc(cup.struserid) area,
cup.strteamcd team,cup.struserid struser, Om_Get_User_Name_Fnc(cup.struserid) username,
pol_nbr policynbr,workq qname, workqtype qtype, DECODE(workQType, 1,Om_Get_User_Name_Fnc(workq) || ' Personal Queue',2,
(SELECT COUNT(*)
FROM wF_rep_item_dtl wrid
WHERE wrid.strCreatedby = cup.struserid
AND wrid.nevent = 2
AND TRUNC(wrid.dtcreated) = TRUNC(SYSDATE)
AND record_type_id != 'SYSINCALLS') "Count",
action action
FROM (SELECT *
FROM (SELECT strItemKey,strCreatedBy,dtCreated,action, row_number() OVER(PARTITION BY strCreatedBy ORDER BY dtCreated desc) ranker,
workq,workqtype,pol_nbr
FROM (SELECT strItemKey,strCreatedBy,dtCreated,'Closed' action,DECODE(record_type_id,'1DEF', to_user_id,record_type_id) workq,
DECODE(record_type_id, '1DEF', 1, 2) workqtype,pol_nbr
FROM wf_rep_item_dtl
WHERE nevent = 2
AND recorD_type_id != 'SYSINCALLS'
UNION ALL
SELECT strItemKey,strUser,dtCreated,'Transfer' action,strToWorkQname,nToWorkQType,
(SELECT POL_NBR
FROM WF_REP_ITEM_DTL wrid
WHERE wrid.strItemKey = wre.strITemKey
AND nevent = 1)
FROM wf_rep_events wre
WHERE neventtype = 3))
WHERE ranker = 1) a, com_user_profile cup
WHERE a.strCreatedBy = cup.strUserId
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
SELECT (SELECT COUNT(*)
FROM wF_rep_item_dtl wrid
WHERE wrid.strCreatedby = cup.struserid
AND wrid.nevent = 2
AND TRUNC(wrid.dtcreated) = TRUNC(SYSDATE)
AND record_type_id != 'SYSINCALLS') "Count",cup.strBrandcd strBrand,Om_Get_User_Name_Fnc(cup.struserid) username, Wf_Com_Reports_Pkg.wf_get_area_fnc(cup.struserid) area,
cup.strteamcd team,cup.struserid struser, pol_nbr policynbr,workq qname, workqtype qtype, DECODE(workQType, 1,Om_Get_User_Name_Fnc(workq) || ' Personal Queue',2,
action action
FROM (SELECT *
FROM (SELECT strItemKey,strCreatedBy,dtCreated,action, row_number() OVER(PARTITION BY strCreatedBy ORDER BY dtCreated desc) ranker,
workq,workqtype,pol_nbr
FROM ( SELECT strItemKey,strUser,dtCreated,'Transfer' action,strToWorkQname,nToWorkQType,
(SELECT POL_NBR
FROM WF_REP_ITEM_DTL wrid
WHERE wrid.strItemKey = wre.strITemKey
AND nevent = 1)
FROM wf_rep_events wre
WHERE neventtype = 3
UNION ALL
SELECT strItemKey,strCreatedBy,dtCreated,'Closed' action,DECODE(record_type_id,'1DEF', to_user_id,record_type_id) workq,
DECODE(record_type_id, '1DEF', 1, 2) workqtype,pol_nbr
FROM wf_rep_item_dtl
WHERE nevent = 2
AND recorD_type_id != 'SYSINCALLS'))
WHERE ranker = 1) a, com_user_profile cup
WHERE a.strCreatedBy = cup.strUserId
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
In the second query the logic is same only sequence of columns in select clause and subquery sequence has been reshuffled.
So end result of this comparision shoud be "OK" as the queries are logically matching.Otherwise we know that there is change in the source query and the result should deisplay "Changed".
Need help to build this sql comparison logic.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need to compare two sqls [message #402894 is a reply to message #402888] |
Wed, 13 May 2009 00:19  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| yd_261076 wrote on Wed, 13 May 2009 07:09 | Attachements were stripped by the site. Hope you get this document.
|
There is a reason for it: nobody is interested in downloading possibly harmful files from total strangers.
If you'd read the Forum Guide, you would learn how easy it is to post readable code.
|
|
|
|
| Re: Need to compare two sqls [message #403168 is a reply to message #402882] |
Thu, 14 May 2009 00:43 |
yd_261076
Messages: 19
Registered: February 2007
|
Junior Member |
|
|
Hi Frank,
Sorry for all the inconvenience caused. Let me try to keep it simple.
I need to compare text of two sql queries to check if query logic has changed in anyway. Please check below queries:
select emp_name,emp_id,city,emp.dept_id
from employee emp,department dept
where emp.dept_id=dept.dept_id;
select emp_id,emp_name,emp.dept_id,city
from department dept,employee emp
where dept.dept_id=emp.dept_id;
Both the queries are same only the clauses are reordered. This should give me result like 'OK'. Only in case the query is logically modified it should show some thnig like 'Changed'.
This comparison will become more complicated if we have SELECT queries with in Select clause or From clause.
Could you help me to build logic to compare similar queries?
Please let me know in case you need more clarification.
Thanks & Regards
Yugandhara
[mod-edit: Frank removed color and corrected code-end tag]
[Updated on: Thu, 14 May 2009 00:45] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Need to compare two sqls [message #403204 is a reply to message #403169] |
Thu, 14 May 2009 03:23 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You might be able to get away with this:
Turn each of the queries into a CREATE TABLE <table_name> AS SELECT * FROM (<your query>) WHERE 1=2;
This will give you two empty tables, and you can compare the contents of USER_TAB_COLUMNS for the two tables to see if the columns that they return are the same.
Do you need to check the rows that the queries would return too?
|
|
|
|
| Re: Need to compare two sqls [message #403348 is a reply to message #402882] |
Thu, 14 May 2009 23:47 |
yd_261076
Messages: 19
Registered: February 2007
|
Junior Member |
|
|
Hi,
USER_TAB_COLUMNS will give me table names in FROM clause and their corresponding Column names.
What I intend to do is actually treat the two queries a Strings and compare them. If the clauses in the query are reordered it should not be the problem. Only if any of the where condition or table names or column names are changed it should prompt me.
Please let me know if you need more clarification.
Thanks & Regards
|
|
|
|
|
|
| Re: Need to compare two sqls [message #403354 is a reply to message #402882] |
Fri, 15 May 2009 00:37 |
yd_261076
Messages: 19
Registered: February 2007
|
Junior Member |
|
|
Hi,
I just implemented the suggestion given by JRowbottom:
Turn each of the queries into a CREATE TABLE <table_name> AS SELECT * FROM (<your query>) WHERE 1=2;
And the in 'user_tab_columns' the table_name column gave all the table names in the FROM clause and column_name column gave their corresponding column names. But this wan not what I was trying to achive...
Thanks
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 15:53:31 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
