CODEWITHSUNDEEP
sqlloops
Haye
Haye

Reputation: 45

How to loop select statement based on the previous select statement?

enter image description here I need all the data to be in one straight line.

I need to create a report and for that i need to summarize all. So i did this select statement first. Query1

Select  t1.scn,t1.vsl_name,  t1.act_arr_dt_tm, t1.act_dept_dt_tm, t1.del_remarks
from vesvoy t1

So based on the select statement above i need to get all the t1.scn to loop the below sql

Query2

Select  t1.scn,t2.void_flg,
MAX(case when t2.inv_num like 'VI%' then t2.inv_num end) as Vessel,
MAX(case when t2.inv_num like 'VI%' then t2.inv_amt end) as Vessel_amt
from pbosinvoiceitem t1
inner join pbosinvoice t2 ON t2.id = t1.master_id
inner join pbosinvtype t4 ON t4.code = t2.inv_type
group by t1.scn,t2.void_flg

so that i can get the result like in the report. I have try to create temp table but the data that i get is all duplicate.

I try combining both queries but the result shows duplicate result

Upvotes: 1

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

I'm sitting here thinking: "What does the first query have to do with the second?" Both are on the same table, vesvoy, so what could the question be. The second is processing the same rows as the first.

I suspect the issue is that the joins are losing rows. So, I suspect that the answer is use left join, rather than inner join. Along the way, get rid of the select distinct. This is generally a bad idea. In combination with a group by on the same non-aggregated columns, it just shows a lack of awareness of SQL.

So, does this address your concern?

SELECT t1.scn,
       MAX(case when t3.inv_num like 'VI%' then t3.inv_num end) as Vessel,
       MAX(case when t3.inv_num like 'VI%' then t3.inv_amt end) as Vessel_amt
FROM vesvoy t1 LEFT JOIN
     pbosinvoiceitem t2
     ON t2.scn = t1.scn LEFT JOIN
     pbosinvoice t3
     ON t3.id = t2.master_id
GROUP BY t1.scn;

This will return NULL for the non-matching rows.

Upvotes: 1

Related Questions