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>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 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', 'YYYY-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-field.
>>>> ..
>>>>
>>>>
>>>
>>>
>>>> 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 sitehttp://cakeqs.organd help others
>>>> with their CakePHP related questions.
>>>>
>>>>
>>>
>>>
>>>> You received this message because you are subscribed to the Google
>>>> Groups "CakePHP" group.
>>>> To post to this group, send email to cake-php@googlegroups.com
>>>> To unsubscribe from this group, send email to
>>>> cake-php+unsubscr...@googlegroups.com<cake-php%2bunsubscr...@googlegroups.com>For
>>>> more options, visit this group athttp://
>>>> groups.google.com/group/cake-php?hl=en
>>>>
>>>>
>>> Check out the new CakePHP Questions site http://cakeqs.org and help
>> others with their CakePHP related questions.
>>
>> You received this message because you are subscribed to the Google Groups
>> "CakePHP" group.
>> To post to this group, send email to cake-php@googlegroups.com
>> To unsubscribe from this group, send email to
>> cake-php+unsubscr...@googlegroups.com<cake-php%2bunsubscr...@googlegroups.com>For
>> more options, visit this group at
>> http://groups.google.com/group/cake-php?hl=en
>>
>>
>
> Check out the new CakePHP Questions site http://cakeqs.org and help others
> with their CakePHP related questions.
>
> You received this message because you are subscribed to the Google Groups
> "CakePHP" group.
> To post to this group, send email to cake-php@googlegroups.com
> To unsubscribe from this group, send email to
> cake-php+unsubscr...@googlegroups.com<cake-php%2bunsubscr...@googlegroups.com>For
> more options, visit this group at
> http://groups.google.com/group/cake-php?hl=en
>
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
