[HELP]Subquery Foxpro di delphi

Beranda
Komunitas
Tech
Programmer Forum
[HELP]Subquery Foxpro di delphi

tyasdwedwe

08-12-2013 06:49

gan help ane coba bikin sub query ga ada yang jalan2..

Code:

Form1.Query3.SQL.Add('SELECT tblabc.chdrnum, tblabc.fund_name, tblcov01.dasar, tblcov02.topup ');

 Form1.Query3.SQL.Add('FROM (SELECT chdrnum, fund_name FROM '+FileNameonly+' WHERE chdrnum = "'+chdrnum+'" GROUP BY fund_name ) AS tblabc ');

 Form1.Query3.SQL.Add('LEFT JOIN ( SELECT chdrnum, sum( unit_alloc ) AS dasar, coverage FROM '+FileNameonly+' WHERE coverage = "01" AND chdrnum = "'+chdrnum+'" GROUP BY fund_name AS tblcov01 ON tblabc.chdrnum = tblcov01.chdrnum ');

 Form1.Query3.SQL.Add('LEFT JOIN ( SELECT chdrnum, sum( unit_alloc ) AS topup, coverage FROM '+FileNameonly+' WHERE coverage = "02" AND chdrnum = "'+chdrnum+'" GROUP BY fund_name ) AS tblcov02 ON tblabc.chdrnum = tblcov02.chdrnum GROUP BY fund_name ');

Code:

Form1.Query3.SQL.Add('INSERT INTO tblcov01 VALUES (SELECT chdrnum, sum( unit_alloc ) AS dasar, coverage FROM '+FileNameonly+' WHERE coverage = "01" AND chdrnum = "'+chdrnum+'" GROUP BY fund_name);');

 Form1.Query3.SQL.Add('INSERT INTO tblcov02 VALUES (SELECT chdrnum, sum( unit_alloc ) AS topup, coverage FROM '+FileNameonly+' WHERE coverage = "02" AND chdrnum = "'+chdrnum+'" GROUP BY fund_name);');

 Form1.Query3.SQL.Add('SELECT tblabc.chdrnum, tblabc.fund_name, tblcov01.dasar, tblcov02.topup ');

 Form1.Query3.SQL.Add('FROM (SELECT chdrnum, fund_name FROM '+FileNameonly+' WHERE chdrnum = "'+chdrnum+'" GROUP BY fund_name ) AS tblabc ');

 Form1.Query3.SQL.Add('LEFT JOIN tblcov01 ON tblabc.chdrnum = tblcov01.chdrnum');

 Form1.Query3.SQL.Add('LEFT JOIN tblcov02 ON tblabc.chdrnum = tblcov02.chdrnum;');

di coba di jalanin di foxpro langsung juga ga bisa begini

Code:

select tblabc.fund_name, tblcov01.dasar, tblcov02.topup from (select chdrnum,fund_name from tblabc_20131118 where chdrnum = "12345678" group by fund_name) tblabc ;

left join (select chdrnum, sum(unit_alloc) dasar from tblabc_20131118 where coverage="01" AND chdrnum = "12345678" GROUP BY fund_name ) tblcov01 ON tblabc.chdrnum=tblcov01.chdrnum;

left join (select chdrnum, sum(unit_alloc) topup from tblabc_20131118 where coverage="02" AND chdrnum = "12345678" GROUP BY fund_name ) tblcov02 ON tblabc.chdrnum=tblcov02.chdrnum

versi mysql

Code:

select tblabc.fund_name, tblcov01.dasar, tblcov02.topup from (select chdrnum,fund_name from tblabc where chdrnum = "12345678" group by fund_name) as tblabc 

left join (select chdrnum, sum(unit_alloc) as dasar from tblabc where coverage="01" AND chdrnum = "12345678" GROUP BY fund_name ) tblcov01 ON tblabc.chdrnum=tblcov01.chdrnum

left join (select chdrnum, sum(unit_alloc) as topup from tblabc where coverage="02" AND chdrnum = "12345678" GROUP BY fund_name ) tblcov02 ON tblabc.chdrnum=tblcov02.chdrnum;

please gan.... emoticon-Berduka (S)