Select * from tab where tname like '%%';
In Advanced-->Covert this filter to Sql in Create/Edit Filter
date '2021-07-02'
For Service dates since 90 days ="Invoice Detail"."Service Date" Between VALUEOF(V_CURR_DATE) and
TimeStampAdd(SQL_TSI_DAY, VALUEOF(V_CURR_DATE) , 90)
========ls===================================================================================================================
Select To_sdate(To_char(Sysdate,'MM-DD-YYYY'),'MM-DD-YYYY') as CurDate,
to_char(Sysdate,'Month') as Month,
to_char(Sysdate,'YYYY') as Year from dual
retrieve the Oracle version information: SELECT * FROM v$version; OR SELECT * FROM v$version WHERE banner LIKE 'Oracle%';
Select To_date(To_char(Sysdate-1,'MM-DD-YYYY'),'MM-DD-YYYY') as PrevDate,
TO_CHAR(add_months(sysdate,-1),'MONTH') as PrevMonth,
to_char(ADD_MONTHS(Sysdate,-12),'YYYY') as PrevYear from dual
select to_char(trunc(sysdate,'MM'),'mm/dd/yyyy')from dual-----Start Date of the Month
select trunc(add_months(sysdate,0),'MM') from dual-----Start Date of the Month
SELECT LAST_DAY(sysdate) FROM dual--------Last Day of the Month
select to_char((next_day(sysdate, 'SAT')-6)-7) from dual----Last week First Day(Sunday)
select to_char(next_day(sysdate, 'SAT')-7) from dual------Last week Last Day(Saturday)
select to_char(next_day(sysdate, 'SAT')-6) from dual---start of the Current week(Sunday)
select to_char(next_day(sysdate, 'SAT')) from dual----Last Day of the Current week(Saturday)
select to_char(next_day(sysdate, 'SAT')-6)|| ' to ' ||to_char(next_day(sysdate, 'SAT')) this_week from dual----Current week dates between start date and end date
select to_char((next_day(sysdate, 'SAT')-6)-7)|| ' to ' ||to_char(next_day(sysdate, 'SAT')-7) last_week from dual----Last week dates between start date and end date
select trunc(add_months(sysdate,-1),'MM') from dual-----First Day of Last Month
select LAST_DAY(add_months(sysdate,-1)) from dual-------Last Day of Last Month
select EXTRACT(MONTH from add_months(sysdate,-1) ) from dual----Last Month Digit(Num)
select TO_CHAR(add_months(sysdate,-1),'MONTH') from dual----Last Month Name
select add_months(sysdate,-1) from dual----Last month same date
select to_char(trunc(sysdate,'MM')+14,'mm/dd/yyyy') day from dual;--------to find 15th day of the month
select to_char(next_day(sysdate, 'SAT')-5) from dual
select to_char(next_day(sysdate, 'FRI')) from dual
============================================================================================================================
V_CURR_DATE_INIT
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY ,
DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))s
"Invoice Detail"."Service Date" Between VALUEOF(IstDayOfLstMon)
and TimeStampAdd(SQL_TSI_DAY, VALUEOF(LstDayOfLstMon) ,'MM')
For Oc Msg Report: Service Date between 08/01/2010 and 08/31/2010
"Invoice Detail"."Service Date" Between VALUEOF(IstDayOfLstMon)
and VALUEOF(LstDayOfLstMon)
"Flight Leg"."Date Of Operation"
----------------------------------------------------------------------------
ops$tkyte%ORA10GR2> select the_date,
2 to_char(next_day(the_date, 'SAT'))|| ' to ' ||to_char(next_day(the_date, 'SAT')-6)
this_week,
3 to_char((next_day(the_date, 'SAT')-6)-7)|| ' to ' ||to_char(next_day(the_date,
'SAT')-7) last_week,
4 to_char(next_day(the_date, 'SAT')+7)|| ' to ' ||to_char(next_day(the_date, 'SAT'))
next_week
5 from (select sysdate+rownum-1 the_date from all_users where rownum <= 7)
6 /
ops$tkyte%ORA10GR2> select the_date,
2 begin_this_week-7 begin_prior_week, end_this_week-7 end_prior_week,
3 begin_this_week, end_this_week,
4 begin_this_week+7 begin_next_week, end_this_week+7 end_next_week
5 from
6 (
7 select the_date,
8 next_day(the_date-7,'mon') begin_this_week, next_day(the_date-2,'sat')
end_this_week
9 from (select sysdate+rownum-1 the_date from all_users )
select week_number,lag(my_date,1) over ( order by my_date ) Start_week ,my_date end_week from
(select trunc(add_months(sysdate,-to_char(sysdate, 'MM') - 12),'y') +r My_Date,
to_char(trunc(add_months(sysdate,-to_char(sysdate, 'MM') - 12),'y') + r,'YYYY') || ' Week# ' ||
to_char(trunc(add_months(sysdate,-to_char(sysdate, 'MM') - 12),'y') + r-1,'IW') Week_Number
from ( select (rownum-1)*7 r
from all_objects
)) W
---------------------------------------------
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
First Day of Year: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFYEAR(CURRENT_DATE )-1) , CURRENT_DATE )
First Day of Quarter: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAY_OF_QUARTER(CURRENT_DATE )-1) , CURRENT_DATE )
First Day of Month: TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE )
Query For Current Year and Quarter and Month
SELECT
TRUNC(cast(TO_CHAR(SYSDATE,'YYYY') as VARCHAR(20))),
TRUNC(CAST(TO_CHAR(SYSDATE,'Q') AS VARCHAR(20))),
TRUNC(CAST(TO_CHAR(SYSDATE,'MM') AS VARCHAR(20)))
FROM DUAL;
=================================================================
select 'Q4' qtr,trunc(sysdate,'Q') start_date,add_months(trunc(sysdate,'Q'),3) - 1 end_date from dual
union
select 'Q3',add_months(trunc(sysdate,'Q'),-3),trunc(sysdate,'Q') - 1 from dual
union
select 'Q2',add_months(trunc(sysdate,'Q'),-6),add_months(trunc(sysdate,'Q'),-3) - 1 from dual
union
select 'Q1',add_months(trunc(sysdate,'Q'),-9),add_months(trunc(sysdate,'Q'),-6) - 1 from dual
order by 1 desc
select add_months(trunc(sysdate,'Q'),-9) from dual-----Q1
select add_months(trunc(sysdate,'Q'),-6) from dual-----Q2
select add_months(trunc(sysdate,'Q'),-3) from dual-----Q3
select trunc(sysdate,'Q') from dual-----------------------------------Q4
select add_months(trunc(sysdate,'Q')+4,-9) from dual-----5th day of Q1
select add_months(trunc(sysdate,'Q')+4,-6) from dual-----5th day of Q2
select add_months(trunc(sysdate,'Q')+4,-3) from dual-----5th day of Q3
select trunc(sysdate,'Q')+4 from dual-----------------------------------5th day of Q4
select add_months(trunc(sysdate,'Q')+4,-9),add_months(trunc(sysdate,'Q')+4,-6),
add_months(trunc(sysdate,'Q')+4,-3),trunc(sysdate,'Q')+4 from dual
VALUEOF("5thdayofQ1")
select * from esvprod2.inv_header
select * from esvprod2.Inv_Header_Pmnt_Req_Remittance
select * from esvprod2.Inv_Remittance
select * from esvprod2.Inv_Error
select * from esvprod2.Esv_Error_Code
select * from esvprod2.Inv_status
Alias_Esv_Error_Code.ESV_ERROR_CODE_ID = Alias_Inv_Error.ESV_ERROR_CODE_ID
Alias_Inv_Header.INV_HEADER_ID = Alias_Inv_Error.INV_HEADER_ID
Alias_Inv_Status.INV_STATUS_ID = Alias_Inv_Header.INV_STATUS_ID
Alias_Inv_Header_Pmnt_Req_Remittance.INV_REMITTANCE_ID = Alias_Inv_Remittance.INV_REMITTANCE_ID
Alias_Inv_Header.INV_HEADER_ID = Alias_Inv_Header_Pmnt_Req_Remittance.INV_HEADER_ID
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
for triggering the 5thDay of EveryQtr
case when CURRENT_DATE = VALUEOF("5thdayofQ4") then "Invoice Detail"."Service Date" else 0 end
and in filter is not equal to / is not in 0
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
select trunc(DEP_DATETIME_ACTUAL_LOCAL) from esvprod2.ESV_DAILY_FLT_LEG --03-JUL-10
select to_char(DEP_DATETIME_ACTUAL_LOCAL,'HH24:MI') from esvprod2.ESV_DAILY_FLT_LEG --03-JUL-10
to_date('10-12-06','MM-DD-YY')
to_date('jan 2007','MON YYYY')
to_date('2007/05/31','YYYY/MM/DD')
to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI')
to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS')
to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss')
to_date('022002','mmyyyy')
to_date('12319999','MMDDYYYY')
to_date(substr( collection_started,1,12),'DD-MON-YY HH24')
to_date('2004/10/14 21', 'yyyy/mm/dd hh24')
TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60)
TRUNC(TO_DATE(W_INSERT_DT,'DD-MM-YY HH24:MI:SS'))='18-08-14'
SELECT column_name FROM ALL_TAB_COLUMNS where COLUMN_NAME like '%WID%' AND table_Name = 'WC_IPM_CONTRACT_TRANSACTION_F';
SELECT column_name,table_Name FROM ALL_TAB_COLUMNS where COLUMN_NAME like '%RIGHT%' AND owner ='FIPMS'
select count(*),INTEGRATION_ID,DATASOURCE_NUM_ID from WC_CDW_ACTIVITY_F group by INTEGRATION_ID,DATASOURCE_NUM_ID having count(*)>1;
select * from all_objects where object_name like 'DP_%';
select * from user_db_links;
select * from all_objects Where OWNER='OLAP_11G' and object_type='TABLE';
select * from all_objects where OWNER='GCW12C_BIPLATFORM' and object_type='TABLE' and object_name like 'S_NQ_%';
select * from all_users where username like 'MTA%' order by created desc;
select * from all_objects where object_name like 'ALL%PRIV%'order by 2;
select * from ALL_TAB_PRIVS_MADE where GRANTEE ='MTA';
---============================================
Current Month: MON-YY format
concat(concat(UPPER(MONTHNAME(current_date)), '-'),SUBSTRING(cast(YEAR(current_date)as char) FROM 3 FOR 4))
First Day of the Previous Year:
TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.
First Day of the Current Year:
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
This calculation returns the first day of the year by deducting one less than the total number of days in the year.
First Day of the Next Year:
TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.
First Day of the Previous Month:
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.
First Day of the Current Month:
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
SELECT TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) FROM "Project - Cost"
This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.
First Day of the Next Month:
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.
Last Day of the Previous Month:
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.
Last Day of Current Month:
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
SELECT TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) FROM "Project - Cost"
From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.
Last Day of the Next Month:
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.
Last Day of Previous Year:
TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.
Last Day of Current Year:
TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.
Last Day of the Next Year:
TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.
First Day of Current Quarter:
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.
Last Day of Current Quarter:
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.
Number of days between First Day of Year and Last Day of Current Month:
TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))
For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn't want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.
select a.integration_id,a.effective_from_dt, a.effective_to_dt, a.* from w_employee_d a Where a.Integration_id like '%\_%' ESCAPE '\'
order by 1;
To Verify the User sessions:
select sid,username,to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss') logon_time from v$session where username not in ('SYS','SYSTEM','DBSNMP') and status='ACTIVE';
select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner;---Check the size of User or Schema in Oracle.
To enforce a complete refresh of the MV:
BEGIN
dbms_mview.refresh('WC_SALES_ORD_LINE_ATT_MV', method => 'C', atomic_refresh => FALSE);
END;
To verify the active reporting DB/session:
Select * from V$instance;
WITH Clause:
The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
This article shows how the WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to retrieve the required information. They merely demonstrate the use of the WITH clause.
Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following. From <https://oracle-base.com/articles/misc/with-clause>
-- Non-ANSI Syntax
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc
WHERE e.deptno = dc.deptno;
-- ANSI Syntax
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e
JOIN (SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc
ON e.deptno = dc.deptno;
Using a WITH clause this would look like the following.
-- Non-ANSI Syntax
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
dept_count dc
WHERE e.deptno = dc.deptno;
-- ANSI Syntax
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e
JOIN dept_count dc ON e.deptno = dc.deptno;
WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.
WITH
dept_costs AS (
SELECT dname, SUM(sal) dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;
select (select count(*) c from W_SALES_ORDER_LINE_F) W_SALES_ORDER_LINE_F,
(select count(*) c from WC_OPM_MATLXACT_F) WC_OPM_MATLXACT_F
from dual;
