Some Functions in Report Builder

To Print in log file

SRW.MESSAGE(1,'String to be printed in log file');

Here first parameter indicates the log file(1).
Second parameter is the string that to be printed in log file.

To Abort from the report

RAISE SRW.program_abort;

Use of Analytical Function

Requirement : I need list of items purchased in a month. If a item purchased more than once in a month then i need the recent purchase order number.

Solution :

SELECT *
  FROM (SELECT segment1, description, ponumber,
               ROW_NUMBER () OVER (PARTITION BY segment1 ORDER BY pocreationdate DESC) mycol
          FROM base_table
         WHERE TO_CHAR (pocreationdate, 'MM YYYY') = '04 2008')
         WHERE mycol = 1

DAY Validation

Hope you all know about SQL functions 'to_char' , 'trim' and 'length'

'to_char' used to convert a date or number type to a character type
'trim' mostly used to delete white spaces in a given string.(we can also trim character from a string . check the SQL oracle user guide)
'length' is used to get the length(no. of characters) of the given string

Please check the following queries to understand well about this

Assume today is MONDAY
select to_char(sysdate,'DAY') from dual;

will return MONDAY. we all know that

select length(to_char(sysdate,'DAY')) from dual;

should return 6. but it will return 9. the reason behind this is to_char(sysdate,'DAY') always return a string with 9 characters. so the MONDAY will have three white spaces at the end.

why its returning always the length of 9?
the answer is the maximum character a DAY can hold is 9(WEDNESDAY). so it always pad white spaces for the remaining length

to get the correct length use the following query

select length(trim(to_char(sysdate,'DAY'))) from dual;

Hope you can use these query in day validation.

Assume today is MONDAY
The following query will return 'Today is not Monday'.

set serveroutput on;
begin
if to_char(sysdate,'DAY')='MONDAY' then
dbms_output.put_line('Today is Monday');
else
dbms_output.put_line('Today is not Monday');
end if;
end;
/

To get the correct result ('Today is Monday') use the following query

set serveroutput on;
begin
if trim(to_char(sysdate,'DAY'))='MONDAY' then
dbms_output.put_line('Today is Monday');
else
dbms_output.put_line('Today is not Monday');
end if;
end;
/

Delete duplicate rows in a table

To delete the duplicate rows from a table.

Method 1 :

create table newtable as (select distinct * from oldtable);

drop table oldtable;

rename newtable to oldtable;

Method 2:

DELETE FROM mytable t1
      WHERE ROWID < (SELECT MAX (ROWID)
                       FROM mytable t2
                      WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2)