jeudi 25 août 2016

MS SQL error selecting count on a subquery

I am trying to select the count of the number of rows returned on a query. The query is

Select a.itm_cd, max(b.doc_num) ,max(c.text) 
from ist b,itm_trn a, ist_cmnt c  
where a.ist_seq_num = b.ist_seq_num 
and a.ist_seq_num = c.ist_seq_num  
and a.ist_wr_dt = b.ist_wr_dt 
and a.new_loc_cd  like 'BOX115' 
and a.ITT_CD = 'XFR' and a.create_dt >'21-AUG-16' 
group by a.itm_cd;

For this particular query I return 3 rows, I need to write a query that returns how many rows are returned.

I have tried this :

Select count(*) from 
(Select a.itm_cd, max(b.doc_num) ,max(c.text) 
from ist b,itm_trn a, ist_cmnt c  
where a.ist_seq_num = b.ist_seq_num 
and a.ist_seq_num = c.ist_seq_num  
and a.ist_wr_dt = b.ist_wr_dt 
and a.new_loc_cd  like 'BOX115' 
and a.ITT_CD = 'XFR' and a.create_dt >'21-AUG-16' 
group by a.itm_cd);

This results in a syntax error

msg 102, level 15, state 1 line 1  incorrect syntax near ')'.

I am not sure what I am doing wrong, I have a similar sql statement that works this way in oracle, but have not found where I am messing up in MSSQL

Aucun commentaire:

Enregistrer un commentaire