RIGHT OUTER JOIN

Hi,

i need an equivalent code to

RIGHT OUTER JOIN

i do not want to use that.

ex,

SELECT dbo.Test1.a, dbo.Test1.b, dbo.Test1.c, dbo.Test.t
FROM dbo.Test RIGHT OUTER JOIN
dbo.Test1 ON dbo.Test.a = dbo.Test1.a

thanks

Will you accept the left join equivalent? Why are you avoiding the RIGHT JOIN?|||

i ran the query in sql2005 thats fine

in access it wont.

|||Well, do you even need to join to the TEST table? Your query doesn't include any data from the TEST table.|||

Hi,

What version of access are you using? Just to see what syntax is supported.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

table a table b

a b c a d

1 10 x3 1 9

2 20 x2 2 8

3 30 x1 5 7

i need to get this without a join:

a b c d

1 10 x3 9

2 20 x2 8

3 30 x1 null

5 null null 7

I have conditions that i should not use right join, i know it is some access issue, i do not know versions.

so basically i write most codes without using joins, though i know ...

thanks,

REXX for windows and MS SQL SERVER

Hi,
I am looking for a sample code in REXX for making MS SQL Server database connectivity and programming in windows 2000 server environment.
Thanks,
SMANIUse the Generic ODBC from Source Forge (http://rexxsql.sourceforge.net/). REXX never really caught on for the PC, so it isn't well represented for the current generation of Windows tools.

-PatPsql

Reusing an SQLconnection

Hi all,
I am accessing one database a bunch of different times all throughout my code...in various functions and different web pages. Is there a a way to create an sqlconnection that I can access all the time, instead of constanting hardcoding which database to go to? I've tried putting the info in another file and just including it where I want the database to open, but I can't use <!-- #INCLUDE --> inside of the server scripts.
Can anyone helpTake a look @.Using Web.Config in ASP.NET and ASP

re-use rowguid, replication fails?

My ROWGUID columns are also my primary keys in many cases. I have
some code that deletes and then reinserts a row that has changes
instead of doing an UPDATE. As such, I re-use the same guid in the
ROWGUID column.
I have found that when I do this, the changes to the row do not make
it back up to the master server during a synchronize, as if the
replication does not recognize that the delete and re-insert has been
done on the row. If I do an update on the row, the change is
recognized and sent back to the master server.
Is this an expected behavior? Is it a no-no to delete a row with
ROWGUID x, then re-insert a new row with the same ROWGUID x?
I can probably whip up a step-by-step example that demonstrates this
behavior if desired.
thanks,
matthew tagliaferri
I take it you are using merge replication. Merge replication uses the
rowguid column to track which row has changed. Its probably not a good idea
to modify this row.
Can you check your conflict tables using the conflict viewer to see if any
conflicts are being logged. If not, please post your repo.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"matt tagliaferri" <mtagliaf@.cleindians.com> wrote in message
news:2cc74fa4.0411181102.5406ca87@.posting.google.c om...
> My ROWGUID columns are also my primary keys in many cases. I have
> some code that deletes and then reinserts a row that has changes
> instead of doing an UPDATE. As such, I re-use the same guid in the
> ROWGUID column.
> I have found that when I do this, the changes to the row do not make
> it back up to the master server during a synchronize, as if the
> replication does not recognize that the delete and re-insert has been
> done on the row. If I do an update on the row, the change is
> recognized and sent back to the master server.
> Is this an expected behavior? Is it a no-no to delete a row with
> ROWGUID x, then re-insert a new row with the same ROWGUID x?
> I can probably whip up a step-by-step example that demonstrates this
> behavior if desired.
> thanks,
> matthew tagliaferri
|||No conflicts exist. I can look at the table in question on the "master"
and "child" server, and the data in the child server is newer than the
data in the master server.
I spent a bit of time setting up some replication logging, this was
useful only in the fact that it didn't show any replication activity on
the table in question.
My collegue and I are setting up a reproducible example now, I will post
here when it is complete.
thanks for the reply,
matt tagliaferri
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Reuse of report elements

I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.

We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.

What is the best way to approach this?

My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.

My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination

If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!

You can't link common bits into a report unfortunately. IMO your best option is to keep using a template report as your base. Because reports are deployed to a RS server, you can't just update a file and expect all the reports to pick it up, the change has to be added to the reports and then they should be redeployed. I do believe there are ways to change the *styles* of already deployed reports, there are some css files you can tweak under the Reports and ReportServer virtual dirs in IIS.

You just have to make sure that whoever orders the changes knows the true cost of implementing it.

|||

Well, the subreport method got us most of the way there, but we had to do things that way.

The only part that I couldn't figure out was the ability to specify the report source for the subreport as a parameter or field. That way we could share the base report across our projects (using sourcesafe) and changes to it would propagate to all the reports (yes, we'd still have to redeploy, but the issue we're trying to avoid is the need to manually edit things, where a change could be forgotten on one report and introduce a bug.)

Reuse of Recursive Queries

I have a recursive query which I use to retrieve a set of identifiers. Something like:

WITH Set (Id)
AS (/* recursive code */)
SELECT Item FROM MyTable T
WHERE T.ItemId = ANY (SELECT Id FROM Set)

What I would like to achieve is to be able to reuse the code in bold in another procedure without duplicating the code (ie. reuse the query building up the set).

What I've done is to create a user-defined function which returns a TABLE parameter. So now, I have something like:

SELECT Item FROM MyTable T
WHERE T.ItemId = ANY(SELECT Id FROM MyFunction())

My question: is this the righ way of doing it? Does the use of a function incurs any relevant performance cost?

Thanks for advice.TVF would be fine. Another option is to convert it into a view.|||If you are making it as a TVF, then make sure it is inline otherwise you will have performance issues.

Re-use filter

I have a chunk of SQL that is use if a particular report needs to have
a point in time parameter. Is there any way to store this code so any
report in the solution has access to it? I don't want any business
users that develop reports to have to type all the SQL in every time.
Any thoughts?You could create a custom assembly that returned this chunk of SQL and then
construct your commands through an expression that calls your custom
assembly.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Austin S" <austinswope@.gmail.com> wrote in message
news:b68c96f7.0409281425.27324a81@.posting.google.com...
>I have a chunk of SQL that is use if a particular report needs to have
> a point in time parameter. Is there any way to store this code so any
> report in the solution has access to it? I don't want any business
> users that develop reports to have to type all the SQL in every time.
> Any thoughts?

returning my blob next problem

Dear All,

I am trying to run the some code which is designed to return an image blob. However it throws an exception

Exception Details:System.IndexOutOfRangeException: ToolbarLogo

Source Error:

Line 55: Response.ClearHeaders() ;
Line 56: Response.ContentType = "image/gif";
Line 57: byte[] arr = (byte[]) sdr["ToolbarLogo"];
Line 58:
Line 59: Response.BinaryWrite(arr);

I can run the sql SP query fine and it returns binary data ok:

Does anyone have any pointers I should look at as to why the error is thrown? Do you think my blobs are corrupt?

Here is the code:

Thank you for takin the time to look

public class GetBanner : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Image Image1;

protected void Page_Load(object sender, EventArgs e)
{
SqlDataReader sdr = null;

int brand_id = 0;
try
{
brand_id = Int32.Parse(Request.QueryString["brand_id"].ToString());
}
catch (Exception)
{
// commented below. Since it is a image handleer
//Response.Write("<HTML>You must supply a brand_id</HTML>");
return;
}

{
SqlDataAdapter daGetBanner = new SqlDataAdapter();


string connectionInfo = ConfigurationSettings.AppSettings["ConnectionInfo"];
using(SqlConnection dbConnection = new SqlConnection(connectionInfo))

{
SqlCommand objCommand = new SqlCommand("usp_get_new_guid_r", dbConnection);
objCommand.CommandType = CommandType.StoredProcedure;

dbConnection.Open();
sdr = objCommand.ExecuteReader();
while (sdr.Read())
{
Response.ClearHeaders() ;
Response.ContentType = "image/gif";
byte[] arr = (byte[]) sdr["ToolbarLogo"];

Response.BinaryWrite(arr);
Response.Flush();
}
sdr.Close();

dbConnection.Close();
}
}
}

if(sdr["ToolbarLogo"] != null ||sdr["ToolbarLogo"] != DBNull.Value )

{

byte[] arr = (byte[]) sdr["ToolbarLogo"];

Response.BinaryWrite(arr);

Response.Flush();

}

Returning more than one of same acct instead of sum for both

Code not combining acct number results.
The code below returns for example:
74981995170968 1595.00 253.54 1341.46 940.40
74981995170968 1595.00 2281.86 3876.86 940.40
It should be:
74981995170968 1595.00 2535.4 1341.36 940.40
above values come from
2281.86 + 253.54 = 2535.4
2535.4 - 1595.00
When I need it to combine the information and it's not because of the CASE statement within my first INNER join where I choose to SUM rmstranamt based on if there are more than one record or not
.
Here's the query:
SELECT rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
rf10.rmstranamt10 AS [Sum rmstranamt 10],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) - rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
END
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 <> 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND rf10.rmstranamt10 = 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
INNER JOIN
(
SELECT DISTINCT RMSFILENUM,
CASE WHEN COUNT(*) > 1 THEN
SUM(rmstranamt) as rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
ELSE
rmstranamt as rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM, rmstranamt
END
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
WHERE rf.rmstrancde IN ('10', '16','18','19','20','21','22','29','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
AND rm.rmsacctnum = '74981995170968'
GROUP BY rm.rmsacctnum, cb.CurrentBalance, rf10.rmstranamt10
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00

sorry I'm getting dizzy just looking at that. However, I think the following line might be flawed:
CASE WHEN COUNT(*) > 1 THEN

Basically this will always return true whenever the rfinanl table has more than one row.

Sorry I can't be of more assistance.

|||

finally, I got this figured out:

INNER JOIN
(
SELECT RMSFILENUM,
SUM(distinct rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM