Home » SQL & PL/SQL » SQL & PL/SQL » Need to compare two sqls
icon3.gif  Need to compare two sqls [message #402882] Tue, 12 May 2009 23:49 Go to next message
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 #402883 is a reply to message #402882] Tue, 12 May 2009 23:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Your post hurts my eyes & is borderline unreadable.

Please use documented <code tags>
Re: Need to compare two sqls [message #402884 is a reply to message #402882] Wed, 13 May 2009 00:02 Go to previous messageGo to next message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
Please see the attachements. Hope this will help.

Thanks
Re: Need to compare two sqls [message #402885 is a reply to message #402882] Wed, 13 May 2009 00:03 Go to previous messageGo to next message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
2nd query ..
Re: Need to compare two sqls [message #402888 is a reply to message #402882] Wed, 13 May 2009 00:09 Go to previous messageGo to next message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
Attachements were stripped by the site. Hope you get this document.
  • Attachment: Queries.zip
    (Size: 47.51KB, Downloaded 102 times)
Re: Need to compare two sqls [message #402894 is a reply to message #402888] Wed, 13 May 2009 00:19 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #403169 is a reply to message #403168] Thu, 14 May 2009 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You would need to build a parser for that. No easy way out.
Re: Need to compare two sqls [message #403204 is a reply to message #403169] Thu, 14 May 2009 03:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #403351 is a reply to message #402882] Fri, 15 May 2009 00:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>USER_TAB_COLUMNS will give me table names in FROM clause
NO
USER_TAB_COLUMNS has nothing to do with "FROM clause"
Re: Need to compare two sqls [message #403354 is a reply to message #402882] Fri, 15 May 2009 00:37 Go to previous message
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
Previous Topic: need help getting primary key from table
Next Topic: How to get sql query output
Goto Forum:
  


Current Time: Fri Dec 09 17:45:38 CST 2016

Total time taken to generate the page: 0.30052 seconds