Unanswered: help porting ms acess sql to postgresql
i have this sql from ms access db... :
SELECT DISTINCTROW T_DIN.CIF_BANK, T_DIN.DIN, T_DIN.NAMA_DEBITUR, T_DEBITUR.ID_DEBITUR, R_DEBITUR_FASILITAS.ID_FASILITAS, T_KREDIT.ID_FASILITAS, T_KREDIT.NO_REKENING
FROM ((T_DIN INNER JOIN T_DEBITUR ON T_DIN.DIN = T_DEBITUR.DIN) INNER JOIN R_DEBITUR_FASILITAS ON T_DEBITUR.ID_DEBITUR = R_DEBITUR_FASILITAS.ID_DEBITUR) INNER JOIN T_KREDIT ON R_DEBITUR_FASILITAS.ID_FASILITAS = T_KREDIT.ID_FASILITAS
WHERE (((T_DIN.CIF_BANK) In (SELECT [CIF_BANK] FROM [T_DIN] As Tmp GROUP BY [CIF_BANK] HAVING Count(*)>1 )))
ORDER BY T_DIN.CIF_BANK;
tried to port in to postgresql, but the result is way beyond my expectation... anybody can help me please...
Just off the bat, DISTINCTROW does not exist in PostgreSQL, so you'll need to look in the documentation to find the equivalent statement. Also, it looks like your statement contains placeholders for form fields ("[CIF_BANK]"). These need to be replaced with actual values.
There may be other problems, especially with all the JOINs going on, but your SQL statement is unreadable. Reformatting it will help you. A lot.
the square brackets are used by msaccess (and sql server) to delimit problematic identifiers (table and column names) which contain special characters or are reserved words
the standard sql (and postgresql) method to delimit an identifier is to use doublequotes -- however, it's better not to bother if the identifier doesn't actually require delimiting
ON t_debitur.din = t_din.din
ON r_debitur_fasilitas.id_debitur = t_debitur.id_debitur
ON t_kredit.id_fasilitas = r_debitur_fasilitas.id_fasilitas
WHERE t_din.cif_bank IN
( SELECT cif_bank
FROM t_din AS tmp
HAVING COUNT(*) > 1 )
after tested it on sql pane, yes it works great, but i don't know why it is a bit faster if i change it into one field only..
is it better to put (*) or one field only?
actually i put it on the pascal/delphi application
so if i put the (*) it make my coding into a line of comment not a line of coding
so i change it to field by this time, tried to find another way to put it in the coding.