Using PowerHouse functions in a cursor

[Joe Boyle]

What I was really trying to get at was that it seemed more likely that the
date calculation was making use of a database date function, rather than an
attempt to make use of the PH sysdate function.

I wonder if Mark's approach, below, might be suitable with this one,

SQL IN SGBASE DECLARE CSR_CONS_PEND CURSOR FOR SELECT   &   
(TRUNC (b.SYSDATE - TO_DATE (a.fecha_origen, 'YYYYMMDD')))  &
from CONSULTAS a , ( select SYSDATE from dual ) b

_______________________________________________

-----Original Message-----
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On Behalf Of
Peter Bateman
Sent: 08 September 2007 00:10
To: powerh-l at lists.sowder.com
Subject: RE: Using PowerHouse functions in a cursor


Hi All:

   Joe Boyle pointed out that my solution has invalid mixed Oracle 
datatypes.
   Thanks, Joe.

Revised code:-
; using PowerHouse's SYSDATE function
>
>>DECLARE CSR_CONS_PEND CURSOR
>>FOR                                           &
>>
>>SELECT                                                                    
>>&
>>
>>   (TRUNC ( TO_DATE ( ( :(ASCII (SYSDATE , 8)) )  , 'YYYYMMDD' )  -    &
                     TO_DATE      (fecha_origen, 'YYYYMMDD')))  &
>>
>>FROM CONSULTAS
>

SYSDATE is also an parameterless Oracle function. So why didn't the SQL
checker recognize this function in function in the cursor?

I don't have ORACLE up at the monent by I would try SYSDATE() hopefully
the SQL syntax checker would recognize it as a function.
>; using Oracle's SYSDATE function
>SQL IN SGBASE &
>
>DECLARE CSR_CONS_PEND CURSOR
>FOR                                           &
>
>SELECT                                                                    &
>
>   (TRUNC (SYSDATE() - TO_DATE (fecha_origen, 'YYYYMMDD')))  &
>
>FROM CONSULTAS
>

If all else fails one could create a view that returned the current datetime

i.e. CREATE VIEW OR_DATETIME  ( OR_SYSDATE )
      AS SELECT SYSDATE FROM DUAL;

>; using the view
>SQL IN SGBASE &
>
>DECLARE CSR_CONS_PEND CURSOR
>FOR                                           &
>
>SELECT                                                                    &
>
>   (TRUNC ( OR_SYSDATE - TO_DATE (fecha_origen, 'YYYYMMDD')))  &
>
>FROM CONSULTAS, OR_DATETIME
>


Regards,
Peter Bateman



>From: "Peter Bateman" <peterbateman808 at hotmail.com>
>To: powerh-l at lists.sowder.com
>Subject: Using PowerHouse functions in cursor
>Date: Fri, 07 Sep 2007 17:22:18 -0300
>
>Hi all:
>The problem is that the SQL is expecting
>SYSDATE to be a column in CONSULTAS
>we need to declare the result of SYSDATE ( a PowerHouse function )
>to be a host variable by using the following syntax
>
>  :( < ph function > )
>
>e.g.
>
>>DECLARE CSR_CONS_PEND CURSOR
>>FOR                                           &
>>
>>SELECT                                                                    
>>&
>>
>>   (TRUNC ( :(SYSDATE) - TO_DATE (fecha_origen, 'YYYYMMDD')))  &
>>
>>FROM CONSULTAS
>
>Regards,
>Peter Bateman
>
>
>
>>From: Richard Sheehan <sheerich at isu.edu>
>>To: "jmalmaga at wanadoo.es" <jmalmaga at wanadoo.es>
>>CC: "powerh-l at lists.sowder.com" <powerh-l at lists.sowder.com>
>>Subject: Re:
>>Date: Fri, 07 Sep 2007 11:08:22 -0600
>>
>>
>>
>>
>>
>>
>>
>>There must be a problem with the list serve.  The messages posted
>>today don't have a subject.
>>
>>
>>
>>Quite disturbing...
>>
>>
>>
>>
>>
>>jmalmaga at wanadoo.es wrote:
>>
>>
>>Hi  all,
>>
>>I have problems with oracle and one cursor using qdesign
>>
>>this is the cursor
>>
>>SQL IN SGBASE &
>>
>> DECLARE CSR_CONS_PEND CURSOR
>>FOR                                           &
>>
>> SELECT                                                                   
 
>>&
>>
>>   (TRUNC (SYSDATE - TO_DATE (fecha_origen, 'YYYYMMDD')))  &
>>
>>FROM CONSULTAS 
>>
>>*E* Error parsing SQL
>>statement.
>>
>>DMS-E-GENERAL, A
>>general exception has occurred during operation 'prepare
>>
>>request'.
>>
>>DMS-E-RBI_COLUMN,
>>Column SYSDATE was not found.
>>
>>the problem is in the
>>field sysdate,  Anybody Know how solve this problem?
>>   ¡Viva los novios! En Match.com lo gritamos todos los
>>días
>>
>>
>>
>>
>>--
>>Richard Sheehan
>>Computer Analyst, Senior
>>208-282-4430
>>Office of Institutional Research
>>Idaho State University
>>Pocatello, ID  83209-8368
>>

_________________________________________________________________
Share More On Messenger with a Windows Live Space 
http://spaces.live.com/?mkt=en-ca

-- 
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: &quot;subscribe&quot; in message body to
powerh-l-request at lists.sowder.com
Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
Add 'site:lists.sowder.com powerh-l' to your search terms to search the list
archive at Google.