X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3624E3A4E11
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 23 Jan 2005 22:59:50 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 89597-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 23 Jan 2005 22:59:41 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.194])
	by svr1.postgresql.org (Postfix) with ESMTP id 5D3A93A2B21
	for <pgsql-sql@postgresql.org>; Sun, 23 Jan 2005 22:59:40 +0000 (GMT)
Received: by wproxy.gmail.com with SMTP id 67so80567wri
	for <pgsql-sql@postgresql.org>; Sun, 23 Jan 2005 14:59:36 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding;
	b=hioSn9Uk+qSMjuVUxrNuB8tTo4jiIJvhXdso57xhJwIEHveME7VZ/dJH0GTJ0AHvQYJIaZ3dfbxNWrIcyRDyrjwZsiE7j2BiwuLwFNd2+LB0pvE1k2xV8hIkOAeHNrQKl7KQg2iTdw63+4qNat+e5ieTra4yAvX2IeGqtObaUYA=
Received: by 10.54.28.15 with SMTP id b15mr174527wrb;
	Sun, 23 Jan 2005 14:59:36 -0800 (PST)
Received: by 10.54.49.75 with HTTP; Sun, 23 Jan 2005 14:59:36 -0800 (PST)
Message-ID: <690e0bf70501231459ed3cb51@mail.gmail.com>
Date: Sun, 23 Jan 2005 14:59:36 -0800
From: Ryan Miranda <ryan.miranda@gmail.com>
Reply-To: Ryan Miranda <ryan.miranda@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Invalid Input syntax for type bigint
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.231 tagged_above=0 required=5 tests=RCVD_BY_IP,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200501/203
X-Sequence-Number: 19991

hello everyone,

I am trying to run the proc below but get an error : invalid input
syntax for type bigint: "2004-10-26" Can anyone suggest what I am
doing wrong here?

Rx

-- Function: public.getdateallocated(date, date)

DROP FUNCTION public.getdateallocated(date, date);

CREATE OR REPLACE FUNCTION public.getdateallocated(date, date)
  RETURNS text AS
'Declare

workflow_t  ix_workflow_task%ROWTYPE;


BEGIN



    	SELECT ix_workflow_task."DATE_COMPLETED",
ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID", ix_workflow_task."TYPE"
	INTO workflow_t from ix_workflow_task
	INNER JOIN ix_workflow_instance ON
ix_workflow_task."WORKFLOW_INSTANCE_KEY" =
ix_workflow_instance."WORKFLOW_INSTANCE_KEY"
	INNER JOIN ix_workflow_instance_to_domain ON
ix_workflow_instance_to_domain."WORKFLOW_INSTANCE_KEY" =
ix_workflow_instance."WORKFLOW_INSTANCE_KEY"
	INNER JOIN ix_core_case ON
ix_workflow_instance_to_domain."DOMAIN_KEY" =
ix_core_case."CORECASEKEY"
	where to_char(ix_workflow_task."DATE_COMPLETED", \'DD-MM-YYYY\') <> \'\'
	AND ix_core_case."DELETED" = 0
	AND ("CORECASEKEY" in (select * FROM getStatusSwitch($1,$2,
\'Assessment\', \'Prosecution\'))
              OR "CORECASEKEY" in (select * from
getStatusSwitch($1,$2, \'Assessment\', \'Investigation\'))
              OR "CORECASEKEY" in (select * from
getStatusSwitch($1,$2, \'Assessment\', \'Other\')))
 	group by  ix_workflow_task."DATE_COMPLETED",
ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID", ix_workflow_task."TYPE"
	having (lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'organise surveillance - 9b\' AND ix_workflow_task."TYPE" = \'Human\'
             or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID")
LIKE \'start case mix workflow - 9\' AND ix_workflow_task."TYPE" =
\'System\'
	     or	lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'finalise case - 13\' AND ix_workflow_task."TYPE" = \'Human\'
	     or lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'complete final priority smart form - 39\' AND
ix_workflow_task."TYPE" = \'Human\'
	     or	lower(ix_workflow_task."WORKFLOW_ACTIVITY_XPDL_ID") LIKE
\'check for case mix type - 17\' AND ix_workflow_task."TYPE" =
\'System\')
        and (ix_workflow_task."DATE_COMPLETED" >= $1 and
ix_workflow_task."DATE_COMPLETED" <= $2);
	

return workflow_t."WORKFLOW_ACTIVITY_XPDL_ID" ||
to_char(workflow_t."DATE_COMPLETED", \'DD-MM-YYYY\');

END;

'
  LANGUAGE 'plpgsql' VOLATILE;

select getdateallocated('10/10/04','12/12/04');