Hi Stephanie,
.IMPORT INFILE Untitled
AXSMOD Oledb_Axsmod 'noprompt'
LAYOUT Layout1
APPLY LabelA WHERE ICA_ID = 101;
Btw, there's a forum for Teradata at Tek-Tips
http://www.tek-tips.com/threadminder.cfm?pid=328
and there's *the* best source of...
Hi randyvol,
"the modify user statements appear to have removed the journals"
No, they didn't. There was an error, because MODIFY USER is DDL and you can't submit DDL within a multistatement, just read the error message :-)
And even if it worked, the journal would still be there;
"modify user...
Hi zanzemaj,
"diagnostic helpstats on for session" is a valid SQL statement, so just submit it.
Some remarks:
Replace UNION with UNION ALL
Try to replace the UNION Derived Tables with a single access to those 4 tables, the main difference is just this year/last year.
Btw, "total estimated time...
Hi zanzemaj,
it looks like there are some statistics missing, because there are lots of "no confidence". And V2R6.1 is quite sensible for missing stats...
First try a "diagnostic helpstats on for session" and check explain for recommended stats...
And better show the query itself and the DDL...
select
a.id,a.effdt,a.e_status,a.act,
b.effdt,b.e_status,b.act
from
(
select
id,effdt,e_status,act,
(select min(effdt)
from x_ps_job a2
where a2.act = 'T'
and a2.effdt > a.effdt) as nextdt
from
x_ps_job a
where
a.emplid = '1234567'
and a.act in...
SQL Server 2000:
select
RECORDID,
SERVICE,
TRANSACTION
from tab join
(select
SERVICE,
max(TRANSACTION) as maxtran
from tab
group by SERVICE
) dt
on tab.SERVICE = dt.SERVICE
and tab.TRANSACTION = dt.maxtran
SQL Server 2005 using SQL:1999 OLAP functions:
select...
If you're using Oracle then you have to get rid of the "AS" before an alias.
But instead of doing some complicated query just use the existing OLAP-functions:
select ...
count(*) over (partition by tab1.col1)
from tab1 join tab2 on tab1.col1 = tab2.col2
Dieter
If your system runs in ANSI mode it should be quite similar to DB2. Check the manuals, especially the ANSI session stuff in:
SQL Reference, Statement and Transaction Processin
Chapter 7: Locking and Transaction Processing
If your system runs in Teradata mode, then it's totally different...
Dieter
The SQL statement is not echoed to the file, you probably mean the column header. To get rid of it use (TITLE '')
.export report file = bla;
select
col1 || '|' || col2 || ',' || ... (TITLE '')
from tab;
Dieter
Using SQL:1999 or SQL:2003
UPDATE TABLE_A A
SET Issue_Flag = '1'
WHERE
EXISTS
(
SELECT * FROM TABLE_B B
WHERE B.Account_Id = A.Account_Id
)
AND 1 =
(
SELECT
ROW_NUMBER() OVER (PARTITION BY Account_ID
ORDER BY Sub_Account_ID DESC...
This should be close:
SELECT
...
CASE WHEN uga.ugid IS NOT NULL THEN 'Y' ELSE 'N' END,
CASE WHEN ugl.ugid IS NOT NULL THEN 'Y' ELSE 'N' END
FROM ug LEFT JOIN u ON ...
(LEFT?) JOIN s ON ...
LEFT JOIN uga ON ug.ugid = ugl.ugid AND u.uid = uga.uid
LEFT JOIN ugl ON ug.ugid = ugl.ugid AND...
select *
from tab
qualify
percent_rank() over (order by sales desc) <= 0.2
Easy to enhance with PARTITION BY, e.g.
percent_rank() over (order by sales desc partition by year) <= 0.2
to get top 20% per year...
Dieter
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.