Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Hi!

I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:

TITLE: Microsoft Visual Studio

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:

1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.

2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.

Any help would be greatly appreciated. Thanks!

Try casting the oracle fields to NUMBER(p,s) with a scale of at least one (1). Even though you look to be working with integers inside Oracle, casting it to a value of nnn.0 won't change that fact. I think then you'll be able to bring it into SSIS and then cast to integer there.|||I've tried to cast as DECIMAL(x, y) and NUMBER(x, y), and it works for "normal" selects, but not when the select contains UNION or is a SQL command variable.|||I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x
|||

JayH wrote:

I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x

For the UNION this seems to work, but I've removed all errors and warnings by casting to char (TO_CHAR(x) as myColumn)). Not pretty, but it works for both the UNION and the SQL command variables. For the UNION I'll change to your suggestion. For the SQL command variable I'll have to stick with TO_CHAR until something better pops up.

Note:

Precision is not valid error is displayed when some function is executed on the numeric column (like SUM, MIN, MAX, AVG, NVL etc) when using the OraOLEDB.Oracle.1. Seems like SSIS can't determine the numeric type when such a function is applied.

Thanks all!

|||Hmm.. Now I got the CAST(x AS NUMBER(w, z)) to work in the SQL command from variable. The meta data is not updated in the source component if you just change the SQL script in the variable (which was causing the problem). You have to change to a table or something else than the SQL command variable. Press OK. Edit the source component to use the SQL command variable again, and then the meta data is updated and it works. Previously I did get that "do you want to update meta data..."-message and pressed OK, but it seems like it was not updated correctly. By changing to a table and then back to the SQL command variable, everything is updated correctly. So now I don't use TO_CHAR anymore.|||

I found everything you said is correct, but I needed to go a bit further. The query against Oracle in the SQL Server 2000 DTS package was adding two numeric fields together, such as "SELECT ..... f1 + f2 AS BALANCE FROM.....". Even though the 2 source fields are the same datatype, decimal(15,2), for some reason SSIS still tripped over the precision and scale of the external and output columns. The work-around was to return both source columns separately, and then add them via a derived column task.

Output column has a precision that is not valid (loading from Oracle using OraOLEDB.Oracle.1)

Hi!

I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:

TITLE: Microsoft Visual Studio

Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:

1) Union

I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"

Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.

2) SQL command from variable

The select basically looks like this:

"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"

Again, even if I cast all columns (like in the union), I still get the same error.

Any help would be greatly appreciated. Thanks!

Try casting the oracle fields to NUMBER(p,s) with a scale of at least one (1). Even though you look to be working with integers inside Oracle, casting it to a value of nnn.0 won't change that fact. I think then you'll be able to bring it into SSIS and then cast to integer there.|||I've tried to cast as DECIMAL(x, y) and NUMBER(x, y), and it works for "normal" selects, but not when the select contains UNION or is a SQL command variable.|||I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x
|||

JayH wrote:

I wonder if putting the UNION or parameters in a subquery and specifying the cast in the outer query would help.

SELECT CAST... FROM (SELECT ... UNION ALL.. ) AS x

For the UNION this seems to work, but I've removed all errors and warnings by casting to char (TO_CHAR(x) as myColumn)). Not pretty, but it works for both the UNION and the SQL command variables. For the UNION I'll change to your suggestion. For the SQL command variable I'll have to stick with TO_CHAR until something better pops up.

Note:

Precision is not valid error is displayed when some function is executed on the numeric column (like SUM, MIN, MAX, AVG, NVL etc) when using the OraOLEDB.Oracle.1. Seems like SSIS can't determine the numeric type when such a function is applied.

Thanks all!

|||Hmm.. Now I got the CAST(x AS NUMBER(w, z)) to work in the SQL command from variable. The meta data is not updated in the source component if you just change the SQL script in the variable (which was causing the problem). You have to change to a table or something else than the SQL command variable. Press OK. Edit the source component to use the SQL command variable again, and then the meta data is updated and it works. Previously I did get that "do you want to update meta data..."-message and pressed OK, but it seems like it was not updated correctly. By changing to a table and then back to the SQL command variable, everything is updated correctly. So now I don't use TO_CHAR anymore.|||

I found everything you said is correct, but I needed to go a bit further. The query against Oracle in the SQL Server 2000 DTS package was adding two numeric fields together, such as "SELECT ..... f1 + f2 AS BALANCE FROM.....". Even though the 2 source fields are the same datatype, decimal(15,2), for some reason SSIS still tripped over the precision and scale of the external and output columns. The work-around was to return both source columns separately, and then add them via a derived column task.

sql

Outer Join not working when using SQL-92

All,

I have a perplexing problem that I hope someone can help me with.

I have the following table struct:

Permission
------
PermissionId
Permission
Description

UserPermission
------
PermissionId
UserId
Active

I am attempting to retrieve all records from the permission table
whether there is a match on UserPermission.PermissionId or not.
Therefore I implemented this query, which does not produce the results
that I expect:

SELECT p.Permission,
up.Active

FROM Permission p
LEFT OUTER JOIN UserPermission up
ONp.[Id] = up.PermissionId
WHERE up.UserId = 3

However, if I exec this query, it works as it is supposed to:
SELECTp.Permission,
up.Active

FROM Permission p, UserPermission up
WHEREp.[Id] *= up.PermissionId AND up.UserId = 3

In the first query, only the records that match on "permissionId" are
returned, in the second all records are returned from the Left table
and those records that do not have matching columns are set to null, as
it should be. My question is, what have I done wrong here?

I am running MS-SQLServer 2000The problem is with the way you are using SQL-92 standard.

Instead use the query as follows:

SELECT P.Permission, up.Active
FROM Permission p
LEFT OUTER JOIN UserPermission up
ON p.permissionid = up.PermissionId
AND up.UserId = 3

Regards
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||Thanks Debian, that did it. Is it then incorrect to use the WHERE
clause when using an outer join? I've looked at books on-line and
could not find a reference to using WHERE clauses with outer joins,
however they appear to be a typical part of inner joins.|||Here is a "cut & paste" lecture:

Here is how OUTER JOINs work in SQL-92. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @. = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;|||thilbert (thilbert@.gmail.com) writes:
> Thanks Debian, that did it. Is it then incorrect to use the WHERE
> clause when using an outer join? I've looked at books on-line and
> could not find a reference to using WHERE clauses with outer joins,
> however they appear to be a typical part of inner joins.

Incorrect and incorrect, it has a different meaning. The FROM clause
lasts all the way to WHERE, so you have

FROM a LEFT JOIN b ON a.col = b.col

Then the WHERE is applied. But if you say

WHERE b.othercol = 3

you are effectively filter out the rows from a which did not have a
matching row in b.

If you say

FROM a LEFT JOIN b ON a.col = b.col AND b.othercol = 3

The condition of othercol becomes part of the join, so that for
rows where a.col = b.col but b.othercol = 2 you will get NULL
values for b.*.

You can also say:

FROM a LEFT JOIN b ON a.col = b.col
WHERE b.othercol = 3 OR b.col IS NULL

But this gives a different result. Here the rows where a.col = b.col
but b.othercol = 2 will be removed from the result set.

See also Celko treatise on the subject.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Outer Join Issues in Sql 2005

Hi,

The following query works fine in Sql 2000 database, but it throws an error ('Query contains an outer-join request that is not permitted.') while executing the query on Sql 2005 database. We have already set the compatibility level of the Sql 2005 database to 'Sql Server 2000(80)'. But still we are getting the error.

select top 2 *
from table1 t1, table2 t2
where t2.col8 =* t1.col1
and t2.col9 =* ( select max(t21.col9) from table2 t21
where t21.col8 =* t2.col8 )

We are planning to convert all our sql 2000 databases to sql 2005 databases. We have used this kind of outer join(=* or *=) in many places in our application. It is also working fine many places in 2005 database, but throw errors in some places only.

Please let me know if any one knows the reason for the error in some places alone. So that we can give priority to those kind of query and change the outer join to 'SQL-92 compliant'. We know that this kind of outer joins are NOT going to be supported in future version of Sql and we are going to change all such outer join. But now we do not have much time to change all such outer join. It will be a great help if any one have answer for this.

Thanks in advance,

Sankar N

Is this error related only to the statements including an outer join on a subquery?

Regards

|||

You will have to modify the query to use the OUTER JOIN syntax. There is no way to make it work in SQL2005 for all cases. The compatibility level doesn't help in this case due to changes in query plan generation in SQL2005 (there are lot of changes, some of the syntax evaluation is more strict, new optimizations etc). The old proprietary *= and =* syntax has lot of ambiguities and it can produce incorrect results. It is best to convert all statements with old syntax or do it on a case-by-case basis which might be problematic.

sql

OUTER JOIN issue

The following stored procedure was created by one of our developers and it's
taking too long to be executed, we already identified that the section of th
e
coding with the problem are the outer joins, can anybody suggest something t
o
improve it?
Thanks
-- Populate performance layer table with latest data
----
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - part 1'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
SELECT isnull(isnull(isnull(poobd.clinical_hierarchy_ward_did,
unknown.clinical_hierarchy_ward_did), sday.clinical_hierarchy_ward_did),
sep.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(isnull(isnull(poobd.clinical_hierarchy_unit_did,
unknown.clinical_hierarchy_unit_did), sday.clinical_hierarchy_unit_did),
sep.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(isnull(isnull(poobd.cost_centre_did, unknown.cost_centre_did),
sday.cost_centre_did), sep.cost_centre_did) as cost_centre_did,
isnull(isnull(isnull(poobd.charge_cost_centre_did,
unknown.charge_cost_centre_did), sday.charge_cost_centre_did),
sep.charge_cost_centre_did) as charge_cost_centre_did,
isnull(isnull(isnull(poobd.budget_date_did, unknown.budget_date_did),
sday.budget_date_did), sep.budget_date_did) as budget_date_did,
isnull(isnull(isnull(poobd.initiative_did, unknown.initiative_did),
sday.initiative_did), sep.initiative_did) as initiative_did,
isnull(unknown.measure_UNKNOWN, 0) as measure_UNKNOWN,
isnull(poobd.measure_POOBD, 0) as measure_POOBD,
isnull(sday.measure_SDAY, 0) as measure_SDAY,
isnull(sep.measure_SEP, 0) as measure_SEP
INTO #activity_measures_part_1
FROM @.measure_UNKNOWN unknown
FULL OUTER JOIN @.measure_POOBD poobd
ON unknown.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
unknown.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
unknown.cost_centre_did = poobd.cost_centre_did and
unknown.charge_cost_centre_did = poobd.charge_cost_centre_did and
unknown.budget_date_did = poobd.budget_date_did and
unknown.initiative_did = poobd.initiative_did
FULL OUTER JOIN @.measure_SDAY sday
ON sday.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
sday.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
sday.cost_centre_did = poobd.cost_centre_did and
sday.charge_cost_centre_did = poobd.charge_cost_centre_did and
sday.budget_date_did = poobd.budget_date_did and
sday.initiative_did = poobd.initiative_did
FULL OUTER JOIN @.measure_SEP sep
ON sep.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
sep.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
sep.cost_centre_did = poobd.cost_centre_did and
sep.charge_cost_centre_did = poobd.charge_cost_centre_did and
sep.budget_date_did = poobd.budget_date_did and
sep.initiative_did = poobd.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_Abort
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - part 2'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
SELECT isnull(isnull(isnull(soobd.clinical_hierarchy_ward_did,
wsep9.clinical_hierarchy_ward_did),ptobd.clinical_hierarchy_ward_did),stobd.
clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(isnull(isnull(soobd.clinical_hierarchy_unit_did,
wsep9.clinical_hierarchy_unit_did),ptobd.clinical_hierarchy_unit_did),stobd.
clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(isnull(isnull(soobd.cost_centre_did,
wsep9.cost_centre_did),ptobd.cost_centre_did),stobd.cost_centre_did) as
cost_centre_did,
isnull(isnull(isnull(soobd.charge_cost_centre_did,
wsep9.charge_cost_centre_did),ptobd.charge_cost_centre_did),stobd.charge_cos
t_centre_did) as charge_cost_centre_did,
isnull(isnull(isnull(soobd.budget_date_did,
wsep9.budget_date_did),ptobd.budget_date_did),stobd.budget_date_did) as
budget_date_did,
isnull(isnull(isnull(soobd.initiative_did,
wsep9.initiative_did),ptobd.initiative_did),stobd.initiative_did) as
initiative_did,
isnull(soobd.measure_SOOBD, 0) as measure_SOOBD,
isnull(wsep9.measure_WSEP9, 0) as measure_WSEP9,
isnull(ptobd.measure_PTOBD, 0) as measure_PTOBD,
isnull(stobd.measure_STOBD, 0) as measure_STOBD
INTO #activity_measures_part_2
FROM @.measure_SOOBD soobd
FULL OUTER JOIN @.measure_WSEP9 wsep9
ON wsep9.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
wsep9.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
wsep9.cost_centre_did = soobd.cost_centre_did and
wsep9.charge_cost_centre_did = soobd.charge_cost_centre_did and
wsep9.budget_date_did = soobd.budget_date_did and
wsep9.initiative_did = soobd.initiative_did
FULL OUTER JOIN @.measure_PTOBD ptobd
ON ptobd.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
ptobd.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
ptobd.cost_centre_did = soobd.cost_centre_did and
ptobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
ptobd.budget_date_did = soobd.budget_date_did and
ptobd.initiative_did = soobd.initiative_did
FULL OUTER JOIN @.measure_STOBD stobd
ON stobd.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
stobd.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
stobd.cost_centre_did = soobd.cost_centre_did and
stobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
stobd.budget_date_did = soobd.budget_date_did and
stobd.initiative_did = soobd.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_Abort
select count(*) from #activity_measures_part_1
select count(*) from #activity_measures_part_2
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - final'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
INSERT INTO EDW_reporting.whs.PERF_activity_budget_ward_by_measure
(clinical_hierarchy_ward_did,
clinical_hierarchy_unit_did,
cost_centre_did,
charge_cost_centre_did,
budget_date_did,
initiative_did,
measure_UNKNOWN,
measure_POOBD,
measure_SDAY,
measure_SEP,
measure_SOOBD,
measure_WSEP9,
measure_PTOBDs,
measure_STOBDs,
batch_id)
SELECT isnull(part1.clinical_hierarchy_ward_did,
part2.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(part1.clinical_hierarchy_unit_did,
part2.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(part1.cost_centre_did, part2.cost_centre_did) as
cost_centre_did,
isnull(part1.charge_cost_centre_did, part2.charge_cost_centre_did) as
charge_cost_centre_did,
isnull(part1.budget_date_did, part2.budget_date_did) as
budget_date_did,
isnull(part1.initiative_did, part2.initiative_did) as initiative_did,
isnull(part1.measure_UNKNOWN, 0) as measure_UNKNOWN,
isnull(part1.measure_POOBD, 0) as measure_POOBD,
isnull(part1.measure_SDAY, 0) as measure_SDAY,
isnull(part1.measure_SEP, 0) as measure_SEP,
isnull(part2.measure_SOOBD, 0) as measure_SOOBD,
isnull(part2.measure_WSEP9, 0) as measure_WSEP9,
isnull(part2.measure_PTOBD, 0) as measure_PTOBD,
isnull(part2.measure_STOBD, 0) as measure_STOBD,
@.BatchID
FROM #activity_measures_part_1 part1
FULL OUTER JOIN #activity_measures_part_2 part2
ON part1.clinical_hierarchy_ward_did =
part2.clinical_hierarchy_ward_did and
part1.clinical_hierarchy_unit_did =
part2.clinical_hierarchy_unit_did and
part1.cost_centre_did = part2.cost_centre_did and
part1.charge_cost_centre_did = part2.charge_cost_centre_did and
part1.budget_date_did = part2.budget_date_did and
part1.initiative_did = part2.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_AbortWithout the table structures, index details and size of tables it is very
difficult to say what is wrong - but it may be worth running it through the
index tuning wizard to see what it comes up with
Have a look at the execution plan and see where the expensive bits are.
May be worth replacing all those nested isnulls with a coalesce of all the
columns in the nested isnulls - makes it much easier to read, less code and
may help.
If no luck try posting the executiuon plan and ddl for all the tables -
someone may then have some more suggestions
Mike John
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:30F9AE02-48DD-4FC9-BD5C-636224FB5CE3@.microsoft.com...
> The following stored procedure was created by one of our developers and
> it's
> taking too long to be executed, we already identified that the section of
> the
> coding with the problem are the outer joins, can anybody suggest something
> to
> improve it?
> Thanks
> -- Populate performance layer table with latest data
> ----
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - part 1'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> SELECT isnull(isnull(isnull(poobd.clinical_hierarchy_ward_did,
> unknown.clinical_hierarchy_ward_did), sday.clinical_hierarchy_ward_did),
> sep.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
> isnull(isnull(isnull(poobd.clinical_hierarchy_unit_did,
> unknown.clinical_hierarchy_unit_did), sday.clinical_hierarchy_unit_did),
> sep.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
> isnull(isnull(isnull(poobd.cost_centre_did, unknown.cost_centre_did),
> sday.cost_centre_did), sep.cost_centre_did) as cost_centre_did,
> isnull(isnull(isnull(poobd.charge_cost_centre_did,
> unknown.charge_cost_centre_did), sday.charge_cost_centre_did),
> sep.charge_cost_centre_did) as charge_cost_centre_did,
> isnull(isnull(isnull(poobd.budget_date_did, unknown.budget_date_did),
> sday.budget_date_did), sep.budget_date_did) as budget_date_did,
> isnull(isnull(isnull(poobd.initiative_did, unknown.initiative_did),
> sday.initiative_did), sep.initiative_did) as initiative_did,
> isnull(unknown.measure_UNKNOWN, 0) as measure_UNKNOWN,
> isnull(poobd.measure_POOBD, 0) as measure_POOBD,
> isnull(sday.measure_SDAY, 0) as measure_SDAY,
> isnull(sep.measure_SEP, 0) as measure_SEP
> INTO #activity_measures_part_1
> FROM @.measure_UNKNOWN unknown
> FULL OUTER JOIN @.measure_POOBD poobd
> ON unknown.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> unknown.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> unknown.cost_centre_did = poobd.cost_centre_did and
> unknown.charge_cost_centre_did = poobd.charge_cost_centre_did
> and
> unknown.budget_date_did = poobd.budget_date_did and
> unknown.initiative_did = poobd.initiative_did
> FULL OUTER JOIN @.measure_SDAY sday
> ON sday.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> sday.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> sday.cost_centre_did = poobd.cost_centre_did and
> sday.charge_cost_centre_did = poobd.charge_cost_centre_did and
> sday.budget_date_did = poobd.budget_date_did and
> sday.initiative_did = poobd.initiative_did
> FULL OUTER JOIN @.measure_SEP sep
> ON sep.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> sep.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> sep.cost_centre_did = poobd.cost_centre_did and
> sep.charge_cost_centre_did = poobd.charge_cost_centre_did and
> sep.budget_date_did = poobd.budget_date_did and
> sep.initiative_did = poobd.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - part 2'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> SELECT isnull(isnull(isnull(soobd.clinical_hierarchy_ward_did,
> wsep9.clinical_hierarchy_ward_did),ptobd.clinical_hierarchy_ward_did),stob
d.clinical_hierarchy_ward_did)
> as clinical_hierarchy_ward_did,
> isnull(isnull(isnull(soobd.clinical_hierarchy_unit_did,
> wsep9.clinical_hierarchy_unit_did),ptobd.clinical_hierarchy_unit_did),stob
d.clinical_hierarchy_unit_did)
> as clinical_hierarchy_unit_did,
> isnull(isnull(isnull(soobd.cost_centre_did,
> wsep9.cost_centre_did),ptobd.cost_centre_did),stobd.cost_centre_did) as
> cost_centre_did,
> isnull(isnull(isnull(soobd.charge_cost_centre_did,
> wsep9.charge_cost_centre_did),ptobd.charge_cost_centre_did),stobd.charge_c
ost_centre_did)
> as charge_cost_centre_did,
> isnull(isnull(isnull(soobd.budget_date_did,
> wsep9.budget_date_did),ptobd.budget_date_did),stobd.budget_date_did) as
> budget_date_did,
> isnull(isnull(isnull(soobd.initiative_did,
> wsep9.initiative_did),ptobd.initiative_did),stobd.initiative_did) as
> initiative_did,
> isnull(soobd.measure_SOOBD, 0) as measure_SOOBD,
> isnull(wsep9.measure_WSEP9, 0) as measure_WSEP9,
> isnull(ptobd.measure_PTOBD, 0) as measure_PTOBD,
> isnull(stobd.measure_STOBD, 0) as measure_STOBD
> INTO #activity_measures_part_2
> FROM @.measure_SOOBD soobd
> FULL OUTER JOIN @.measure_WSEP9 wsep9
> ON wsep9.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> wsep9.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> wsep9.cost_centre_did = soobd.cost_centre_did and
> wsep9.charge_cost_centre_did = soobd.charge_cost_centre_did and
> wsep9.budget_date_did = soobd.budget_date_did and
> wsep9.initiative_did = soobd.initiative_did
> FULL OUTER JOIN @.measure_PTOBD ptobd
> ON ptobd.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> ptobd.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> ptobd.cost_centre_did = soobd.cost_centre_did and
> ptobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
> ptobd.budget_date_did = soobd.budget_date_did and
> ptobd.initiative_did = soobd.initiative_did
> FULL OUTER JOIN @.measure_STOBD stobd
> ON stobd.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> stobd.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> stobd.cost_centre_did = soobd.cost_centre_did and
> stobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
> stobd.budget_date_did = soobd.budget_date_did and
> stobd.initiative_did = soobd.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort
>
> select count(*) from #activity_measures_part_1
> select count(*) from #activity_measures_part_2
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - final'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> INSERT INTO EDW_reporting.whs.PERF_activity_budget_ward_by_measure
> (clinical_hierarchy_ward_did,
> clinical_hierarchy_unit_did,
> cost_centre_did,
> charge_cost_centre_did,
> budget_date_did,
> initiative_did,
> measure_UNKNOWN,
> measure_POOBD,
> measure_SDAY,
> measure_SEP,
> measure_SOOBD,
> measure_WSEP9,
> measure_PTOBDs,
> measure_STOBDs,
> batch_id)
> SELECT isnull(part1.clinical_hierarchy_ward_did,
> part2.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
> isnull(part1.clinical_hierarchy_unit_did,
> part2.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
> isnull(part1.cost_centre_did, part2.cost_centre_did) as
> cost_centre_did,
> isnull(part1.charge_cost_centre_did, part2.charge_cost_centre_did) as
> charge_cost_centre_did,
> isnull(part1.budget_date_did, part2.budget_date_did) as
> budget_date_did,
> isnull(part1.initiative_did, part2.initiative_did) as initiative_did,
> isnull(part1.measure_UNKNOWN, 0) as measure_UNKNOWN,
> isnull(part1.measure_POOBD, 0) as measure_POOBD,
> isnull(part1.measure_SDAY, 0) as measure_SDAY,
> isnull(part1.measure_SEP, 0) as measure_SEP,
> isnull(part2.measure_SOOBD, 0) as measure_SOOBD,
> isnull(part2.measure_WSEP9, 0) as measure_WSEP9,
> isnull(part2.measure_PTOBD, 0) as measure_PTOBD,
> isnull(part2.measure_STOBD, 0) as measure_STOBD,
> @.BatchID
> FROM #activity_measures_part_1 part1
> FULL OUTER JOIN #activity_measures_part_2 part2
> ON part1.clinical_hierarchy_ward_did =
> part2.clinical_hierarchy_ward_did and
> part1.clinical_hierarchy_unit_did =
> part2.clinical_hierarchy_unit_did and
> part1.cost_centre_did = part2.cost_centre_did and
> part1.charge_cost_centre_did = part2.charge_cost_centre_did and
> part1.budget_date_did = part2.budget_date_did and
> part1.initiative_did = part2.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort|||Consider using temporary tables instead of table variables. The options to
create indexes on table variables are very limited.
For better help, please post DDL, sample data and expected results.
ML
http://milambda.blogspot.com/|||Only a minor point (not performance related), but
you can use a single "coalesce" instead of multiple
nested "isnull"s. This may make your query easier to
read.

Outer join difficulty

Can someone please help me with the following join ? I have two tables that I need to join. The select statement for the first part is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The table that is produced is as follows:-

Rundate Accno Sharename NotAtHome
20031128 260869 PANGBOURNE 123200
20031128 260869 PARAMOUNT 221100
20031128 260869 PRISAIB. 221100

The table I wish to join on, is as follows:-

SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename

The result is as follows:-

Rundate Accno Sharename NotAtHome
20031103 260869 PANGBOURNE 123200

Note that Paramount and Prisaib are missing from the second table.

I need to create an output table showing all three records with the missing two having 0 under "NotAtHome".

I'd much appreciate any assistance with this..

thanks!(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q1
LEFT OUTER JOIN
(SELECT * FROM dbo.Holdings
WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
ORDER BY Sharename) q2
ON q1.AccNo = q2.AccNo;

Assuming AccNo is a key.

The default value for non-matching rows is Null, however if you do indeed want a numerical then,

I am not entirely sure of MSSQL syntax however I believe it's the following for Case statements.

select attributes, (case NotAtHome If Null Then 0 Else NotAtHome END) As "NotAtHome"
from
(
Above query
)|||you would not need ORDER BY in the subqueries, and in fact the query is a bit neater without subqueries

the NotAtHome column is weird, i left it as t1.NotAtHome as alkemac specified, but i think he/she might have meant t2.NotAtHome select t1.Rundate
, t1.Accno
, t1.Sharename
, case when t2.AccNo is null
then 0
else t1.NotAtHome
end as NotAtHome
from dbo.Holdings t1
left outer
join dbo.Holdings t2
on t1.AccNo = t2.AccNo
and t2.Rundate = '20031103'
and t2.Sharename like 'P%'
where t1.Accno = '260869'
and t1.Rundate = '20031128'
and t1.Sharename like 'P%'rudy
http://r937.com/

outer join column

Thanks in advance for any help.
I'm trying to attempt the following with one table/query.

I have a table called TABLE_1
It has the following fields:

SSNTITLELOCATION
322EXDALLAS
568REPFT_WORTH
536EXDALLAS
485SLSYOUNGSTOWN
854BROFEEDER
258EXEVANSVILLE
478TEMPTROY
861SLSDALLAS

I want to
select SSN, TITLE, LOCATION
from table_1
where ssn = ('322', '536', '258', '478', '861')
and title = 'EX'
and location = 'DALLAS'

The problem, however, it that I want all of the SSN records on my list.
My table should look like this:

SSNTITLELOCATION
322EXDALLAS
536EXDALLAS
258nullnull
478nullnull
861nullnull

I can easily do this on two tables with a left outer join. I do not
know how to accomplish same (theory) with one table.

Any help on how to do this would be appreciated. Thanksalex wrote:
> Thanks in advance for any help.
> I'm trying to attempt the following with one table/query.
> I have a table called TABLE_1
> It has the following fields:
> SSNTITLELOCATION
> 322EXDALLAS
> 568REPFT_WORTH
> 536EXDALLAS
> 485SLSYOUNGSTOWN
> 854BROFEEDER
> 258EXEVANSVILLE
> 478TEMPTROY
> 861SLSDALLAS
> I want to
> select SSN, TITLE, LOCATION
> from table_1
> where ssn = ('322', '536', '258', '478', '861')
> and title = 'EX'
> and location = 'DALLAS'
> The problem, however, it that I want all of the SSN records on my list.
> My table should look like this:
> SSNTITLELOCATION
> 322EXDALLAS
> 536EXDALLAS
> 258nullnull
> 478nullnull
> 861nullnull
> I can easily do this on two tables with a left outer join. I do not
> know how to accomplish same (theory) with one table.
> Any help on how to do this would be appreciated. Thanks

It looks like you could use a self-join:

SELECT T1.ssn, T2.title, T2.location
FROM table_1 AS T1
LEFT JOIN table_1 AS T2
ON T1.ssn = T2.ssn
AND T2.title = 'EX'
AND T2.location = 'DALLAS'
WHERE T1.ssn IN ('322', '536', '258', '478', '861') ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Please view the solution below in a fixed font.
--
Regards,
Doug Lawry
www.douglawry.webhop.org

_____
___| SQL |________________________________________________

CREATE TABLE table_1
(
[SSN] SMALLINT,
[Title] VARCHAR(4),
[Location] VARCHAR(20)
)

INSERT INTO table_1 VALUES (322, 'EX', 'DALLAS')
INSERT INTO table_1 VALUES (568, 'REP', 'FT_WORTH')
INSERT INTO table_1 VALUES (536, 'EX', 'DALLAS')
INSERT INTO table_1 VALUES (485, 'SLS', 'YOUNGSTOWN')
INSERT INTO table_1 VALUES (854, 'BRO', 'FEEDER')
INSERT INTO table_1 VALUES (258, 'EX', 'EVANSVILLE')
INSERT INTO table_1 VALUES (478, 'TEMP', 'TROY')
INSERT INTO table_1 VALUES (861, 'SLS', 'DALLAS')

SELECT
[SSN],
CASE
WHEN [Title] = 'EX' AND [Location] = 'DALLAS'
THEN [Title]
ELSE NULL
END AS [Title],
CASE
WHEN [Title] = 'EX' AND [Location] = 'DALLAS'
THEN [Location]
ELSE NULL
END AS [Location]
FROM
table_1
WHERE
[SSN] IN ('322', '536', '258', '478', '861')

_________
___| Results |____________________________________________

SSN Title Location
-- -- -------
322 EX DALLAS
536 EX DALLAS
258 NULL NULL
478 NULL NULL
861 NULL NULL

(5 row(s) affected)

__________________________________________________ ________

"alex" <sql_aid@.yahoo.com> wrote in message
news:1142614348.612950.22570@.j33g2000cwa.googlegro ups.com...
> Thanks in advance for any help.
> I'm trying to attempt the following with one table/query.
> I have a table called TABLE_1
> It has the following fields:
> SSN TITLE LOCATION
> 322 EX DALLAS
> 568 REP FT_WORTH
> 536 EX DALLAS
> 485 SLS YOUNGSTOWN
> 854 BRO FEEDER
> 258 EX EVANSVILLE
> 478 TEMP TROY
> 861 SLS DALLAS
> I want to
> select SSN, TITLE, LOCATION
> from table_1
> where ssn = ('322', '536', '258', '478', '861')
> and title = 'EX'
> and location = 'DALLAS'
> The problem, however, it that I want all of the SSN records on my list.
> My table should look like this:
> SSN TITLE LOCATION
> 322 EX DALLAS
> 536 EX DALLAS
> 258 null null
> 478 null null
> 861 null null
> I can easily do this on two tables with a left outer join. I do not
> know how to accomplish same (theory) with one table.
> Any help on how to do this would be appreciated. Thanks|||Thanks for the response. Let me pose the example this way:

I'm attempting to write a query against a table (Table_A). Let's
say the table has three columns with various records. Column_1 lists
the names of vehicle manufacturers. I have a preexisting list that
I'm attempting to bounce of my table. I want, however, all of the
names in my preexisting list to appear in my results. If the criterion
doesn't exit, it should say 'NULL'. Thanks for any help.

For example:

Table_A

COLUMN_1COLUMN_2COLUMN_3
FordBlue4door
ChevyBlk2door
HondaBlue4door
NissanRed2door

My preexisting list looks like this:

Ford
Chevy
Honda
Nissan
Toyota
Jeep

I want to select every record from COLUMN_2 and _3 that = Blue and
4door.
I want my results to look like this:

Vehicle_MakeColumn_2Column_3
FordBlue4door
Chevynullnull
HondaBlue4door
Nissannullnull
Toyotanullnull
Jeepnullnull|||alex wrote:
> Thanks for the response. Let me pose the example this way:
> I'm attempting to write a query against a table (Table_A). Let's
> say the table has three columns with various records. Column_1 lists
> the names of vehicle manufacturers. I have a preexisting list that
> I'm attempting to bounce of my table. I want, however, all of the
> names in my preexisting list to appear in my results. If the criterion
> doesn't exit, it should say 'NULL'. Thanks for any help.
> For example:
> Table_A
> COLUMN_1COLUMN_2COLUMN_3
> FordBlue4door
> ChevyBlk2door
> HondaBlue4door
> NissanRed2door
> My preexisting list looks like this:
> Ford
> Chevy
> Honda
> Nissan
> Toyota
> Jeep
> I want to select every record from COLUMN_2 and _3 that = Blue and
> 4door.
> I want my results to look like this:
> Vehicle_MakeColumn_2Column_3
> FordBlue4door
> Chevynullnull
> HondaBlue4door
> Nissannullnull
> Toyotanullnull
> Jeepnullnull

Please post DDL in future otherwise you are forcing us to guess what
your datatypes, keys, constraints and RI look like. The best way to
post sample data is as INSERT statements.

What is the "preexisting list" in your example? There is no list or
array data structure in SQL. The most sensible way to supply that set
of values is in a table. Use a table variable if you don't have a
permanent table. Example:

CREATE TABLE table_a (vehicle_make VARCHAR(10) NOT NULL PRIMARY KEY,
column_2 VARCHAR(10) NOT NULL, column_3 VARCHAR(10) NOT NULL);
INSERT INTO table_a VALUES ('Ford', 'Blue', '4door');
INSERT INTO table_a VALUES ('Chevy', 'Blk', '2door');
INSERT INTO table_a VALUES ('Honda', 'Blue', '4door');
INSERT INTO table_a VALUES ('Nissan', 'Red', '2door');

CREATE TABLE table_b (vehicle_make VARCHAR(10) NOT NULL PRIMARY KEY);
INSERT INTO table_b VALUES ('Ford');
INSERT INTO table_b VALUES ('Chevy');
INSERT INTO table_b VALUES ('Honda');
INSERT INTO table_b VALUES ('Nissan');
INSERT INTO table_b VALUES ('Toyota');
INSERT INTO table_b VALUES ('Jeep');

SELECT B.vehicle_make, A.column_2, A.column_3
FROM table_b AS B
LEFT JOIN table_a AS A
ON B.vehicle_make = A.vehicle_make
AND column_2 = 'Blue'
AND column_3 = '4door' ;

Result:

vehicle_make column_2 column_3
---- ---- ----
Chevy NULL NULL
Ford Blue 4door
Honda Blue 4door
Jeep NULL NULL
Nissan NULL NULL
Toyota NULL NULL

(6 row(s) affected)

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||I'm a fan of temp tables:

create table #results
(ssnvarchar(10)
titlevarchar(10),
locationvarchar(32))

insert #results (ssn)
values ('322')
insert #results (ssn)
values ('536')
insert #results (ssn)
values ('258')
insert #results (ssn)
values ('478')
insert #results (ssn)
values ('861')

update r
setr.title = t.title,
r.location = t.location
from #results r,
table_1 t
where r.ssn = t.ssn
and t.title = 'EX'
and t.location = 'DALLAS'

select * from #results|||actually, this:

insert #results (ssn)
values ('322')
insert #results (ssn)
values ('536')
insert #results (ssn)
values ('258')
insert #results (ssn)
values ('478')
insert #results (ssn)
values ('861')

can also be done like this:

insert #results (ssn)
exec ('
select '322'
select '536'
select '258'
select '478'
select '861'
')|||Try this:

select a.* from table_1 a left outer join
(
select SSN, TITLE, LOCATION
from table_1
where ssn = ('322', '536', '258', '478', '861')
and title = 'EX'
and location = 'DALLAS' ) b on a.ssn=b.ssn -- assuming that ssn is a
unique key
where a.ssn = ('322', '536', '258', '478', '861')|||(Michael.Suarez@.gmail.com) writes:
> insert #results (ssn)
> values ('322')
> insert #results (ssn)
> values ('536')
> insert #results (ssn)
> values ('258')
> insert #results (ssn)
> values ('478')
> insert #results (ssn)
> values ('861')
> can also be done like this:
> insert #results (ssn)
> exec ('
> select '322'
> select '536'
> select '258'
> select '478'
> select '861'
> ')

Or

insert #results (ssn)
select '322' union all
select '536' union all
select '258' union all
select '478' union all
select '861'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to join "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisG
See if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
David Portas
SQL Server MVP
|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1
|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
David Portas
SQL Server MVP
|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.
|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.330487.242630@.z14g2000cwz.googlegr oups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegr oups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "aXb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model (described in the op) if possible.
Thanks,
ChrisG
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegr oups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need* it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG

Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to join "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisGSee if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
--
David Portas
SQL Server MVP
--|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
--
David Portas
SQL Server MVP
--|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.330487.242630@.z14g2000cwz.googlegroups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "aXb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
---
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model (described in the op) if possible.
Thanks,
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need* it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG

Outer Join across many-to-many table ?

Given the following data model...
Table a (id int PK)
Table b (id int PK)
Table aXb (a.id int FK, b.id int FK, UNIQUE(a.id, b.id)
Scenario: "b" essentially represents a table of picklist data. I want to joi
n "a" to "b" in such a way that I get all rows in "b"
for each unique row in "a" (typically done with an outer join when "b" has a
FK to "a").
I tried this...
SELECT * FROM a
INNER JOIN aXb ON a.id = aXb.a.id
LEFT OUTER JOIN b ON aXb.b.id = b.id
WHERE a.id = 1
...but it isn't giving me what I want.
Can this be done?
Thanks,
ChrisGSee if this is what you want:
SELECT A.id, B.id
FROM A, B
WHERE A.id = 1
David Portas
SQL Server MVP
--|||I think the first join should be the outer join. If there are no pick
records for a client (I'm guessing that's what a is), there will be no
record for them in aXb, and no record in the result. Actually, I think
you need the outer join for both joins.
(That seems really odd and/or dangerous to me that you have periods in
the field names a.id, b.id in aXb. I guess those aren't the real names)
Or maybe you could use a subquery with one outer join:
SELECT * FROM a
LEFT OUTER JOIN
(Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
ON a.id = PICK.a.id
WHERE a.id = 1|||or maybe:
SELECT *
FROM A
CROSS JOIN B
LEFT JOIN AXB
ON A.id = AXB.a_id
AND B.id = AXB.b_id
WHERE A.id = 1
David Portas
SQL Server MVP
--|||Wouldn't that result just be a bunch of 1's with all the id's from b
(assuming 1 is in a)? You need aXb to limit the pick records for a.id=1.|||"Jerry Porter" <jerryp@.personablepc.com> wrote in message news:1110302991.33
0487.242630@.z14g2000cwz.googlegroups.com...
|I think the first join should be the outer join. If there are no pick
| records for a client (I'm guessing that's what a is), there will be no
| record for them in aXb, and no record in the result. Actually, I think
| you need the outer join for both joins.
|
| (That seems really odd and/or dangerous to me that you have periods in
| the field names a.id, b.id in aXb. I guess those aren't the real names)
That's pseudo-sql ;-)
| Or maybe you could use a subquery with one outer join:
| SELECT * FROM a
| LEFT OUTER JOIN
| (Select * From aXb INNER JOIN b ON aXb.b.id = b.id As PICK)
| ON a.id = PICK.a.id
| WHERE a.id = 1
Sorry, that didn't work.
Thanks, tho.
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Both of your suggestions worked as I asked. (I didn't ask the right question
, tho). I was hoping to see a null in the "aXb" join so
I knew which rows in "b" linked to the row in "a". All the columns in the "a
Xb" join are returning NULL
I'll take off my obtuse hat and try to better state what I'm looking for.
"a" = Users
"b" = Roles
"aXb" = UsersXRoles
I'm looking to create a view that shows each user and all the roles they can
be assigned to. I was hoping to alias a column of the
UsersXRoles table to indicate assignment, i.e.,
User Roles Assigned
---
UserA Group1 Yes
UserA Group2 No
UserA Group3 Yes
UserB Group1 No
UserB Group2 No
UserB Group3 Yes
etc.
I'm open to any suggestions. I'd like to stick with the existing data model
(described in the op) if possible.
Thanks,
ChrisG|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110303093.154839.269330@.l41g2000cwc.googlegroups.com...
| or maybe:
|
| SELECT *
| FROM A
| CROSS JOIN B
| LEFT JOIN AXB
| ON A.id = AXB.a_id
| AND B.id = AXB.b_id
| WHERE A.id = 1
|
| --
| David Portas
| SQL Server MVP
Just wanted to followup and state that this query works exactly as I *need*
it to. I just wasn't paying close attention when I was
cutting, pasting and editing from all my trial scripts.
Thanks David P.
ChrisG

Outer Join - Returning Customers Who Haven't ordered a product

Hi,
Is there any way to make the following LEFT OUTER JOIN statement work in a
similar fashion as the SUBQUERY statement that follows it? Customers 1 and
3
haven't ordered anything for the OrderDate '2006-01-01' and I want to exclud
e
them from the display in the LEFT OUTER JOIN query (like they are excluded i
n
the SUBQUERY statement).
select c.customerid
, c.customername
from customers c
left outer join orders o
on c.customerid = o.customerid
and o.orderdate = '2006-01-01'
customerid customername
-- ----
1 Brian
2 Andy
3 Steve
4 Delaney
select c.customerid
, c.customername
from customers c
where c.customerid not in
(select c.customerid from orders o
where c.customerid = o.customerid
and o.orderdate = '2006-01-01' )
customerid customername
-- ----
2 Andy
4 Delaney
Thanks,
IanHello, Ian
You can use this query:
select c.customerid
, c.customername
from customers c
left outer join orders o
on c.customerid = o.customerid
and o.orderdate = '2006-01-01'
WHERE o.customerid IS NULL
But I think it's better with a subquery (and it's more clear):
select c.customerid
, c.customername
from customers c
where c.customerid not in
(select o.customerid from orders o
where o.orderdate = '2006-01-01' )
Razvan|||Razvan

> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01' )
In this case we may want to add AND o.customerid IS NOT NULL
Just my 2 cents
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139292357.248363.83490@.g14g2000cwa.googlegroups.com...
> Hello, Ian
> You can use this query:
> select c.customerid
> , c.customername
> from customers c
> left outer join orders o
> on c.customerid = o.customerid
> and o.orderdate = '2006-01-01'
> WHERE o.customerid IS NULL
> But I think it's better with a subquery (and it's more clear):
> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01' )
> Razvan
>|||Hi, Uri
You are right. Since the OP did show the DDL, I assumed that a
"customerid" column in an "orders" table would not allow NULL-s, but I
may be wrong... :) But in this case, I still think that he should fix
the DDL, not the query. Anyway, if we are not talking about orders and
customers, your comment raises a very good point.
Razvan|||Hi Razvan,
Thanks for the very quick answer. I should have tried that myself. I
actually simplified the query because what I really need to do is this: "Lis
t
all customers who have never ordered a particular product". Again, I can do
this via a Subquery with no problem but I'm told this can also be done via
LEFT OUTER JOINS and I can't get the following to work, so I must be missing
something.
SELECT c.CustomerID, p.productname
FROM Customers c
LEFT JOIN Orders O ON c.CustomerID = o.CustomerID
LEFT JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
LEFT JOIN Products p ON od.ProductNumber = p.ProductNumber
AND p.productname = 'Toshiba TV'
GROUP BY c.CustomerID,p.productname
ORDER BY c.CustomerID
The result set I get is this:
CustomerID productname
-- ----
1001 Toshiba TV
1001 NULL
1002 NULL
1003 Toshiba TV
1003 NULL
1004 Toshiba TV
1004 NULL
1005 NULL
1005 Toshiba TV
1006 NULL
1007 NULL
1007 Toshiba TV
1008 Toshiba TV
1008 NULL
1009 Toshiba TV
1009 NULL
1010 NULL
1010 Toshiba TV
What I want to be able to do is to eliminate CustomerIDs 1002 and 1006 from
the display because they have never ordered a "Toshiba TV". Adding a WHERE
clause of "WHERE c.CustomerID IS NULL" immediately before the GROUP BY claus
e
doesn't work (it results in 0 rows being returned).
Thanks,
Ian|||Hi guys,
Actually, adding an AND clause won't work here as it returns all four rows
that I mention in my original post. My original SUBQUERY without the AND
clause gives me what I want (i.e. just the two rows). Nulls are not allowed
for Customerid in the Order table.

> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01'
and o.customerid IS NULL)
"Razvan Socol" wrote:

> Hi, Uri
> You are right. Since the OP did show the DDL, I assumed that a
> "customerid" column in an "orders" table would not allow NULL-s, but I
> may be wrong... :) But in this case, I still think that he should fix
> the DDL, not the query. Anyway, if we are not talking about orders and
> customers, your comment raises a very good point.
> Razvan
>|||Rats...I got it backwards. What I meant to say at the bottom of my post was
that I *only* want to report Customers 1002 and 1006 because they are the
only ones who have *not* ordered a Toshiba TV at some point in time.
"Ian Fisher" wrote:

> Hi Razvan,
> Thanks for the very quick answer. I should have tried that myself. I
> actually simplified the query because what I really need to do is this: "L
ist
> all customers who have never ordered a particular product". Again, I can
do
> this via a Subquery with no problem but I'm told this can also be done via
> LEFT OUTER JOINS and I can't get the following to work, so I must be missi
ng
> something.
> SELECT c.CustomerID, p.productname
> FROM Customers c
> LEFT JOIN Orders O ON c.CustomerID = o.CustomerID
> LEFT JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
> LEFT JOIN Products p ON od.ProductNumber = p.ProductNumber
> AND p.productname = 'Toshiba TV'
> GROUP BY c.CustomerID,p.productname
> ORDER BY c.CustomerID
> The result set I get is this:
> CustomerID productname
> -- ----
> 1001 Toshiba TV
> 1001 NULL
> 1002 NULL
> 1003 Toshiba TV
> 1003 NULL
> 1004 Toshiba TV
> 1004 NULL
> 1005 NULL
> 1005 Toshiba TV
> 1006 NULL
> 1007 NULL
> 1007 Toshiba TV
> 1008 Toshiba TV
> 1008 NULL
> 1009 Toshiba TV
> 1009 NULL
> 1010 NULL
> 1010 Toshiba TV
> What I want to be able to do is to eliminate CustomerIDs 1002 and 1006 fro
m
> the display because they have never ordered a "Toshiba TV". Adding a WHER
E
> clause of "WHERE c.CustomerID IS NULL" immediately before the GROUP BY cla
use
> doesn't work (it results in 0 rows being returned).
> Thanks,
> Ian|||Hello, Ian
Please post DDL (as CREATE TABLE statements) and sample data (as INSERT
INTO ... VALUES ...). See: http://www.aspfaq.com/etiquette.asp?id=5006
I think it's possible to do this with LEFT JOIN-s instead of a "NOT IN"
subquery, but it will require a derived table (a subquery in the FROM
clause) or a view.
Razvan|||Hi Razvan,
I figured it out with the help of a colleague....thanks a lot for your help
.
SELECT c.CustomerID, c.CustFirstName, c.CustLastName
FROM Customers c
LEFT JOIN
(SELECT c1.customerid as cid from customers c1
join orders o on o.CustomerID = c1.customerid
JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
JOIN Products p ON od.ProductNumber = p.ProductNumber
WHERE p.productname = 'Viscount Microshell Helmet') c2
ON c2.cid = c.CustomerID
WHERE c2.cid IS NULL
Best Regards,
Ian|||> [...] what I really need to do is this: "List all customers
> who have never ordered a particular product".
> Again, I can do this via a Subquery with no problem
> but I'm told this can also be done via LEFT OUTER JOINS [...]
Since you did not provide DDL and sample data, I have used Northwind
for the following queries. After a little more thinking, I have found a
way to write this query using outer joins, even without using a derived
table.
So, instead of this simple and clear query:
SELECT CompanyName FROM Customers
WHERE CustomerID NOT IN (
SELECT CustomerID FROM Orders o
INNER JOIN [Order details] od ON o.OrderID=od.OrderID
INNER JOIN Products p ON od.ProductID=p.ProductID
WHERE p.ProductName='Chang'
)
we can write a query using LEFT JOIN-s, with a weird placement of the
ON clauses:
SELECT CompanyName FROM Customers c
LEFT JOIN Orders o
LEFT JOIN [Order details] od
LEFT JOIN Products p ON od.ProductID=p.ProductID
ON o.OrderID=od.OrderID
ON o.CustomerID=c.CustomerID
AND p.ProductName='Chang'
WHERE o.CustomerID IS NULL
or we can use a RIGHT JOIN:
SELECT CompanyName FROM Orders o
INNER JOIN [Order details] od ON o.OrderID=od.OrderID
INNER JOIN Products p ON od.ProductID=p.ProductID
RIGHT JOIN Customers c ON o.CustomerID=c.CustomerID
AND p.ProductName='Chang'
WHERE o.CustomerID IS NULL
However, if we examine the execution plans, we can see that the first
query (which is also easier to understand) has a lower cost than the
other two queries (which have the same execution plan).
For more informations, see:
http://groups.google.com/group/micr...br />
2579509b
http://groups.google.com/group/micr...br />
22a96b90
Razvan

Out of memory error when running large report

I have built a large report but when I try to execute inside of the Report
Designer, I get the following error and then an abort:
[An error has occurred during report processing
Exception of type System.OutOfMemoryException was thrown]
I have applied SRS SP1 and here is the MSD info:
Microsoft Visual Basic .NET 69457-005-1826837-18410
Microsoft SQL Server Report Designer Version 8.00.878.00
Running on XP SP2 with 1.25 gig RAM
Is there a work-around for this?
Thanks for any help!http://support.microsoft.com/?kbid=840709
This article may help you.
| From: "Mike Harbinger" <MikeH@.Cybervillage.net>
| Subject: Out of memory error when running large report
| Date: Thu, 6 Jan 2005 18:47:55 -0800
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#$rvPNG9EHA.1408@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 69.106.50.114
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10|||Thanks for the reponse Brad, but I am not getting the error from the report
server but from inside of the .Net Visual Studio Reporting Services
designer.
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:g7L9ZjO9EHA.3200@.cpmsftngxa10.phx.gbl...
> http://support.microsoft.com/?kbid=840709
> This article may help you.
>
> --
> | From: "Mike Harbinger" <MikeH@.Cybervillage.net>
> | Subject: Out of memory error when running large report
> | Date: Thu, 6 Jan 2005 18:47:55 -0800
> | Lines: 18
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <#$rvPNG9EHA.1408@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 69.106.50.114
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:38909
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have built a large report but when I try to execute inside of the
> Report
> | Designer, I get the following error and then an abort:
> |
> | [An error has occurred during report processing
> | Exception of type System.OutOfMemoryException was thrown]
> |
> |
> | I have applied SRS SP1 and here is the MSD info:
> | Microsoft Visual Basic .NET 69457-005-1826837-18410
> | Microsoft SQL Server Report Designer Version 8.00.878.00
> |
> | Running on XP SP2 with 1.25 gig RAM
> |
> | Is there a work-around for this?
> |
> | Thanks for any help!
> |
> |
> |
>

Out in email.

I am using extended stored procedure xp_sendmail to send the email now my
question is, is there any way in can write the out of following query into
one file and send it to through email? or output directly goes to email..
restore FILELISTONLY from disk = 'c:\fullpath.bak'
Thanks
Probably a few ways but a couple off the top of my head -
Execute the command using osql and specify an output file in
the osql command.
Execute the command within a job - for the job step doing
the restore filelistonly, go to the advanced tab and specify
an output file.
You can then send the output file as an attachment using
xp_sendmail.
-Sue
On Mon, 30 Oct 2006 08:20:28 -0500, "Rogers"
<naissani@.hotmail.com> wrote:

>I am using extended stored procedure xp_sendmail to send the email now my
>question is, is there any way in can write the out of following query into
>one file and send it to through email? or output directly goes to email..
>restore FILELISTONLY from disk = 'c:\fullpath.bak'
>Thanks
>
|||Thanks Sue...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0ladk2d0g0n0gm69qv3ot2tgq5fagd7vkr@.4ax.com...
> Probably a few ways but a couple off the top of my head -
> Execute the command using osql and specify an output file in
> the osql command.
> Execute the command within a job - for the job step doing
> the restore filelistonly, go to the advanced tab and specify
> an output file.
> You can then send the output file as an attachment using
> xp_sendmail.
> -Sue
> On Mon, 30 Oct 2006 08:20:28 -0500, "Rogers"
> <naissani@.hotmail.com> wrote:
>

OUT and OUTPUT - Stored procedure parameters.

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET-

--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @.Product varchar(40)

, @.MaxPrice money

, @.ComparePrice money OUTPUT

, @.ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice;

-- Populate the output variable @.ListPprice.

SET @.ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice);

-- Populate the output variable @.compareprice.

SET @.ComparePrice = @.MaxPrice;

GO

USE

DECLARE @.ComparePrice money, @.Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@.ComparePrice OUT,

@.Cost OUTPUT

IF @.Cost <= @.ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

-

Partial Result Set

-

--Product List Price

-

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.

Well, OUT and OUTPUT keywords are synonyms, just as INT and INTEGER are.|||

Really?

AAAAAAAARGH it's all clear now. I can't see why in the example they mix and match however though - it just brings confusion into the equation.

No point in answering the other part of my Q: I 'm sure the example iterates throught the execution of the SP, but doesn't show it.

Thank you Sergey.

Other than UNION

Hello,

Bear with me (not had much sleep last night), pls see following ddl,
dml and comments for what is desired, I don't have a problem getting
desired result(s), however, I'm wondering if there's another (better)
solution than UNION operator in this case. TIA.

-- DDL
CREATE TABLE #TMP (col varchar(10));

-- DML
insert into #TMP
values('A124');

insert into #TMP
values('A127');

insert into #TMP
values('A12728');

insert into #TMP
values('A17282');

insert into #TMP
values('BCD');

insert into #TMP
values('BCD');

insert into #TMP
values('CDSS');

insert into #TMP
values('DS');

insert into #TMP
values('YUUEI');

-- goal: get one row with col data starting with 'A' and distict rows
for the rest

select top 1 col
from #TMP
where col LIKE 'A%'

UNION

select distinct col
from #TMP
where col NOT LIKE 'A%'On 16 Mar 2005 11:19:33 -0800, NickName wrote:

>Bear with me (not had much sleep last night), pls see following ddl,
>dml and comments for what is desired, I don't have a problem getting
>desired result(s), however, I'm wondering if there's another (better)
>solution than UNION operator in this case. TIA.

Hi Nick,

Well, using UNION ALL instead of UNION would be the obvious improvement.
Or you could use the following (completely different) approach. Test it
in your environment to see which version performs best.

select distinct col
from #TMP AS t1
where col not like 'A%'
or not exists (select *
from #TMP AS t2
where col like 'A%'
and t2.col < t1.col)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the response, Hugo. I was not paying enough attention. The
"DISTINCT" key word is not necessary in previous posting because UNION
would return unique records anyway, as for UNION ALL, it does not seem
applicable here because it would return duplicate rows as well, which
was/is not desired.

Don

Hugo Kornelis wrote:
> On 16 Mar 2005 11:19:33 -0800, NickName wrote:
> >Bear with me (not had much sleep last night), pls see following ddl,
> >dml and comments for what is desired, I don't have a problem getting
> >desired result(s), however, I'm wondering if there's another
(better)
> >solution than UNION operator in this case. TIA.
> Hi Nick,
> Well, using UNION ALL instead of UNION would be the obvious
improvement.
> Or you could use the following (completely different) approach. Test
it
> in your environment to see which version performs best.
> select distinct col
> from #TMP AS t1
> where col not like 'A%'
> or not exists (select *
> from #TMP AS t2
> where col like 'A%'
> and t2.col < t1.col)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On 16 Mar 2005 18:16:12 -0800, NickName wrote:

>Thanks for the response, Hugo. I was not paying enough attention. The
>"DISTINCT" key word is not necessary in previous posting because UNION
>would return unique records anyway, as for UNION ALL, it does not seem
>applicable here because it would return duplicate rows as well, which
>was/is not desired.

Hi Don,

In your original code, your first query will always return one row, so
it can't have duplicates; the second uses DISTINCT to eliminate
duplicates. But since you don't use UNION ALL, the server still has to
check if the row from the first result set is also in the second result
set, and eliminate it if it is. You and I know that this can never
happen (as a result of mutual exclusive WHERRE clauses), so we can save
the server some work by adding ALL to the UNION keyword.

If you remove the DISTINCT from the second query, then you must indeed
use UNION, not UNION ALL. You still might to check if there's a big
difference in execution times (probably not, but you never know). Oh,
and do include my UNION-less version in your performance tests as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo, I appreciate your opinion. As my original posting indicated
"I was not in thinking mode when I composed the msg", for just now when
I took a closer look, I notice that the data set is small, hence,
almost "any" query would do the job well, not really necessarily for
optimization, Query Analyzer wasn't even able to calculate execution
time, however, for large data set, your other approach would be
meaningful to try. Again thanks.

Don

osql will not come up MSDE (sp3) or earlier

When I try to run osql, I get the following error:
c:\program files\Microsoft SQL Server\80\Tools\Binn\

> osql -E -d CCOP or osql -U sa -P AStrongPassword -d CCOP
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
I can start the instance "CCOP" from sqlmangr without any
issue, but when I try to run osql against the database, I
can never get past the startup
-d is the switch for the database name, not the instance name. Use the -S
switch to specify the instance name.
If you are not sure of the instance name, issue a osql -L for a list of SQL
Server instances.
See
http://msdn.microsoft.com/library/de...ta_01_2r1e.asp
for more information on running the osql utility.
Terri Morton
MVP - ASP/ASP.NET
"Ed H" <anonymous@.discussions.microsoft.com> wrote in message
news:0f4301c4ff5e$4c209930$a401280a@.phx.gbl...
> When I try to run osql, I get the following error:
> c:\program files\Microsoft SQL Server\80\Tools\Binn\
>
> [Shared Memory]SQL Server does not exist or access denied.
> [Shared Memory]ConnectionOpen (Connect()).
> I can start the instance "CCOP" from sqlmangr without any
> issue, but when I try to run osql against the database, I
> can never get past the startup
|||
>--Original Message--
>-d is the switch for the database name, not the instance
name. Use the -S
>switch to specify the instance name.
>If you are not sure of the instance name, issue a osql -
L for a list of SQL
>Server instances.
>See
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/acdata/ac_8_mta_01_2r1e.asp
>for more information on running the osql utility.
>Terri Morton
>MVP - ASP/ASP.NET
>"Ed H" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0f4301c4ff5e$4c209930$a401280a@.phx.gbl...
CCOP[vbcol=seagreen]
denied.[vbcol=seagreen]
any[vbcol=seagreen]
database, I
>
>.
> Thx Terri. I had to re-install amoung other things.
The MSDN Help was a very valuable.

osql to connect to a Database

I am running MSDE 2000 Rel A on a Windows XP machine and am able to
connect to it by doing the following:
osql -U sa
or
osql -E
But how do I connect to a Particular Database or see all the tables
etc? I am a but familiar with sql (which could be the problem) and an
trying to do the following that I can with it:
sql user_name/database_name@.password
so that I can connect directly to the DB I'm interested and then I can
do:
select tablenames;
desc table_name;
select * from table_name;
etc
Any ideas? Like I mentioned, maybe I'm trying to do things that is not
supposed to be possible with osql (which would really suck).
- manzoorosql -S (local) -E -d pubs
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Manzoorul Hassan" <manzoorul.hassan@.gmail.com> wrote in message
news:1105052724.438484.94120@.z14g2000cwz.googlegroups.com...
>I am running MSDE 2000 Rel A on a Windows XP machine and am able to
> connect to it by doing the following:
> osql -U sa
> or
> osql -E
> But how do I connect to a Particular Database or see all the tables
> etc? I am a but familiar with sql (which could be the problem) and an
> trying to do the following that I can with it:
> sql user_name/database_name@.password
> so that I can connect directly to the DB I'm interested and then I can
> do:
> select tablenames;
> desc table_name;
> select * from table_name;
> etc
> Any ideas? Like I mentioned, maybe I'm trying to do things that is not
> supposed to be possible with osql (which would really suck).
> - manzoor
>|||Hi Hassan,
From your posting it looks like 'sa' password is blank (that is incase you
are not giving any password when OSQL utility prompts).
Best practice is to change the 'sa' pwd to something else, as it gives
access to anybody in your network, with sql client.
If you are not using blank pwd, please ignore this message.
--
Thanks
Yogish|||thanx a million!!!!
- manzoor

osql -o

I would like to create a dynamic output file with the following code:
Declare @.cmd varchar(1000)
Declare @.filepath varchar(1000)
Set @.filepath = '\\server\path\filename+date.csv >>>> I would like the
mm/dd/yy appended to the filename then .csv extension
Set @.cmd = 'Osql -E -S MyservA -d msdb -Q "sp_databases" /o "@.Filepath" -w
2000' >>>> here is where I would like the new filename to be used.
Exec master..xp_cmdshell @.cmd
Is there also anyway to log this to a file to track execution ?
Thanks...Hi,
you might be interested in that:
http://www.databasejournal.com/feat...cle.php/3386661
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Hoosbruin" <Hoosbruin@.Kconline.com> schrieb im Newsbeitrag
news:BNGdnQ_yUIxBcuvfRVn-1Q@.kconline.com...
>I would like to create a dynamic output file with the following code:
>
> Declare @.cmd varchar(1000)
> Declare @.filepath varchar(1000)
> Set @.filepath = '\\server\path\filename+date.csv >>>> I would like the
> mm/dd/yy appended to the filename then .csv extension
> Set @.cmd = 'Osql -E -S MyservA -d msdb -Q "sp_databases" /o "@.Filepath" -w
> 2000' >>>> here is where I would like the new filename to be used.
> Exec master..xp_cmdshell @.cmd
>
> Is there also anyway to log this to a file to track execution ?
>
> Thanks...
>
>
>

oSql Error Handling

Can anyone please tell me why my error handling does not work. I am using the following code in a script file which I pass as an input file to oSql:

IF (@.@.ERROR <> 0)
EXIT(1)
ELSE
EXIT(0)

I have also tried labels as written below to no avail:
IF (@.@.ERROR <> 0) GOTO error_control

success_control:
EXIT(0)

error_control:
EXIT(1)

If anyone knows the solution could they please let me know.I've just written a little script as well and it seems that 'EXIT' must be the last statement within the script.

Example:
SET NOCOUNT ON

DECLARE @.rc int
SET @.rc = 0

IF ((SELECT DB_NAME()) = 'pubs') BEGIN
SET @.rc = 99
GOTO COMMON_EXIT
END

SELECT DB_NAME()

COMMON_EXIT:
EXIT(SELECT @.rc)
GO

The EXIT that will set the MS-DOS ERRORLEVEL variable.

Example (1)
C:\Work>osql -E -Styche -isctest.sql -n -h-1
master

0

C:\Work>echo %errorlevel%
0

C:\Work>


Example (2) against Pubs
C:\Work>osql -E -Styche -isctest.sql -n -dpubs -h-1
99

C:\Work>echo %errorlevel%
99

C:\Work>

Also one thing about @.@.ERROR, it is affect by all T-SQL statements.

Example

UPDATE blah blah blah

IF (@.@.ROWCOUNT = 0) BEGIN
IF (@.@.ERROR = 0)
SET @.RC = 0
ELSE
SET @.RC = 1
END

@.@.ERROR will always be 0, since the IF statement is successful it will override the @.@.ERROR value set by the UPDATE statement.

UPDATE blah blah blah

SELECT @.Rows = @.@.ROWCOUNT,
@.Err = @.@.ERROR

IF (@.Rows = 0) BEGIN
IF (@.Err = 0)
SET @.RC = 0
ELSE
SET @.RC = 1
END