โดยปกติการคิวรี่ข้อมูลจากวันที่ เราสามารถเขียน SQL ใส่เงื่อนไขจากวันที่ได้ตรงๆ อยู่แล้ว เช่น

select * from tasks where due_date = $P{date}-- orselect * from tasks where due_date between $P{start_date} and $P{end_date}

แต่ถ้าต้องการเพิ่มเงื่อนไขว่าถ้า $P{date} มีค่าเท่ากับ null ให้แสดงค่าทั้งหมดออกมา เราจะไม่สามารถใช้ $P{date} is null ได้เหมือนกับพารามิเตอร์แบบ String

select * from tasks where $P{date} is null or due_date = $P{date}
Error filling print... Error executing SQL statement for : sample-report
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : sample-report

หมายเหตุ ถ้า $P{date} มีค่าเป็น null จะสามารถทำงานได้ แต่ถ้ามีค่าจะไม่สามารถทำงานได้

วิธีการที่ถูกต้อง คือ ต้องใส่ $X{} expression มาช่วยในการตรวจสอบค่า null ดังนี้

select * from tasks where $X{EQUAL, NULL, date} or due_date = $P{date}

นอกจากนี้ยังสามารถใช้ $X{} expression กับ Date ได้อีก ดังนี้

$X{LESS, column, date} คือ column value < $P{date}
$X{LESS], column, date} คือ column value<= $P{date}
$X{GREATER, column, date} คือ column value> $P{date}
$X{[GREATER, column, date} คือ column value>= $P{date}
$X{BETWEEN, column, start_date, end_date} คือ column value >= $P{start_date} AND column value <= $P{end_date}

หมายเหตุ ส่วนตัวลอง $X{BETWEEN, column, start_date, end_date} แล้วไม่ได้ผล เพราะมันดันเอาเวลาไปใช้ด้วย

ตัวอย่าง

ถ้าต้องสร้างรายงานแสดงรายการงาน พร้อมแสดงวันที่ส่งงาน โดยสามารถค้นหาได้จากช่วงวันที่ หรือแสดงทั้งหมดเมื่อวันที่เป็น null ตามนี้

Step 1: เตรียมฐานข้อมูล

ในตัวอย่างนี้ ผมได้เตรียมข้อมูลเอาไว้แล้วเป็น PostgreSQL ซึ่งสามารถรันได้จาก Docker ตามนี้

docker container run --rm -p 5433:5432 somprasongd/pg10-sample-db

แต่ถ้าต้องการสร้างตารางเองก็สามารถใช้คำสั่งด้านล่างนี้ได้

CREATE TABLE public.tasks (id serial NOT NULL,name varchar(100) NOT NULL,due_date date NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),CONSTRAINT tasks_pk PRIMARY KEY (id));CREATE INDEX tasks_due_date_idx ON tasks USING btree (due_date);INSERT INTO public.tasks (name, due_date) VALUES('Task #1', now()), ('Task #2', now() + interval '2 day'), ('Task #3', now() + interval '4 day'), ('Task #4', now() + interval '6 day'), ('Task #5', now() + interval '8 day'), ('Task #6', now() + interval '10 day'), ('Task #7', now() + interval '20 day'), ('Task #8', now() + interval '22 day'), ('Task #9', now() + interval '30 day'), ('Task #10', now() + interval '60 day');-- ข้อมูลจะเริ่มจากวันที่ที่นำข้อมูลเข้าครั้งแรก เช่น วันนี้วันที่ 11 Oct 2018 Task #1 จะมี due_date เป็นวันที่ 11 Oct 2018

Step 2: เชื่อมต่อฐานข้อมูลกับโปรแกรม iReport

เนื่องจากผมใช้ฐานข้อมูลจาก docker โดยกำหนด

port: 5433
database name: sample-db
username & password: postgres

Step 3: สร้างไฟล์ sample-report.jrxml

เมื่อสร้างไฟล์ใหม่จะได้ออกมาแบบนี้

เริ่มจากสร้าง parameter 2 ตัว คือ
– $P{start_date} เป็น java.util.Date ค่าเริ่มต้นเป็น null
– $P{end_date} เป็น java.util.Date ค่าเริ่มต้นเป็น null

เสร็จแล้วเขียน SQL ตามนี้

SELECT name, due_date
FROM tasks
WHERE ($X{EQUAL, NULL, start_date} or $X{EQUAL, NULL, end_date} or
due_date between $P{start_date} and $P{end_date})
ORDER BY due_date

จัดหน้าจอตามตัวอย่าง