Announcement

Collapse
No announcement yet.

I wish you'd fix this!

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

  • I wish you'd fix this!

    I first highlighted this bug during the SC5 phase. It applies to MySQL, MS-SQL, Access, SQLlite and seemingly it still hasn't been fixed.

    Here's the SQL
    Code:
    SELECT Allocations.ID, Allocations.Area, AllocAreas.UserCode, AllocAreas.name, Allocations.Portion, AllocPortions.UserCode, AllocPortions.name, 
    Allocations.Row, AllocRows.UserCode, AllocRows.Name, Allocations.Number, Allocations.`Sub-Number`, Allocations.Interment, Allocations.Status, RegisterData.ID AS RegisterIndex
    FROM (AllocRows INNER JOIN (AllocPortions INNER JOIN (AllocAreas INNER JOIN AllocSectionIndex ON AllocAreas.ID = AllocSectionIndex.AREA) 
    ON AllocPortions.ID = AllocSectionIndex.PORTION) ON AllocRows.ID = AllocSectionIndex.ROW) INNER JOIN (Allocations LEFT JOIN RegisterData ON Allocations.ID = 
    RegisterData.AllocationCode) ON AllocSectionIndex.ID = Allocations.SectionID
    ORDER BY Allocations.Area, Allocations.Portion, Allocations.Row, Allocations.Number, Allocations.Interment;
    Note the field name in bold. The above runs in SC8's SQL builder.

    At runtime, and error occurs. The first two CHARS are dropped off the first field in the select statement. See attached screen dump. Compare the SQL to the fields list at left.

    Aliasing the field has no effect in SC8. Aliasing the table name has no effect in SC8.

    Thank you
    Attached Files
    Last edited by GuiGuy; 02-07-2015, 01:51 AM.
    The GuiGuy
    ... from Down Under

  • #2
    Just a quick update; in earlier versions of SC I was able to alias some fields as a possible work-around. This no longer works in SC8:
    Code:
     Error
    Error while accessing the database:
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
    SelectLimit(SELECT ocations.ID as allocid, AllocAreas.name as areaname, AllocPortions.name as portionname, 
    AllocRows.Name as rowname, Allocations.`Number` as allocations_number, Allocations.`Sub-Number` as sc_field_0, 
    Allocations.Interment as allocations_interment, Allocations.Status as allocations_status, 
    RegisterData.RegisterGroupID as registergroup, RegisterData.Surname as registerdata_surname, RegisterData.`Given Names` as sc_field_1, 
    RegisterData.Town as registerdata_town, RegisterData.Gender as registerdata_gender, RegisterData.Aged as registerdata_aged 
    FROM
     ((((( AllocSectionIndex INNER JOIN Allocations ON AllocSectionIndex.AREA = Allocations.Area AND AllocSectionIndex.PORTION = 
    Allocations.Portion AND AllocSectionIndex.ROW = Allocations.Row) INNER JOIN AllocAreas ON AllocSectionIndex.AREA = AllocAreas.ID) 
    INNER JOIN AllocPortions ON AllocSectionIndex.PORTION = AllocPortions.ID) INNER JOIN AllocRows ON AllocSectionIndex.ROW = AllocRows.ID) 
    LEFT OUTER JOIN RegisterData ON Allocations.ID = RegisterData.AllocationCode) , 12, 0)
    It has become a Showstopper!
    Last edited by GuiGuy; 02-07-2015, 01:49 AM.
    The GuiGuy
    ... from Down Under

    Comment


    • #3
      Everytime I have issues with complex queries in SC, I move the complex query to a new view in the DB engine, so that in SC I can use a very basic:
      SELECT * FROM 'viewname'
      If needed, in SC you can always apply a WHERE clause to a SELECT on a view:
      SELECT * FROM 'viewname' WHERE ...
      But I don't know if it applies to your problem.

      Comment


      • #4
        +1 on robydago's workaround.

        I have found ScriptCase to be very limited on what SQL will, or won't, work.

        On MS SQL and MySQL, you can also make this a stored function or a stored procedure, then call the function from SC8's SQL. I do this a lot.

        Comment


        • #5
          Originally posted by Giblet535 View Post
          +1 on robydago's workaround.

          I have found ScriptCase to be very limited on what SQL will, or won't, work.

          On MS SQL and MySQL, you can also make this a stored function or a stored procedure, then call the function from SC8's SQL. I do this a lot.
          Same here. Sometimes I solve issues like this syntax. select a.field, a.otherfield, b.somefield from table1 a, table 2 b where a.key=b.key

          I think that it has something to do with the sql parser of scriptcase.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            Hi,
            Thanks for the attempt to cheer me up, people. Anyway, the reason the issue got up my goat was that I had an MS-Access db project underway and SC7 didn't support views. But I see now, my ongoing ODBC problems notwithstanding, SC8 can now process Access' "queries" as views, which has sort of solved things.
            However, I have now used a different work-around, which is not to use complex SQL. Instead, just use the table and use lookups. A little more tedious but much more robust.
            Nevertheless, I wish they'd fix it. After all, how hard can it be given it is always the first field in the SQL clause that gets hammered?
            Cheers
            The GuiGuy
            ... from Down Under

            Comment

            Working...
            X