PostgreSql: Getting Date in Different Format and group by month

Select  to_char(date_inserted, ‘YYYYMM’) as year_month,

to_char(date_inserted, ‘YYYY’) as year_string,

to_char(date_inserted, ‘month’) as month_string

from myTable

group by year_month

order by year_month;

In the above example,

  • year_month value is displayed as 200804, here 2008 is year and 04 is month.
  • year_string value is displayed as 2008, its a year.
  • month_string value is displayed as april, its the month in date (04 – april month)

iReport: Applying Condition depending on the parameter value passed

i had the problem

like

i want to keep the where condition depending on the value of parameter is pass.

i tried different things, and final got through with following solution.

select * from deptartment

where

(CASE WHEN $P{param_dept_codice} = -1

THEN true

ELSE dept_codice = $P{param_dept_codice}

END)

above query displays all the records from deptartment table, if you pass the param_dept_codice as -1 .

if you pass some exact deptartment id other than -1, then it will return only those records that satisfies the condition.

Note: i gave -1 for checking because i m sure that i will not have any record with that dept_codice.


CASE…WHEN…END

in Postgre SQL,

we have CASE…WHEN…END.

syntax :

CASE WHEN condition1 THEN value1

WHEN condition2 THEN value2

WHEN condition3 THEN value3

ELSE value4

END

Example :

SELECT    emp_codice, emp_descrizione, emp_dept,
(CASE WHEN emp_dept = ‘S’ THEN  ‘SALES’
WHEN emp_dept = ‘M’ THEN ‘MARKETTING’
ELSE ‘GENERAL’
END) as emp_dept_desc
FROM        public.tbl_emp