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