Advertisement

10.15.2008 at 09:59PM PDT, ID: 23819274
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
6.4

PEAR MDB2 query not working for 2 of 4 fields, but works fine from TOAD

Asked by TerryAtOpus in PHP and Databases, PHP Scripting Language, Oracle 9.x

Tags: , , , , ,

Hi, I've been trying to convert a webapp from using the PEAR DB package to MDB2.

The following query works nicely when I run it from TOAD (ie directly against the Oracle database), and worked with the DB package, but when run through PHP/PEAR MDB2 the prog_cnt and clm_cnt values are empty in the result array, despite there being values for most rows. The per_program and per_descr values always come out fine.

Can anyone suggest how I might be able to fix this query?

It appears I cannot directly use subqueries such as "(select count(*) from v_nm_task where clm_no = per.per_program and contract_id = 133)" because the performance is terrible (and no, I don't think I can add an index, due to the way the database is set up).Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

$sql =  "select per_program,per_descr,prog_cnt,clm_cnt
from ams_period per left outer join
     (select clm_no, count(*) clm_cnt
	    from v_nm_task where contract_id=133  group by clm_no) clm on per.per_PROGRAM = clm.clm_no 
     left outer join
     (select prog_no, count(*) prog_cnt 
	   from v_nm_task where contract_id=133 group by prog_no) prog on per.per_PROGRAM = prog.prog_no
where per_con_id=133
order by per.PER_PROGRAM"  ;
 
$ora_db->setFetchMode(MDB2_FETCHMODE_ASSOC);
$res=$ora_db->query($sql);
if (MDB2::isError($res)) {
  die ($res->getMessage());
}else{
  while ($rec = $res->fetchRow()) {
    print_r($rec);
...
 
 
Output:
Array ( [per_program] => 1 [per_descr] => OCT-03 [prog_cnt] => [clm_cnt] => ) 
Array ( [per_program] => 2 [per_descr] => NOV-03 [prog_cnt] => [clm_cnt] => )  
Array ( [per_program] => 3 [per_descr] => DEC-03 [prog_cnt] => [clm_cnt] => )  
...
Attachments:
 
ScreenHunter-pic.jpg (12 KB) (File Type Details) 
Screenshot of SQL result in TOAD (results are as desired here)
Screenshot of SQL result in TOAD (results are as desired here)
 
 
Keywords: PEAR MDB2 query not working for 2 …
Title
1 Getting pear into PHP
2 checking locking in oracle with toad
3 PEAR
4 PEAR frontend
5 Spooling in Toad
6 Is there any way to open an excel spr…
 
Loading Advertisement...
 
[+][-]11.06.2008 at 08:32PM PST, ID: 22901962

View this solution now by starting your 14-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: PHP and Databases, PHP Scripting Language, Oracle 9.x
Tags: oracle, pear, mdb2, db, php, sql
Sign Up Now!
Solution Provided By: TerryAtOpus
Participating Experts: 0
Solution Grade: A
 
 
 
Loading Advertisement...
20081112-EE-VQP-43 - Hierarchy / EE_QW_EXPERT_20070906