Building SQL with Query Designer

rbuser

I built a query but when I copied the SQL to Management Studio it return no
records. I deleted a join an re-added it while in Studio and it rearranged
the joins a little bit and now it returns the correct results. But the
Diagram pane looks the same.

I think the problem is that I have a table that joins with 3 other tables.

Here is what I did in RB Query Designer:

SELECT tblTrans.Num
FROM tblTrans AS tblTrans INNER JOIN
tblSplit AS tblSplit ON tblSplit.TransID =
tblTrans.TransID RIGHT OUTER JOIN
tblVendor AS tblVendor ON tblVendor.VendorID =
tblSplit.PayeeID INNER JOIN
tblChartOfAccounts AS tblChartOfAccounts ON
tblChartOfAccounts.ChartOfAccountsID = tblSplit.FromAcctID
http://www.storagesidekick.com/NotWork.gif

And here is how SQL Studio changed it:
SELECT tblTrans.Num
FROM tblVendor AS tblVendor RIGHT OUTER JOIN
tblTrans AS tblTrans INNER JOIN
tblSplit AS tblSplit ON tblSplit.TransID =
tblTrans.TransID INNER JOIN
tblChartOfAccounts AS tblChartOfAccounts ON
tblChartOfAccounts.ChartOfAccountsID = tblSplit.FromAcctID ON
tblVendor.VendorID = tblSplit.PayeeID
http://www.storagesidekick.com/Works.gif

I can't seem to duplicate what SQL Studio makes because I don't know how to
join a table and not give a field to join.


-Rob

PS- I didn't copy the WHERE clause because I can make that the same using RB
Query Designer and is not the problem.

nicocizik

Hi Rob,

The Query Designer in ReportBuilder does not support joining on other joins.
One option may be to create a view of the two inner joins, then preform the
right outer join in ReportBuilder on that view. Another option would be to
manually edit the SQL in the Query Designer however you will loose all
visual linking ability.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

I created a view, but I don't see it listed in the list of tables in the
Query Designer, is it listed somewhere else?

nicocizik

Hi Rob,

Which version of ReportBuilder and Delphi are you using?

Which database and connectivity are you using? Currently most plugins are
set up to display tables and views however sometimes it is necessary to
enhance the GetTableNames routine to allow the use of views.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

RB 10.06
Delphi 2007
Connecting to MSSQL 2005

rbuser

I noticed that the views show up during run time, but not when I double
click a report in the IDE.

nicocizik

Hi Rob,

In my quick testing with SQL Server 2005 and ReportBuilder 10.07, I was able
to select all tables and views at runtime and designtime.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com