1
You can get the TZD abbreviations with this query:
SELECT tzabbrev, tzname, TZ_OFFSET(tzname)
FROM V$TIMEZONE_NAMES
WHERE tzname = 'America/Edmonton'
TZABBREV TZNAME TZ_OFFSET(TZNAME)
---------- ------------------ ------------------
LMT America/Edmonton -06:00
MST America/Edmonton -06:00
MDT America/Edmonton ...
1
WITH temp_mytbl AS ( -- without aggregation in output expression / HAVING clause
-- GROUP BY must be replaced with DISTINCT
-- which is less expensive
SELECT DISTINCT iex_id, dte, agent_name, schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum
)
...
1
Your own solution might be suboptimal but I am not sure there is a way to solve the problem elegantly and/or efficiently without at least some redundancy. I managed to avoid hitting the customers table more than once, but I still had to reference multiqueue twice. This is the query I ended up with:
SELECT
q.ID,
q.Content,
q.Volume,
q.CustomerID,
...
1
For recent versions, you can use window functions:
SELECT ID, CustomerID, Volume, Content, runtot
FROM (
SELECT ID
, CustomerID
, Content
, sum(Volume) over (
partition by CustomerID
order by ID
) as runtot
FROM multiqueue
) AS q1
WHERE runtot < 2000;
EDIT:
If you want at ...
Only top voted, non community-wiki answers of a minimum length are eligible
