Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: help porting ms acess sql to postgresql

    i have this sql from ms access db... :
    Code:
    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...

  2. #2
    Join Date
    May 2008
    Posts
    277
    You cannot just plunk SQL from MS Access into PostgreSQL verbatim.

    First, try reformatting your sql into something that's readable (try googling for "sql coding standard" or "sql formatting").

    Then go through the statement, making changes as necessary. This will be useful: PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: SQL Commands

    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i agree with futurity's advice

    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
    Code:
    SELECT 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_debitur.din = t_din.din 
    INNER 
      JOIN r_debitur_fasilitas 
        ON r_debitur_fasilitas.id_debitur = t_debitur.id_debitur 
    INNER 
      JOIN t_kredit 
        ON t_kredit.id_fasilitas = r_debitur_fasilitas.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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2009
    Posts
    4
    i'm really new in sql
    if i join only 2 table, it works exactly like what i want,
    but when i narrow down the table to 4 table and
    tried to write in pgsql like this :

    SELECT 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;

    it is work but not the result i wanted to...

    this sql actually to find a duplicate CIF_BANK record in T_DIN where the records have no recods connection with T_DEBITUR table, R_DEBITUR_FASILITAS, AND T_KREDIT table


    sorry if it still sounds silly.. new learn on sql

  5. #5
    Join Date
    Oct 2009
    Posts
    4
    i tried the sql from r937
    just a little modification at the last line :

    Code:
      
    HAVING COUNT(*) > 1 )
    change the asterik marks with some field name so it doesn't come with wild card on it

    it works great.. thanks a lot.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ferry_kns
    change the asterik marks with some field name so it doesn't come with wild card on it
    wha????

    COUNT(*) counts rows, and is perfectly valid in that subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2009
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •