Home » SQL & PL/SQL » SQL & PL/SQL » Get the records with alternate workflow value (Oracle 10.2.0.1.0)
Get the records with alternate workflow value [message #419976] Wed, 26 August 2009 05:48 Go to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hi ,

I have a dataset. It looks like the data given in teh query .


SELECT 1        party_id, 
       2        srvc_id, 
       'abc'    srvc_txt, 
       1        auth_lvl, 
       1        approvers, 
       'ALT_WF' alt_wf_nm 
FROM   dual 
UNION 
SELECT 1            party_id, 
       2            srvc_id, 
       'abc'        srvc_txt, 
       1            auth_lvl, 
       1            approvers, 
       'default_WF' alt_wf_nm 
FROM   dual 
UNION 
SELECT 1            party_id, 
       2            srvc_id, 
       'abc'        srvc_txt, 
       2            auth_lvl, 
       1            approvers, 
       'default_WF' alt_wf_nm 
FROM   dual 
UNION 
SELECT 1        party_id, 
       2        srvc_id, 
       'abc'    srvc_txt, 
       2        auth_lvl, 
       2        approvers, 
       'ALT_WF' alt_wf_nm 
FROM   dual 
UNION 
SELECT 1        party_id, 
       2        srvc_id, 
       'abc'    srvc_txt, 
       3        auth_lvl, 
       3        approvers, 
       'ALT_WF' alt_wf_nm 
FROM   dual 
UNION ALL 
SELECT 2        party_id, 
       2        srvc_id, 
       'abc'    srvc_txt, 
       1        auth_lvl, 
       1        approvers, 
       'XYZ_WF' alt_wf_nm 
FROM   dual 
UNION 
SELECT 2            party_id, 
       2            srvc_id, 
       'abc'        srvc_txt, 
       1            auth_lvl, 
       1            approvers, 
       'default_WF' alt_wf_nm 
FROM   dual 
UNION 
SELECT 2            party_id, 
       2            srvc_id, 
       'abc'        srvc_txt, 
       2            auth_lvl, 
       1            approvers, 
       'default_WF' alt_wf_nm 
FROM   dual 
UNION 
SELECT 2        party_id, 
       2        srvc_id, 
       'abc'    srvc_txt, 
       2        auth_lvl, 
       2        approvers, 
       'XYZ_WF' alt_wf_nm 
FROM   dual 
UNION 
SELECT 2        party_id, 
       2        srvc_id, 
       'abc'    srvc_txt, 
       3        auth_lvl, 
       3        approvers, 
       'XYZ_WF' alt_wf_nm 
FROM   dual 
UNION ALL 
SELECT 3            party_id, 
       2            srvc_id, 
       'abc'        srvc_txt, 
       1            auth_lvl, 
       1            approvers, 
       'default_WF' alt_wf_nm 
FROM   dual 
UNION 
SELECT 3            party_id, 
       2            srvc_id, 
       'abc'        srvc_txt, 
       2            auth_lvl, 
       1            approvers, 
       'default_WF' alt_wf_nm 
FROM   dual 
ORDER BY party_id, 
         srvc_id, 
         srvc_txt, 
         auth_lvl, 
         approvers 


based on the above data set ,
for given party and service I can have more than one set of work flows .
e.g. party 1 has two work flows
default and xyz
i want to show the records for the work flow other than default in case party has got more than one work flow .
Else records with default work flow .

is it possible in a single query ?

Thanks for your help .

regards
Prajakta
Re: Get the records with alternate workflow value [message #419979 is a reply to message #419976] Wed, 26 August 2009 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
for given party and service I can have more than one set of work flows .

Which is which column?

Quote:
.g. party 1 has two work flows
default and xyz

I don't see and 'xyz' nor 'default' in your data.

Please be precise in your explainations.

What is the expected result from your test case? Why?

Regards
Michel
Re: Get the records with alternate workflow value [message #419984 is a reply to message #419979] Wed, 26 August 2009 06:02 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hi Michel ,

The Dataset created has follwing columns
party_id,srvc_id,srvc_txt, auth_lvl, approvers, alt_wf_nm

Given party_id,srvc_id can have more than one alt_wf_nm i.e. workflows .
e.g.
party_id 2 has 'XYZ_WF' and 'default_WF'
'default_WF' is used for default workflow
'XYZ_WF' is used for alternate workflow (this value would be different for different party_id and srvc_ids)

If I have more than one workflows i.e. alt_wf_nm for given party_id and srvc_id then I should be showing records related to
alternate workflow .
In case alternate workflow is not present then I sjould be showing records related to default workflow along with above mentioned columns
i.e. party_id,srvc_id,srvc_txt, auth_lvl, approvers,

Thanks in Advance
Prajakta
Re: Get the records with alternate workflow value [message #419993 is a reply to message #419984] Wed, 26 August 2009 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 26 August 2009 12:51
...
What is the expected result from your test case? Why?

Regards
Michel


Re: Get the records with alternate workflow value [message #420034 is a reply to message #419984] Wed, 26 August 2009 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25039
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/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results
Post detailed explanation how & why test data gets transformed into expected/desired results.
Re: Get the records with alternate workflow value [message #420039 is a reply to message #420034] Wed, 26 August 2009 12:31 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member


CREATE TABLE party_authlevel ( 
  party_id  NUMBER, 
  srvc_id   NUMBER, 
  srvc_text VARCHAR2(100), 
  auth_lvl  NUMBER, 
  approvers NUMBER, 
  alt_wf_nm VARCHAR2(100)) ;

INSERT INTO party_authlevel 
(SELECT 1        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        1        auth_lvl, 
        1        approvers, 
        'ALT_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 1            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        1            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 1            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        2            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 1        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        2        auth_lvl, 
        2        approvers, 
        'ALT_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 1        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        3        auth_lvl, 
        3        approvers, 
        'ALT_WF' alt_wf_nm 
 FROM   dual 
 UNION ALL 
 SELECT 2        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        1        auth_lvl, 
        1        approvers, 
        'XYZ_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 2            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        1            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 2            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        2            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 2        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        2        auth_lvl, 
        2        approvers, 
        'XYZ_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 2        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        3        auth_lvl, 
        3        approvers, 
        'XYZ_WF' alt_wf_nm 
 FROM   dual 
 UNION ALL 
 SELECT 3            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        1            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 3            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        2            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 ORDER BY party_id, 
          srvc_id, 
          srvc_txt, 
          auth_lvl, 
          approvers) ;


COMMIT ;




I Want to get the result with alternate name in case of party id 1 it should give me rows for
'ALT_wF'
In case of party id 3 it should give result where alt_wf_nm is 'default_WF'

I have tried following query .
Please let me know whether its correct /
is there any other way to achieve the output in single query.

( At this moment , I don't have oracle /any other db ..I am putting the query..
but have not yet tried it on sql )


SELECT party_id, 
       srvc_id, 
       srvc_txt, 
       auth_lvl, 
       approvers, 
       alt_wf_nm 
FROM   (SELECT party_id, 
               srvc_id, 
               srvc_txt, 
               auth_lvl, 
               approvers, 
               alt_wf_nm, 
               Dense_rank() 
                 OVER(PARTITION BY party_id,srvc_id ORDER BY CASE alt_wf_nm 
                   WHEN 'default_WF' THEN 2 
                   ELSE 1 
                 END) rk 
        FROM   party_authlevel) 
WHERE  rk = 1; 



Thanks for the patience.

Thanks & Regrads.
Prajakta
Re: Get the records with alternate workflow value [message #420040 is a reply to message #420039] Wed, 26 August 2009 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 26 August 2009 14:04
Michel Cadot wrote on Wed, 26 August 2009 12:51
...
What is the expected result from your test case? Why?

Regards
Michel




Is this so difficult to answer this question?
Re: Get the records with alternate workflow value [message #420045 is a reply to message #420039] Wed, 26 August 2009 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
more invalid SQL

81   UNION ALL
 82   SELECT 3		  party_id,
 83  	     2		  srvc_id,
 84  	     'abc'	  srvc_txt,
 85  	     1		  auth_lvl,
 86  	     1		  approvers,
 87  	     'default_WF' alt_wf_nm
 88   FROM	dual
 89   UNION
 90   SELECT 3		  party_id,
 91  	     2		  srvc_id,
 92  	     'abc'	  srvc_txt,
 93  	     2		  auth_lvl,
 94  	     1		  approvers,
 95  	     'default_WF' alt_wf_nm
 96   FROM	dual
 97   ORDER BY party_id,
 98  	       srvc_id,
 99  	       srvc_txt,
100  	       auth_lvl,
101  	       approvers) ;
 ORDER BY party_id,
 *
ERROR at line 97:
ORA-00907: missing right parenthesis

Re: Get the records with alternate workflow value [message #420046 is a reply to message #420040] Wed, 26 August 2009 13:22 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hi Michel,

I thought I answered the question

"I Want to get the result with alternate name

in case of party id 1 it should give me rows for
'ALT_wF'
In case of party id 3 it should give rows where alt_wf_nm is 'default_WF'"

for Why:
If there is workflow other than default then i want to show the records with alternate workflow
else records with default workflow should be shown

Regards
Prajakta K
Re: Get the records with alternate workflow value [message #420047 is a reply to message #420046] Wed, 26 August 2009 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I thought I answered the question

No you did not.
The answer is the list of rows and columns that should be the result of your requirements for the data of your test case and why each one of THESE rows.

Regards
Michel
Re: Get the records with alternate workflow value [message #420048 is a reply to message #419976] Wed, 26 August 2009 13:41 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>based on the above data set ,
>for given party and service I can have more than one set of work flows .
>e.g. party 1 has two work flows
>default and xyz
>i want to show the records for the work flow other than default in case party has got more than one work flow .
>Else records with default work flow .
>
>is it possible in a single query ?

You start out stating above.
Please define what you mean by "work flows", by words & examples.
What is meant by " more than one work flow "?

  PARTY_ID    SRVC_ID SRVC_TEXT 			 AUTH_LVL  APPROVERS ALT_WF_NM
---------- ---------- -------------------------------- ---------- ---------- --------------------------------
	 1	    2 abc					1	   1 ALT_WF
	 1	    2 abc					1	   1 default_WF
	 1	    2 abc					2	   1 default_WF
	 1	    2 abc					2	   2 ALT_WF
	 1	    2 abc					3	   3 ALT_WF
	 2	    2 abc					1	   1 XYZ_WF
	 2	    2 abc					1	   1 default_WF
	 2	    2 abc					2	   1 default_WF
	 2	    2 abc					2	   2 XYZ_WF
	 2	    2 abc					3	   3 XYZ_WF
	 3	    2 abc					1	   1 default_WF
	 3	    2 abc					2	   1 default_WF
Re: Get the records with alternate workflow value [message #420078 is a reply to message #420048] Wed, 26 August 2009 23:37 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hi Michel/Blackswan ,

Thanks for looking into my query.
When i posted the syntax yesteday ..was not having db ..hence the syntax errors .
Please find the correct syntax as given

CREATE TABLE party_authlevel ( 
  party_id  NUMBER, 
  srvc_id   NUMBER, 
  srvc_text VARCHAR2(100), 
  auth_lvl  NUMBER, 
  approvers NUMBER, 
  alt_wf_nm VARCHAR2(100)) ;

INSERT INTO party_authlevel 
(SELECT 1        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        1        auth_lvl, 
        1        approvers, 
        'ALT_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 1            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        1            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 1            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        2            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 1        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        2        auth_lvl, 
        2        approvers, 
        'ALT_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 1        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        3        auth_lvl, 
        3        approvers, 
        'ALT_WF' alt_wf_nm 
 FROM   dual 
 UNION ALL 
 SELECT 2        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        1        auth_lvl, 
        1        approvers, 
        'XYZ_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 2            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        1            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 2            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        2            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 2        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        2        auth_lvl, 
        2        approvers, 
        'XYZ_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 2        party_id, 
        2        srvc_id, 
        'abc'    srvc_txt, 
        3        auth_lvl, 
        3        approvers, 
        'XYZ_WF' alt_wf_nm 
 FROM   dual 
 UNION ALL 
 SELECT 3            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        1            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual 
 UNION 
 SELECT 3            party_id, 
        2            srvc_id, 
        'abc'        srvc_txt, 
        2            auth_lvl, 
        1            approvers, 
        'default_WF' alt_wf_nm 
 FROM   dual) ;


COMMIT ;



I have tried a bit and have resolved the query as



SELECT party_id, 
       srvc_id, 
       srvc_text, 
       auth_lvl, 
       approvers, 
       alt_wf_nm 
FROM   (SELECT party_id, 
               srvc_id, 
               srvc_text, 
               auth_lvl, 
               approvers, 
               alt_wf_nm, 
               Dense_rank() 
                 OVER(PARTITION BY party_id,srvc_id ORDER BY CASE alt_wf_nm 
                   WHEN 'default_WF' THEN 2 
                   ELSE 1 
                 END) rk 
        FROM   party_authlevel) 
WHERE  rk = 1; 




Thanks for your time .

Regards
Prajakta.

[Updated on: Wed, 26 August 2009 23:40]

Report message to a moderator

Re: Get the records with alternate workflow value [message #420080 is a reply to message #419976] Wed, 26 August 2009 23:41 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
SELECT party_id, 
       srvc_id, 
       srvc_text, 
       auth_lvl, 
       approvers, 
       alt_wf_nm 
FROM   (SELECT party_id, 
               srvc_id, 
               srvc_text, 
               auth_lvl, 
               approvers, 
               alt_wf_nm, 
               Dense_rank() 
                 OVER(PARTITION BY party_id,srvc_id ORDER BY CASE alt_wf_nm 
                   WHEN 'default_WF' THEN 2 
                   ELSE 1 
                 END) rk 
        FROM   party_authlevel) 
WHERE  rk = 1;


is this thread now resolved?
Re: Get the records with alternate workflow value [message #420082 is a reply to message #420080] Wed, 26 August 2009 23:46 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Yes if we dont have other/better alternative for the query I have written.

Regards
Prajakta

[Updated on: Wed, 26 August 2009 23:53]

Report message to a moderator

Re: Get the records with alternate workflow value [message #420088 is a reply to message #420082] Thu, 27 August 2009 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prajakta001 wrote on Thu, 27 August 2009 06:46
Yes if we dont have other/better alternative for the query I have written.

Regards
Prajakta

I don't know as I still don't know what should be the result for your test data.

Regards
Michel

Re: Get the records with alternate workflow value [message #420141 is a reply to message #420082] Thu, 27 August 2009 04:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Prajakta001 - if you want to get the most out of this site, you REALLY need to get the hang of reading the replies to your posts and providing the information / answers that people ask for.

We're not asking just for fun - we're asking because you know lots of things about your problem that we don't, and we're trying to fill in the things that you're assuming we know.

That's why we like test cases - a table with a set of data and the exact results that you want from that set of data - it provides all the information that we need to solve the problem.

I'm not certain what results you're looking for, but I think this should do the trick:
 select party_id
       ,srvc_id
       ,srvc_text
       ,auth_lvl
       ,MAX(alt_wf_nm) KEEP (DENSE_RANK LAST ORDER BY case when  alt_wf_nm = 'default_WF' then 0 else 1 end)
 from party_authlevel
 group by party_id
         ,srvc_id
         ,srvc_text
         ,auth_lvl;

  PARTY_ID    SRVC_ID SRVC_TEXT           AUTH_LVL MAX(ALT_WF_NM
---------- ---------- ----------------- ---------- -------------
         1          2 abc                        1 ALT_WF
         1          2 abc                        2 ALT_WF
         1          2 abc                        3 ALT_WF
         2          2 abc                        1 XYZ_WF
         2          2 abc                        2 XYZ_WF
         2          2 abc                        3 XYZ_WF
         3          2 abc                        1 default_WF
         3          2 abc                        2 default_WF

8 rows selected.
Re: Get the records with alternate workflow value [message #420265 is a reply to message #420141] Thu, 27 August 2009 23:40 Go to previous message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Thanks for the query . Thats exactly the output i wanted .

I agree to all whatever you have written . I have also tried my best to explain my problem and also the exact output query..
May be missed on posting output of teh final query.
( I haven't assumed anytime that you are asking for fun )

May be still a scope for me to explain it more accurately ....

Thanks everyone .

Prajakta

[Updated on: Thu, 27 August 2009 23:42]

Report message to a moderator

Previous Topic: user created with default automatic privilege
Next Topic: year comparison - pls help (merged)
Goto Forum:
  


Current Time: Tue Dec 06 02:33:52 CST 2016

Total time taken to generate the page: 0.09230 seconds