Home » RADICORE » How To » LIST2 Pattern Problem
Re: LIST2 Pattern Problem [message #2141 is a reply to message #2120] |
Fri, 19 June 2009 09:58 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to previous message](/theme/default/images/down.png) |
ajwid01
Messages: 26 Registered: June 2009 Location: UK
|
Junior Member |
|
|
I'll park the primary key problem for right now, until I can properly debug it.
I did have the query pasted in notepad for you, until a scheduled update rebooted my PC (when apparently it shouldn't have), and I lost it.
Will be easy enough to recreate. I'll put back the original query, let it error and then post you the results.
Roughly, from what I recall it's:
I think the HAVING clause (count(dict_table.table_id {?}) gets converted to a WHERE clause, but when it gets re-written by Oracle in the separate oracle php class, it's no longer a COUNT(), as the outer SELECT has converted it to a designated column id from the inner select's alias.
[ Hope that makes sense. ]
And I think Oracle is still griping about the inner select's in-line query not being in the 'group by'.
[ This is the bit that I rewrote as a function call, to avoid the in-line query and so allow a group by on it. ]
This is my modified section from the db_table.class.inc:
if ($item == 'table_id') {
// get data from the database, ignoring any tables which have no entries on DICT_COLUMN
$this->sql_select = 'dict_table.table_id, table_desc, count(dict_column.table_id) as column_count';
$this->sql_orderby = 'dict_table.table_id';
$this->sql_ordery_seq = 'asc';
$this->sql_from = 'dict_table '
. 'LEFT JOIN dict_column ON (dict_table.database_id=dict_column.database_id AND dict_table.table_id=dict_column.table_id) ';
$this->sql_having = 'column_count > 0';
$this->sql_groupby = 'dict_table.table_id, table_desc,AJW_RAD_GET_REL_TABLE_COUNT(DICT_TABLE.DATABASE_ID, DICT_TABLE.TABLE_ID) ';
$data = $this->getData($where);
// convert each row into 'id=id' in the output array
foreach ($data as $row => $rowdata) {
$rowvalues = array_values($rowdata);
$array[$rowvalues[0]] = $rowvalues[0];
} // foreach
return $array;
} // if
The main bits are the function call for REL_COUNT, and the changing of the HAVING clause away from the count. I think yours USED to be like this, before avoiding the aliased column that Oracle didn't like.
But now, by the time it gets converted from HAVING to WHERE to the Oracle specific PHP class, it can reference the column directly again.
The simple (rough and ready) code for the REL_COUNT replacement was just:
CREATE OR REPLACE FUNCTION AJW_RAD_GET_REL_TABLE_COUNT(IN_DB VARCHAR2, IN_TAB VARCHAR2) RETURN NUMBER
IS
V_RETURN NUMBER;
BEGIN
BEGIN
SELECT COUNT(*)
INTO V_RETURN
FROM DICT_RELATIONSHIP
WHERE
DATABASE_ID_SNR = IN_DB AND
TABLE_ID_SNR = IN_TAB;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
RETURN V_RETURN;
END;
/
I think that kinda works.
Don't think I changed anything else.
|
|
|
![Read Message Read Message](/theme/default/images/read.png) |
|
LIST2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 04:52
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: AJM on Thu, 18 June 2009 05:25
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 05:58
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: AJM on Thu, 18 June 2009 06:17
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 06:24
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 07:19
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: AJM on Thu, 18 June 2009 09:36
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 09:40
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: AJM on Thu, 18 June 2009 10:10
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: AJM on Mon, 22 June 2009 11:19
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 09:52
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: AJM on Thu, 18 June 2009 10:19
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 09:54
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 10:30
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 10:36
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: AJM on Thu, 18 June 2009 10:47
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 11:01
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: AJM on Thu, 18 June 2009 11:44
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Thu, 18 June 2009 12:02
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: AJM on Thu, 18 June 2009 12:52
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LINK2 Pattern Problem
By: ajwid01 on Fri, 19 June 2009 08:40
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LIST2 Pattern Problem
By: AJM on Fri, 19 June 2009 09:11
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LIST2 Pattern Problem
By: ajwid01 on Fri, 19 June 2009 09:58
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LIST2 Pattern Problem
By: AJM on Fri, 19 June 2009 10:55
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LIST2 Pattern Problem
By: ajwid01 on Mon, 22 June 2009 09:30
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LIST2 Pattern Problem
By: AJM on Mon, 22 June 2009 09:51
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LIST2 Pattern Problem
By: ajwid01 on Mon, 22 June 2009 11:10
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LIST2 Pattern Problem
By: ajwid01 on Mon, 22 June 2009 11:35
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LIST2 Pattern Problem
By: ajwid01 on Mon, 22 June 2009 11:38
|
![Read Message Read Message](/theme/default/images/read.png) |
|
Re: LIST2 Pattern Problem
By: AJM on Mon, 22 June 2009 11:48
|
Goto Forum:
Current Time: Sat Aug 17 08:35:42 EDT 2024
Total time taken to generate the page: 0.07496 seconds
|