Complex find() using Oracle

2010-02-18 Thread TonyFugere 
I've search this group and Google for a solution without any luck.

I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:

$this-find('all',
  array(
'fields' = array(
  'ServiceData.stat_date',
  'SUBSTR(ServiceData.stat_parameter,1,4) AS
ServiceData.schema',
  'SUBSTR(ServiceData.stat_parameter,19,2) AS
ServiceData.state',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS ServiceData.service2',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS ServiceData.service3',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS ServiceData.service4',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS ServiceData.service5',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS ServiceData.service6',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS ServiceData.service7',
  'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS ServiceData.service8'
),
'conditions' = array(
  'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
'customer_lookup',
  'SUBSTR(ServiceData.stat_parameter, 1, 4)' = $active_schema,
  'ServiceData.stat_server LIKE' = 'PRODUCTION%',
  'ServiceData.stat#' = 999,
  'ServiceData.stat_date' = $load_date,
),
'group' = array(
  'ServiceData.stat_date',
  'SUBSTR(ServiceData.stat_parameter,1,4)',
  'SUBSTR(ServiceData.stat_parameter,19,2)'
),
'order' = array('SUBSTR(ServiceData.stat_parameter,19,2)')
  )
);

This is the resulting query:

SELECT
  ServiceData.stat_date
  , SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
  , SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS ServiceData.service1
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS ServiceData.service2
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS ServiceData.service3
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS ServiceData.service4
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS ServiceData.service5
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS ServiceData.service6
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS ServiceData.service7
  , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_value,0)) AS ServiceData.service8
FROM
  SERVICE_DATA ServiceData
WHERE
  SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
  AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
  AND ServiceData.stat_server LIKE 'PRODUCTION%'
  AND ServiceData.stat_number = 999
  AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
DD HH24:MI:SS')
GROUP BY
  ServiceData.stat_date
  , SUBSTR(ServiceData.stat_parameter,1,4)
  , SUBSTR(ServiceData.stat_parameter,19,2)
ORDER BY
  SUBSTR(ServiceData.stat_parameter,19,2) ASC

This results in data returning in this form:

array(43) {
  [0]=
  array(3) {
[ServiceData]=
array(4) {
  [stat_date]=
  string(19) 2010-02-17 09:00:02
  [schema]=
  string(4) SCHEMA_A
  [state]=
  string(2) ()
  [statvalue]=
  string(3) 116
}
[SUM(DECODE(SUBSTR(ServiceData]=
array(1) {
  [stat_parameter,22)]=
  string(3) 107
}
[0]=
array(3) {
  ['service1']=
  string(2) 40
  ['service2']=
  string(3) 116
  ['service3']=
  string(2) 17
}
  }
}

Notice the odd array keys SUM(DECODE(SUBSTR(ServiceData and
'service1' (this is the decode value in single ticks). Also the
value to service1 is the value that belongs in service2, service2 has
the value for service5, and service3 has the value for service8???

If I remove the SUM(DECODE(SUBSTR( fields, then everything works as
expected:

array(43) {
  [0]=
  array(1) {
[ServiceData]=
array(3) {
  [stat_date]=
  string(19) 2010-02-17 09:00:02
  [schema]=
  string(4) SCHEMA_A
  [state]=
  string(2) ()
}
  }
}

I have tried the afterFind solution posted by TekNoid, but it did not
work.
http://teknoid.wordpress.com/2008/09/29/dealing-with-calculated-fields-in-cakephps-find/

Any hints or ideas on how I can overcome the issues of having such a
complex SUM DECODE of a SUBSTR?

Check out the new CakePHP Questions site http://cakeqs.org and help

Re: Complex find() using Oracle

2010-02-18 Thread Martin Radosta 
CakePhp is not correctly parsing the alias for the decode fields, thats 
why you see decode... on array keys.
Try using expression for fields. If that works, should also fix the data 
missmatch.


Regards

MARTIN



On 02/18/2010 02:38 PM, TonyFugere wrote:

I've search this group and Google for a solution without any luck.

I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:

$this-find('all',
   array(
 'fields' =  array(
   'ServiceData.stat_date',
   'SUBSTR(ServiceData.stat_parameter,1,4) AS
ServiceData.schema',
   'SUBSTR(ServiceData.stat_parameter,19,2) AS
ServiceData.state',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS ServiceData.service2',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS ServiceData.service3',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS ServiceData.service4',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS ServiceData.service5',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS ServiceData.service6',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS ServiceData.service7',
   'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS ServiceData.service8'
 ),
 'conditions' =  array(
   'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
'customer_lookup',
   'SUBSTR(ServiceData.stat_parameter, 1, 4)' =  $active_schema,
   'ServiceData.stat_server LIKE' =  'PRODUCTION%',
   'ServiceData.stat#' =  999,
   'ServiceData.stat_date' =  $load_date,
 ),
 'group' =  array(
   'ServiceData.stat_date',
   'SUBSTR(ServiceData.stat_parameter,1,4)',
   'SUBSTR(ServiceData.stat_parameter,19,2)'
 ),
 'order' =  array('SUBSTR(ServiceData.stat_parameter,19,2)')
   )
);

This is the resulting query:

SELECT
   ServiceData.stat_date
   , SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
   , SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS ServiceData.service1
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS ServiceData.service2
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS ServiceData.service3
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS ServiceData.service4
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS ServiceData.service5
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS ServiceData.service6
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS ServiceData.service7
   , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_value,0)) AS ServiceData.service8
FROM
   SERVICE_DATA ServiceData
WHERE
   SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
   AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
   AND ServiceData.stat_server LIKE 'PRODUCTION%'
   AND ServiceData.stat_number = 999
   AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
DD HH24:MI:SS')
GROUP BY
   ServiceData.stat_date
   , SUBSTR(ServiceData.stat_parameter,1,4)
   , SUBSTR(ServiceData.stat_parameter,19,2)
ORDER BY
   SUBSTR(ServiceData.stat_parameter,19,2) ASC

This results in data returning in this form:

array(43) {
   [0]=
   array(3) {
 [ServiceData]=
 array(4) {
   [stat_date]=
   string(19) 2010-02-17 09:00:02
   [schema]=
   string(4) SCHEMA_A
   [state]=
   string(2) ()
   [statvalue]=
   string(3) 116
 }
 [SUM(DECODE(SUBSTR(ServiceData]=
 array(1) {
   [stat_parameter,22)]=
   string(3) 107
 }
 [0]=
 array(3) {
   ['service1']=
   string(2) 40
   ['service2']=
   string(3) 116
   ['service3']=
   string(2) 17
 }
   }
}

Notice the odd array keys SUM(DECODE(SUBSTR(ServiceData and
'service1' (this is the decode value in single ticks). Also the
value to service1 is the value that belongs in service2, service2 has
the value for service5, and service3 has the value for service8???

If I remove the SUM(DECODE(SUBSTR( fields, then everything works as
expected:

array(43) {
   [0]=
   array(1) {
 [ServiceData]=
 array(3) {
   [stat_date]=
   string(19) 2010-02-17 09:00:02
   [schema]=
   string(4) SCHEMA_A
   [state]=
   string(2)

Re: Complex find() using Oracle

2010-02-18 Thread TonyFugere 
I don't follow what you mean by Try using expression for fields. Can
you clarify or give me an example?

On Feb 18, 11:12 am, Martin Radosta martinrado...@gmail.com wrote:
 CakePhp is not correctly parsing the alias for the decode fields, thats
 why you see decode... on array keys.
 Try using expression for fields. If that works, should also fix the data
 missmatch.

 Regards

 MARTIN

 On 02/18/2010 02:38 PM, TonyFugere wrote:

  I've search this group and Google for a solution without any luck.

  I can read (SELECT) data, but cannot change this database at all. To
  get the data I run the following find:

  $this-find('all',
     array(
       'fields' =  array(
         'ServiceData.stat_date',
         'SUBSTR(ServiceData.stat_parameter,1,4) AS
  ServiceData.schema',
         'SUBSTR(ServiceData.stat_parameter,19,2) AS
  ServiceData.state',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
  ServiceData.stat_value,0)) AS ServiceData.service1',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
  ServiceData.stat_value,0)) AS ServiceData.service2',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
  ServiceData.stat_value,0)) AS ServiceData.service3',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
  ServiceData.stat_value,0)) AS ServiceData.service4',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
  ServiceData.stat_value,0)) AS ServiceData.service5',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
  ServiceData.stat_value,0)) AS ServiceData.service6',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
  ServiceData.stat_value,0)) AS ServiceData.service7',
         'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
  ServiceData.stat_value,0)) AS ServiceData.service8'
       ),
       'conditions' =  array(
         'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
  'customer_lookup',
         'SUBSTR(ServiceData.stat_parameter, 1, 4)' =  $active_schema,
         'ServiceData.stat_server LIKE' =  'PRODUCTION%',
         'ServiceData.stat#' =  999,
         'ServiceData.stat_date' =  $load_date,
       ),
       'group' =  array(
         'ServiceData.stat_date',
         'SUBSTR(ServiceData.stat_parameter,1,4)',
         'SUBSTR(ServiceData.stat_parameter,19,2)'
       ),
       'order' =  array('SUBSTR(ServiceData.stat_parameter,19,2)')
     )
  );

  This is the resulting query:

  SELECT
     ServiceData.stat_date
     , SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
     , SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
  ServiceData.stat_value,0)) AS ServiceData.service1
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
  ServiceData.stat_value,0)) AS ServiceData.service2
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
  ServiceData.stat_value,0)) AS ServiceData.service3
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
  ServiceData.stat_value,0)) AS ServiceData.service4
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
  ServiceData.stat_value,0)) AS ServiceData.service5
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
  ServiceData.stat_value,0)) AS ServiceData.service6
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
  ServiceData.stat_value,0)) AS ServiceData.service7
     , SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
  ServiceData.stat_value,0)) AS ServiceData.service8
  FROM
     SERVICE_DATA ServiceData
  WHERE
     SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
     AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
     AND ServiceData.stat_server LIKE 'PRODUCTION%'
     AND ServiceData.stat_number = 999
     AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
  DD HH24:MI:SS')
  GROUP BY
     ServiceData.stat_date
     , SUBSTR(ServiceData.stat_parameter,1,4)
     , SUBSTR(ServiceData.stat_parameter,19,2)
  ORDER BY
     SUBSTR(ServiceData.stat_parameter,19,2) ASC

  This results in data returning in this form:

  array(43) {
     [0]=
     array(3) {
       [ServiceData]=
       array(4) {
         [stat_date]=
         string(19) 2010-02-17 09:00:02
         [schema]=
         string(4) SCHEMA_A
         [state]=
         string(2) ()
         [statvalue]=
         string(3) 116
       }
       [SUM(DECODE(SUBSTR(ServiceData]=
       array(1) {
         [stat_parameter,22)]=
         string(3) 107
       }
       [0]=
       array(3) {
         ['service1']=
         string(2) 40
         ['service2']=
         string(3) 116
         ['service3']=
         string(2) 17
       }
     }
  }

  Notice the odd array keys SUM(DECODE(SUBSTR(ServiceData and
  'service1' (this is the decode value in single ticks). Also the
  value to service1

Re: Complex find() using Oracle

2010-02-18 Thread Martin Radosta 

Check the text case in this commit for an example:

http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9

let me know if that fix the problem, if not, you can prepare a test case 
and report the issue.




On 02/18/2010 03:21 PM, TonyFugere wrote:

I don't follow what you mean by Try using expression for fields. Can
you clarify or give me an example?

On Feb 18, 11:12 am, Martin Radostamartinrado...@gmail.com  wrote:
   

CakePhp is not correctly parsing the alias for the decode fields, thats
why you see decode... on array keys.
Try using expression for fields. If that works, should also fix the data
missmatch.

Regards

MARTIN

On 02/18/2010 02:38 PM, TonyFugere wrote:

 

I've search this group and Google for a solution without any luck.
   
 

I can read (SELECT) data, but cannot change this database at all. To
get the data I run the following find:
   
 

$this-find('all',
array(
  'fields' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
ServiceData.schema',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
ServiceData.state',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
ServiceData.stat_value,0)) AS ServiceData.service2',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
ServiceData.stat_value,0)) AS ServiceData.service3',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
ServiceData.stat_value,0)) AS ServiceData.service4',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
ServiceData.stat_value,0)) AS ServiceData.service5',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
ServiceData.stat_value,0)) AS ServiceData.service6',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
ServiceData.stat_value,0)) AS ServiceData.service7',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
ServiceData.stat_value,0)) AS ServiceData.service8'
  ),
  'conditions' =array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' =$active_schema,
'ServiceData.stat_server LIKE' ='PRODUCTION%',
'ServiceData.stat#' =999,
'ServiceData.stat_date' =$load_date,
  ),
  'group' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
  ),
  'order' =array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
);
   
 

This is the resulting query:
   
 

SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
, SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
ServiceData.stat_value,0)) AS ServiceData.service1
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
ServiceData.stat_value,0)) AS ServiceData.service2
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
ServiceData.stat_value,0)) AS ServiceData.service3
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
ServiceData.stat_value,0)) AS ServiceData.service4
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
ServiceData.stat_value,0)) AS ServiceData.service5
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
ServiceData.stat_value,0)) AS ServiceData.service6
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
ServiceData.stat_value,0)) AS ServiceData.service7
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
ServiceData.stat_value,0)) AS ServiceData.service8
FROM
SERVICE_DATA ServiceData
WHERE
SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
AND ServiceData.stat_server LIKE 'PRODUCTION%'
AND ServiceData.stat_number = 999
AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
DD HH24:MI:SS')
GROUP BY
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4)
, SUBSTR(ServiceData.stat_parameter,19,2)
ORDER BY
SUBSTR(ServiceData.stat_parameter,19,2) ASC
   
 

This results in data returning in this form:
   
 

array(43) {
[0]=
array(3) {
  [ServiceData]=
  array(4) {
[stat_date]=
string(19) 2010-02-17 09:00:02
[schema]=
string(4) SCHEMA_A
[state]=
string(2) ()
[statvalue]=
string(3) 116
  }
  [SUM(DECODE(SUBSTR(ServiceData]=
  array(1) {
[stat_parameter,22)]=
string(3) 107
  }
  [0]=
  array(3) {
['service1']=
string(2) 40

Re: Complex find() using Oracle

2010-02-18 Thread Tony Fugere 
It does not fix the problem. Am I doing it correctly?

$dbo = $this-getDataSource();
$expression =
$dbo-expression('SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
\'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1');
$fields = array('ServiceData.stat_date',
 $expression);
$this-find('all', array($fields,...etc, etc.)

Still results in the ugly data.

HOWEVER, I did notice the use of CASE WHEN statements in the test case. So,
I switched my DECODE to CASE WHEN and it works as expected. I will draw up a
test case and report the issue of DECODE failing to function properly.

Thanks for your help Martin!

-Tony

On Thu, Feb 18, 2010 at 11:40 AM, Martin Radosta martinrado...@gmail.comwrote:

 Check the text case in this commit for an example:


 http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9

 let me know if that fix the problem, if not, you can prepare a test case
 and report the issue.




 On 02/18/2010 03:21 PM, TonyFugere wrote:

 I don't follow what you mean by Try using expression for fields. Can
 you clarify or give me an example?

 On Feb 18, 11:12 am, Martin Radostamartinrado...@gmail.com  wrote:


 CakePhp is not correctly parsing the alias for the decode fields, thats
 why you see decode... on array keys.
 Try using expression for fields. If that works, should also fix the data
 missmatch.

 Regards

 MARTIN

 On 02/18/2010 02:38 PM, TonyFugere wrote:



 I've search this group and Google for a solution without any luck.




 I can read (SELECT) data, but cannot change this database at all. To
 get the data I run the following find:




 $this-find('all',
array(
  'fields' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
 ServiceData.schema',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
 ServiceData.state',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
 ServiceData.stat_value,0)) AS ServiceData.service1',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
 ServiceData.stat_value,0)) AS ServiceData.service2',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
 ServiceData.stat_value,0)) AS ServiceData.service3',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
 ServiceData.stat_value,0)) AS ServiceData.service4',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
 ServiceData.stat_value,0)) AS ServiceData.service5',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
 ServiceData.stat_value,0)) AS ServiceData.service6',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
 ServiceData.stat_value,0)) AS ServiceData.service7',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
 ServiceData.stat_value,0)) AS ServiceData.service8'
  ),
  'conditions' =array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
 'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' =$active_schema,
'ServiceData.stat_server LIKE' ='PRODUCTION%',
'ServiceData.stat#' =999,
'ServiceData.stat_date' =$load_date,
  ),
  'group' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
  ),
  'order' =array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
 );




 This is the resulting query:




 SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
, SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',
 ServiceData.stat_value,0)) AS ServiceData.service1
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service2',
 ServiceData.stat_value,0)) AS ServiceData.service2
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service3',
 ServiceData.stat_value,0)) AS ServiceData.service3
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service4',
 ServiceData.stat_value,0)) AS ServiceData.service4
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service5',
 ServiceData.stat_value,0)) AS ServiceData.service5
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service6',
 ServiceData.stat_value,0)) AS ServiceData.service6
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service7',
 ServiceData.stat_value,0)) AS ServiceData.service7
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service8',
 ServiceData.stat_value,0)) AS ServiceData.service8
 FROM
SERVICE_DATA ServiceData
 WHERE
SUBSTR(ServiceData.stat_parameter, 5, 13) = 'customer_lookup'
AND SUBSTR(ServiceData.stat_parameter, 1, 4) = 'SCHEMA_A'
AND ServiceData.stat_server LIKE 'PRODUCTION%'
AND ServiceData.stat_number = 999
AND ServiceData.stat_date = TO_DATE('2010-02-17 09:00:02', '-MM-
 DD HH24:MI:SS')
 GROUP

Re: Complex find() using Oracle

2010-02-18 Thread Martin Radosta 

I would only change:

$this-find('all', array('fields' = $fields,...etc, etc.)

Would be great if you post the test case (or even better, a patch) to 
avoid other people waste time with the same issue.


Bye

MARTIN



On 02/18/2010 05:39 PM, Tony Fugere wrote:

It does not fix the problem. Am I doing it correctly?

$dbo = $this-getDataSource();
$expression = 
$dbo-expression('SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 
\'service1\',

ServiceData.stat_value,0)) AS ServiceData.service1');
$fields = array('ServiceData.stat_date',
 $expression);
$this-find('all', array($fields,...etc, etc.)

Still results in the ugly data.

HOWEVER, I did notice the use of CASE WHEN statements in the test 
case. So, I switched my DECODE to CASE WHEN and it works as expected. 
I will draw up a test case and report the issue of DECODE failing to 
function properly.


Thanks for your help Martin!

-Tony

On Thu, Feb 18, 2010 at 11:40 AM, Martin Radosta 
martinrado...@gmail.com mailto:martinrado...@gmail.com wrote:


Check the text case in this commit for an example:


http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9

let me know if that fix the problem, if not, you can prepare a
test case and report the issue.




On 02/18/2010 03:21 PM, TonyFugere wrote:

I don't follow what you mean by Try using expression for
fields. Can
you clarify or give me an example?

On Feb 18, 11:12 am, Martin Radostamartinrado...@gmail.com
mailto:martinrado...@gmail.com  wrote:

CakePhp is not correctly parsing the alias for the decode
fields, thats
why you see decode... on array keys.
Try using expression for fields. If that works, should
also fix the data
missmatch.

Regards

MARTIN

On 02/18/2010 02:38 PM, TonyFugere wrote:


I've search this group and Google for a solution
without any luck.


I can read (SELECT) data, but cannot change this
database at all. To
get the data I run the following find:


$this-find('all',
   array(
 'fields' =array(
   'ServiceData.stat_date',
   'SUBSTR(ServiceData.stat_parameter,1,4) AS
ServiceData.schema',
   'SUBSTR(ServiceData.stat_parameter,19,2) AS
ServiceData.state',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service1\',
ServiceData.stat_value,0)) AS ServiceData.service1',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service2\',
ServiceData.stat_value,0)) AS ServiceData.service2',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service3\',
ServiceData.stat_value,0)) AS ServiceData.service3',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service4\',
ServiceData.stat_value,0)) AS ServiceData.service4',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service5\',
ServiceData.stat_value,0)) AS ServiceData.service5',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service6\',
ServiceData.stat_value,0)) AS ServiceData.service6',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service7\',
ServiceData.stat_value,0)) AS ServiceData.service7',
 
 'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),

\'service8\',
ServiceData.stat_value,0)) AS ServiceData.service8'
 ),
 'conditions' =array(
   'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
'customer_lookup',
   'SUBSTR(ServiceData.stat_parameter, 1, 4)' =  
 $active_schema,

   'ServiceData.stat_server LIKE' ='PRODUCTION%',
   'ServiceData.stat#' =999,
   'ServiceData.stat_date' =$load_date,
 ),
 'group' =array(
   'ServiceData.stat_date',
   'SUBSTR(ServiceData.stat_parameter,1,4)',
   'SUBSTR(ServiceData.stat_parameter,19,2)'
 ),
 'order' =  
 array('SUBSTR(ServiceData.stat_parameter,19,2)')

   )
);


This is the resulting query:

Re: Complex find() using Oracle

2010-02-18 Thread Tony Fugere 
Do I post those here? If so, here goes... I only have time for a quick test
case

/**
 * test that fields() will accept objects made from DboSource::expression
 *
 * @return void
 */
function testFieldsWithExpression() {
$expression =
$this-testDb-expression(SUM(DECODE(SUBSTR(Sample.id,1), '1', 1, 0)) AS
\Sample.case_col\);
$result = $this-testDb-fields($this-Model, null, array(id,
$expression));
$expected = array(
'`TestModel`.`id`',
SUM(DECODE(SUBSTR(Sample.id,1), '1', 1, 0)) AS
\Sample.case_col\
);
$this-assertEqual($result, $expected);
}

One could argue that I shouldn't be using DECODE anyways as it is Oracle SQL
specific. If this app were to change databases, I would have to rewrite a
lot of custom fields that don't have cross-platform SQL. Is that a valid
argument IYO?

On Thu, Feb 18, 2010 at 1:51 PM, Martin Radosta martinrado...@gmail.comwrote:

  I would only change:

 $this-find('all', array('fields' = $fields,...etc, etc.)

 Would be great if you post the test case (or even better, a patch) to avoid
 other people waste time with the same issue.

 Bye

 MARTIN




 On 02/18/2010 05:39 PM, Tony Fugere wrote:

 It does not fix the problem. Am I doing it correctly?

 $dbo = $this-getDataSource();
 $expression =
 $dbo-expression('SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22),
 \'service1\',
 ServiceData.stat_value,0)) AS ServiceData.service1');
 $fields = array('ServiceData.stat_date',
  $expression);
 $this-find('all', array($fields,...etc, etc.)

 Still results in the ugly data.

 HOWEVER, I did notice the use of CASE WHEN statements in the test case. So,
 I switched my DECODE to CASE WHEN and it works as expected. I will draw up a
 test case and report the issue of DECODE failing to function properly.

 Thanks for your help Martin!

 -Tony

 On Thu, Feb 18, 2010 at 11:40 AM, Martin Radosta 
 martinrado...@gmail.comwrote:

 Check the text case in this commit for an example:


 http://github.com/cakephp/cakephp1x/commit/02330b2d9c292110240c606e976e182c973897e9

 let me know if that fix the problem, if not, you can prepare a test case
 and report the issue.




 On 02/18/2010 03:21 PM, TonyFugere wrote:

 I don't follow what you mean by Try using expression for fields. Can
 you clarify or give me an example?

 On Feb 18, 11:12 am, Martin Radostamartinrado...@gmail.com  wrote:


 CakePhp is not correctly parsing the alias for the decode fields, thats
 why you see decode... on array keys.
 Try using expression for fields. If that works, should also fix the data
 missmatch.

 Regards

 MARTIN

 On 02/18/2010 02:38 PM, TonyFugere wrote:



 I've search this group and Google for a solution without any luck.




 I can read (SELECT) data, but cannot change this database at all. To
 get the data I run the following find:




 $this-find('all',
array(
  'fields' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4) AS
 ServiceData.schema',
'SUBSTR(ServiceData.stat_parameter,19,2) AS
 ServiceData.state',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service1\',
 ServiceData.stat_value,0)) AS ServiceData.service1',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service2\',
 ServiceData.stat_value,0)) AS ServiceData.service2',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service3\',
 ServiceData.stat_value,0)) AS ServiceData.service3',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service4\',
 ServiceData.stat_value,0)) AS ServiceData.service4',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service5\',
 ServiceData.stat_value,0)) AS ServiceData.service5',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service6\',
 ServiceData.stat_value,0)) AS ServiceData.service6',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service7\',
 ServiceData.stat_value,0)) AS ServiceData.service7',
'SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), \'service8\',
 ServiceData.stat_value,0)) AS ServiceData.service8'
  ),
  'conditions' =array(
'SUBSTR(ServiceData.stat_parameter, 5, 13)' =
 'customer_lookup',
'SUBSTR(ServiceData.stat_parameter, 1, 4)' =$active_schema,
'ServiceData.stat_server LIKE' ='PRODUCTION%',
'ServiceData.stat#' =999,
'ServiceData.stat_date' =$load_date,
  ),
  'group' =array(
'ServiceData.stat_date',
'SUBSTR(ServiceData.stat_parameter,1,4)',
'SUBSTR(ServiceData.stat_parameter,19,2)'
  ),
  'order' =array('SUBSTR(ServiceData.stat_parameter,19,2)')
)
 );




 This is the resulting query:




 SELECT
ServiceData.stat_date
, SUBSTR(ServiceData.stat_parameter,1,4) AS ServiceData.schema
, SUBSTR(ServiceData.stat_parameter,19,2) AS ServiceData.state
, SUM(DECODE(SUBSTR(ServiceData.stat_parameter,22), 'service1',