HackneyJunglist Blog

Tuesday, 24 May 2016

PL/SQL: Tips for Oracle SQL Developer:

Query to see log files that have been created and which user they have been allocated to

select * from S_SRM_TASK_HIST;

ALTER SESSION to replicate Siebel when running SQL Queries

Did you know that when you run queries directly on SQL developer or TOAD, it runs it with different parameters than what Siebel does? To ensure a similar performance and SQL execution as Siebel, run the following ALTER SESSION commands before running your query:

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

ALTER SESSION SET "_OPTIMIZER_SORTMERGE_JOIN_ENABLED" = FALSE;

ALTER SESSION SET "_OPTIMIZER_JOIN_SEL_SANITY_CHECK" = TRUE;

ALTER SESSION SET "_HASH_JOIN_ENABLED" = FALSE;

The following flushes the Data Cache for testing SQL performance again and again

You need the correct rights to run this!

alter system flush buffer_cache

Setting SQL Developer to use Datetime

Tools > Preferences > Database -- NLS Parameters

Datetime Format: DD-MON-RRRR HH24:MI:SS

Date Format: DD-MON-RR

Alternatively run the following command:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

Then you can run the following query without the TO_DATE function:

SELECT count(*)

FROM SIEBEL.S_EVT_FUL_REQ T1, SIEBEL.S_USER

WHERE T1.CREATED_BY = SIEBEL.S_USER.ROW_ID (+)

AND SIEBEL.S_USER.LOGIN IN ('REHMANA')

AND T1.CREATED >= '20/07/2014 07:00:00';

Using Dates in Queries

select COUNT(*) FROM SIEBEL.S_ORG_EXT

WHERE ACCNT_TYPE_CD = 'Provider'

AND X_HC_RENEWAL_DT >= TO_DATE('01/06/2013','DD/MM/YYYY')

AND X_HC_RENEWAL_DT < TO_DATE('01/09/2013','DD/MM/YYYY')

ORDER BY X_HC_RENEWAL_DT ASC;

UPDATING A DATE

UPDATE EVENT_FUND
SET CREATION_DATE = TO_DATE('01042016', 'DDMMYYYY')where EVENT_ID = '3003650'

Using sysdate in Queries

select attrib_03, attrib_04, count(ext.row_id) from siebel.s_srv_req base, siebel.s_srv_req_x ext

where base.row_id = ext.row_id

and base.created > sysdate-365

and sr_stat_dt is not null

group by attrib_03, attrib_04

order by count(ext.row_id) desc;

Updating Dates

UPDATE SIEBEL.S_CASE_XSET X_FAC_DUE_DT = to_date('27-APR-13', 'dd-mon-yy') WHERE PAR_ROW_ID = '1-BH0GR5'

Concatenating Columns in a select statement

select a.OU_ID || a.PROD_ID

from siebel.S_ORG_PROD a, siebel.S_PROD_INT b

where b.TYPE = 'GST'

and A.PROD_ID = b.ROW_ID

group by a.OU_ID || a.PROD_ID

having count(0) > 1

select distinct PER_TITLE || ' ' || FST_NAMe || ' ' || LAST_NAME, count(0)

from siebel.S_CONTACT

group by PER_TITLE || ' ' || FST_NAMe || ' ' || LAST_NAME

having count(0) > 1

order by count(0) desc

A Select query feeding into Select query

Example 1

select

T1.ROW_ID,

T1.CREATED,

T4.LOGIN AS CREATED_BY,

T1.LAST_UPD,

T3.LOGIN AS LAST_UPD_BY,

T1.DB_LAST_UPD,

T1.DB_LAST_UPD_SRC,

T1.MODIFICATION_NUM,

T1.PARTY_ID,

T1.REL_PARTY_ID,

T2.NAME

from SIEBEL.S_PARTY_REL T1

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T2

ON T1.PARTY_ID = T2.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_USER T3

ON T1.LAST_UPD_BY = T3.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_USER T4

ON T1.LAST_UPD_BY = T4.PAR_ROW_ID

WHERE PARTY_ID IN

(SELECT T1.PARTY_ID from SIEBEL.S_PARTY_REL T1

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T2 ON T1.PARTY_ID = T2.ROW_ID

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T3 ON T1.REL_PARTY_ID = T3.ROW_ID

WHERE T1.REL_TYPE_CD = 'Provider'

AND T2.ACCNT_TYPE_CD = 'Location'

AND T3.ACCNT_TYPE_CD = 'Provider'

GROUP BY T1.PARTY_ID, T2.NAME

having count(*) > 1)

AND T1.REL_TYPE_CD = 'Provider'

ORDER BY T1.CREATED ASC

Example 2

select ORG_ID, PROD_ID, LAST_UPD from
(SELECT
ORG_PROD.OU_ID ORG_ID,
ORG_PROD.PROD_ID PROD_ID,
ORG_PROD.LAST_UPD LAST_UPD,
PROD.TYPE, COUNT(*)
FROM SIEBEL.S_ORG_PROD ORG_PROD INNER JOIN SIEBEL.S_PROD_INT PROD ON PROD.ROW_ID=ORG_PROD.PROD_ID AND TYPE='GST'
GROUP BY ORG_PROD.OU_ID, ORG_PROD.PROD_ID, PROD.TYPE
HAVING COUNT(*) > 1)

order by ORG_ID, PROD_ID desc

Group By and Count on Two or more Columns

Select
Count(*),
B.NAME,
C.NAME
from SIEBEL.S_TMSHT_ITEM A
LEFT OUTER JOIN SIEBEL.S_PROJ B
ON A.PROJ_ID = B.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PROJITEM C
ON A.PROJITEM_ID = C.ROW_ID
GROUP BY B.NAME, C.NAME

select Count(*), A.PROC_NAME , A.STATUS_CD
FROM SIEBEL.S_WFR_PROC A
Left Outer Join SIEBEL.S_REPOSITORY B
ON A.REPOSITORY_ID = B.ROW_ID
WHERE B.NAME = 'Siebel Repository'
AND A.STATUS_CD = 'COMPLETED'
GROUP BY A.PROC_NAME, A.STATUS_CD
HAVING Count(*) > 1

Using MINUS to subtract Select Query Result a from Select Query Result B

--------All Providers that have one Active Location------
--------SELECT QUERY A: NOTE Only outputs one Column-----
select
A.ROW_ID
from SIEBEL.S_ORG_EXT A
INNER JOIN SIEBEL.S_ORG_EXT B
ON A.PAR_ROW_ID = B.PAR_OU_ID
-------Provider-----------------
WHERE A.OU_TYPE_CD = 'Primary Dental Care'
AND A.ACCNT_TYPE_CD = 'Provider'
AND A.CUST_STAT_CD = 'Registered'
-------Location-----------------
AND B.OU_TYPE_CD = 'Primary Dental Care'
AND B.ACCNT_TYPE_CD = 'Location'
AND B.CUST_STAT_CD = 'Active'
GROUP BY A.ROW_ID
having count(*) = 1
MINUS
------- All Providers that have a location with Product DEN
--------SELECT QUERY B: NOTE Only outputs one Column--------
select
A.ROW_ID
from SIEBEL.S_ORG_EXT A
INNER JOIN SIEBEL.S_ORG_EXT B
ON A.PAR_ROW_ID = B.PAR_OU_ID
LEFT OUTER JOIN SIEBEL.S_ORG_PROD C
ON B.PAR_ROW_ID = C.OU_ID
LEFT OUTER JOIN SIEBEL.S_PROD_INT D
ON C.PROD_ID = D.ROW_ID
-------Provider-----------------
WHERE A.OU_TYPE_CD = 'Primary Dental Care'
AND A.ACCNT_TYPE_CD = 'Provider'
AND A.CUST_STAT_CD = 'Registered'
-------Location-----------------
AND B.OU_TYPE_CD = 'Primary Dental Care'
AND B.ACCNT_TYPE_CD = 'Location'
AND B.CUST_STAT_CD = 'Active'
-------Product-------------------
AND D.NAME = 'DEN'

select A.INTEGRATION_ID FROM SIEBEL.S_ORG_EXT A LEFT OUTER JOIN SIEBEL.S_ORG_EXT C ON A.ROW_ID = C.PAR_OU_ID WHERE A.OU_TYPE_CD = 'Primary Medical Services' AND C.ACCNT_TYPE_CD = 'Location' AND C.CUST_STAT_CD = 'Active' GROUP BY A.INTEGRATION_ID HAVING Count(*) = 1 INTERSECT select A.INTEGRATION_ID FROM SIEBEL.S_ORG_EXT A LEFT OUTER JOIN SIEBEL.S_ORG_EXT_XM B ON A.ROW_ID = B.PAR_ROW_ID WHERE A.OU_TYPE_CD = 'Primary Medical Services' AND B.TYPE = 'Location' AND B.ATTRIB_04 = 'GP Practice' GROUP BY A.INTEGRATION_ID HAVING Count(*) = 1

Bringing back the n number of rows

Just include the following AND statement in the WHERE clause

AND rownum <= 29001

Updating Two Columns on a Table and avoiding two updates

UPDATE Table1 T1 SET
T1.name = (SELECT T2.name FROM Table2 T2 WHERE T2.id = T1.id),
T1.desc = (SELECT T2.desc FROM Table2 T2 WHERE T2.id = T1.id)
WHERE T1.id IN (SELECT T2.id FROM Table2 T2 WHERE T2.id = T1.id);

update EIM_POSITION a SET
a.PARTY_UID = (select b.ROW_ID from siebel.S_USER b where b.LOGIN = a.ACC_COMMENTS)
where a.ACC_COMMENTS in
(select b.LOGIN from siebel.S_USER b where a.ACC_COMMENTS = b.LOGIN)

Setting bring back a value according to a condition

SELECT
A.ROW_ID AS ORG_ROW_ID,
'UPDATE' AS ACTION,
CASE WHEN a.OU_TYPE_CD = 'NHS Healthcare Organisation' THEN '01/04/2010 00:00:00'
WHEN a.OU_TYPE_CD = 'Social Care Org' THEN '01/10/2010 00:00:00'
WHEN a.OU_TYPE_CD = 'Independent Healthcare Org'   THEN '01/10/2010 00:00:00'
WHEN a.OU_TYPE_CD = 'Primary Dental Care' THEN '01/04/2011 00:00:00'
WHEN a.OU_TYPE_CD = 'Independent Ambulance' THEN '01/04/2011 00:00:00'
WHEN a.OU_TYPE_CD = 'Primary Medical Services'   THEN '01/10/2011 00:00:00'
ELSE 'IGNORE'
END AS "NEW_DATE"
------------------------------------
from SIEBEL.S_ORG_EXT A
LEFT OUTER JOIN SIEBEL.S_ORG_PROD B
ON A.PAR_ROW_ID = B.OU_ID
LEFT OUTER JOIN SIEBEL.S_PROD_INT C
ON B.PROD_ID = C.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORG_EXT_X D
ON A.PAR_ROW_ID = D.ROW_ID
where
a.OU_TYPE_CD = 'NHS Healthcare Organisation'
AND b.WARRANTY_START_DT < TO_DATE('01/04/2010','DD/MM/YYYY')
AND C.TYPE = 'Regulated Activity';

Useful SQL to see all the Active Columns on a List Applet

SELECT
a.name,
c.name,
e.name as List_name,
e.field_name as Field_Name,
f.row_id,
g.type,
g.join_name,
g.col_name,
g.mvlink_name
FROM siebel.s_applet a
LEFT OUTER JOIN siebel.s_appl_web_tmpl c ON a.row_id = c.applet_id
LEFT OUTER JOIN siebel.s_list d on A.row_id = d.applet_id
LEFT OUTER JOIN siebel.s_list_column e on d.row_id = e.LIST_ID
LEFT OUTER JOIN siebel.s_buscomp f on A.buscomp_name = f.NAME
LEFT OUTER JOIN siebel.s_field g on f.row_id = g.buscomp_id
WHERE a.name IN('Service Request List Applet')
AND a.repository_id = '1-36VK-1'
AND c.repository_id = '1-36VK-1'
AND d.repository_id = '1-36VK-1'
AND d.repository_id = '1-36VK-1'
AND f.repository_id = '1-36VK-1'
AND c.name = 'Edit List'
AND e.HTML_TYPE IN ('Field','TextArea','CheckBox')
AND e.INACTIVE_FLG = 'N'
AND g.name = e.field_name

Working out if a field appears on any screen
Select distinct
A.NAME AS VIEW_NAME
--B.WEB_TEMPLATE AS WEB_TEMPLATE,
--C.NAME AS APPLET_NAME
FROM Siebel.S_VIEW A
LEFT OUTER JOIN Siebel.S_VIEW_WEB_TMPL B
ON B.VIEW_ID = A.ROW_ID
LEFT OUTER JOIN Siebel.S_VIEW_WTMPL_IT C
ON C.VIEW_WEB_TMPL_ID = B.ROW_ID
WHERE C.NAME IN
(-- SQL listing all List Columns
select
A.NAME AS APPLET_NAME
from SIEBEL.S_APPLET A
LEFT OUTER JOIN SIEBEL.S_APPL_WEB_TMPL B
ON B.APPLET_ID = A.ROW_ID
LEFT OUTER JOIN SIEBEL.S_APPL_WTMPL_IT C
ON C.APPL_WEB_TMPL_ID = B.ROW_ID
WHERE
--Code to find all relevant List Applet Controls
--Specifing the fields that are being searched for
((B.NAME = 'Edit List' AND (C.CTRL_NAME = 'Assigned To' OR C.CTRL_NAME = 'Owner') AND C.REPOS_TYPE = 'List Item')
OR
--Code to find all relevant Form Applet Controls
--Specifing the fields that are being searched for
(B.NAME = 'Edit' AND (C.CTRL_NAME = 'Assigned To' OR C.CTRL_NAME = 'Owner') AND C.REPOS_TYPE = 'FormattedHtml'))
)
--And additional filter on the View and it's name
AND A.NAME LIKE 'CQC%Enq%' OR A.NAME LIKE 'HC%Enq%'
ORDER BY A.NAME ASC

MAPs from LIST APPLET To BC To TABLE To COLUMN

select
Applet.NAME                           AS APPLET_NAME,
--AppletWebTemplate.NAME                AS WEB_TEMPLATE_NAME,
--AppletWebTemplateItem.CTRL_NAME       AS WEB_TEMPLATE_ITEM_CTRL_NAME,
--AppletList.NAME                       AS LIST_NAME,
--AppletList.ROW_ID                     AS LIST_ID,
--AppletListColumn.ROW_ID               AS APPLET_LIST_COLUMN_ROW_ID,
--AppletListColumn.LIST_ID              AS APPLET_LIST_COLUMN_LIST_ID,
--AppletListColumn.NAME                 AS LIST_COLUMN_NAME,
--AppletListColumn.DISPLAY_NAME_REF     AS DISPLAY_NAME_REF,
--SymbolicString.ROW_ID,
--SymbolicString.NAME                   AS SymbolicStringNAME,
--SymbolicStringTrans.STRING_VALUE    AS LABEL
CASE
    WHEN SymbolicStringTrans.STRING_VALUE IS NOT NULL THEN SymbolicStringTrans.STRING_VALUE
    ELSE AppletListColumn.DISPLAY_NAME
END                                   AS APPLET_FIELD_LABEL,
BusComp.NAME                          AS BUSCOMP,
Field.NAME                            AS FIELD_NAME,
BusComp.TABLE_NAME,
Field.COL_NAME,
--Field.COL_NAME
DBView.ALIAS AS DATABASE_VIEW,
DBViewColumn.ALIAS AS DATABASE_VIEW_COLUMN,
CONCAT(CONCAT(DBView.ALIAS,'_'),DBViewColumn.ALIAS) AS KEY

FROM SIEBEL.S_APPLET Applet
INNER JOIN SIEBEL.S_APPL_WEB_TMPL AppletWebTemplate ON AppletWebTemplate.APPLET_ID = Applet.ROW_ID
INNER JOIN SIEBEL.S_APPL_WTMPL_IT AppletWebTemplateItem ON AppletWebTemplateItem.APPL_WEB_TMPL_ID = AppletWebTemplate.ROW_ID
INNER JOIN SIEBEL.S_LIST AppletList ON AppletList.APPLET_ID = Applet.ROW_ID
INNER JOIN SIEBEL.S_LIST_COLUMN AppletListColumn ON AppletListColumn.LIST_ID = AppletList.ROW_ID
LEFT OUTER JOIN SIEBEL.S_SYM_STR SymbolicString ON AppletListColumn.DISPLAY_NAME_REF = SymbolicString.NAME
INNER JOIN SIEBEL.S_REPOSITORY Repository ON SymbolicString.REPOSITORY_ID = Repository.ROW_ID
LEFT OUTER JOIN SIEBEL.S_SYM_STR_INTL SymbolicStringTrans ON SymbolicStringTrans.SYM_STR_ID = SymbolicString.ROW_ID
INNER JOIN SIEBEL.S_BUSCOMP BusComp ON Applet.BUSCOMP_NAME = BusComp.NAME
INNER JOIN SIEBEL.S_FIELD Field ON Field.NAME = AppletListColumn.FIELD_NAME
INNER JOIN SIEBEL.S_TABLE DBView ON BusComp.TABLE_NAME = DBView.NAME
INNER JOIN SIEBEL.S_COLUMN DBViewColumn ON DBViewColumn.NAME = Field.COL_NAME
WHERE applet.NAME LIKE 'WP OES%List Applet'

AND applet.INACTIVE_FLG = 'N'
AND AppletListColumn.INACTIVE_FLG = 'N'
AND AppletWebTemplate.NAME = 'Edit List'
AND AppletList.NAME = 'List'
AND AppletWebTemplateItem.ITEM_NUM > 500
AND AppletWebTemplateItem.CTRL_NAME = AppletListColumn.NAME
AND Repository.NAME = 'Siebel Repository'
AND Field.BUSCOMP_ID = BusComp.ROW_ID
AND DBViewColumn.TBL_ID = DBView.ROW_ID

ORDER BY DBView.ALIAS DESC, DBViewColumn.ALIAS DESC
WORKFLOWS

SQL to find out if the current version of the Workflow.

Is the latest version in the Repository.

SELECT

wdd.RoW_ID,

wdd.created,

usr_crt.login created_login,

wdd.last_upd,

usr_upd.login updated_login,

wdd.NAME DEPLOYED_NAME,

wdd.REPOSITORY_VERSION DEPLOYED_VERSION,

wdd.deploy_status_cd DEPLOYED_STATUS,

REP_WF.NAME AS REP_NAME,

REP_WF.VERSION REP_VERSION,

REP_WF.STATUS_CD REP_STATUS

FROM siebel.s_wfa_dploy_def wdd,

siebel.s_user usr_crt,

siebel.s_user usr_upd,

SIEBEL.S_WFR_PROC REP_WF,

SIEBEL.S_REPOSITORY REPOS

WHERE wdd.created_by = usr_crt.row_id

AND wdd.last_upd_by = usr_upd.row_id

AND REP_WF.NAME = concat(concat(wdd.NAME , ': '), wdd.REPOSITORY_VERSION)

AND REP_WF.REPOSITORY_ID = REPOS.ROW_ID

AND wdd.deploy_status_cd = 'ACTIVE'

AND wdd.NAME NOT LIKE '%Simulation%'

AND REPOS.NAME = 'Siebel Repository'

AND REP_WF.STATUS_CD <> 'COMPLETED'

Running SQL a SCRIPT to generate results in DBMS Output Tab

DECLARE

V_RESULT VARCHAR2(7);

BEGIN

SELECT COUNT(*) INTO V_RESULT FROM SIEBEL.S_ACTIVITY_ATT