Announcement

Collapse
No announcement yet.

I have an unusual group by and need help

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • I have an unusual group by and need help

    Hi All,

    I am relatively new to MySQL and I have come across a situation that I am not sure how to deal with it. The project is for a bookkeeping system.

    I have a table called ledger_codes, and it has a PK of an integer that is auto incremented. I also have a column that records the actual ledger code, but is not auto incremented as the user sets the value.

    I also have a table of transactions. One of the columns records the link to a ledger code. It is linked to the PK field in the ledger code table.

    When I do a "group by" of these transactions, I need to group the records by the ledger code field, and not the PK field as is recorded in the transactions database.

    I am not sure how to do this.

    I can do it by the linking code easily enough, but not sure how to do it by another field. Do I do some join in the SQL statement? I am not sure how SC handles joins etc.

    Thanks

    Tony

  • #2
    I have experimented with an inner join and it works in the MySQL engine. Below is the SQL SELECT statement in the grid SQL setting. As I said, it returns the correct data and the correct sorting.

    SELECT
    ta_id,
    ta_tran,
    ta_date,
    ta_ref,
    ta_glcode,
    ta_amount,
    ta_gst,
    ta_net,
    ta_drcr,
    ta_bastype,
    ta_note,
    ta_type,
    gl_code
    FROM
    allocations
    INNER JOIN
    ledger_codes ON allocations.ta_glcode = ledger_codes.gl_id
    WHERE
    ta_date BETWEEN '2014-07-01' AND '2015-06-30'
    ORDER BY
    gl_code, ta_date

    However, it does not work in SC. I get an error on compile that states that the "Search fields undefined"

    Any idea what I am doing wrong?

    Thanks

    Tony

    Comment


    • #3
      SOLVED - I have an unusual group by and need help

      I have managed to solve this by using an inner join, but BEWARE changing your SQL statement with a join can turn the whole application into a dogs breakfast.

      The search fields undefined error turned to grid fields undefined error, and then the edit fields setting truncated the table name on the id field and would not save the fields to be displayed.

      In the end, I added a single character alias in the SQL statement, one for each table, I did this because a.id_field works better than allocations.id_field (truncation issue noted above).

      Then you have to change all of the links and code in events to match adding the alias.

      It then worked fine.

      Tony

      Comment


      • #4
        Originally posted by fiscal View Post
        I have experimented with an inner join and it works in the MySQL engine. Below is the SQL SELECT statement in the grid SQL setting. As I said, it returns the correct data and the correct sorting.

        SELECT
        ta_id,
        ta_tran,
        ta_date,
        ta_ref,
        ta_glcode,
        ta_amount,
        ta_gst,
        ta_net,
        ta_drcr,
        ta_bastype,
        ta_note,
        ta_type,
        gl_code
        FROM
        allocations
        INNER JOIN
        ledger_codes ON allocations.ta_glcode = ledger_codes.gl_id
        WHERE
        ta_date BETWEEN '2014-07-01' AND '2015-06-30'
        ORDER BY
        gl_code, ta_date

        However, it does not work in SC. I get an error on compile that states that the "Search fields undefined"

        Any idea what I am doing wrong?

        Thanks

        Tony
        I've seen search fields undefined come by quite often, quite certain for me it's just been whenever I change my SQL it empties the fields it knows for the advanced search so you have to set them again. (think it's a bug....)
        Far as giving the tables alias's I'd normally suggest it anyways, makes it a bit easier to read in general.

        Comment

        Working...
        X