Jump to URL - Syntax wrong?

Hello,
I realize this topic has been beaten to death but I'm still having trouble
with a report.
I'm trying to open up an asp.net page in a new window. Here is what I'm
using based on examples I've read here so far:
= "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum=" & Fields!Name.Value ','_blank'))"
I do this through right clicking on a textbox and going to its advanced
navigation properties. Now the report builds fine with no errors and once
it's done building instead of giving me a preview of the report it says
something like: "Invalid URL type must use http://, https://, file://, etc.".
Is there some other way of doing this by using LinkTarget or something? I
am hoping my syntax is just wrong here. I have RS 2000 with SP2 installed.
Help!
ThanksAlso, when testing this you might need to deploy it to test it properly.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> Hello,
> I realize this topic has been beaten to death but I'm still having trouble
> with a report.
> I'm trying to open up an asp.net page in a new window. Here is what I'm
> using based on examples I've read here so far:
> => "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> & Fields!Name.Value ','_blank'))"
> I do this through right clicking on a textbox and going to its advanced
> navigation properties. Now the report builds fine with no errors and once
> it's done building instead of giving me a preview of the report it says
> something like: "Invalid URL type must use http://, https://, file://,
> etc.".
>
> Is there some other way of doing this by using LinkTarget or something? I
> am hoping my syntax is just wrong here. I have RS 2000 with SP2
> installed.
> Help!
> Thanks|||Here is one that works.
= "javascript:void(window.open('http://www.google.com','_blank'))"
Looking at yours I would say that you need to do this:
="javascript:void(window.open('http://www.mysite.com/subdir/form.aspx?ctrlnum="
& Fields!Name.Value & "','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> Hello,
> I realize this topic has been beaten to death but I'm still having trouble
> with a report.
> I'm trying to open up an asp.net page in a new window. Here is what I'm
> using based on examples I've read here so far:
> => "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> & Fields!Name.Value ','_blank'))"
> I do this through right clicking on a textbox and going to its advanced
> navigation properties. Now the report builds fine with no errors and once
> it's done building instead of giving me a preview of the report it says
> something like: "Invalid URL type must use http://, https://, file://,
> etc.".
>
> Is there some other way of doing this by using LinkTarget or something? I
> am hoping my syntax is just wrong here. I have RS 2000 with SP2
> installed.
> Help!
> Thanks|||You mean I could have had it right and it would still give me that syntax
error?
"Bruce L-C [MVP]" wrote:
> Also, when testing this you might need to deploy it to test it properly.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John" <John@.discussions.microsoft.com> wrote in message
> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> > Hello,
> >
> > I realize this topic has been beaten to death but I'm still having trouble
> > with a report.
> >
> > I'm trying to open up an asp.net page in a new window. Here is what I'm
> > using based on examples I've read here so far:
> >
> > => > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> > & Fields!Name.Value ','_blank'))"
> >
> > I do this through right clicking on a textbox and going to its advanced
> > navigation properties. Now the report builds fine with no errors and once
> > it's done building instead of giving me a preview of the report it says
> > something like: "Invalid URL type must use http://, https://, file://,
> > etc.".
> >
> >
> > Is there some other way of doing this by using LinkTarget or something? I
> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
> > installed.
> >
> > Help!
> > Thanks
>
>|||What happens to me from development is I get a flash and a ding sound and
nothing happens.
No, I think you have your syntax incorrect. You are trying to assemble a
string. Look at this part: & Fields!Name.Value ','_blank'))"
it should be & Fields!Name.Value & "','_blank'))"
You want to append the two strings together. The value in your feild and the
rest of the string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:2C4D09B7-C68E-4BFB-AA86-B2B8D36665B2@.microsoft.com...
> You mean I could have had it right and it would still give me that syntax
> error?
> "Bruce L-C [MVP]" wrote:
>> Also, when testing this you might need to deploy it to test it properly.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John" <John@.discussions.microsoft.com> wrote in message
>> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
>> > Hello,
>> >
>> > I realize this topic has been beaten to death but I'm still having
>> > trouble
>> > with a report.
>> >
>> > I'm trying to open up an asp.net page in a new window. Here is what
>> > I'm
>> > using based on examples I've read here so far:
>> >
>> > =>> > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
>> > & Fields!Name.Value ','_blank'))"
>> >
>> > I do this through right clicking on a textbox and going to its advanced
>> > navigation properties. Now the report builds fine with no errors and
>> > once
>> > it's done building instead of giving me a preview of the report it says
>> > something like: "Invalid URL type must use http://, https://, file://,
>> > etc.".
>> >
>> >
>> > Is there some other way of doing this by using LinkTarget or something?
>> > I
>> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
>> > installed.
>> >
>> > Help!
>> > Thanks
>>|||Great thanks!
I will test this as soon as I can and let you know.
"Bruce L-C [MVP]" wrote:
> What happens to me from development is I get a flash and a ding sound and
> nothing happens.
> No, I think you have your syntax incorrect. You are trying to assemble a
> string. Look at this part: & Fields!Name.Value ','_blank'))"
> it should be & Fields!Name.Value & "','_blank'))"
> You want to append the two strings together. The value in your feild and the
> rest of the string.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John" <John@.discussions.microsoft.com> wrote in message
> news:2C4D09B7-C68E-4BFB-AA86-B2B8D36665B2@.microsoft.com...
> > You mean I could have had it right and it would still give me that syntax
> > error?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Also, when testing this you might need to deploy it to test it properly.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "John" <John@.discussions.microsoft.com> wrote in message
> >> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> >> > Hello,
> >> >
> >> > I realize this topic has been beaten to death but I'm still having
> >> > trouble
> >> > with a report.
> >> >
> >> > I'm trying to open up an asp.net page in a new window. Here is what
> >> > I'm
> >> > using based on examples I've read here so far:
> >> >
> >> > => >> > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> >> > & Fields!Name.Value ','_blank'))"
> >> >
> >> > I do this through right clicking on a textbox and going to its advanced
> >> > navigation properties. Now the report builds fine with no errors and
> >> > once
> >> > it's done building instead of giving me a preview of the report it says
> >> > something like: "Invalid URL type must use http://, https://, file://,
> >> > etc.".
> >> >
> >> >
> >> > Is there some other way of doing this by using LinkTarget or something?
> >> > I
> >> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
> >> > installed.
> >> >
> >> > Help!
> >> > Thanks
> >>
> >>
> >>
>
>|||I just wanted to say this is exactly what I needed. It still gives me a
"invalid url" error inside VS but it works fine once I deploy it and test
inside IE.
Thanks!
"Bruce L-C [MVP]" wrote:
> Here is one that works.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> Looking at yours I would say that you need to do this:
> => "javascript:void(window.open('http://www.mysite.com/subdir/form.aspx?ctrlnum="
> & Fields!Name.Value & "','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> > Hello,
> >
> > I realize this topic has been beaten to death but I'm still having trouble
> > with a report.
> >
> > I'm trying to open up an asp.net page in a new window. Here is what I'm
> > using based on examples I've read here so far:
> >
> > => > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> > & Fields!Name.Value ','_blank'))"
> >
> > I do this through right clicking on a textbox and going to its advanced
> > navigation properties. Now the report builds fine with no errors and once
> > it's done building instead of giving me a preview of the report it says
> > something like: "Invalid URL type must use http://, https://, file://,
> > etc.".
> >
> >
> > Is there some other way of doing this by using LinkTarget or something? I
> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
> > installed.
> >
> > Help!
> > Thanks
>
>|||Make sure you are running at least RS 2000 SP1 on the report server and
report designer for this to work.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"John" <John@.discussions.microsoft.com> wrote in message
news:1B63DA2A-1655-477E-BBCA-7839423DBD52@.microsoft.com...
> Great thanks!
> I will test this as soon as I can and let you know.
>
> "Bruce L-C [MVP]" wrote:
>> What happens to me from development is I get a flash and a ding sound and
>> nothing happens.
>> No, I think you have your syntax incorrect. You are trying to assemble a
>> string. Look at this part: & Fields!Name.Value ','_blank'))"
>> it should be & Fields!Name.Value & "','_blank'))"
>> You want to append the two strings together. The value in your feild and
>> the
>> rest of the string.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John" <John@.discussions.microsoft.com> wrote in message
>> news:2C4D09B7-C68E-4BFB-AA86-B2B8D36665B2@.microsoft.com...
>> > You mean I could have had it right and it would still give me that
>> > syntax
>> > error?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Also, when testing this you might need to deploy it to test it
>> >> properly.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "John" <John@.discussions.microsoft.com> wrote in message
>> >> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I realize this topic has been beaten to death but I'm still having
>> >> > trouble
>> >> > with a report.
>> >> >
>> >> > I'm trying to open up an asp.net page in a new window. Here is what
>> >> > I'm
>> >> > using based on examples I've read here so far:
>> >> >
>> >> > =>> >> > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
>> >> > & Fields!Name.Value ','_blank'))"
>> >> >
>> >> > I do this through right clicking on a textbox and going to its
>> >> > advanced
>> >> > navigation properties. Now the report builds fine with no errors
>> >> > and
>> >> > once
>> >> > it's done building instead of giving me a preview of the report it
>> >> > says
>> >> > something like: "Invalid URL type must use http://, https://,
>> >> > file://,
>> >> > etc.".
>> >> >
>> >> >
>> >> > Is there some other way of doing this by using LinkTarget or
>> >> > something?
>> >> > I
>> >> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
>> >> > installed.
>> >> >
>> >> > Help!
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||It is common to get different behaviour in the dev environment than in
prod... We always go ahead and deploy and test both places before we decide
that something doesn't work.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OYsFzsqqFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Make sure you are running at least RS 2000 SP1 on the report server and
> report designer for this to work.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "John" <John@.discussions.microsoft.com> wrote in message
> news:1B63DA2A-1655-477E-BBCA-7839423DBD52@.microsoft.com...
>> Great thanks!
>> I will test this as soon as I can and let you know.
>>
>> "Bruce L-C [MVP]" wrote:
>> What happens to me from development is I get a flash and a ding sound
>> and
>> nothing happens.
>> No, I think you have your syntax incorrect. You are trying to assemble a
>> string. Look at this part: & Fields!Name.Value ','_blank'))"
>> it should be & Fields!Name.Value & "','_blank'))"
>> You want to append the two strings together. The value in your feild and
>> the
>> rest of the string.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John" <John@.discussions.microsoft.com> wrote in message
>> news:2C4D09B7-C68E-4BFB-AA86-B2B8D36665B2@.microsoft.com...
>> > You mean I could have had it right and it would still give me that
>> > syntax
>> > error?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Also, when testing this you might need to deploy it to test it
>> >> properly.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "John" <John@.discussions.microsoft.com> wrote in message
>> >> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I realize this topic has been beaten to death but I'm still having
>> >> > trouble
>> >> > with a report.
>> >> >
>> >> > I'm trying to open up an asp.net page in a new window. Here is
>> >> > what
>> >> > I'm
>> >> > using based on examples I've read here so far:
>> >> >
>> >> > =>> >> > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
>> >> > & Fields!Name.Value ','_blank'))"
>> >> >
>> >> > I do this through right clicking on a textbox and going to its
>> >> > advanced
>> >> > navigation properties. Now the report builds fine with no errors
>> >> > and
>> >> > once
>> >> > it's done building instead of giving me a preview of the report it
>> >> > says
>> >> > something like: "Invalid URL type must use http://, https://,
>> >> > file://,
>> >> > etc.".
>> >> >
>> >> >
>> >> > Is there some other way of doing this by using LinkTarget or
>> >> > something?
>> >> > I
>> >> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
>> >> > installed.
>> >> >
>> >> > Help!
>> >> > Thanks
>> >>
>> >>
>> >>
>>
>

Joins issue

Sorry if this is the wrong group but..
I have a query that still has a few minor issues the main problem i had with
nulls is sorted however i am joining 5 tables together and if a row doesnt
exist in a table i dont get a row at all, i have a table that i know a
record always exists in and i am using left outer joins to join it to other
tables. I thought that a left join would get a record regardless of whether
or not there is a matching record. My query is posted below so you can
maybe let me know whats wrong with it, i am sorry for the lack of aliases
and probably readibility but i havent really had time to sort it.
SELECT dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance, dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC +
dbo.DM_OtherCredit.redemtionsecured AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee, dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee, dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear, dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails LEFT OUTER JOIN
dbo.DM_Valuation ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_Valuation.FK_ApplicationID LEFT
OUTER JOIN
dbo.DM_Payout ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_Mortgage ON dbo.DM_LoanDetails.FK_ApplicationID
= dbo.DM_Mortgage.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_OtherCredit ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1') AND
(dbo.DM_LoanDetails.FieldIdent = '1:1') AND (dbo.DM_Valuation.FieldIdent =
'1:1') AND
(dbo.DM_Payout.FieldIdent = '1:1') AND
(dbo.DM_OtherCredit.FieldIdent = '1:1' OR
dbo.DM_OtherCredit.FieldIdent = '1:2' OR
dbo.DM_OtherCredit.FieldIdent = '1:3' OR
dbo.DM_OtherCredit.FieldIdent = '1:4' OR
dbo.DM_OtherCredit.FieldIdent = '1:5' OR
dbo.DM_OtherCredit.FieldIdent = '1:6' OR
dbo.DM_OtherCredit.FieldIdent = '1:7' OR
dbo.DM_OtherCredit.FieldIdent = '1:8' OR
dbo.DM_OtherCredit.FieldIdent = '1:9' OR
dbo.DM_OtherCredit.FieldIdent = '1:10')
Thanks in advanceA LEFT OUTER JOIN will always return rows, provided that your WHERE
criteria doesn't limit the results of your query using a column from
the inner side of the join. In your case, the criteria :
(dbo.DM_Mortgage.FieldIdent = '1:1')
tells SQL Server to limit the results to include data from both
DM_LoanDetails (all rows) and DM_Mortgage (only those rows where
FieldIDent = '1:1'). Basically, you've nullified your OUTER JOIN.
HTH,
Stu|||So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didn=B4t mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||Yeah the query is right the 1:1 condition needs to be there otherwise it
returns other iterations of the record and you end up with dupes i didnt
design the database its software that was ourchased a few years before i
started here, its hard to explain why the iterations are there and why they
work, I do need that clause in there though i have tried it without and
still get the same results.
I can better explain my problem now i think. The sql i have given is used
in another view that performs some calculations and basically if the value
is null makes it zero, the problem lies in the dm_payout and dm_valuation
tables, basically the case has died before anyone has been able to complete
the fields i need from those tables.
However i need to show what the value of the deal was regardless of whether
or not we got to add our fees on top, so if they wanted 100k but no other
fields were completed then it should show 100k
As i have mentioned this calculation is done in another view, the problem
lies in the fact that no record exists in the payout or valuation table so
it is for some unknown reason causing it not to get any results at all.
This other view (main view) is as follows
We have a table of phone numbers of people who have called in on a certain
number that we got from our dialler database this is joined to a table in
the database that has the phone number so that we can get the
fk_applicationID, this is present in all the tables as it is the unique
identifier. We then join this table to another table to get the persons
surname and 2 views, one of the views tells us what the applications status
is of the record, the 2nd view accesses the information in the view which is
the SQL i posted. Basically this view only pulls the required information
that is needed from the view i posted and if the value is null sets it to
zero. I then in this (main view) add the fields together i need.
I get all the records i would expect but i get null where the value of the
calculation should be because in the view i posted no row is returned.
I hope this is making sense. Maybe i wont be able to have a value here and
null is all i can expect but as i said a left join should as far as i know
just give me the rest of the information which then shouldnt mess up my
calc.
thanks for the help so far
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132142679.094129.141370@.g14g2000cwa.googlegroups.com...
So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didnt mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||On Wed, 16 Nov 2005 10:46:25 -0000, Steven Scaife wrote:

>Sorry if this is the wrong group but..
>I have a query that still has a few minor issues the main problem i had wit
h
>nulls is sorted however i am joining 5 tables together and if a row doesnt
>exist in a table i dont get a row at all, i have a table that i know a
>record always exists in and i am using left outer joins to join it to other
>tables. I thought that a left join would get a record regardless of whethe
r
>or not there is a matching record. My query is posted below so you can
>maybe let me know whats wrong with it, i am sorry for the lack of aliases
>and probably readibility but i havent really had time to sort it.
Hi Stevan,
A quick visit to http://www.sqlinform.com/ was all it took to get the
SQL a whole lot more readable. Here's a better formatted version of your
query:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1')
AND (dbo.DM_LoanDetails.FieldIdent = '1:1')
AND (dbo.DM_Valuation.FieldIdent = '1:1')
AND (dbo.DM_Payout.FieldIdent = '1:1')
AND (dbo.DM_OtherCredit.FieldIdent = '1:1'
OR dbo.DM_OtherCredit.FieldIdent = '1:2'
OR dbo.DM_OtherCredit.FieldIdent = '1:3'
OR dbo.DM_OtherCredit.FieldIdent = '1:4'
OR dbo.DM_OtherCredit.FieldIdent = '1:5'
OR dbo.DM_OtherCredit.FieldIdent = '1:6'
OR dbo.DM_OtherCredit.FieldIdent = '1:7'
OR dbo.DM_OtherCredit.FieldIdent = '1:8'
OR dbo.DM_OtherCredit.FieldIdent = '1:9'
OR dbo.DM_OtherCredit.FieldIdent = '1:10')
Now, it is immediately clear that the reason for your query not working,
is that you build WHERE clauses on columns from all outer-join'ed
tables. Stu already explained why that is bad - but it seems that he
only catched one of the culprits.
If you really need these joins to be outer joins, then you'll have to
move all selections from the WHERE clause to the ON clauses:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
AND dbo.DM_Valuation.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
AND dbo.DM_Payout.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
AND dbo.DM_Mortgage.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
AND dbo.DM_OtherCredit.FieldIdent IN ('1:1', '1:2', '1:3', '1:4',
'1:5', '1:6', '1:7', '1:8', '1:9', '1:10')
WHERE dbo.DM_LoanDetails.FieldIdent = '1:1'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

join queries

ok...here's the thing, i've been trying to join two queries in one but i'm not getting any results, maybe i'm doing it wrong i don't know
here are the queries:
select tbEntidadesMoradas.codEntidade, Entidades.Entidade, Moradas.Morada, Moradas.CodPostal, Moradas.Localidade FROM Entidades INNER JOIN tbEntidadesMoradas ON Entidades.CodEntidade = tbEntidadesMoradas.codEntidade INNER JOIN Moradas ON tbEntidadesMoradas.codMorada = Moradas.codMorada
the other one
select tbEntidadesAltContact.codEntidade, tbEntidadesAltContact.codAltContc, ContactosAlternativos.Nome, ContactosAlternativos.Morada, ContactosAlternativos.Contacto FROM tbEntidadesAltContact INNER JOIN ContactosAlternativos ON tbEntidadesAltContact.codAltContc = ContactosAlternativos.codConAlt
what i want is to join them or re-arrange them for the outup of the two of them to be only one..
thanks in advance
Can you not use a union? Now do the queries relate to each other.