set block property (group by) [message #600210] |
Sun, 03 November 2013 05:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
m_shehpar
Messages: 73 Registered: October 2010 Location: Pakistan
|
Member |
|
|
Hi all,
i have scenario wherein i want to set block property before execute_query is fired. i want to do something like below, but setting group by and having clause is not working out. kindly help in this regard
cri1 := ' CANCEL_DATE IS NULL ';
cri2 := ' HAVING VERSION = (select max(version) from nfa_headers_all where nfa_number = NFA_HEADERS_ALL.NFA_NUMBER)
GROUP BY NLA_ID, NHA_ID, LINE_NUM, DESCRIPTION, BH_CC_ID, QUANTITY, TAX_FOREIGN_VENDOR, UNIT_AMOUNT, FUNC_AMOUNT, LAST_UPDATED_BY,
LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, CONSUMED_AMOUNT_PKR ';
GO_BLOCK('NFA_HEADERS_ALL');
SET_BLOCK_PROPERTY('NFA_HEADERS_ALL', default_where, cri1 || cri2);
EXECUTE_QUERY;
|
|
|
|
|
Re: set block property (group by) [message #600217 is a reply to message #600216] |
Sun, 03 November 2013 07:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Here's something: modify data block's source. Now, it is - what? A table? Change it to a stored procedure. This is rather "complex" (if compared to an ordinary table/view source), but it can be done. Search my previous posts for these keywords; if I remember well, I provided an example of how to do that.
Because, with a stored procedure, you have some sort of "flexibility" available and maybe you could accomplish what you are after.
|
|
|