Loading ...
Sorry, an error occurred while loading the content.

Re: Error with joins and auto-generated search query (Firebird 1.5.3)

Expand Messages
  • Jason Wharton
    Marco, This issue is because Firebird 2.0 does not return the relation alias name, nor a relation name for the columns participating in the GROUP BY
    Message 1 of 3 , Nov 22, 2006
    View Source
    • 0 Attachment
      Marco,

      This issue is because Firebird 2.0 does not return the relation
      alias name, nor a relation name for the columns participating in the
      GROUP BY aggregation.

      My only resort is to parse the SQL and look for a table alias being
      used on the column name, which you are doing, and override what the
      server returns so that we can have an accurate query put together
      that won't trip up on the ambiguous column problem.

      I believe it would be a good idea to raise this issue with the
      Firebird development team and challenge them on not returning the
      relation alias used.

      Jason Wharton


      --- In IBObjects@yahoogroups.com, "Marco Menardi" <mmenaz@...> wrote:
      >
      > IBO 4.6.Aa Firebird 1.5.3, Delphi 6 pro, Windows2000sp4.
      > I have a problem using native IBO controls and the "search" mode
      with
      > Firebrid 1.5.3 that is less forgiving about ambiguos field name.
      IBO
      > automatically generates a query that has no table qualifier, and if
      > that field is present in both tables I join, I get an error... how
      can
      > I avoid it? I've tried setting "KeyRelation" property (I know it's
      > used for upates, but, you know...) or keylinks (but got an
      error...)
      > but without success. Any clue?
      > The query is:
      > SELECT FORNITORI.FORNITORE_ID
      > , FORNITORI.DESCRIZIONEDISP
      > , SUM(SCADENZIARIO.TOT_CHIUSO) AS CHIUSO
      > , SUM(SCADENZIARIO.TOT_APERTO) AS APERTO
      > , SUM(SCADENZIARIO.TOT_CHIUSO - SCADENZIARIO.TOT_APERTO) AS
      SALDO
      > FROM FORNITORI
      > INNER JOIN SCADENZIARIO ON (FORNITORI.FORNITORE_ID =
      > SCADENZIARIO.FORNITORE_ID)
      > GROUP BY FORNITORI.FORNITORE_ID, FORNITORI.DESCRIZIONEDISP
      >
      > When IBO generates the query searching for FORNITORE_ID value you
      have:
      > SELECT FORNITORI.FORNITORE_ID
      > , FORNITORI.DESCRIZIONEDISP
      > , SUM(SCADENZIARIO.TOT_CHIUSO) AS CHIUSO
      > , SUM(SCADENZIARIO.TOT_APERTO) AS APERTO
      > , SUM(SCADENZIARIO.TOT_CHIUSO - SCADENZIARIO.TOT_APERTO) AS
      SALDO
      > FROM FORNITORI
      > INNER JOIN SCADENZIARIO ON (FORNITORI.FORNITORE_ID =
      > SCADENZIARIO.FORNITORE_ID)
      > WHERE FORNITORE_ID STARTING 'FF0001'
      > GROUP BY FORNITORI.FORNITORE_ID, FORNITORI.DESCRIZIONEDISP
      > ORDER BY DESCRIZIONEDISP DESC
      >
      >
      > and then, of course, the error:
      > Project ammwine2000.exe raised exception class EIB_ISCError with
      > message 'ISC ERROR CODE:335544569
      > ISC ERROR MESSAGE:
      > Dynamic SQL Error
      > SQL error code = -204
      > Ambiguous field name between table FORNITORI and table SCADENZIARIO
      > FORNITORE_ID
      > '. Process stopped. Use Step or Run to continue.
      >
      > So I need a way to tell IBO to make something like:
      > WHERE FORNITORI.FORNITORE_ID STARTING 'FF0001'
      >
      > Btw, if I set KeyLinks to FORNITORI.FORNITORE_ID and I simply open
      the
      > query I get the error:
      > Invalid KeyLinks entry: FORNITORI.FORNITORE_ID.
      >
      > Any help is much apreciated, thanks
      > Marco Menardi
      >
    • Marco Menardi
      I ve tested with FB 2.0.1, that should have this issue fixed, but the problem is the same. Could you take a look also to an unfixed bug that hurts my app
      Message 2 of 3 , Mar 22, 2007
      View Source
      • 0 Attachment
        I've tested with FB 2.0.1, that should have this issue fixed, but the
        problem is the same.
        Could you take a look also to an unfixed bug that hurts my app badly?
        "IB_Grid OnCellClick not fired if Ctrl or Shift key is pressed: 4.5B"
        http://tech.groups.yahoo.com/group/IBObjects/message/37816
        Thu Jan 5, 2006 2:11 pm

        also reported again here with a brief thread (and another bug I don't
        care anymore):
        "Jason, have you had a look at those 2 bugs?"
        http://tech.groups.yahoo.com/group/IBObjects/message/38716
        Fri May 12, 2006 12:31 am
        Thanks :)
        Marco


        --- In IBObjects@yahoogroups.com, "Jason Wharton" <supportlist@...> wrote:
        >
        > Marco,
        >
        > This issue is because Firebird 2.0 does not return the relation
        > alias name, nor a relation name for the columns participating in the
        > GROUP BY aggregation.
        >
        > My only resort is to parse the SQL and look for a table alias being
        > used on the column name, which you are doing, and override what the
        > server returns so that we can have an accurate query put together
        > that won't trip up on the ambiguous column problem.
        >
        > I believe it would be a good idea to raise this issue with the
        > Firebird development team and challenge them on not returning the
        > relation alias used.
        >
        > Jason Wharton
        >
        >
        > --- In IBObjects@yahoogroups.com, "Marco Menardi" <mmenaz@> wrote:
        > >
        > > IBO 4.6.Aa Firebird 1.5.3, Delphi 6 pro, Windows2000sp4.
        > > I have a problem using native IBO controls and the "search" mode
        > with
        > > Firebrid 1.5.3 that is less forgiving about ambiguos field name.
        > IBO
        > > automatically generates a query that has no table qualifier, and if
        > > that field is present in both tables I join, I get an error... how
        > can
        > > I avoid it? I've tried setting "KeyRelation" property (I know it's
        > > used for upates, but, you know...) or keylinks (but got an
        > error...)
        > > but without success. Any clue?
        > > The query is:
        > > SELECT FORNITORI.FORNITORE_ID
        > > , FORNITORI.DESCRIZIONEDISP
        > > , SUM(SCADENZIARIO.TOT_CHIUSO) AS CHIUSO
        > > , SUM(SCADENZIARIO.TOT_APERTO) AS APERTO
        > > , SUM(SCADENZIARIO.TOT_CHIUSO - SCADENZIARIO.TOT_APERTO) AS
        > SALDO
        > > FROM FORNITORI
        > > INNER JOIN SCADENZIARIO ON (FORNITORI.FORNITORE_ID =
        > > SCADENZIARIO.FORNITORE_ID)
        > > GROUP BY FORNITORI.FORNITORE_ID, FORNITORI.DESCRIZIONEDISP
        > >
        > > When IBO generates the query searching for FORNITORE_ID value you
        > have:
        > > SELECT FORNITORI.FORNITORE_ID
        > > , FORNITORI.DESCRIZIONEDISP
        > > , SUM(SCADENZIARIO.TOT_CHIUSO) AS CHIUSO
        > > , SUM(SCADENZIARIO.TOT_APERTO) AS APERTO
        > > , SUM(SCADENZIARIO.TOT_CHIUSO - SCADENZIARIO.TOT_APERTO) AS
        > SALDO
        > > FROM FORNITORI
        > > INNER JOIN SCADENZIARIO ON (FORNITORI.FORNITORE_ID =
        > > SCADENZIARIO.FORNITORE_ID)
        > > WHERE FORNITORE_ID STARTING 'FF0001'
        > > GROUP BY FORNITORI.FORNITORE_ID, FORNITORI.DESCRIZIONEDISP
        > > ORDER BY DESCRIZIONEDISP DESC
        > >
        > >
        > > and then, of course, the error:
        > > Project ammwine2000.exe raised exception class EIB_ISCError with
        > > message 'ISC ERROR CODE:335544569
        > > ISC ERROR MESSAGE:
        > > Dynamic SQL Error
        > > SQL error code = -204
        > > Ambiguous field name between table FORNITORI and table SCADENZIARIO
        > > FORNITORE_ID
        > > '. Process stopped. Use Step or Run to continue.
        > >
        > > So I need a way to tell IBO to make something like:
        > > WHERE FORNITORI.FORNITORE_ID STARTING 'FF0001'
        > >
        > > Btw, if I set KeyLinks to FORNITORI.FORNITORE_ID and I simply open
        > the
        > > query I get the error:
        > > Invalid KeyLinks entry: FORNITORI.FORNITORE_ID.
        > >
        > > Any help is much apreciated, thanks
        > > Marco Menardi
        > >
        >
      Your message has been successfully submitted and would be delivered to recipients shortly.