Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id AF1049F98DC for ; Wed, 3 Oct 2007 13:44:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 14592-08 for ; Wed, 3 Oct 2007 13:44:40 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by postgresql.org (Postfix) with ESMTP id 74D5F9F98AA for ; Wed, 3 Oct 2007 13:44:40 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so6219549waf for ; Wed, 03 Oct 2007 09:44:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:from:to:subject:date:message-id:mime-version:content-type:x-mailer:thread-index:x-mimeole; bh=/6AFhgxTSv5l6cFQv+0i8J8OybXyTuLutnilaiPKY8Q=; b=ZHh/MjnqtpIZtVCUabvQ0LhqERhAul8sVlrfMC5b1zmOCeGgmfpMkCBT+3o1IjWNgYA6iS3gd4Q2ijJLU8CGoEYAfETcvtP1ED5qYI57E67GNOaCnn/A/smVbUv+iJQ0q6XvYt0sx9G8xWWIkDRTIvLS3IcMX/574XViigcSSnM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:from:to:subject:date:message-id:mime-version:content-type:x-mailer:thread-index:x-mimeole; b=JXi3ho09MBXFzHf6olSVIs0nPO1BaekDokVq2dTYjRvceH19hCxh7/LW14Q0zAUU9d5CqV40GXBh1w6FRLlX+GzGvkNMybFeQnysFXTVIu9L/QB8WsbIBRcRYyOnvM2ZYt2INQOWI8Y1ASkORVtb8gQH4+5pNuhf8IXea4erqd8= Received: by 10.114.201.1 with SMTP id y1mr7037318waf.1191429878548; Wed, 03 Oct 2007 09:44:38 -0700 (PDT) Received: from hengky ( [125.162.255.170]) by mx.google.com with ESMTPS id j39sm952332waf.2007.10.03.09.44.34 (version=SSLv3 cipher=RC4-MD5); Wed, 03 Oct 2007 09:44:38 -0700 (PDT) From: "Hengky Lie" To: Subject: Need help with CASE statement in Function Date: Thu, 4 Oct 2007 00:44:04 +0800 Message-ID: <000d01c805dc$9e8db450$1d00a8c0@hengky> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000E_01C8061F.ACB0F450" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcgF3Jv0vODXiyFfQqeilj6rETB/iQ== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/13 X-Sequence-Number: 29385 This is a multi-part message in MIME format. ------=_NextPart_000_000E_01C8061F.ACB0F450 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Dear friends, I am a new user to postgreSQL and really need help to solve my "stupid ?" problem. I have created function with 4 arguments like this : CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate date, ProductID varchar, storeID varchar) RETURNS SETOF "public"."tbltrans" AS $body$ /* New function body */ select * from tbltrans where tbltrans."Date" between $1 and $2 and upper(tbltrans."ProductID")=upper($3) and tbltrans."StoreID"=$4 order by tbltrans."Tanggal"; $body$ LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; --------------------- My Question is : How to make argument 4 optional ? When IS NULL the function will show all transaction between date $1 and $2 and product ID=$3 But when $4 is not null then the fucntion will show all transaction between date $1 and $2 and product ID=$3 AND STOREID=$4. I really appreciate any suggestions. Thanks a lot. Hengky ------=_NextPart_000_000E_01C8061F.ACB0F450 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Dear friends,

I am a new user to postgreSQL and really need help to = solve my “stupid ?” problem.

I have created function with 4 arguments like = this :

CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate date, = ProductID varchar, storeID varchar) RETURNS SETOF = "public"."tbltrans" AS

$body$

/* New function body = */

select * from = tbltrans

where tbltrans."Date” between $1 = and $2

and = upper(tbltrans."ProductID")=3Dupper($3)

and = tbltrans."StoreID"=3D$4

order by = tbltrans."Tanggal";

$body$

LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

---------------------

My Question is : How to make argument 4 optional ? = When IS NULL the function will show all transaction between date $1 and $2 and = product ID=3D$3

But when $4 is not null then the fucntion will show = all transaction between date $1 and $2 and product ID=3D$3 AND = STOREID=3D$4.

I really appreciate any = suggestions.

Thanks a lot.

Hengky

------=_NextPart_000_000E_01C8061F.ACB0F450--