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 Report & Multi Value vs. Single Value

I have a parameterized main report that contains a link to a sub report with parameters. Then from the sub report I have a link that takes you back to main report. If I use multi value parameters or single value parameters the links going back and forth between the two reports works just dandy, when I run it on my PC. However when I deploy the reports to the Web Server, the link going from the sub report back to the main report only works if the parameters are single select. Going from the Main to the Sub it doesn’t matter.

This is the error I get using multi select parameters going from the sub report back to the main.

The path of the item "(null)" is not valid. The path must be less than 260 characters long and must start with slash. Other restrictions apply. (rsInvalidItemPath)

Any suggestions?

By any chance, are the drillthrough links located in the page header or page footer?

-- Robert

Joining two tables with repeating rows

Hi. I am trying to get data from two different tables. The first table contains user access data for access to different modules of our application. There are only records for the modules that the user has access to. So if User1 can only access 2 of the 5 modules, there will be 2 User1 records. The second table lists the modules.

UserAccess table:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User2 1 1

Modules table:

ModuleID Description

1 Mod1

2 Mod2

3 Mod3

4 Mod4

5 Mod5

What I am trying to select is a list of all modules for each user, whether they have access or not. So basically I would like the data from the Modules table to repeat 5 rows for each user. This is a sample of the output I am trying to get:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User1 3 Null

User1 4 Null

User1 5 Null

User2 1 1

User2 2 Null

User2 3 Null

User2 4 Null

User2 5 Null

I've tried all sorts of joins but haven't been able to get this to happen. Not sure if this is possible? Thanks!!!

The magic words you want here are "CROSS JOIN". Previously known as 'comma'.

First make yourself a table of users. If you have this in a separate table already, then great. If you don't, slap yourself, write a post-it note to do it later, and make do with:

select *
from
(select distinct UserID from UserAccess) u

Now do your cross join to the modules table.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m

Note - there's no ON clause here... you want every possible combination.

Now join this to your UserAccess table to see if they have access or not. You'll want to use a LEFT JOIN to make sure you don't eliminate the rows you already have.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m
left join
UserAccess ua
on ua.moduleid = m.moduleid
and ua.userid = u.userid

Now, ua will have null records for the times when there is no record to match (it's the way LEFT JOIN works). So you can just have a look to see if one of the records which can't be null (like userid) is null or not...

select u.UserID, m.ModuleID, case when ua.UserID is null then 1 else 0 end as AccessLevel

from

(select distinct UserID from UserAccess) u

cross join

Modules m

left join

UserAccess ua

on ua.moduleid = m.moduleid

and ua.userid = u.userid

Hope this works for you!

Rob|||

Part of the problem is that you need to have a users table. Then this becomes a bit easier of a task. So I built one in the query:

--sample tables (please include in future if you can...)
create table userAccess
(
userId varchar(8)
,moduleId int
,accessLevel int
,primary key (userId, moduleId)
)
insert into userAccess
select 'User1', 1, 1
union all
select 'User1', 2, 1
union all
select 'User2', 1, 1

create table modules
(
moduleId int
,description varchar(10)
,primary key (moduleId)
)
insert into modules
select 1, 'Mod1'
union all
select 2, 'Mod2'
union all
select 3, 'Mod3'
union all
select 4, 'Mod4'
union all
select 5, 'Mod5'

This query will get it for you:

select users.userId, modules.moduleId,userAccess.accessLevel
from (select distinct userId
from userAccess) as users --creates the users table with all users that have some access
cross join modules --cross join it with modules to get the all modules for all users set.

left outer join userAccess --then left join it to the access table to get the accessLevel column...
on userAccess.userId = users.userId
and modules.moduleId = userAccess.moduleId

|||Apparently I worked on my solution for at least 15 minutes :)|||15 minutes? Really?|||

Thank you both so much!!! I KNEW there was a way to do this but I couldn't quite get there. I do have a users table so luckily I do not have to slap myself.

Thanks again!!!

|||

You try to find out in

www.sqlzoo.com

Joining two columns together to be searched as one

Hi all,

I have three questions to ask that hopefully can be answered. My database server is MySQL 4.1 and my database table contains the following example data:

Id | Store | Make | Model | Price
----------------
1 | Store1 | JVC | AV36D502 | 100.00
2 | Store2 | JVC | AV36D502 | 400.00
3 | Store1 | Panasonic | CT32HX41 | 250.00
4 | Store2 | Panasonic | CT32HX41 | 350.00
5 | Store1 | Sony | KV32S42 | 400.00
6 | Store2 | Sony | KV32S42 | 500.00

(1) Is it possibe to have in my select statement the following: "WHERE ((Make, Model) = 'Sony KV32S42')", I have already tried this but it throws up errors. What I'm trying to do is to some how join together the Make and Model columns and then to see if it matches the search term.

(2) If I was to "SELECT DISTINCT Make" or equivalent, rather than returning 3 rows is there anyway to return just the one row but with the value of "JVC Panasonic Sony" i.e. a string of all Makes, or is this impossible.

and (3) I have another select statement "SELECT DISTINCT Make, MIN(Price), MAX(Price)" with the output shown below. If I "ORDER BY Price ASC" the default is that it orders it by the maximum price, what I want to do is to order it by the minimum price only. "ORDER BY MIN(Price) ASC" has errors or again is this impossible.

Make | MIN(Price) | MAX(Price)
------------
JVC | 100.00 | 400.00
Panasonic | 250.00 | 350.00
Sony | 400.00 | 500.00

I know I'm asking a lot but again I just can't find any good information on the net. Thanking you in advance.(1) where concat_ws(' ',make.model) = 'Sony KV32S42'

(2) with the GROUP_CONCAT function

(3) you can't use DISTINCT here

select Make, MIN(Price) as minprice, MAX(Price) as maxprice
from yourtable group by Make order by minprice|||(1) WHERE make = 'Sony' AND model = 'KV32S42'.
(2) AFAIK, this is impossible with "standard" SQL.
(3) See r937's solution, or alternatively "ORDER BY 2".|||Many thanks guys,

(1) and (3) work perfectly now, (2) will work once I upgrade to mySQL 4.1 or 5.0|||btw, your design is denormalized... Is there a reason you don't store make and model in a seperate table?|||I'm sorry I don't understand, why would you want to put make and model in seperate tables, surely that would make the design more complex? Or would it be better (more efficient) if I just had the one column called "product" which would store the make and model, i.e. Sony KV32S42.|||It would make the model slighly more "complex" in that you will have an extra table, sure. However it will make the model much simpler in that you will have far more flexibility with a "normalized" design. Read up at this site (http://r937.com/relational.html). It has some good models for what I'm talking about.

You do have the right general idea though. If you were to put make and model in a seperate table with a unique key, you could then use that key to store prices for competitors. When it comes time to report, your queries become far easier and more intuitive, with less margin for error.

joining multiple tables

I have a quick question regarding the joining of multiple tables.

I have one main table that contains a TranID and some other fields. Then, I have about five tables that have detail information for each of the records in the main table. There is a one to one relationship between a detail record and the main record. The reason they are split between several detail tables is because the detail information is different based on TypeID.

My question is this: How can I join more than one of the detail tables?

This returns nothing....can someone explain why and hopefully provide a solution?

select * from MainTable m
Inner Join DetailTable1 d1 on m.TranID = d1.TranID
Inner Join DetailTable2 d2 on m.TranID = d2.TranID
Inner Join DetailTable3 d3 on m.TranID = d3.TranID
Inner Join DetailTable4 d4 on m.TranID = d4.TranID
Inner Join DetailTable5 d5 on m.TranID = d5.TranID

Thanks very much...If it returns nothing then one or more of the inner join conditions conditions are not met. That would be something like no d1TranID matching any m.TranID. Because they are all inner joins it only takes one bad join condition to break the whole thing.

To diagnose the problem you might try using Left Outer Joins and Right Outer Joins to see where matches are not being made between MainTable and each of the DetailTables. Something like:

Select m.TrandID,d1,TranID,d2,TranID,d3,TranID,d4,TranID,d5,TranID
from MainTable m
Left Outer Join DetailTable1 d1 on m.TranID = d1.TranID
Left Outer Join DetailTable2 d2 on m.TranID = d2.TranID
Left Outer Join DetailTable3 d3 on m.TranID = d3.TranID
Left Outer Join DetailTable4 d4 on m.TranID = d4.TranID
Left Outer Join DetailTable5 d5 on m.TranID = d5.TranID

and then to see where the detail table(s) have TranIDs that the MainTable doesn't have:

Select m.TrandID,d1,TranID,d2,TranID,d3,TranID,d4,TranID,d5,TranID
from MainTable m
Right Outer Join DetailTable1 d1 on m.TranID = d1.TranID
Right Outer Join DetailTable2 d2 on m.TranID = d2.TranID
Right Outer Join DetailTable3 d3 on m.TranID = d3.TranID
Right Outer Join DetailTable4 d4 on m.TranID = d4.TranID
Right Outer Join DetailTable5 d5 on m.TranID = d5.TranID

This should indicate what the problem(s) are.

Joining Data between 2 SQL Server machines

I’m trying to retrieve data from between two separate SQL server machines.
I
have Server A and B on separate machine. Server A contains the majority of
my data and I’m developing my select on that server. I’ve discovered th
at I
also need data from Server B. How do I accomplish this?
Code:
Select ColumnA, ColumnB, ColumnC
From TableA inner join
ColumnA ON
(Select ColumnD, ColumnE
From ServerB.dbo.TableG.ColumnD) = ColumnA
where ColumnB = 1
I only need to retrieve two columns from server B and join them in my
select on server A.You'll need to create a linked server. See
http://msdn2.microsoft.com/en-us/library/ms190479(SQL.90).aspx
for information on the topic. After the linked server is defined, you can
query the table on Server B using a four-part name:
SELECT * FROM ServerB.[database name].dbo.[table name]
"Larry Bird" wrote:

> I’m trying to retrieve data from between two separate SQL server machine
s. I
> have Server A and B on separate machine. Server A contains the majority o
f
> my data and I’m developing my select on that server. I’ve discovered
that I
> also need data from Server B. How do I accomplish this?
> Code:
> Select ColumnA, ColumnB, ColumnC
> From TableA inner join
> ColumnA ON
> (Select ColumnD, ColumnE
> From ServerB.dbo.TableG.ColumnD) = ColumnA
> where ColumnB = 1
> I only need to retrieve two columns from server B and join them in my
> select on server A.
>

Join tables in SQL 6.5 with SQL 7.0

I have a table in SQL that contains a timestamp column and I'm traying access it from a select statement in SQL 7.0, but I have a problem..., this is the error message:Server:
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata for a column. Metadata information was changed at execution time.\
but I can access other table in the same server...Consider simply not selecting the timestamp column. (Do not attempt to update or insert values into timestamp columns either.)

Timestamp columns generally do not need to be accessed for typical user purposes. Timestamp columns are system updated each time a row is inserted or updated (in a table object containing a timestamp column)

Join tables

hi
I got a confusing problem.I have 2 tables (Table_1 , Table_2) whit relation
On table_1.key and table_2.fkey.I need to get a table contains information from
2 table : title , key , fkey for mindate,mindate , describtion of mindate.
so I tried to write a function for returning (select top(1) * from table_2 order by date) so only
I need to connect this function to table_1. Here for running function I need to send Key to function
and I dont know how I can do that because wnehe I try to join them I get Error message.in seccond try I made a procedure
like this : SELECT Table_1.title, Table_1.[Key], Table_2.fkey, Table_2.date, Table_2.describtion
FROM Table_1 INNER JOIN
Table_2 ON Table_1.[Key] = Table_2.fkey where Table_2.[key]=(select top(1) Table_2.[key] from table_2 where fkey=Table_1.[key] order by date )

it works perfectly but gets more time to run whene we have 2000 records.Speed goes down ...

table_1:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[title] [nchar](10) COLLATE Arabic_CI_AS NULL,
[Key] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

table_2:
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
[date] [datetime] NULL,
[fkey] [int] NULL,
[key] [int] IDENTITY(1,1) NOT NULL,
[describtion] [nchar](10) COLLATE Arabic_CI_AS NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[key] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([fkey])
REFERENCES [dbo].[Table_1] ([Key])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]

data:
insert into table_1 (title) values('Title1')
insert into table_1 (title) values('Title2')
insert into table_1 (title) values('Title3')

insert into table_2 (date,fkey,describtion) values('2006/10/11',1,'dis 1')
insert into table_2 (date,fkey,describtion) values('2006/10/12',1,'dis 2')
insert into table_2 (date,fkey,describtion) values('2006/10/14',1,'dis 3')
insert into table_2 (date,fkey,describtion) values('2006/10/12',2,'dis 4')
insert into table_2 (date,fkey,describtion) values('2006/10/10',2,'dis 5')
insert into table_2 (date,fkey,describtion) values('2006/10/12',2,'dis 6')
insert into table_2 (date,fkey,describtion) values('2006/10/11',3,'dis 7')
insert into table_2 (date,fkey,describtion) values('2006/10/13',3,'dis 8')
insert into table_2 (date,fkey,describtion) values('2006/10/12',3,'dis 9')
insert into table_2 (date,fkey,describtion) values('2006/10/11',3,'dis 10')

resault :
Title1 1 1 2006-10-11 00:00:00.000 dis 1
Title2 2 2 2006-10-10 00:00:00.000 dis 5
Title3 3 3 2006-10-11 00:00:00.000 dis 7

hai,

First you can create a view

create view mindatelist as
select min(date) as mindate,fkey from Table_2
group by fkey

then run below query..

SELECT Table_1.title, Table_1.[Key], Table_2.fkey, Table_2.date, Table_2.describtion
FROM Table_1
INNER JOIN Table_2 ON Table_1.[Key] = Table_2.fkey
inner join mindatelist on mindatelist.fkey=Table_1.[Key] and Table_2.date=mindatelist.mindate

Next check the performance of this query...

Jefy

|||the resault is rong because we get 2 rows for key # 3
Title1 1 1 2006-10-11 00:00:00.000 dis 1
Title2 2 2 2006-10-10 00:00:00.000 dis 5
Title3 3 3 2006-10-11 00:00:00.000 dis 7
Title3 3 3 2006-10-11 00:00:00.000 dis 10|||

Koosha:

One thing to understand is that your code without the scalar function will run faster than with the scalar function; there is a certain amount of additional overhead that goes with the scalar function pluse the optimizer doesn't optimize scalar functions well. I tried a few things to try to speed up your query. First, I ran your query to get a performance baseline. I then modified the query into what is listed below and tested with the sample data you provided to verify that it qualitatively looked correct.

Next, I generated 2048 entries for table_1 and 32767 entries for table_2 and benchmarked under these circumstances. This is a really small sample set to benchmark with, but I still think the results will at least be indicative. I then compared the modified code to the original code; the time reduction associated with the new code was about 70%; the IO reduction of the new code was about 99%. This does NOT mean that the code is optimized, but it does mean that it is improved -- at least under my particular test circumstances.

Next, I did an experiment to see if a cover index might improve performance. The cover index reduced IO by a very thin 8% or so. Execution time was reduced by about 30%. I would suggest that if this function is not critical that I would probably NOT implement a cover index -- I just don't think it is going to be worth the overhead. Here is the query:

Dave

select t1.title,
t1.[key],
t2.fkey,
t2.date,
t2.describtion
from table_1 t1
inner join
( select [key],
fkey,
date,
describtion,
row_number () over
( partition by fkey
order by date, [key]
) as seq
from table_2
) t2
on t1.[key] = t2.fkey
and t2.seq = 1

|||it works perfectly.thanks for ur helping

Join records of each group

Hello, thanks in advance for your help / comments

I have 2 tables:

SampleInfo contains 2 columns: SampleID & SampleName
Analysis contains 2 columns: SampleID & Elements

I link these 2 tables, get the SampleName & Elements out by the code:

SELECT SampleInfo.SampleName, Analysis.Elements

FROM SampleInfo INNER JOIN Analysis ON (SampleInfo.SampleID = Analysis.SampleID)

It would display

SampleName | Elements
A | a
A | b
A | c
A | f
B | a
B | g
B | l
C | c
C | s
C | o
C | m
C | n

I need to display the report as following:

SampleName | Elements
A | a, b, c, f
B | a, g, l
C | c, s, o, m, n

QUESTION: is it possible? If it is, how should I do this?

FYI, I use CR10 & SQLServer 2000 database

Regards,
tHi,

I found some solution to yor post.

I created a report with excel as datasource.

Grouped the report on Sample Name field.
Created two formula fields to get the results.
1. Elements -- Formula Field
Code for formula filed as follows :
whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;
2.ResetElementValue -- Formula Field
whileprintingrecords;
shared stringvar Elements;
Elements:="";
-- Place the @.Elements fromula filed in detail section and suppress the section.
-- Place the Group Name in Group Footer section
-- Place @.Elements in the same group Footer section
-- Place the ResetElementValue formula filed in the Group Header section and suppres

Try with the following format and let me know will it fulfills your requirement.

Thanks,
Vidu.
-- Group|||Vidu,
Thanks for your help. It was a great start. I only had to change the Elements (formula field) a little as below. If I don't have the ELSE IF, it will double the last element in each group.

whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else IF Elements <> Right(Elements, length({Sheet1_.Elements}) then
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;

Again, your help is truly appreciated.|||or place the formula in group footer and suppress the details and group header

Join Query Help

Thank you for your help. I hope this makes sense.
I have a table that contains 8 rows. Each row describes a phone type
(office, voice, etc) with a primary key and description, thats all.
Another table contains customer rows (first name, last, etc), and a primary
key, customerid.
A third table contains phone numbers as they relate to customers. A PhoneId
field is the primary key for this table. The customerid and the phone type
primary key are foreign keys in this table.
A customer can have many phone types. I am trying to write a query that gets
the existing phone types with phone, along with those phone types that the
customer does NOT have. My WHERE clause in this query is what I think is
breaking it.
A full outer join is not accomplishing this. I only get those numbers that
already are assigned to the customer. Since there are 8 types of numbers I
expect 8 rows.
SELECT
p.PhoneType, ph.PhoneTypeFK, ph.AreaCode, ph.PhoneNumber, ph.Extension,
FROM Phones ph
FULL OUTER JOIN PhoneTypes p
on ph.PhoneTypeFK = p.PhoneTypePK
WHERE ph.CustomerID = 12345
How can I get all 8 rows?never mind guys... I got it as soon as I posted it. I am now doing an inner
query that returns the phone numbers assigned and FULL OUTER JOINing that
with an outer query that retrieves all my phone types on the PhoneTypeFK.
Thanks
"Learning SQL Server" <no.mail.com> wrote in message
news:OsLsLaEXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> Thank you for your help. I hope this makes sense.
> I have a table that contains 8 rows. Each row describes a phone type
> (office, voice, etc) with a primary key and description, thats all.
> Another table contains customer rows (first name, last, etc), and a
primary
> key, customerid.
> A third table contains phone numbers as they relate to customers. A
PhoneId
> field is the primary key for this table. The customerid and the phone type
> primary key are foreign keys in this table.
> A customer can have many phone types. I am trying to write a query that
gets
> the existing phone types with phone, along with those phone types that the
> customer does NOT have. My WHERE clause in this query is what I think is
> breaking it.
> A full outer join is not accomplishing this. I only get those numbers that
> already are assigned to the customer. Since there are 8 types of numbers I
> expect 8 rows.
> SELECT
> p.PhoneType, ph.PhoneTypeFK, ph.AreaCode, ph.PhoneNumber, ph.Extension,
> FROM Phones ph
> FULL OUTER JOIN PhoneTypes p
> on ph.PhoneTypeFK = p.PhoneTypePK
> WHERE ph.CustomerID = 12345
> How can I get all 8 rows?
>
>|||even better - a simple left join does the trick.
"Learning SQL Server" <no.mail.com> wrote in message
news:OsLsLaEXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> Thank you for your help. I hope this makes sense.
> I have a table that contains 8 rows. Each row describes a phone type
> (office, voice, etc) with a primary key and description, thats all.
> Another table contains customer rows (first name, last, etc), and a
primary
> key, customerid.
> A third table contains phone numbers as they relate to customers. A
PhoneId
> field is the primary key for this table. The customerid and the phone type
> primary key are foreign keys in this table.
> A customer can have many phone types. I am trying to write a query that
gets
> the existing phone types with phone, along with those phone types that the
> customer does NOT have. My WHERE clause in this query is what I think is
> breaking it.
> A full outer join is not accomplishing this. I only get those numbers that
> already are assigned to the customer. Since there are 8 types of numbers I
> expect 8 rows.
> SELECT
> p.PhoneType, ph.PhoneTypeFK, ph.AreaCode, ph.PhoneNumber, ph.Extension,
> FROM Phones ph
> FULL OUTER JOIN PhoneTypes p
> on ph.PhoneTypeFK = p.PhoneTypePK
> WHERE ph.CustomerID = 12345
> How can I get all 8 rows?
>
>

JOIN outside of WHERE clause ? ? ? ?

view 1

I have a view that is drawing from two tables. Each table contains fields representing cube coordinates. The view is filtering the results based on some simple logic (where the defference between two values in the tables are greater than x) this part works fine.

view 2

notes field

I want to include a note field in my view. This field will contain the contents of a note field from another view. This second view also contains coordinates that I can use to map the notes to the appropriate rows in view 1. However, if I join the views in my FROM clause, I will end up filtering my resultset down to rows that correspond to view 2's contents.

I want to have the full contents of view 1, displayed with a note field containing the note field content from view 2 only in the rows that have corresponding notes. (some rows will have notes, some will not)

eg.

VIEW 1

row1 row2 row3 note_row (from view 2)

fsdfs sdfsdf sdfsdf <no note>

sdfs sdfsd sdfsd "note"

sdfsdf sdfsdf ssdfsd <no note>

so... my question: is there any way that I can include this field without joining the views in my FROM clause (meking my resultset exclusive)..... possibly somehow in fields list of the select statement?

THANKS!

Sounds like a UNION or UNION ALL would do the trick because you could avoid joining the two recordsets. Create that as your inner query and query it as a virtual table, maybe?

Lee Everest

www.texastoo.com/sqlblog

|||

You're looking at left join.

e.g.

Code Snippet

select v1.*, v2.notes

from v1 left join v2 on v1.id=v2.id

|||Thanks. I realized this in the middle of the night last night and tried it out... the LEFT JOIN works. Thanks.

JOIN on fields where ther may be NULL values

I need to extract data from 4 tables. Each of these
tables contains a field called 'page'. In some instances
this field is null (otherwise an integer).
Here's my problem: I need to join these tables on
the 'id' field and 'page', e.g.
SELECT t1.id, t1.name, t1.date, t2.action, t3.country,
t4.region
FROM table1 t1
LEFT OUTER JOIN table2 t2 on t1.id=t2.id AND
t1.page=t2.page
LEFT OUTER JOIN table3 t3 on t1.id=t3.id AND
t1.page=t3.page
LEFT OUTER JOIN table4 t4 on t1.id=t4.id AND
t1.page=t4.page
WHERE t1.category='questions' AND t1.flag='t'
The query runs and returns data; however, for those rows
where 'page' is null, I do not get any data from tables
t2, t3, t4.
What am I doing wrong?
Thanks,
Rafal
Hi Rafal,
Left Outer Join returns all the records from the table on the Left (in your
case t1) and data that is matching the criteria from the table on the right
(in you case T2 or T3 or T4).
If you want records from both the table, irrespective of whether the
condition matches, you need to use Full Outer Join.
When you use Full Outer Join, data from both the tables is returned, if a
match for the criteria is found then the repective coulmn gets the value
else it will return null.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

JOIN on fields where ther may be NULL values

I need to extract data from 4 tables. Each of these
tables contains a field called 'page'. In some instances
this field is null (otherwise an integer).
Here's my problem: I need to join these tables on
the 'id' field and 'page', e.g.
SELECT t1.id, t1.name, t1.date, t2.action, t3.country,
t4.region
FROM table1 t1
LEFT OUTER JOIN table2 t2 on t1.id=t2.id AND
t1.page=t2.page
LEFT OUTER JOIN table3 t3 on t1.id=t3.id AND
t1.page=t3.page
LEFT OUTER JOIN table4 t4 on t1.id=t4.id AND
t1.page=t4.page
WHERE t1.category='questions' AND t1.flag='t'
The query runs and returns data; however, for those rows
where 'page' is null, I do not get any data from tables
t2, t3, t4.
What am I doing wrong?
Thanks,
RafalHi Rafal,
Left Outer Join returns all the records from the table on the Left (in your
case t1) and data that is matching the criteria from the table on the right
(in you case T2 or T3 or T4).
If you want records from both the table, irrespective of whether the
condition matches, you need to use Full Outer Join.
When you use Full Outer Join, data from both the tables is returned, if a
match for the criteria is found then the repective coulmn gets the value
else it will return null.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

JOIN ON CONTAINS(Table1.Field1, Table2.Field2)

Hi,

I am getting errors in the following... Is it even possible to join on
CONTAINS?

SELECT ListA.Content
FROM ListA LEFT OUTER JOIN ListB
ON CONTAINS(ListB.Content, ListA.Content)
WHERE ListB.Content IS NULL

Thanks!Hi

CONTAINS does not take a column as the second parameter, therefore you you
can't use it.

These may be an alternative:

SELECT A.Content
FROM ListA A LEFT OUTER JOIN ListB B
ON CHARINDEX( B.Content, A.Content ) > 0
WHERE B.Content IS NULL

SELECT A.Content
FROM ListA A LEFT OUTER JOIN ListB B
ON A.Content LIKE '%' + B.Content + '%'
WHERE B.Content IS NULL

John

"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:fihfc.9552$k05.6725@.newsread2.news.pas.earthl ink.net...
> Hi,
> I am getting errors in the following... Is it even possible to join on
> CONTAINS?
> SELECT ListA.Content
> FROM ListA LEFT OUTER JOIN ListB
> ON CONTAINS(ListB.Content, ListA.Content)
> WHERE ListB.Content IS NULL
> Thanks!|||I'm not sure if that would work. The reason I need to use CONTAINS (or an
equivalent) is that I need to do an inflectional search. I abbreviated the
code as much as possible for the group... Perhaps too much. What I really
need to join on is something like:

SELECT ListA.Content
FROM ListA LEFT OUTER JOIN ListB
ON CONTAINS(ListB.Content, FORMS OF(INFLECTIONAL, ListA.Content))
WHERE ListB.Content IS NULL

Any ideas?

Thanks!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:Qfigc.1635$kk5.16882717@.news-text.cableinet.net...
> Hi
> CONTAINS does not take a column as the second parameter, therefore you you
> can't use it.
> These may be an alternative:
> SELECT A.Content
> FROM ListA A LEFT OUTER JOIN ListB B
> ON CHARINDEX( B.Content, A.Content ) > 0
> WHERE B.Content IS NULL
>
> SELECT A.Content
> FROM ListA A LEFT OUTER JOIN ListB B
> ON A.Content LIKE '%' + B.Content + '%'
> WHERE B.Content IS NULL
> John
> "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:fihfc.9552$k05.6725@.newsread2.news.pas.earthl ink.net...
> > Hi,
> > I am getting errors in the following... Is it even possible to join on
> > CONTAINS?
> > SELECT ListA.Content
> > FROM ListA LEFT OUTER JOIN ListB
> > ON CONTAINS(ListB.Content, ListA.Content)
> > WHERE ListB.Content IS NULL
> > Thanks!|||Hi

CONTAINS does not take a column as the second parameter, therefore you you
can't use it.

These may be an alternative:

SELECT A.Content
FROM ListA A LEFT OUTER JOIN ListB B
ON CHARINDEX( B.Content, A.Content ) > 0
WHERE B.Content IS NULL

SELECT A.Content
FROM ListA A LEFT OUTER JOIN ListB B
ON A.Content LIKE '%' + B.Content + '%'
WHERE B.Content IS NULL

John

"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:fihfc.9552$k05.6725@.newsread2.news.pas.earthl ink.net...
> Hi,
> I am getting errors in the following... Is it even possible to join on
> CONTAINS?
> SELECT ListA.Content
> FROM ListA LEFT OUTER JOIN ListB
> ON CONTAINS(ListB.Content, ListA.Content)
> WHERE ListB.Content IS NULL
> Thanks!|||I'm not sure if that would work. The reason I need to use CONTAINS (or an
equivalent) is that I need to do an inflectional search. I abbreviated the
code as much as possible for the group... Perhaps too much. What I really
need to join on is something like:

SELECT ListA.Content
FROM ListA LEFT OUTER JOIN ListB
ON CONTAINS(ListB.Content, FORMS OF(INFLECTIONAL, ListA.Content))
WHERE ListB.Content IS NULL

Any ideas?

Thanks!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:Qfigc.1635$kk5.16882717@.news-text.cableinet.net...
> Hi
> CONTAINS does not take a column as the second parameter, therefore you you
> can't use it.
> These may be an alternative:
> SELECT A.Content
> FROM ListA A LEFT OUTER JOIN ListB B
> ON CHARINDEX( B.Content, A.Content ) > 0
> WHERE B.Content IS NULL
>
> SELECT A.Content
> FROM ListA A LEFT OUTER JOIN ListB B
> ON A.Content LIKE '%' + B.Content + '%'
> WHERE B.Content IS NULL
> John
> "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:fihfc.9552$k05.6725@.newsread2.news.pas.earthl ink.net...
> > Hi,
> > I am getting errors in the following... Is it even possible to join on
> > CONTAINS?
> > SELECT ListA.Content
> > FROM ListA LEFT OUTER JOIN ListB
> > ON CONTAINS(ListB.Content, ListA.Content)
> > WHERE ListB.Content IS NULL
> > Thanks!