Jump To URL not working

Hi,
I'm using Reporting Services 2000 with the latest SP. The javascript code in
Jump To URL is giving a Page Cannot be Displayed message.
Here is the code:
="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
Globals!ReportFolder + "/Detail&Country_Code=" +
Parameters!Country_Code.Value + "&ITEMID=" + Fields!Parent_Product_Code.Value
+ "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
Parameters!Language.Value +
"&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width = 580,height=750,top=200'))"
Can someone plz help?The links work fine if i access the report from localhost/ReportServer
but not when i access from ReportManager.
Is this a known issue?
"Gaurav" wrote:
> Hi,
> I'm using Reporting Services 2000 with the latest SP. The javascript code in
> Jump To URL is giving a Page Cannot be Displayed message.
> Here is the code:
> ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> Globals!ReportFolder + "/Detail&Country_Code=" +
> Parameters!Country_Code.Value + "&ITEMID=" + Fields!Parent_Product_Code.Value
> + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> Parameters!Language.Value +
> "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width => 580,height=750,top=200'))"
> Can someone plz help?
>|||Get this complete link display on a text box (ofcourse without javascript
syntax) and see where you are going wrong.
Amarnath
"Gaurav" wrote:
> The links work fine if i access the report from localhost/ReportServer
> but not when i access from ReportManager.
> Is this a known issue?
>
> "Gaurav" wrote:
> > Hi,
> >
> > I'm using Reporting Services 2000 with the latest SP. The javascript code in
> > Jump To URL is giving a Page Cannot be Displayed message.
> >
> > Here is the code:
> > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> > Globals!ReportFolder + "/Detail&Country_Code=" +
> > Parameters!Country_Code.Value + "&ITEMID=" + Fields!Parent_Product_Code.Value
> > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> > Parameters!Language.Value +
> > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width => > 580,height=750,top=200'))"
> >
> > Can someone plz help?
> >|||I couldn't resolve this. Even if i put in a link to google.com it doesn't
work.
The following is not working:
="javascript:void(window.open('http://www.google.com','_blank'))"
Any other suggestions?
"Amarnath" wrote:
> Get this complete link display on a text box (ofcourse without javascript
> syntax) and see where you are going wrong.
> Amarnath
> "Gaurav" wrote:
> > The links work fine if i access the report from localhost/ReportServer
> > but not when i access from ReportManager.
> >
> > Is this a known issue?
> >
> >
> > "Gaurav" wrote:
> >
> > > Hi,
> > >
> > > I'm using Reporting Services 2000 with the latest SP. The javascript code in
> > > Jump To URL is giving a Page Cannot be Displayed message.
> > >
> > > Here is the code:
> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> > > Globals!ReportFolder + "/Detail&Country_Code=" +
> > > Parameters!Country_Code.Value + "&ITEMID=" + Fields!Parent_Product_Code.Value
> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> > > Parameters!Language.Value +
> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width => > > 580,height=750,top=200'))"
> > >
> > > Can someone plz help?
> > >|||This looks OK. What version are you one. The ability to do this was
introduced with SP1 of RS 2000. You need to be on SP1 or greater for RS 2000
and any version of RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
news:E823758C-0A43-433F-B7C5-1EB1E9D98627@.microsoft.com...
>I couldn't resolve this. Even if i put in a link to google.com it doesn't
> work.
> The following is not working:
> ="javascript:void(window.open('http://www.google.com','_blank'))"
> Any other suggestions?
> "Amarnath" wrote:
>> Get this complete link display on a text box (ofcourse without javascript
>> syntax) and see where you are going wrong.
>> Amarnath
>> "Gaurav" wrote:
>> > The links work fine if i access the report from localhost/ReportServer
>> > but not when i access from ReportManager.
>> >
>> > Is this a known issue?
>> >
>> >
>> > "Gaurav" wrote:
>> >
>> > > Hi,
>> > >
>> > > I'm using Reporting Services 2000 with the latest SP. The javascript
>> > > code in
>> > > Jump To URL is giving a Page Cannot be Displayed message.
>> > >
>> > > Here is the code:
>> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
>> > > Globals!ReportFolder + "/Detail&Country_Code=" +
>> > > Parameters!Country_Code.Value + "&ITEMID=" +
>> > > Fields!Parent_Product_Code.Value
>> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
>> > > Parameters!Language.Value +
>> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width
>> > > =>> > > 580,height=750,top=200'))"
>> > >
>> > > Can someone plz help?
>> > >|||Thanks for the reply Bruce.
I am using Version 8.00.1038.00.
"Bruce L-C [MVP]" wrote:
> This looks OK. What version are you one. The ability to do this was
> introduced with SP1 of RS 2000. You need to be on SP1 or greater for RS 2000
> and any version of RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
> news:E823758C-0A43-433F-B7C5-1EB1E9D98627@.microsoft.com...
> >I couldn't resolve this. Even if i put in a link to google.com it doesn't
> > work.
> >
> > The following is not working:
> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> >
> > Any other suggestions?
> >
> > "Amarnath" wrote:
> >
> >> Get this complete link display on a text box (ofcourse without javascript
> >> syntax) and see where you are going wrong.
> >>
> >> Amarnath
> >>
> >> "Gaurav" wrote:
> >>
> >> > The links work fine if i access the report from localhost/ReportServer
> >> > but not when i access from ReportManager.
> >> >
> >> > Is this a known issue?
> >> >
> >> >
> >> > "Gaurav" wrote:
> >> >
> >> > > Hi,
> >> > >
> >> > > I'm using Reporting Services 2000 with the latest SP. The javascript
> >> > > code in
> >> > > Jump To URL is giving a Page Cannot be Displayed message.
> >> > >
> >> > > Here is the code:
> >> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> >> > > Globals!ReportFolder + "/Detail&Country_Code=" +
> >> > > Parameters!Country_Code.Value + "&ITEMID=" +
> >> > > Fields!Parent_Product_Code.Value
> >> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> >> > > Parameters!Language.Value +
> >> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width
> >> > > => >> > > 580,height=750,top=200'))"
> >> > >
> >> > > Can someone plz help?
> >> > >
>
>|||That should be SP2. It should be working for you.
Copy and paste this in:
= "javascript:void(window.open('http://www.google.com','_blank'))"
Also, do you have popup blocker running?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
news:DD368BFC-B8B5-4EC6-994D-F95E603FF9FD@.microsoft.com...
> Thanks for the reply Bruce.
> I am using Version 8.00.1038.00.
> "Bruce L-C [MVP]" wrote:
>> This looks OK. What version are you one. The ability to do this was
>> introduced with SP1 of RS 2000. You need to be on SP1 or greater for RS
>> 2000
>> and any version of RS 2005.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
>> news:E823758C-0A43-433F-B7C5-1EB1E9D98627@.microsoft.com...
>> >I couldn't resolve this. Even if i put in a link to google.com it
>> >doesn't
>> > work.
>> >
>> > The following is not working:
>> > ="javascript:void(window.open('http://www.google.com','_blank'))"
>> >
>> > Any other suggestions?
>> >
>> > "Amarnath" wrote:
>> >
>> >> Get this complete link display on a text box (ofcourse without
>> >> javascript
>> >> syntax) and see where you are going wrong.
>> >>
>> >> Amarnath
>> >>
>> >> "Gaurav" wrote:
>> >>
>> >> > The links work fine if i access the report from
>> >> > localhost/ReportServer
>> >> > but not when i access from ReportManager.
>> >> >
>> >> > Is this a known issue?
>> >> >
>> >> >
>> >> > "Gaurav" wrote:
>> >> >
>> >> > > Hi,
>> >> > >
>> >> > > I'm using Reporting Services 2000 with the latest SP. The
>> >> > > javascript
>> >> > > code in
>> >> > > Jump To URL is giving a Page Cannot be Displayed message.
>> >> > >
>> >> > > Here is the code:
>> >> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
>> >> > > Globals!ReportFolder + "/Detail&Country_Code=" +
>> >> > > Parameters!Country_Code.Value + "&ITEMID=" +
>> >> > > Fields!Parent_Product_Code.Value
>> >> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
>> >> > > Parameters!Language.Value +
>> >> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width
>> >> > > =>> >> > > 580,height=750,top=200'))"
>> >> > >
>> >> > > Can someone plz help?
>> >> > >
>>|||Even after disabling the popup bloker, its not running.
Any other thoughts?
"Bruce L-C [MVP]" wrote:
> That should be SP2. It should be working for you.
> Copy and paste this in:
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> Also, do you have popup blocker running?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
> news:DD368BFC-B8B5-4EC6-994D-F95E603FF9FD@.microsoft.com...
> > Thanks for the reply Bruce.
> > I am using Version 8.00.1038.00.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> This looks OK. What version are you one. The ability to do this was
> >> introduced with SP1 of RS 2000. You need to be on SP1 or greater for RS
> >> 2000
> >> and any version of RS 2005.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
> >> news:E823758C-0A43-433F-B7C5-1EB1E9D98627@.microsoft.com...
> >> >I couldn't resolve this. Even if i put in a link to google.com it
> >> >doesn't
> >> > work.
> >> >
> >> > The following is not working:
> >> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> >> >
> >> > Any other suggestions?
> >> >
> >> > "Amarnath" wrote:
> >> >
> >> >> Get this complete link display on a text box (ofcourse without
> >> >> javascript
> >> >> syntax) and see where you are going wrong.
> >> >>
> >> >> Amarnath
> >> >>
> >> >> "Gaurav" wrote:
> >> >>
> >> >> > The links work fine if i access the report from
> >> >> > localhost/ReportServer
> >> >> > but not when i access from ReportManager.
> >> >> >
> >> >> > Is this a known issue?
> >> >> >
> >> >> >
> >> >> > "Gaurav" wrote:
> >> >> >
> >> >> > > Hi,
> >> >> > >
> >> >> > > I'm using Reporting Services 2000 with the latest SP. The
> >> >> > > javascript
> >> >> > > code in
> >> >> > > Jump To URL is giving a Page Cannot be Displayed message.
> >> >> > >
> >> >> > > Here is the code:
> >> >> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> >> >> > > Globals!ReportFolder + "/Detail&Country_Code=" +
> >> >> > > Parameters!Country_Code.Value + "&ITEMID=" +
> >> >> > > Fields!Parent_Product_Code.Value
> >> >> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> >> >> > > Parameters!Language.Value +
> >> >> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width
> >> >> > > => >> >> > > 580,height=750,top=200'))"
> >> >> > >
> >> >> > > Can someone plz help?
> >> >> > >
> >>
> >>
> >>
>
>

Jump to URL - New Window - Ampersand

I have a Jump to URL hyperlink action using the "javascript:
void(window.open)" code. This works great, except when one of the parameters
contains an ampersand. I get an error saying that the "path of the item is
not valid. The full path must be less than 260 characters long, muster start
with slash..."
One of my parameters for the report that first comes up is
"OfficeDescription" which may contain an ampersand ("Boise R&D"). I'm
passing this to the URL as listed below. It only fails if there is that
ampersand in the OfficeDescription ("Boise Sales" works fine). I'm tried
replacing the "&" with %26 but I can't get that to work.
This works fine if I use a "Jump to Report" and pass the parameters that
way, but I need to pop up the report in a new window.
="javascript:void(window.open('"&Globals!ReportServerUrl+ "?" &
Globals!ReportFolder & "/Producer Detail&OfficeDescription=" &
Parameters!OfficeDescription.Value & "&Producer=" & Fields!Producer.Value &
"&rc%3aParameters=false','_blank','height=530px,left=1px,location=no,menubar=no,resizable=yes,scrollbars=yes,toolbar=no,top=1px,width=780px'))"
Thanks for any advice anyone cares to offer!Parameter values must be URLEncoded. Please perform the following steps:
* load the report in report designer
* go to the report menu -> report properties -> References tab
* in the "References:" list, add a reference to System.Web, and click OK
* in the report, modify your expression to use the HttpUtility.UrlEncode
function (see MSDN:
http://msdn2.microsoft.com/en-us/library/system.web.httputility.urlencode):
="javascript:void(window.open('"&Globals!ReportServerUrl+ "?" &
Globals!ReportFolder & "/Producer Detail&OfficeDescription=" &
System.Web.HttpUtility.UrlEncode(Parameters!OfficeDescription.Value) &
"&Producer=" & System.Web.HttpUtility.UrlEncode(Fields!Producer.Value) &
"&rc%3aParameters=false','_blank','height=530px,left=1px,location=no,menubar=no,resizable=yes,scrollbars=yes,toolbar=no,top=1px,width=780px'))"
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"kaibh" <kaibh@.discussions.microsoft.com> wrote in message
news:7EAE0CF0-2C9C-47A8-8A2D-B5861AFA5740@.microsoft.com...
>I have a Jump to URL hyperlink action using the "javascript:
> void(window.open)" code. This works great, except when one of the
> parameters
> contains an ampersand. I get an error saying that the "path of the item
> is
> not valid. The full path must be less than 260 characters long, muster
> start
> with slash..."
> One of my parameters for the report that first comes up is
> "OfficeDescription" which may contain an ampersand ("Boise R&D"). I'm
> passing this to the URL as listed below. It only fails if there is that
> ampersand in the OfficeDescription ("Boise Sales" works fine). I'm tried
> replacing the "&" with %26 but I can't get that to work.
> This works fine if I use a "Jump to Report" and pass the parameters that
> way, but I need to pop up the report in a new window.
> ="javascript:void(window.open('"&Globals!ReportServerUrl+ "?" &
> Globals!ReportFolder & "/Producer Detail&OfficeDescription=" &
> Parameters!OfficeDescription.Value & "&Producer=" & Fields!Producer.Value
> &
> "&rc%3aParameters=false','_blank','height=530px,left=1px,location=no,menubar=no,resizable=yes,scrollbars=yes,toolbar=no,top=1px,width=780px'))"
> Thanks for any advice anyone cares to offer!
>|||Robert, thanks for the feedback, however I'm still getting the same error:
Reporting Services Error
The path of the item '/OnDemand/Producer Detail,C' is not valid.The full
path must be less than 260 characters long, must start with slash; other
restrictions apply. Check the documentation for complete set of restrictions.
(rsInvalidItemPath)
The HTML generated for this link looks like:
<a style="text-decoration:none;color:Black"
href="javascript:void(window.open('https://my.reportserver.com/ReportServer?/OnDemand/Producer%20Detail&OfficeDescription=Boise+r%26D&Producer=JSL&rc%3aParameters=false','_blank','height=530px,left=1px,location=no,menubar=no,resizable=yes,scrollbars=yes,toolbar=no,top=1px,width=780px'))" TARGET="_top">JSL</a>
The "Boise R&D" is getting encoded as "Boise+R%26D", so that's good right?
But since I'm still getting this error, I assume there must be something else
that I'm missing.
Thanks
"Robert Bruckner [MSFT]" wrote:
> Parameter values must be URLEncoded. Please perform the following steps:
> * load the report in report designer
> * go to the report menu -> report properties -> References tab
> * in the "References:" list, add a reference to System.Web, and click OK
> * in the report, modify your expression to use the HttpUtility.UrlEncode
> function (see MSDN:
> http://msdn2.microsoft.com/en-us/library/system.web.httputility.urlencode):
> ="javascript:void(window.open('"&Globals!ReportServerUrl+ "?" &
> Globals!ReportFolder & "/Producer Detail&OfficeDescription=" &
> System.Web.HttpUtility.UrlEncode(Parameters!OfficeDescription.Value) &
> "&Producer=" & System.Web.HttpUtility.UrlEncode(Fields!Producer.Value) &
> "&rc%3aParameters=false','_blank','height=530px,left=1px,location=no,menubar=no,resizable=yes,scrollbars=yes,toolbar=no,top=1px,width=780px'))"
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.|||I also am trying to do this. It works great in the IDE, but I get the
following error when publishing the report to Report Manager on my local
machine and any servers.
Request for the permission of type System.Web.AspNetHostingPermission,
System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed.
It is probably permissions. How would I modify rssrvpolicy.config to allow
the execution of System.Web assembly?
Dan Bock
"kaibh" wrote:
> Robert, thanks for the feedback, however I'm still getting the same error:
> Reporting Services Error
> The path of the item '/OnDemand/Producer Detail,C' is not valid.The full
> path must be less than 260 characters long, must start with slash; other
> restrictions apply. Check the documentation for complete set of restrictions.
> (rsInvalidItemPath)
> The HTML generated for this link looks like:
> <a style="text-decoration:none;color:Black"
> href="http://links.10026.com/?link=javascript:void(window.open('https://my.reportserver.com/ReportServer?/OnDemand/Producer%20Detail&OfficeDescription=Boise+r%26D&Producer=JSL&rc%3aParameters=false','_blank','height=530px,left=1px,location=no,menubar=no,resizable=yes,scrollbars=yes,toolbar=no,top=1px,width=780px'))" TARGET="_top">JSL</a>
> The "Boise R&D" is getting encoded as "Boise+R%26D", so that's good right?
> But since I'm still getting this error, I assume there must be something else
> that I'm missing.
> Thanks
>
> "Robert Bruckner [MSFT]" wrote:
> > Parameter values must be URLEncoded. Please perform the following steps:
> > * load the report in report designer
> > * go to the report menu -> report properties -> References tab
> > * in the "References:" list, add a reference to System.Web, and click OK
> > * in the report, modify your expression to use the HttpUtility.UrlEncode
> > function (see MSDN:
> > http://msdn2.microsoft.com/en-us/library/system.web.httputility.urlencode):
> > ="javascript:void(window.open('"&Globals!ReportServerUrl+ "?" &
> > Globals!ReportFolder & "/Producer Detail&OfficeDescription=" &
> > System.Web.HttpUtility.UrlEncode(Parameters!OfficeDescription.Value) &
> > "&Producer=" & System.Web.HttpUtility.UrlEncode(Fields!Producer.Value) &
> > "&rc%3aParameters=false','_blank','height=530px,left=1px,location=no,menubar=no,resizable=yes,scrollbars=yes,toolbar=no,top=1px,width=780px'))"
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
>

Jump to URL

to open a report in a new window

i looked up the following line of code

=window.open('" & Globals!ReportServerUrl & "?" & Globals!ReportFolder & "/" & "myreport" & "&amp;rs:Command=Render&amp;rc:toolbar=true','','width=800,height=600,left=10,top=10,resizable=1,menubar=no,location=no,status=no'),_top"

I do not understand what & Globals!ReportServerUrl & "?" & Globals!ReportFolder & "/" & "myreport" &

this implies.

I have the current report name as

http://houapps277/ReportServer/Pages/ReportViewer.aspx?%2fIMS-Reports%2fCR000648

and I need to jump to

http://houapps277/ReportServer/Pages/ReportViewer.aspx?%2fIMS-Reports%2fCR000699

both reports are in same folder IMS-Reports.

Thanks

"javascript:void window.open('ReportViewer.aspx? %2fCR000699', '_top', 'location=0, menubar=0, toolbar=0, resizable=1, width=800')"

use the above code.

In reportviewer.aspx page you need to write the code as

rptViewer.ServerReport.ReportServerUrl = new Uri(http://Reportservername);

rptViewer.ServerReport.ReportPath = "/IMS-Reports/" + Request.QueryString[0];

rptViewer.ProcessingMode = ProcessingMode.Remote;

Hope it will help you

Rakam.

|||

Thanks for the reply rakam,

Where do I write and store reportviewer.aspx?

Sorry for being illiterate here.

Thanks

|||

Just use the following code

"javascript:void window.open('http://houapps277/ReportServer/Pages/ReportViewer.aspx?%2fIMS-Reports%2fCR000699', '_top', 'location=0, menubar=0, toolbar=0, resizable=1, width=800')"

hope it will work

sql

joning result of dynamic queries

i m making the code of search button in aspx page using C#.

i have multiple dynamic queries that take data from multiple tables

but i dnt know how to join the result of these queries.

example:

1) first query using table1

2)second query using table2

3)third query using table3

how to join the result of these queries at asp.net page?

thanx in advance

If the result sets of all the queries are similar (ie. same number of columns of the same data types)

Then just issue one command with all three queries union'd together

Code Snippet

SELECT x,y,z From Table1

UNION ALL

SELECT x, y z From Table2

UNION All

SELECT x, y, z From Table 3

|||

thanx for reply but it is not useful at all

because i m asking how to combine the result of dynamic queries that relate multiple tables and u r sending simple sql query.i willl try to explain my problem in moredetail as follow.

TABLE NAME COLUMNNAMES

1) tbQualification -- ResumeId,Graduation,PostGraduation

2) tbStrength-- Resumeid,Strength

3)tbExperience Resumeid,YearExperience,MonthExperience

there is a button of SEARCH .clicking on this button search is to be made in all columns fields.this is to be done throgh dynamic query or stored procedure.

this cant be done using simple sql query because we are writting code in aspx page.

plizz help me out

all replies are welcome

Thanx.

|||

You can store the result of each query in the temp table then join those temp tables for your final query...

Code Snippet

Create Table #tbQualificationResult (

ResumeId int,

Graduation varchar(20),

PostGraduation varchar(20)

)

Create Table #tbStrength (

Resumeid int,

Strength int

)

Create Table #tbExperience (

Resumeid int,

YearExperience int,

MonthExperience int

)

Insert Into #tbQualificationResult

Exec sp_executesql 'Your Dynamic Search Query'

Insert Into #tbStrength

Exec sp_executesql 'Your Dynamic Search Query'

Insert Into #tbExperience

Exec sp_executesql 'Your Dynamic Search Query'

Select

COALESCE(A.ResumeId, B.ResumeId, C.ResumeId)

,A.Graduation

,A.PostGraduation

,C.Strength

,C.YearExperience

,C.MonthExperience

From

#tbQualificationResult A

Full OUTER JOIN #tbStrength B on A.ResumeId = B.ResumeId

FULL OUTER JOIN #tbExperience C On A.ResumeId = C.ResumeId

|||

One approach to your issue, from my response and Mani's, is to code the combinations on the back-end in SQL Server.

However, this can also be done at the BI or UI tier.

See these articles for creating datasets with multiple tables and establishing datarelations between those tables.

http://msdn2.microsoft.com/en-us/library/ss7fbaez(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/ay82azad(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/d6s958d6(VS.80).aspx

|||

thanx a lot for reply.

plizz tel how i write query where u say"your dynamic query" because i dnt know how to write dynamic query in sql.

i know only in aspx page and it is different to write in sql so pliz tel.

need urgently

thanx

sql

Joins on same table

I'm having two general problems trying to do a JOIN. I have a table with
three fields {Code, Date, Amount}. Code+Date is a unique key. I'm trying
to get a rowset with 1) one row for each unique Code+Date pair, 2) and
with each row containing, {Code, Amount for Date-A and Amount for
Date-B}. Basically, I want to create two temp tables with the Amounts for
a specified Date and then Join them.

The problems are
1) I'm trying to do this in SQL-Server 7 with a single stantment, and
2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
returned with NULLs.

Anybody have any wisdom on this??
ThanksThe following gets me what I want, using Temp tables. I'm just trying to
figure out how to combine the Selects into a single statment.

Thanks

----------------

--temp with each Code
Drop Table #T0;
Select Code
Into #T0
From tblSearch
Order by Code;

--temp with amounts for 1st date
Drop Table #T1;
Select Code, Date, Amount
Into #T1
From tblSearch
Where Date = 20031102
Order by Code, Date;

-- amounts for 2nd date
Drop Table #T2;
Select Code, Date, Amount
Into #T2
From tblSearch
Where Date = 20031103
Order by Code, Date;

--put everything together
Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
'd2' from #T0
Left Outer Join #T1
On #T0.Code = #T1.Code
Left Outer Join #T2
On #T0.Code = #T2.Code
Order By #T0.Code|||[posted and mailed, please reply in news]

Chris (chris@.hicom.net) writes:
> I'm having two general problems trying to do a JOIN. I have a table
> with three fields {Code, Date, Amount}. Code+Date is a unique key.
> I'm trying to get a rowset with 1) one row for each unique Code+Date
> pair, 2) and with each row containing, {Code, Amount for Date-A and
> Amount for Date-B}. Basically, I want to create two temp tables with
> the Amounts for a specified Date and then Join them.
> The problems are
> 1) I'm trying to do this in SQL-Server 7 with a single stantment, and
> 2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
> returned with NULLs.

Just rewriting the temp-table thing you had with derived tables
gives:

SELECT DISTINCT #T0.Code, #T1.Date, #T1.Amount 'd1',
#T2.Date, #T2.Amount 'd2'
FROM tblSearch #T0
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031102') AS #T1
ON #T0.Code = #T1.Code
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031103') AS #T2
ON #T0.Code = #T2.Code
ORDER BY #T0.Code

But if I understand this correctly, it seems that you could get away with:

SELECT Code = coalesce(a.Code, b.Code), a.Date, d1 = a.Amount,
b.Date, d2 = b.Amount
FROM tblSearch a
FULL JOIN tblSearch b ON a.Code = b.Code
AND a.Date = b.Date
AND a.Date = '20031102'
AND b.Date = '20031103'

All this works on SQL7.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Chris,

You can replace temp tables with derived tables. Alternatively, as
the temp tables are selecting from the same table tblSearch, you can
also re-write the query using CASE. Note I'm using a mssqlserver
non-standard syntax. I just find it easier to read.

"Date=CASE when Date = 20031102 then Date else null end"
instead of
"CASE when Date = 20031102 then Date else null end as Date"

SELECT
Distinct
Code,
Date=CASE when Date = 20031102 then Date else null end,
Amount=CASE when Date = 20031102 then Amount else null end,
Date=CASE when Date = 20031103 then Date else null end,
Amount=CASE when Date = 20031103 then Amount else null end,
FROM tblSearch
ORDER BY code

> --temp with each Code
> Drop Table #T0;
> Select Code
> Into #T0
> From tblSearch
> Order by Code;
> --temp with amounts for 1st date
> Drop Table #T1;
> Select Code, Date, Amount
> Into #T1
> From tblSearch
> Where Date = 20031102
> Order by Code, Date;
> -- amounts for 2nd date
> Drop Table #T2;
> Select Code, Date, Amount
> Into #T2
> From tblSearch
> Where Date = 20031103
> Order by Code, Date;
> --put everything together
> Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
> 'd2' from #T0
> Left Outer Join #T1
> On #T0.Code = #T1.Code
> Left Outer Join #T2
> On #T0.Code = #T2.Code
> Order By #T0.Code|||The derived table approach gets me what I want -- one row per Code.

It seems that Coalesce doesn't help reduce the normal number of rows from
the Join.

Thanks very much for looking for a solution.|||The derived tables gets both Amounts into the same row, while the Case
still results in two (Distinct) rows.

I need to get a better SQL reference -- the book I'm using does not cover
derived tables.

Thanks very much.|||"Chris" <chris@.hicom.net> wrote in message news:<2bcNb.32425$G04.6661104@.news4.srv.hcvlny.cv.net>...
> The derived tables gets both Amounts into the same row, while the Case
> still results in two (Distinct) rows.
> I need to get a better SQL reference -- the book I'm using does not cover
> derived tables.
> Thanks very much.

Use GROUP if you want combine them into the same row. DISTINCT only
filters the rows.

SELECT
Code,
Date=max(CASE when Date = 20031102 then Date else null end),
Amount=max(CASE when Date = 20031102 then Amount else null end),
Date=max(CASE when Date = 20031103 then Date else null end),
Amount=max(CASE when Date = 20031103 then Amount else null end)
FROM tblSearch
GROUP BY code
ORDER BY code|||Chris (chris@.hicom.net) writes:
> It seems that Coalesce doesn't help reduce the normal number of rows
> from the Join.

That's right. The coalesce() function takes a list of values as parameters,
and return the first value in the list that is not NULL. Since the second
query included a full join, any of a.code and b.code could be NULL, so be
sure that we had a value here, I used coalesce(a.Code, b.Code).

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Excellent!

-- Thanks|||louis nguyen (louisducnguyen@.hotmail.com) writes:
> SELECT
> Code,
> Date=max(CASE when Date = 20031102 then Date else null end),
> Amount=max(CASE when Date = 20031102 then Amount else null end),
> Date=max(CASE when Date = 20031103 then Date else null end),
> Amount=max(CASE when Date = 20031103 then Amount else null end)
> FROM tblSearch
> GROUP BY code
> ORDER BY code

Note that date literals requires quotes. 20031103 is a number, and
attempt to convert it to datetime results in overflow.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Joining two dropdown lists to create third dropdownlist

How do i add two values of Dropdownlist 1 and 2 to create No.3

Is there an easy way through Visual Studio to do this than Realms of code that i have been reading online,

Surely, there is some command that is like Select Distinct Name from Clients WHEREcity=@.city +country=@.country

Or is it not this easy?

For multiple conditions you would need to use AND.

Select Distinct Name from Clients WHEREcity=@.city ANDcountry=@.country.

|||

Thanks,

The statement "Select Distinct Name from Clients WHEREcity=@.city ANDcountry=@.country." works fine in the query builder but how do i link Dropdownlist 2 to it.

I have tried all different ways in the Define Paramers Option in Dropdownlist 3 but have had no luck.

Any help would be appreciated.

I am working through VStudio2005 and am new to programming so am building dropdownlists from the toolbox etc.

Thanks,

Sully

Joining Rows in a SubQuery

I have the following code in a stored procedure
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
order by CategoryName
it's output is similar to this:
CatID | ParentId | CategoryName | pagecount
6 1 Cat1 0
4 Null Cat2 3
I would like to make a new column (say Newtext) adn Return something like th
is
CatID | ParentId | CategoryName | pagecount | NewText
6 1 Cat1 0 Cat1 (0)
4 Null Cat2 3 Cat2 (3)
How can i join the data to have this result?
I would hope that i could do something like CategoryName + "(" + pagecount +
")" in some sort of sql statement.
Thanks for any input
Lots of ways, here's one
SELECT CatID, ParentId, CategoryName,pagecount ,
CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
NewText
FROM
(
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
) X
order by CategoryName|||One approach is to use a derived table, embedding the existing query
in the FROM clause of an outer query:
SELECT *,
NewText =
CategoryName + '(' + convert(varchar(8),pagecount) + ')'
FROM (<query as you stated it> ) as X
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
<Fabuloussites@.discussions.microsoft.com> wrote:

>I have the following code in a stored procedure
>SELECT CatID, ParentId, CategoryName, (select count(*)
>from members WHERE DirectoryCat = DirectoryCats.CatID and
>InFreeDirectory=1 and ApproveDirectory=1) pagecount
>FROM DirectoryCats
>where active=1
>order by CategoryName
>
>it's output is similar to this:
>CatID | ParentId | CategoryName | pagecount
>6 1 Cat1 0
>4 Null Cat2 3
>I would like to make a new column (say Newtext) adn Return something like t
his
>CatID | ParentId | CategoryName | pagecount | NewText
>6 1 Cat1 0 Cat1 (0)
>4 Null Cat2 3 Cat2 (3)
>How can i join the data to have this result?
>I would hope that i could do something like CategoryName + "(" + pagecount
+
>")" in some sort of sql statement.
>Thanks for any input|||thanks for the fast and helpful response.
"markc600@.hotmail.com" wrote:

>
> Lots of ways, here's one
>
> SELECT CatID, ParentId, CategoryName,pagecount ,
> CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
> NewText
> FROM
> (
> SELECT CatID, ParentId, CategoryName, (select count(*)
> from members WHERE DirectoryCat = DirectoryCats.CatID and
> InFreeDirectory=1 and ApproveDirectory=1) pagecount
> FROM DirectoryCats
> where active=1
> ) X
> order by CategoryName
>|||thanks for the fast and helpful response.
"Roy Harvey" wrote:

> One approach is to use a derived table, embedding the existing query
> in the FROM clause of an outer query:
> SELECT *,
> NewText =
> CategoryName + '(' + convert(varchar(8),pagecount) + ')'
> FROM (<query as you stated it> ) as X
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
> <Fabuloussites@.discussions.microsoft.com> wrote:
>
>

Joining 2 symetric table trees

Hello

This is my code which dosent work

SELECT Person_1.PersonName, Statistics_1.DateEmission AS DateEmission1,

Statistics_2.DateEmission AS DateEmission2

FROM Statistics AS Statistics_1 INNER JOIN

Person AS Person_1 ON Statistics_1.PersonID = Person_1.PersonId LEFT OUTER JOIN

Person AS Person_2 ON Person_1.PersonId = Person_2.PersonId INNER JOIN

Statistics AS Statistics_2 ON Person_2.PersonId = Statistics_2.PersonID

WHERE (Person_1.PersonId = @.PersonID) AND (Statistics_1.DateBack = @.Date1) AND

(Statistics_2.DateBack = @.Date2)

In fact the code in bold is exactly the same with 2 different instances of tables Person and statistics, My problem is when this condition (Statistics_1.DateBack = @.Date1) AND (Statistics_2.DateBack = @.Date2) is true I got my result but when (Statistics_1.DateBack = @.Date1) OR (Statistics_2.DateBack = @.Date2) IS False I got nothing,

I need to get a not empty result when (Statistics_1.DateBack = @.Date1) is true as I used a LEFT OUTER JOIN between the 2 trees.

I don't understand why my code is wrong, can someone show me how to fix this issue.

Thanks in advance.

Does the example below return the results you're expecting?

If not, then could you provide sample data for both tables, along with the expected results?

Thanks
Chris

SELECT Person_1.PersonName,
Statistics_1.DateEmission AS DateEmission1,
Statistics_2.DateEmission AS DateEmission2
FROM [Statistics] AS Statistics_1
INNER JOIN [Person] AS Person_1 ON Statistics_1.PersonID = Person_1.PersonId
LEFT OUTER JOIN ([Person] AS Person_2
INNER JOIN [Statistics] AS Statistics_2 ON Person_2.PersonId = Statistics_2.PersonID AND Statistics_2.DateBack = @.Date2
) ON Person_1.PersonId = Person_2.PersonId
WHERE (Person_1.PersonId = @.PersonID)
AND (Statistics_1.DateBack = @.Date1)

|||EDIT: Never mind, looks like Chris beat me to it. ;)

|||

Thanks Chris it works :D

do u have an explanation Why my SQL Code didnt work ?

|||

Fekih Mehdi wrote:

Thanks Chris it works :D

do u have an explanation Why my SQL Code didnt work ?

It's because of the order of the joins. They're handled in the order written (functionally speaking - I believe the query optimizer will sometimes do them in a different order internally if it'll perform better).

So first it does the inner join, which gives no unmatched rows. Then the left outer join is performed, bringing some unmatched rows into the record set. Then the next inner join is performed, and since those previous unmatched rows now don't match anything in this join, they're filtered out again.

Join Tables that don't have exact matches

I have a table that has a Sales Tax Code and an Invoice Date (ex. 7/04/07)
and another table that has the Sales Tax Code, Tax Rate, and Effective Date.
The second table can have the same Sales Tax Code with a different Tax Rate
and Effective Date. I want to select only one Tax Rate record from the
second table and that is the one that has the greatest effective date that is
also less than the invoice date.
I have tried the Top 1 method in a join and where statement, but it is not
actually limiting the records from the second table.
can you post some sample records and expected result set out of it.
Vishal Parkar
vgparkar@.yahoo.co.in
"Queryless in SLC" wrote:

> I have a table that has a Sales Tax Code and an Invoice Date (ex. 7/04/07)
> and another table that has the Sales Tax Code, Tax Rate, and Effective Date.
> The second table can have the same Sales Tax Code with a different Tax Rate
> and Effective Date. I want to select only one Tax Rate record from the
> second table and that is the one that has the greatest effective date that is
> also less than the invoice date.
> I have tried the Top 1 method in a join and where statement, but it is not
> actually limiting the records from the second table.

join statement

when I am trying to optimize a proc, I saw one code which I don't know

select a.col1, b.col1

from tbl1 a, tbl2 b

where a.col2*=b.col2

what does this mean ( what will be the result set)

how can I write it in different way

Thanks

This is an old-style outer join which is actually deprecated in sql server 2005. Your code can be written this way

select
a.col1,
b.col2
from tbl1 a
left join tbl2 b ON a.col2 = b.col2

join problem

Hi All

I am tring to get some data from the customer table and linking it to a view

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status = cs.description

The reference between the tables is the account_status and the description

The error I am getting back is:

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Not sure what i means.....

check the collation of the two fields account_status and description
even if they are from the same type they have to be from the same collation
|||The columns in your JOIN might not be of the same COLLATION. You can work around this problem by adding a COLLATE statement on after you column names to explicitly set the collation...

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status COLLATE SQL_Latin1_General_CP1_CI_AS = cs.description COLLATE SQL_Latin1_General_CP1_CI_AS|||

The columns are NOT sharing the same collation, therefore they cannot be joined easily. What you an d is, to force them (or one of them) to use the same (normally one of the already existing one of the columns) collation like te other by using the COLLATE keyword:

select ca.account_status, cs.code from customer as ca
inner join v_customer_status as cs on ca.account_status = cs.description COLLATE SQL_Latin1_General_CP1_CI_AI

(change the collation and the column for your special enviroment)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thanks for all you help, it sorted the problem

Join on a third table

I currently have a join on 2 tables. An abbreviation of the code is as follow:

sql = "SELECT pd.RaceID, ir.EventPl, ir.RacePl FROM PartData pd INNER JOIN IndResults ir ON pd.PartID = ir.PartID WHERE pd.RaceID = " & lRaceID & " ORDER By EventPl"

Here's the problem: I need to change the WHERE clause to a field value from another table. How do I incorporate a join with a third table?

Thanks!select T1.*, T3.*
from T1 join T2 on T1.id=T2.id join T3 on T2.altid = T3.id|||...do I need to use JOIN as opposed to INNER JOIN? Also, do I need to give T2 and altid?

Thanks!|||Have you gone thru JOINS topic in books online, has got code examples so.|||I will do that!