below query will tell the date between which you want to extract record.
select (next_day(TRUNC(sysdate),’Mon’)-14), (next_day(TRUNC(sysdate),’SUN’)-7) from dual
so your query will be:
Column_Name between (next_day(TRUNC(sysdate),’Mon’)-14) and (next_day(TRUNC(sysdate),’SUN’)-7)
Note: you can adjust your week by changing the value in day (Mon,Sat………)
This document is intended to provide cause and corrective action information about Siebel Error Message SBL-ADM-01042: Login failed for specified username, password, and ODBC datasource combination.
The Server Manager (ServerMgr) component could not login to the database using the ODBC datasource configured for the enterprise, and using the login name and password you provided.
Retry the connection, verifying the username and password specified. Check the Server Manager (ServerMgr) log files to determine whether the ODBC datasource has been specified correctly. Use the odbcsql utility to test the odbc datasource, username and password.
This article discusses the problematic use of BETWEEN when comparing dates in SQL and shows a better method for date range comparison.
Note: All the below SQL is written for Oracle Database.
Lets say that we need SQL to query for all activities (S_EVT_ACT) that has a Start Date (TODO_PLAN_START_DT) between 01-Jan-2010 and 31-Mar-2010. If the SQL was written as below this would be incorrect:
SELECT ACTIVITY_UID
FROM S_EVT_ACT
WHERE TODO_PLAN_START_DT BETWEEN TO_DATE(’01-JAN-10′) AND TO_DATE(’31-MAR-10′);
The problem is that the date specified as ’01-JAN-10′ is really saying: 01-JAN-10 12:00 AM and ’31-MAR-10′ is really saying: 31-MAR-10 12:00 AM. If you had an activity with TODO_PLAN_START_DT = 31-MAR-10 09:50 AM this activity would not be included. The above SQL will only look for activities that are on or before 12AM on 31-MAR-10 – which is actually excluding all of 31-MAR-10. Unless you had an activity which started at 12AM.
The correct way to write the SQL is always like this:
SELECT ACTIVITY_UID
FROM S_EVT_ACT
WHERE TODO_PLAN_START_DT >= TO_DATE(’01-JAN-10′)
AND TODO_PLAN_START_DT < TO_DATE(’31-MAR-10) + 1;
This will ensure that all of 01-JAN-10 is included and also that all of 31-MAR-10 will be included in the range.
Converting UTC Dates
In Siebel we often use UTC dates for field types. We can determine if a Siebel BC Field’s column is stored in UTC because it will have Type: DTYPE_UTCDATETIME.
So when we need to write SQL as above for a Siebel Field’s column that is stored in UTC we would need to convert the UTC stored column to ensure we get the correct range. So lets say we are in Australian Eastern Standard Time (like me) we could write the above SQL like this if TODO_PLAN_START_DT is stored in UTC:
SELECT ACTIVITY_UID
FROM S_EVT_ACT
WHERE CAST((FROM_TZ(CAST(TODO_PLAN_START_DT AS TIMESTAMP), ‘GMT’) AT TIME ZONE ‘Australia/NSW’) AS DATE) <= TO_DATE(’01-JAN-10′)
AND CAST((FROM_TZ(CAST(TODO_PLAN_START_DT AS TIMESTAMP), ‘GMT’) AT TIME ZONE ‘Australia/NSW’) AS DATE) < TO_DATE(’31-MAR-10) + 1;
However the above SQL may have problematic performance because it would not use any index on the TODO_PLAN_START_DT column, a full table scan would occur. This is because we have manipulated the predicate column (TODO_PLAN_START_DT) with functions which prevents index usage. It is always best practice to modify the non-predicate column so instead of converting the TODO_PLAN_START_DT from UTC to local time, we will convert our local time to UTC as such:
SELECT ACTIVITY_UID
FROM S_EVT_ACT
WHERE TODO_PLAN_START_DT <= CAST((FROM_TZ(CAST(TO_DATE(’01-JAN-10′) AS TIMESTAMP), ‘Australia/NSW’) AT TIME ZONE ‘GMT’) AS DATE)
AND TODO_PLAN_START_DT < CAST((FROM_TZ(CAST(TO_DATE(’31-MAR-10′) AS TIMESTAMP), ‘Australia/NSW’) AT TIME ZONE ‘GMT’) AS DATE) + 1;
This article shows how to build a SQL to identify Siebel MVG duplicate records specifically identifying the MVG duplicate that is not the primary. This allows us to easily identify the duplicate MVG record we wish to delete without risk of deleting the primary MVG record. We do not want risk of deleting the primary MVG record because any MVF based on this MVG would display as blank if the MVG has no primary record.
If I want to identify the Contact MVG (S_CONTACT_XM) duplicate records in an Oracle DB I can run SQL:
SELECT *
FROM
(
SELECT
xm.PAR_ROW_ID
,xm.NAME
,ROW_NUMBER() OVER (PARTITION BY xm.PAR_ROW_ID || xm.NAME || xm.TYPE ORDER BY NULL) RID
,xm.ROW_ID
,xm.TYPE
FROM S_CONTACT_XM xm
)
WHERE RID > 1;
This SQL will perform a ROW_NUMBER partition based on the user key of S_CONTACT_XM (PAR_ROW_ID, NAME, TYPE). Therefore any record with RID > 1 is a duplicate based on this partition.
Lets say for a particular S_CONTACT_XM.TYPE = ‘Type1′ the primary id column on the parent contact is S_CONTACT.X_PR_TYPE1_ID. The SQL above cannot guarantee that the duplicate records selected are not the primary. Therefore deleting them may result in no primary for that MVG any longer. We can update our SQL to query for the duplicates first, then requery the entire duplicate set by filtering based on the initial query set and assign a value to a new column (ISPRIMARY) of value 1 if the MVG record is a primary and 0 if it is not. Now we have a list of each duplicate pair with ISPRIMARY = 1 if it is a primary and 0 if it is not:
select (
CASE
WHEN (xm.ROW_ID = con.X_PR_TYPE1_ID)
THEN (1)
ELSE (0)
END
) ISPRIMARY,
xm.*,
con.X_PR_TYPE1_ID
from s_contact_xm xm, s_contact con
where con.ROW_ID = xm.PAR_ROW_ID
and xm.NAME || xm.PAR_ROW_ID || xm.TYPE
IN
(
SELECT rec.NAME || rec.PAR_ROW_ID || rec.TYPE
from
(
SELECT xm.PAR_ROW_ID
,xm.NAME
,ROW_NUMBER() OVER (PARTITION BY xm.PAR_ROW_ID || xm.NAME || xm.TYPE ORDER BY NULL) RID
,xm.TYPE
FROM S_CONTACT_XM xm
where xm.type=’Type1′
) rec
where rec.RID > 1
)
Now to out of the above data set we want to only select one record out of the duplicate pair, the one that is not a primary. So we use the above query as our data set, query this data set adding another partition based on the user key PAR_ROW_ID, NAME, TYPE and order this partition by the ISPRIMARY DESC, this way the primary duplicate MVG record will appear first. Now if we only select filter only records were the partition RID > 1 then we will never select the first (primary) MVG duplicate record:
SELECT ROW_ID
from
(
select ROW_NUMBER() OVER (PARTITION BY r.PAR_ROW_ID || r.NAME || r.TYPE ORDER BY r.ISPRIMARY DESC) RID,
r.ROW_ID
FROM
(
select (
CASE
WHEN (xm.ROW_ID = con.X_PR_TYPE1_ID)
THEN (1)
ELSE (0)
END
) ISPRIMARY,
xm.*,
con.X_PR_TYPE1_ID
from s_contact_xm xm, s_contact con
where con.ROW_ID = xm.PAR_ROW_ID
and xm.NAME || xm.PAR_ROW_ID || xm.TYPE
IN
(
SELECT rec.NAME || rec.PAR_ROW_ID || rec.TYPE
from
(
SELECT xm.PAR_ROW_ID
,xm.NAME
,ROW_NUMBER() OVER (PARTITION BY xm.PAR_ROW_ID || xm.NAME || xm.TYPE ORDER BY NULL) RID
,xm.TYPE
FROM S_CONTACT_XM xm
where xm.type=’Type1′
) rec
where rec.RID > 1
)
) r
)
WHERE RID > 1;
