create table process_table(row_id,claim_id,seq_num) as ( SELECT
w.claim_id,w.seq_num FROM ( SELECT row_number() over(),DISTINCT
claim_id,seq_num,DENSE_RANK() OVER (PARTITION BY
a.icn_num,a.trans_type_cd,a.program_type_cd,b.line_item_cd,b.line_item_sub_cd
ORDER BY b.claim_id,b.seq_num )
FROM
claims a,claim_lines b
WHERE   b.client_cd='320'
AND a.client_cd = b.client_cd
AND a.claim_id  = b.claim_id
AND b.error_cd != 'CA5007'
AND a.error_cd != 'CA5007'
AND (a.logical_delete_ind <>'Y') AND    (b.logical_delete_ind <>'Y') )
w WHERE w.row_num>1 )
data initially deferred refresh deferred

[IBM][CLI Driver][DB2/NT64] SQL0206N  "DISTINCT" is not valid in the
context where it is used.  SQLSTATE=42703


Can someone help in rewriting this so it works? I am struggling for
sometime now..

On Dec 1, 7:18=A0pm, Arun Srini <arunro...@gmail.com> wrote:
> create table process_table(row_id,claim_id,seq_num) as ( SELECT
> w.claim_id,w.seq_num FROM ( SELECT row_number() over(),DISTINCT
> claim_id,seq_num,DENSE_RANK() OVER (PARTITION BY
> a.icn_num,a.trans_type_cd,a.program_type_cd,b.line_item_cd,b.line_item_su=
b_cd
> ORDER BY b.claim_id,b.seq_num )
> FROM
> claims a,claim_lines b
> WHERE =A0 b.client_cd=3D'320'
> AND a.client_cd =3D b.client_cd
> AND a.claim_id =A0=3D b.claim_id
> AND b.error_cd !=3D 'CA5007'
> AND a.error_cd !=3D 'CA5007'
> AND (a.logical_delete_ind <>'Y') AND =A0 =A0(b.logical_delete_ind <>'Y') =
)
> w WHERE w.row_num>1 )
> data initially deferred refresh deferred
>
> [IBM][CLI Driver][DB2/NT64] SQL0206N =A0"DISTINCT" is not valid in the
> context where it is used. =A0SQLSTATE=3D42703
>
> Can someone help in rewriting this so it works? I am struggling for
> sometime now..

Thanks guys.. corrected myself....

ConnTime..:     08:06:19.623    UOW Start.:     08:28:20.926    Appl
name.:            db2bp    DB2 user..:         BCULINP
               OS user...:          bculinp    Agent id..:
55272    Coord DBP.:                0    Coord id..:            77259
               Client pid:             9467    Hash
joins:                0    Hash loops:                0    HJoin
ovf.:                0
               SQL Stmts.:                6
Sorts.....:                0    Sort time.:            0.000    Sorts
ovf.:                0
               Rows Read.:    1,451,899,214    Rows
Sel..:                0    Read/Sel..:                0    Rows
Wrtn.:    2,487,331,321
               Rows Ins..:                0    Rows
Upd..:                0    Rows Del..:                0    Locks
held:              156
               Trans.....:              105    Open
Curs.:                0    Rem Cursor:                0
Memory....:            18.8M
               Dyn. SQL..:                8    Static
SQL:                8    Cpu Time..:         8915.551
AvgCpuStmt:          557.221



This is a snapshot from the db2top command in linux. The above said
mqt is running. How can I know the progress? how much it has written,
like load query command or something?