Home » Developer & Programmer » Application Express & MOD_PLSQL » Select approved/rejected for each Role if N° of roles can change (APEX 4.0)
Select approved/rejected for each Role if N° of roles can change [message #542586] Wed, 08 February 2012 02:39 Go to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Hello Smile

I need help with the following:
What I'd like is a report in my app to display something like:
PROJECT NAME   R1   R2   R3   Y/N
---------------------------------
example 1      1    0    0     N
esample 2      1    1    1     Y
projectttt     0    1    1     N

where "R1", "R2" and "R3" are roles which must EACH approve the project for it to advance (1 is approved 0 is not).
Y/N is then whether or not the project is approved overall.
My query is currently:
SELECT
  p.project_name,
  p.date,
  (SELECT pr.is_approved
    FROM project_review pr
    WHERE pr.project_id = p.project_id
    AND role = 'R1'
  ) R1,
  (SELECT pr.is_approved
    FROM project_review pr
    WHERE pr.project_id = p.project_id
    AND role = 'R2'
  ) R2,
  (SELECT pr.is_approved
    FROM project_review pr
    WHERE pr.project_id = p.project_id
    AND role = 'R3'
  ) R3
  FROM project p
where p.project_id = :P2_PROJECT_ID

But what I need is a kind of dynamic query; I have a table with the different roles in (ROLES) which CAN be edited (roles change, added etc.). So what I need is (1)for the query for this report to change with that table...Is that possible?
The link table between the two is the PROJECT_REVIEW table where the ROLES can add comments if they've rejected it or whatever.

The other thing is (2) How would I get the Y/N column to display like that i.e. If any of the roles reject the project then it is 'N' if they all accept it's 'Y'
-I thought I could put an item in with a query to check none of the reviews are rejected and then have the column display the value of the item but I though since I'm asking about the other thing, might as well ask if there's a better way Smile

Thanks in advance for the help Smile

(P.S. although this is in APEX I guess it is really an SQL problem so sorry if its more relevant in the SQL & PL/SQL forum...)
Re: Select approved/rejected for each Role if N° of roles can change [message #542588 is a reply to message #542586] Wed, 08 February 2012 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

In addition, this has been asked and answered many times and the answer is: it is not possible in pure SQL.
But it possible with a function that returns a ref cursor.

Regards
Michel

[Updated on: Wed, 08 February 2012 02:50]

Report message to a moderator

Re: Select approved/rejected for each Role if N° of roles can change [message #542591 is a reply to message #542588] Wed, 08 February 2012 02:59 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
I'll look it up, thanks
Re: Select approved/rejected for each Role if N° of roles can change [message #542664 is a reply to message #542591] Wed, 08 February 2012 07:34 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
Also in Apex it's possible to build the query dynamically and return it as a varchar2 variable. When you have this requirement for it to be possible that the number/names of the columns are different at any time you will have to make sure that you pick "Use Generic Column Names (parse query at runtime only)" in the radiobutton shown below the source field. If you then have in your report attributes "Column Names (InitCap)" chosen, you could use the role name as an alias for your columns, and that would be what is shown as a column header in Apex.
Re: Select approved/rejected for each Role if N° of roles can change [message #542668 is a reply to message #542664] Wed, 08 February 2012 07:42 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
sorry, is this instead of or in addition to the ref cursor?
Re: Select approved/rejected for each Role if N° of roles can change [message #542671 is a reply to message #542668] Wed, 08 February 2012 07:46 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
Instead of.
Re: Select approved/rejected for each Role if N° of roles can change [message #542672 is a reply to message #542671] Wed, 08 February 2012 07:56 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Ok, and where do I go to build the query dynamically? the Query builder?


I found this: http://www.apex-blog.com/oracle-apex/dynamic-report-regions-tutorial-32.html
Is this it?

[Updated on: Wed, 08 February 2012 07:58]

Report message to a moderator

Re: Select approved/rejected for each Role if N° of roles can change [message #542675 is a reply to message #542672] Wed, 08 February 2012 08:20 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
Yes this is what I mean. A small example.
In region definition I used this:
declare
    myquery varchar2(1000);
begin
    myquery := 'SELECT ';
    for i in 1..:P3_NUM_COLS loop
        myquery := myquery || i || ' ' || ' mycol_name_' || i;
        if i <> :P3_NUM_COLS then
            myquery := myquery || ', ';
        end if;
    end loop;
    
    myquery := myquery || ' from dual';
    return myquery;
end;


And the result you can see below.
./fa/9821/0/
  • Attachment: result.jpg
    (Size: 13.44KB, Downloaded 318 times)
Re: Select approved/rejected for each Role if N° of roles can change [message #542676 is a reply to message #542675] Wed, 08 February 2012 08:32 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Ok awesome, thanks Smile
Re: Select approved/rejected for each Role if N° of roles can change [message #542680 is a reply to message #542676] Wed, 08 February 2012 09:45 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Hi, I'm having a problem with it. I've got my item P3_QUERY with source type "SQL query (returning colon separated values)"
SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(', (select pmr.IS_APPROVED
    from PMO_PROJECT_MILESTONE_REVIEW pmr
    where pmr.PROJECT_MILESTONE_ID = pm.PROJECT_MILESTONE_ID
    and  = '||rr.ROLE_ID||' '||rr.ROLE_CODE , '*')), '*', '') conc
FROM
  (SELECT rr.ROLE_ID,
    rr.ROLE_CODE,
    ROW_NUMBER() OVER (ORDER BY rr.ROLE_ID) R
  FROM PMO_ROLE rr
  ) rr
  START WITH r       =1
  CONNECT BY PRIOR r = r-1;

which is giving the result:
, (select pmr.IS_APPROVED 
    from PMO_PROJECT_MILESTONE_REVIEW pmr 
    where pmr.PROJECT_MILESTONE_ID = pm.PROJECT_MILESTONE_ID 
    and  = 1 R1, (select pmr.IS_APPROVED 
    from PMO_PROJECT_MILESTONE_REVIEW pmr 
    where pmr.PROJECT_MILESTONE_ID = pm.PROJECT_MILESTONE_ID 
    and  = 2 R2, (select pmr.IS_APPROVED 
    from PMO_PROJECT_MILESTONE_REVIEW pmr 
    where pmr.PROJECT_MILESTONE_ID = pm.PROJECT_MILESTONE_ID 
    and  = 3 R3

Which I think is right

And then I have my query for the report:
DECLARE
  v_query varchar2(1000);
BEGIN
  IF :P3_QUERY is not null THEN
    v_query := 'select pm.PROJECT_MILESTONE_ID, cp.CHECKPOINT_NAME, pm.MILESTONE_DATE' || 
:P3_QUERY ||' from PMO_PROJECT_MILESTONE pm, PMO_CHECKPOINT cp where pm.PROJECT_ID = ' || 
:P3_PROJECT_ID ||' and cp.checkpoint_code = pm.checkpoint_code';
  ELSE
  v_query := 'SELECT 1 FROM dual WHERE 1=0';
  END IF;
  return(v_query);
END;


but i'm getting the error:
failed to parse SQL query:
ORA-00921: unexpected end of SQL command
where the report should be...
is there an obvious problem I'm missing orr..?

thanks

[Updated on: Thu, 09 February 2012 13:30] by Moderator

Report message to a moderator

Re: Select approved/rejected for each Role if N° of roles can change [message #542685 is a reply to message #542680] Thu, 09 February 2012 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The way to debug this kind of stuff is to display the query you want to execute (BEFORE or instead of you try to execute it).

Regards
Michel
Re: Select approved/rejected for each Role if N° of roles can change [message #542699 is a reply to message #542685] Thu, 09 February 2012 14:45 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
What I can see immediately is that you have several opening parentheses, but no closing ones. Also you have for instance "and = 1" which would cause an error. What I normally do for these things is that I copy and paste the whole code into a pl/sql region and then wrap the return value in a htp.p instead of return (maybe also surrounded by pre tags to keep the indentation), so in your case htp.p('<pre>' || v_query || '</pre>')
Re: Select approved/rejected for each Role if N° of roles can change [message #542705 is a reply to message #542699] Thu, 09 February 2012 16:28 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
Just another comment.
where pm.PROJECT_ID = ' || 
:P3_PROJECT_ID ||' and cp.checkpoint_code = pm.checkpoint_code'

should be replaced by:
where pm.PROJECT_ID = :P3_PROJECT_ID and cp.checkpoint_code = pm.checkpoint_code'

Note that I removed the concatenation of the value of :P3_PROJECT_ID and rather just put in the variable name in the string. The real value will be substituted by apex itself when it runs your query (as a dynamic query with a bind variable).
Re: Select approved/rejected for each Role if N° of roles can change [message #542768 is a reply to message #542705] Fri, 10 February 2012 02:33 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
There were lots of stupid little mistakes in there, sorry I've fixed it all up now,
thanks Smile

On an unrelated note, I couldn't access the site yesterday or the day before's evening...was that just me or was the site down?
Re: Select approved/rejected for each Role if N° of roles can change [message #542769 is a reply to message #542768] Fri, 10 February 2012 02:34 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Black Hawk was down.
Re: Select approved/rejected for each Role if N° of roles can change [message #544730 is a reply to message #542769] Thu, 23 February 2012 09:19 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Hi, I've just noticed a problem with this: If there are more than 6 or 7 ROLES I get the following error:
ORA-01489: result of string concatenation is too long
 Error ERR-1019 Error computing item default value: page=1 name=P1_QUERY.  
Which presumably just means the result is too long for the one item to hold?
In which case I can just use a second item with a similar source and put them both in the PL/SQL report thingy, right?

My question is, what do I add to the two item's sources to make sure i'm not selecting the same roles twice? or not missing one?

thanks
Re: Select approved/rejected for each Role if N° of roles can change [message #544749 is a reply to message #544730] Thu, 23 February 2012 10:34 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How large is that item, anyway? Any chance to make it larger?
Re: Select approved/rejected for each Role if N° of roles can change [message #544786 is a reply to message #544749] Thu, 23 February 2012 15:50 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
The size of the item shouldn't matter. That is more related to the display and to what the user can type on the screen. What can actually be held in it is not defined by you. I believe that the limit is 4000. After experimenting a bit I see that the value you return for the source of the query can anyways be max 4000 characters so even if you used two items you wouldn't be able to just concatenate them and return them like that to make the query source bigger. If you can have lots and lots and lots of these milestones I think you should go for building a pl/sql region for showing it.
Re: Select approved/rejected for each Role if N° of roles can change [message #544823 is a reply to message #544786] Fri, 24 February 2012 02:45 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
I copy - pasted my query to word to see how many characters were being returned and it's already over 4000 (4594 without spaces, 5171 with) So I don't think the limit is 4000...or if it is it must be counting the "|| :P1_QUERY ||" as those 15 characters rather than the 3500 odd that the item is holding...

[UPDATE] TEST: I just created a copy of P1_QUERY (P1_QUERYY) and concatenated it in the report query source and it actually worked fine, I had 12 roles in the table (2 of each I have currently). So I think actually just having 2 or 3 items and concatenating them all would work...

[Updated on: Fri, 24 February 2012 02:55]

Report message to a moderator

Re: Select approved/rejected for each Role if N° of roles can change [message #544832 is a reply to message #544823] Fri, 24 February 2012 03:27 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
Ok, but in that case you could create a varchar2 variable of length 32767 and compute the whole query in your query procedure instead of using a page item. If you want to use 3 or 4 page items you would have the problem you're mentioning where you'd have to distribute your query in such a way that you cover all checkpoints, but each of them just once. It would be a lot easier to build the query in your report source instead.
Re: Select approved/rejected for each Role if N° of roles can change [message #544836 is a reply to message #544832] Fri, 24 February 2012 03:43 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
I'm sorry, I'm not sure I understood.
I can get rid of the item?
The item is to make the query dynamic is there another way of doing that?
Re: Select approved/rejected for each Role if N° of roles can change [message #544839 is a reply to message #544836] Fri, 24 February 2012 03:57 Go to previous messageGo to next message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
You saw what I did in the example I gave you. I didn't use an Apex item to hold the query or part of it. The item was only used to find the criteria for what the query should be. You can just loop through the result of a query giving the roles you need and add each part of the query to the variable as you go through.
Re: Select approved/rejected for each Role if N° of roles can change [message #544887 is a reply to message #544839] Fri, 24 February 2012 08:19 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Ok I think I see...though won't that give the problem of having too many characters returned in the query?
Re: Select approved/rejected for each Role if N° of roles can change [message #544892 is a reply to message #544887] Fri, 24 February 2012 08:53 Go to previous message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
You said you were able to return more than 4000 characters for your query now, right? (you checked it in word you said) So then returning this long variable shouldn't be a problem. But the apex items can't hold more than 4000.
Previous Topic: Creating items based on a pl/sql function OR referencing report columns in a pl/sql query
Next Topic: HOw can i send a report as attachment in apex
Goto Forum:
  


Current Time: Thu Oct 02 10:18:38 CDT 2014

Total time taken to generate the page: 0.09739 seconds