Jump to Report Issue

I have a "Jump to Report" issue. I have created a report that runs itself
with new parameters when a cell is clicked on. It works great except that
when it is run on the web, the "Jump to Report" has no access to the
parameters. I need to be able to have the parameters visible so the users
can change out their values at will.
I can not find any settings to turn on or off the parameters in the Jump-to
screens.You have to use Jump to URL. Here is an example of an expression that I use
for this. Note the global variable that puts in the server name of where the
report is running from.
=Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
First(Fields!manifstdocno.Value, "LoadID") &"&WasteIDNum=" &
First(Fields!wasteidnum.Value, "LoadID")
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message
news:F9EDC25A-F1E3-48A5-9359-D6C4DEF27D4F@.microsoft.com...
> I have a "Jump to Report" issue. I have created a report that runs itself
> with new parameters when a cell is clicked on. It works great except that
> when it is run on the web, the "Jump to Report" has no access to the
> parameters. I need to be able to have the parameters visible so the users
> can change out their values at will.
> I can not find any settings to turn on or off the parameters in the
Jump-to
> screens.

jump to report in the same frame of calling report

Hi,
i had created a report (ReportA) in which i m calling another report (ReportB). I had added a textbox (tb) in ReportA and had set the navigation property of tb to "jump to report" and given a report name ReportB (i.e. the second report).
Now, i m using asp.net report service,
i had created an asp form in which i had added a frame, n in tat frame i had added the report service control, this report service control calls my first report (ReportA),when i click on the textbox (tb) it jumps to another report (ReportB), now the problem is when i click textbox (tb), it jumps to ReportB but this report takes whole area of internet explorer.
wat i want is when i click textbox (tb), the next report (ReportB) should come in the same frame where my first report (ReportA) was.
Plz help me out.
Thanx.
--
Message posted via http://www.sqlmonster.comIn the URL for ReportA, you'll need to Add:
&rc:LinkTarget=nameofFrame
Then in your textbox tb, you'll need to set your navigation property to jump
to URL, giving it the URL of ReportB.
"Imran Saiyed via SQLMonster.com" wrote:
> Hi,
> i had created a report (ReportA) in which i m calling another report (ReportB). I had added a textbox (tb) in ReportA and had set the navigation property of tb to "jump to report" and given a report name ReportB (i.e. the second report).
> Now, i m using asp.net report service,
> i had created an asp form in which i had added a frame, n in tat frame i had added the report service control, this report service control calls my first report (ReportA),when i click on the textbox (tb) it jumps to another report (ReportB), now the problem is when i click textbox (tb), it jumps to ReportB but this report takes whole area of internet explorer.
> wat i want is when i click textbox (tb), the next report (ReportB) should come in the same frame where my first report (ReportA) was.
> Plz help me out.
> Thanx.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi,
Thanx for ur suggestion, but
firstly,
i had created my project such tat whenever new session of project is opened, it ask for username and password. therefore if i add the url of ReportB in the navigation property of textbox (tb) "jump to url", then whenever tb is clicked, it creates a new session for ReportB, so first the login screen occurs, then it enters into the project, then again i have to go to ReportA, then click tb. Therefore i was using "Jump to report" property which directly opens the ReportB.
secondly,
now i had removed the frame for some problem i was facing, n now i m using table (i.e. <td><\td>) between this td the reportviewer control is added. so i want to show ReportB in this table.
Thanx.
--
Message posted via http://www.sqlmonster.com

Jump to Report - misdirection

I have created a baseline folder with several reports, which I use as a "link to" from production folders for a variety of departments. Moreover, I have used a "Jump To Report" configuration as a drill-through to another report. The problem is that when I click on the link it executes the report in the baseline folder versus the report in the production department folder. How do I direct the execution to the appropriate production department folder(s)? Thanks in advance for any assistance.

The link to the drillthrough report can either be relative to the current report, for ex:

detailsreport.rdl - which means we will look for it in the current folder, or

..\OtherFolder\detailsreport.rdl - relative to the path of the current report

or absolute:

/folder1/folder2/detailsreport.rdl - this path is relative to the report server root.

Either one of these can probably be made to work for your scenario.

Thanks

Tudor

|||

I tried each option that you presented; however, I still don't get the results that I think should occur. I do, however, think we may be on the right track.

Let me restate the requirements a little differently. Let's say that I use the "Baseline" as the central storage area for all of my reports. Then, let's say I have two departments: Dept A and Dept B. Each department has its own separate folder with links to the same reports in Baseline; but, the parameters passed from each Department is somewhat different. For example, each would have a different department Id to distinquish itself.

Using this scenario and with the drill-through set as 'detailsreport.rdl', I would think that when executing the rdl from Dept A folder would use the detailsreport.rdl in Dept A folder. So far, this has not been the case for me. It executes the detailsreport.rdl in Baseline.

|||

That is correct. If you use linked reports, the relative links will be relative to the reports that contain the definition, not the link.

Jump to report

Hi all,

I have created a hyperlink(textbox) with an action property setting of "Jump to report" The report I am jumping to takes 5 parameters. I want to pass 4 of the parameters from the main report and have the user enter the 5th. I have mapped the 4 parameters and in preview mode, everthing works great. In preview mode, the parametrs are passed and the last parameter is left waiting for entry. After deployment, this is not the case. In the deployed state, I simply get an error that parameter #5 is blank. Is there there something I am missing?

regards,

Bill

I am having the same issue. I have a report that you can jump to via a hyperlink with parametes but I also need the ability to jump to the report and have the user get prompted for the parameter. Works in Visual Studio but not when I deploy.

The only workaround I have found is to use the jump to URL and put the "http//<servername>/reportserver?/<FolderName>/<reportname>" in there. You'd have to tack on the report parameters on the end of the url

|||Just a bump. Hoping someone has some insight here.

Jump to report

Hi all,

I have created a hyperlink(textbox) with an action property setting of "Jump to report" The report I am jumping to takes 5 parameters. I want to pass 4 of the parameters from the main report and have the user enter the 5th. I have mapped the 4 parameters and in preview mode, everthing works great. In preview mode, the parametrs are passed and the last parameter is left waiting for entry. After deployment, this is not the case. In the deployed state, I simply get an error that parameter #5 is blank. Is there there something I am missing?

regards,

Bill

I am having the same issue. I have a report that you can jump to via a hyperlink with parametes but I also need the ability to jump to the report and have the user get prompted for the parameter. Works in Visual Studio but not when I deploy.

The only workaround I have found is to use the jump to URL and put the "http//<servername>/reportserver?/<FolderName>/<reportname>" in there. You'd have to tack on the report parameters on the end of the url

|||Just a bump. Hoping someone has some insight here.

Jump to report

Hi all,

I have created a hyperlink(textbox) with an action property setting of "Jump to report" The report I am jumping to takes 5 parameters. I want to pass 4 of the parameters from the main report and have the user enter the 5th. I have mapped the 4 parameters and in preview mode, everthing works great. In preview mode, the parametrs are passed and the last parameter is left waiting for entry. After deployment, this is not the case. In the deployed state, I simply get an error that parameter #5 is blank. Is there there something I am missing?

regards,

Bill

I am having the same issue. I have a report that you can jump to via a hyperlink with parametes but I also need the ability to jump to the report and have the user get prompted for the parameter. Works in Visual Studio but not when I deploy.

The only workaround I have found is to use the jump to URL and put the "http//<servername>/reportserver?/<FolderName>/<reportname>" in there. You'd have to tack on the report parameters on the end of the url

|||Just a bump. Hoping someone has some insight here.sql

Julian Day

I have created an auxiliary calendar table with the help of
http://www.aspfaq.com/2519
I would like to add a column for the daynumber of year (e.g. 1-365), and
populate it. I am new to sql and am not finding help with this. Any help
would be greatly appreciated.select datepart(dayofyear, getdate())
-- or
select datepart(dy, getdate())
"Nice_Out" wrote:

> I have created an auxiliary calendar table with the help of
> http://www.aspfaq.com/2519
> I would like to add a column for the daynumber of year (e.g. 1-365), and
> populate it. I am new to sql and am not finding help with this. Any help
> would be greatly appreciated.|||Thanks.
"KH" wrote:
> select datepart(dayofyear, getdate())
> -- or
> select datepart(dy, getdate())
>
> "Nice_Out" wrote:
>sql

Joins and inices

I can't understand this behavior:
I'm doing a join on a large table, and I created a
clustered index on the column in the join condition.
I would expect that this should enhance performance,
because of the merge join advantage.
It didn't perform well.
I then tried making that index non-clustered, and doing
a clustered index on an identity column (as a primary
key).
It's weird, but the join performs far better this way.
The only way I can explain this is that the non-clustered
index keeps the values of the join column in fewer pages,
and therefore closer together on disk (fewer reads).
Then again, there must still be the additional seek to
get to the rest of the "data" that row contains.
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
If anyone has any experience with this, or suggestions,
I'd be really grateful for some help.
Thanks in advance,
Andrew.It would be easier to offer ideas if you posted DDL and ideally insert
statements to load some sample data...
this doesn't answer all of your questions but as an FYI...
<<
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
the NC index actually keeps the clustering key as part of it's own key
information at the leaf level of the NC index. So... you're actually
changing the contents of the NC index if you get rid of the clustering key.
Also, NC index is stored entirely differently if there is no clustered index
at all...
--
Brian
"Andrew" <a@.b.com> wrote in message
news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
> I can't understand this behavior:
> I'm doing a join on a large table, and I created a
> clustered index on the column in the join condition.
> I would expect that this should enhance performance,
> because of the merge join advantage.
> It didn't perform well.
> I then tried making that index non-clustered, and doing
> a clustered index on an identity column (as a primary
> key).
> It's weird, but the join performs far better this way.
> The only way I can explain this is that the non-clustered
> index keeps the values of the join column in fewer pages,
> and therefore closer together on disk (fewer reads).
> Then again, there must still be the additional seek to
> get to the rest of the "data" that row contains.
> Even stranger, if the clustered index on the identity col-
> umn (PK) is not there, the join doesn't perform well also.
> If anyone has any experience with this, or suggestions,
> I'd be really grateful for some help.
> Thanks in advance,
> Andrew.|||Still...
><<
>Even stranger, if the clustered index on the identity
col-
>umn (PK) is not there, the join doesn't perform well
also.
>the NC index actually keeps the clustering key as part
of it's own key
>information at the leaf level of the NC index. So...
you're actually
>changing the contents of the NC index if you get rid of
the clustering key.
>Also, NC index is stored entirely differently if there
is no clustered index
>at all...
>--
I realize the NC index keeps the clustering key as part
of it's own key, but I had understood that SQL server
keeps a RID (row identifier) internally when there is no
clustered index... so rather thank keeping the clustered
index in the leaf, the RID is kept.
Now, if accessing that RID is slow, I can understand, but
I'd imagined it as a kind of internal indexed identity
of its own. I guess this doesn't make sense, though.
As a follow up, though, is there no use for a NC index
without a clustered index when you want to access columns
outside of the NC indexed column?
Also, can a column be both a clustered and a non-clustered
index (would this help on join performance to access those
other columns?).
Thanks again,
Andrew
>Brian
>
>"Andrew" <a@.b.com> wrote in message
>news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
>> I can't understand this behavior:
>> I'm doing a join on a large table, and I created a
>> clustered index on the column in the join condition.
>> I would expect that this should enhance performance,
>> because of the merge join advantage.
>> It didn't perform well.
>> I then tried making that index non-clustered, and doing
>> a clustered index on an identity column (as a primary
>> key).
>> It's weird, but the join performs far better this way.
>> The only way I can explain this is that the non-
clustered
>> index keeps the values of the join column in fewer
pages,
>> and therefore closer together on disk (fewer reads).
>> Then again, there must still be the additional seek to
>> get to the rest of the "data" that row contains.
>> Even stranger, if the clustered index on the identity
col-
>> umn (PK) is not there, the join doesn't perform well
also.
>> If anyone has any experience with this, or suggestions,
>> I'd be really grateful for some help.
>> Thanks in advance,
>> Andrew.
>
>.
>

Joining two tables to modify a tree structure with more information

I'm trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry for the tabs that won't work)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
1506 3823136510667
1507 3824136510667
1509 3825136410667
1509 3826136410667
I have tried many ways, but I'm not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable

Thanks :)Worked it out.Used inner join to get the first data, and run an update afterwards to update level 3 and 4sql

Joining two tables to modify a tree structure

I’m trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but I’m not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

Quote:

Originally Posted by KingKong07

Im trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but Im not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

I think you have to Innerjoin the tables

Just go through this|||Thanks for answer, I had made it a bit to complex.

Worked it out.

Used inner join to get the first data, and run an update afterwards to update level 3 and 4|||

Quote:

Originally Posted by KingKong07

Im trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but Im not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

1- i think you should have a MainCatalogId in your second table.because if you have many Maincatalog you can not save relation for other Maincatalog if you have repeated code of items.
2-you must create a dataset returning storedprocedure with the columns for your output.
3-in your storedprocedure you must have three cursor with three loop for three level(suppose you have three level),each loop contains parent loop.then fetch what you want to output for your output dataset.
4-use your storedprocedure As a table in your application .

Joining 2 fields: Redundancy results..need help

I am working on this access DB that has been created by some consulting firm couple of years ago, which I wasnt here at that time.

The DB is being by college faculty members to store about students who study abroad..
now,
there are students who has more than one major.. and when I run query to find out how many students are studying abroad..
it shows me more than the actual number of students who are studying abroad, because it shows same student twice because of double major..
In other words, it inserts two records for a student who has 2 majors into the DB.

Now my question is, is it possible to combine 2 records into one record on query results?
I know you can do the following: SELECT StudentName = 'major + major'

but the problem is, the name for field major is the same.. so I cannot say in my query 'Major + Major' to combine 2 records. it doesnt work..

let me know if anyone has solution to this, that will be greatful...
thanks,

moradCan you post your table structure? That would help me give you more specific answers.

The short answer is to pick one of the majors as the "most important", and select only that row using a condition in the WHERE clause. If you want access to both rows, use a LEFT JOIN to get access to the second row. If you post a DDL declaration, I can give you more specific help.

-PatP|||I just attached the structure..
well, there is most important major on this database..
and all records are stored in that table...

for example.. here is my query

First Name Last Name Host Country ProgramName Sponsor
Rebecca AINSWO Griffith University Australia N/A Direct
Ronda ALEXAN Universitat at Bonn Germany N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Nicholas Applin University of Wollongong Australia N/A Western Michigan University

I get two records of Ander! because Matt Ander has double major, thus he has double records...two records are the same except the major.

let me know what you think...
thanks|||Now I've got the stuff that I needed to get specific! Try using:SELECT *
FROM tblMajor AS a
LEFT JOIN tblMajor AS b
ON (b.SID = a.SID)
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID)
AND (a.MajId < b.MajId OR b.MajId IS NULL);I'm pretty sure that this will give you what you want.

-PatP|||it worked :) but here is the thing though..
it only selected the duplicates, what about the other records that are not duplicate..

the query doesnt show them...
what do I have to add to show the rest... let me know :)
because I tried to use the union and I couldnt t use it union because I need to have same amount of columns for two tables.

thanks.. I appreciate your help|||Crud! The syntax I posted works with real SQL, but not with Jet (the default engine supplied with MS-Access). You could use something like:SELECT a.*
, (SELECT Max(b.MajId)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);This works around an ugly limitation of the Jet database engine.

-PatP|||I tried that today at work, and it worked, but the only thing is that I wanted to change is, instead of showing what record number of the 2nd major for the student, I wanted to show the actual 2nd Major Name.

in other words I want second_major to show the actual name of the major and not the field number of 2nd major..

let me know if you have an idea..

thanks|||Picky, picky, picky... ;)SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);...should fix you right up!

-PatP|||hey pat,
thanks for great help...

Now, what I asked you about was for tblMajor.

what I am trying to do now, is run a query to list students and their majors as well as their minors..

When I tried to run your code for Majors.. it worked, and it added a new field called second major.

but now, when I try to include Minor in my query, it would show the same problem, because student can have more than one minor.

So basically, the objective is to get the following results
SID, Major, 2nd Major, Minor, 2nd Minor

And the minor table is same as major table design as shown above in my previous post.

Now what I want to know, is how to combine these two queries into one

this:
SELECT a.*, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS [Second Major]
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);

AND

SELECT e.*, (SELECT Max(f.Minor)
FROM tblMinor AS f
WHERE f.SID = e.SID
AND e.MinId < b.MinId) AS [Second Minor]
FROM tblMinor AS e
WHERE e.MinId = (SELECT Min(g.MinId)
FROM tblMinor AS g
WHERE g.SID = e.SID);

Now I was thinking of having using SID as relationship between them, but then I couldnt figure it out.. let me know if you have an idea of how to..

thanks|||The second minor throws an interesting wrinkle into the query, because it now makes a three set intersection instead of just two. You'll need to test this carefully with your data, but I think that you can use:SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMajor AS d
WHERE d.SID = a.SID
AND d.Minor <> a.Minor) AS second_minor
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);The gist of this query is that the first row you'd find if the table was sorted by SID then by MajId is assumed to contain the student's "primary" major and minor. The b and d subqueries find the largest Major and Minor that aren't the "primary" values. While this makes perfect sense to me as an outsider, it may or may not make sense in terms of your data, YMMV (your milage may vary). Test this carefully, but logically it should work.

-PatP|||I've tried the code you posted with few tweeks and I was able to get it through

SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS d, tblMinor AS e
WHERE d.SID = a.SID
AND d.Minor <> e.Minor) AS second_minor
FROM tblMajor AS a, tblProcessInfo, tblMinor
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID) AND tblProcessInfo.SID = a.SID AND tblProcessInfo.Term = '041';

Now, this query only shows the 2nd minor and not the first minor..
I tried to select tblMinor.Minor in the select statement, and that didnt help much..
let me know what you think..

thanks..|||This is a pure crap-shoot, based on the assumption that the tblMinor structure is exactly like the tblMajor structure. You'll need to test this very carefully before you "bless" this into production!!!SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS e
WHERE e.SID = a.SID
AND a.MajId < e.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS e
WHERE e.SID = b.SID
AND b.MinId < e.MinId) AS second_minor
FROM tblProcessInfo AS p
JOIN tblMajor AS a
ON (a.SID = p.SID
AND a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID))
LEFT JOIN tblMinor AS b
on (b.SID = p.SID
AND b.MinId = (SELECT Min(d.MinId)
FROM tblMinor AS d
WHERE d.SID = a.SID))
WHERE tblProcessInfo.Term = '041';If this doesn't work, I'd suggest that you create a "play" copy of your MDB file. Butcher the names and the universities to avoid giving out any usable personal information and post the MDB so I can work with your structures instead of having to guess about everything.

Better yet, see if you can find some enterprising grad student scrabbling for some way to get a few co-op dollars or even just some resum worthy experience! I'm sure that some of them would eat this kind of problem alive, and grovel for the opportunity!

-PatP|||hey pat, thanks for your great help..
I was gone for finals and projects that were due..but everything is back to normal now :)

Now, I need a logical explanation for this problem..

When I run a query of how many students were in a certain country from year 2000 to 2004 I get 490 Students (No duplication records)

And when I run a query of how many students with majors (tblMajor Does have duplication records because of having more than one major) that went to that country from 2000 to 2004.. I get 435

I am missing 50 records when I link tblMajor.SID with tblPermInfo.SID and run a query.

I just dont get it why?!
I thought for myself, that tblPermInfo maybe is giving 490 because there is duplication of records for having more than one major, but then it is not linked to tblMajor..so there is no duplication in what so ever.
but when i run a query where SID of tblMajor and tblPermInfo is matched...it only gives me 435...
so there are SIDs that are left over because there is no match btw tables..right?

What other logical reasons could there be..
let me know what you think..
thanks

kicker

Join two views using server aliases

Hello everybody,
I'm working for a hospital and for this job, I created two views using the
server aliases (linked servers).
Both these views are working correctly.
The design of the first view is:
SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
SPECIALTY_DESCRIPTION AS NAME
FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
ORDER BY SPECIALTY_DESCRIPTION
And for the second view is:
SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE AS
SPECIALTY_CODE
FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
ORDER BY NAME
The problem appaers when I try to join both these views with, for example,
this query:
select c.ID
from VW_CONSULTANTS c
left outer join VW_SPECIALTIES s
on c.SPECIALTY_CODE=s.CODE
I have these error messages:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
For me, the problem is because we try to join two views using server aliases
and the sql server is not successfull in creating the temporary table to put
the results of my select...
But I don't know what I have to do to fix the problem.
Can you help me please?
StF"StF" <StF@.discussions.microsoft.com> wrote in message
news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> Hello everybody,
> I'm working for a hospital and for this job, I created two views using the
> server aliases (linked servers).
> Both these views are working correctly.
> The design of the first view is:
> SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> SPECIALTY_DESCRIPTION AS NAME
> FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> ORDER BY SPECIALTY_DESCRIPTION
> And for the second view is:
> SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> AS
> SPECIALTY_CODE
> FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> ORDER BY NAME
> The problem appaers when I try to join both these views with, for example,
> this query:
> select c.ID
> from VW_CONSULTANTS c
> left outer join VW_SPECIALTIES s
> on c.SPECIALTY_CODE=s.CODE
>
I don't know exactly what's happening, but I know how to fix it.
Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
In SQL 2000 this would cause queries against the view to be sorted. But
this behavior was never guaranteed, and it doesn't always happen in 2005.
David|||Thank you very much.
In fact, we use SQL Server 2000 and that's why I used 'TOP 100 PERCENT'.
So, I removed this thing and the 'ORDER BY' and now my select is working.
Thank you again.
StF
"David Browne" wrote:

> "StF" <StF@.discussions.microsoft.com> wrote in message
> news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> I don't know exactly what's happening, but I know how to fix it.
> Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
> In SQL 2000 this would cause queries against the view to be sorted. But
> this behavior was never guaranteed, and it doesn't always happen in 2005.
> David
>
>

Join two views using server aliases

Hello everybody,
I'm working for a hospital and for this job, I created two views using the
server aliases (linked servers).
Both these views are working correctly.
The design of the first view is:
SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
SPECIALTY_DESCRIPTION AS NAME
FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
ORDER BY SPECIALTY_DESCRIPTION
And for the second view is:
SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE AS
SPECIALTY_CODE
FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
ORDER BY NAME
The problem appaers when I try to join both these views with, for example,
this query:
select c.ID
from VW_CONSULTANTS c
left outer join VW_SPECIALTIES s
on c.SPECIALTY_CODE=s.CODE
I have these error messages:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
For me, the problem is because we try to join two views using server aliases
and the sql server is not successfull in creating the temporary table to put
the results of my select...
But I don't know what I have to do to fix the problem.
Can you help me please?
StF"StF" <StF@.discussions.microsoft.com> wrote in message
news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> Hello everybody,
> I'm working for a hospital and for this job, I created two views using the
> server aliases (linked servers).
> Both these views are working correctly.
> The design of the first view is:
> SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> SPECIALTY_DESCRIPTION AS NAME
> FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> ORDER BY SPECIALTY_DESCRIPTION
> And for the second view is:
> SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> AS
> SPECIALTY_CODE
> FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> ORDER BY NAME
> The problem appaers when I try to join both these views with, for example,
> this query:
> select c.ID
> from VW_CONSULTANTS c
> left outer join VW_SPECIALTIES s
> on c.SPECIALTY_CODE=s.CODE
>
I don't know exactly what's happening, but I know how to fix it.
Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
In SQL 2000 this would cause queries against the view to be sorted. But
this behavior was never guaranteed, and it doesn't always happen in 2005.
David|||Thank you very much.
In fact, we use SQL Server 2000 and that's why I used 'TOP 100 PERCENT'.
So, I removed this thing and the 'ORDER BY' and now my select is working.
Thank you again.
StF
"David Browne" wrote:
> "StF" <StF@.discussions.microsoft.com> wrote in message
> news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> > Hello everybody,
> >
> > I'm working for a hospital and for this job, I created two views using the
> > server aliases (linked servers).
> > Both these views are working correctly.
> >
> > The design of the first view is:
> > SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> > SPECIALTY_DESCRIPTION AS NAME
> > FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> > ORDER BY SPECIALTY_DESCRIPTION
> >
> > And for the second view is:
> > SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> > AS
> > SPECIALTY_CODE
> > FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> > WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> > ORDER BY NAME
> >
> > The problem appaers when I try to join both these views with, for example,
> > this query:
> > select c.ID
> > from VW_CONSULTANTS c
> > left outer join VW_SPECIALTIES s
> > on c.SPECIALTY_CODE=s.CODE
> >
> I don't know exactly what's happening, but I know how to fix it.
> Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
> In SQL 2000 this would cause queries against the view to be sorted. But
> this behavior was never guaranteed, and it doesn't always happen in 2005.
> David
>
>