1

I am having problems displaying a view I created (successfully in sqlite3) using ruby.

Below is the sqlite view

CREATE VIEW members_ytd_totals AS
    SELECT id AS MemberID,
    first_name,
    last_name,
    ipu_dues.total AS TotalDues,
    ipu_levy.total AS TotalLevy,
    ipu_fines.total AS TotalFines,
    ipu_donation.total AS TotalDonations,
    ipu_registration.total AS TotalRegistrations

FROM ipu_profile
       INNER JOIN
       ipu_dues ON ipu_dues.id = ipu_profile.id
       INNER JOIN
       ipu_levy ON ipu_levy.id = ipu_profile.id
       INNER JOIN
       ipu_fines ON ipu_fines.id = ipu_profile.id
       INNER JOIN
       ipu_donation ON ipu_donation.id = ipu_profile.id
       INNER JOIN
       ipu_registration ON ipu_registration.id = ipu_profile.id;

normally i should be able to use a simple select statement in ruby to display this view like

require 'sqlite3'
require 'sqlite_print_table'
db = SQLite3::Database.open 'database.db'
db.print_table("SELECT * FROM members_ytd_totals", guide_tolerance: 4, width_tolerance: 5, margin: 10)

however I get A database Exception occurred - ambiguous column name: id

however inside sqlite3, this view was created properly, with no errors and I can see the view in Sqlite studio. I just cannot display it in ruby. Any ideas what the problem can be?

Thanks

Charlie_oscar
  • 57
  • 5

1 Answers1

0

When you say I can see the view in Sqlite studio you mean that the View was created without any errors.
But did you tried to use it, say with:

SELECT * FROM members_ytd_totals;

you would get the same error.
Use aliases, especially in cases like this where multiple joins are involved and always qualify the column names with the table's name/alias to avoid ambiguouities:

CREATE VIEW members_ytd_totals AS 
SELECT 
  p.id AS MemberID, p.first_name, p.last_name, 
  d.total AS TotalDues, l.total AS TotalLevy, 
  f.total AS TotalFines, n.total AS TotalDonations, 
  r.total AS TotalRegistrations 
FROM ipu_profile p
INNER JOIN ipu_dues d ON d.id = p.id 
INNER JOIN ipu_levy l ON l.id = p.id 
INNER JOIN ipu_fines f ON f.id = p.id 
INNER JOIN ipu_donation n ON n.id = p.id 
INNER JOIN ipu_registration r ON r.id = p.id;
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Yes I tried SELECT * FROM members_ytd_totals; and same error. Also I aliased everything and still same error. – Charlie_oscar May 01 '20 at 17:31
  • I saw the edit that you attempted to my answer (you should not edit my answer but your question). You did **not** qualify **all** the column names with the table's alias. Especially `id` which is the ambiguous column. Just copy and paste my code. – forpas May 01 '20 at 18:01
  • 1
    Sorry I edited your answer i error. Yes It works perfectly now that I have qualified all the column. Excellent!!! thanks in million – Charlie_oscar May 01 '20 at 18:20