Having trouble distilling something down to a single query

Ok, lets say I have 2 tables, A & B.

Lets say table a has the key A.record_id, and the field emp.

Say table b has the key B.record_id, a foreign key B.a_id that links it to table A, and the field B.date. Now, I want to join these tables as so:

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN "DBA"."B" TABLE B ON
A."RECORD_ID" = B."A_ID"}

You see, I want a list of all A.record_id, whether or not I get a return from the B table.

The problem arises when I want to limit the dates via B.date. It's clear to me what the problem is here, I just don't know a way around it.

WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

So basically, now I'm not getting any a.record_id's for a's that are linked to a b that fall outside of that date range.

Summing up I want...

All A + B where there is a B.date in that range
No A+B for results that are not within the entered date range.
All A's, regardless of if there is a linked B.
All A's, even if there are linked B's outside of the date range.
All in 1 statement (due to environment limitations).

Thanks for your help. I'm pretty much self taught here, so I apologize for not having the language knowledge to make this question more concise. Of course if I knew better how to explain what I'm trying to do then I'd probably know how to do it. ;-)

Mock Sample Data

table A
A001 bill
A002 bill
A003 bill
A004 frank
A005 frank
A006 bob

table B
B001 A001 1/1/2004
B002 A001 1/15/2004
B003 A001 4/1/2004
B004 A003 5/1/2004
B005 A004 1/1/2004
B006 A005 3/3/2004

Mock Results

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A003 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL
A006 bob NULL NULL


edit: added mock data/resultsI'd use:(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))-PatP|||Sorry, that's how it's in there now, editing above to reflect.

Not the problem.|||What version of which database engine are you using?

-PatP|||Using Sybase 9.|||I just noticed some apparent inconsistancies in your query. Could you post the entire query as you are submitting it so that I can try it? I'm using version 8, but I'd expect that to be close enough.

-PatP|||SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" A LEFT OUTER JOIN "DBA"."B" B ON
A."RECORD_ID" = B."A_ID"}
WHERE
(B."DATE" IS NULL OR
(B."DATE" >= {d '2004-01-01'} AND
B."DATE" <= {d '2004-01-31'}))

Mock Current Results From Earlier Mock Data

A001 bill B001 1/1/2004
A001 bill B002 1/15/2004
A002 bill NULL NULL
A004 frank B004 1/1/2004
A005 frank NULL NULL

Thanks for looking at this.|||Give up? ;-)|||Try a LEFT OUTER JOIN to a Subquery that restricts your table B.

SELECT
A."RECORD_ID", A."EMP",
B."RECORD_ID", B."DATE"
FROM
{ oj "DBA"."A" TABLE_A LEFT OUTER JOIN
(Select *
FROM "DBA"."B" TABLE B where ((B.DAte <='1/31/2004' and B.Date >='1/01/2004') OR B.DAte is NULL)) as S1
on A."RECORD_ID" = S1."A_ID"}

I may have a typo with the brackets up there, but something like that should work.

The key is that you are creating a subquery with results restricted to your data range, and then naming that subquery S1. Then the results of S1 are joined to table A.|||1. All A + B where there is a B.date in that range
2. No A+B for results that are not within the entered date range.
3. All A's, regardless of if there is a linked B.
4. All A's, even if there are linked B's outside of the date range.
5. All in 1 statement (due to environment limitations).
Unless I am missing something, - there are contradicting conditions in your requirements:

If #1 is to be met Then #3 & #4 cannot be
If #2 is to be met Then #4 cannot be
If #3 is to be met Then B.date is NULL, thus #1 cannot be
If #4 is to be met...see above

Can you clarify?

Having problems with text datatype!

Hi,

pls can anyone help me to solve the error generated by this query,

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spAP_PS_VENDOR_CONVER]

AS

SET NOCOUNT ON

SELECT UPPER(SETID) AS SETID

,UPPER(VENDOR_ID)AS VENDOR_ID

,CONVER_DT

,CONVER_SEQ_NUM

,CNTCT_SEQ_NUM

,UPPER(CONVER_TOPIC) AS CONVER_TOPIC

,UPPER(OPRID)AS OPRID

,REVIEW_DAYS

,REVIEW_DATE

,REVIEW_NEXT_DATE

,UPPER(KEYWORD1) AS KEYWORD1

,UPPER(KEYWORD2) AS KEYWORD2

,UPPER(KEYWORD3) AS KEYWORD3

,CAST(ISNULL(DESCRLONG,'') AS VARCHAR(200)) AS DESCRLONG

,PROCESS_INSTANCE

,MAX(EY_SF_UPDATE_DTTM) AS EY_SF_UPDATE_DTTM

,PROCESS_DTTM

,CREATED_DTTM

,UPPER(EY_SF_ACTN_FLG) AS EY_SF_ACTN_FLG

,UPPER(EY_SF_STATUS) AS EY_SF_STATUS

FROM Metastorm.dbo.AP_PS_VENDOR_CONVER

WHERE EY_SF_STATUS='N'

GROUP BY SETID,VENDOR_ID,CONVER_DT,CONVER_SEQ_NUM,CNTCT_SEQ_NUM,CONVER_TOPIC,OPRID

,REVIEW_DAYS,REVIEW_DATE,REVIEW_NEXT_DATE,KEYWORD1,KEYWORD2,KEYWORD3,DESCRLONG

,PROCESS_INSTANCE,PROCESS_DTTM,CREATED_DTTM,EY_SF_ACTN_FLG

,EY_SF_STATUS

SET NOCOUNT OFF

Msg 306, Level 16, State 2, Procedure spAP_PS_VENDOR_CONVER, Line 4

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Regards,

Sg

sorry forgot to tell that the descrlong is a text column

Regards,

sg

|||

You couldn't use text, ntext, image datatype in group by.

Try use following statement in group by:

Code Snippet

GROUP BY CAST(ISNULL(DESCRLONG,'') AS VARCHAR(200)), .....

|||

Hi Konstantin,

Thanks a lot.

Regards,

Sg

Having problems with distinct and count

Hi,
Here is the query I am trying to achieve and having syntax issues
Select count(distinct name, number) from results.
To replicate the situation use the following SQL
create table results (name varchar(100), number int)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test2', 2)
insert into results values ('test2', 2)
insert into results values ('test2', 2)
Basically the return of the query should be 2. I can achieve this by
doing following query
select count(*) from
(select distinct [name], [number] from results) a
but I want to do it one query as the later query is a big hit on the
performance.
On a large sample of data the second query takes around 2 seconds.
Any help would be appreciated.
Thanks
SAIan index on (name, number) might speed it up

Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!
Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!
|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.
|||On Apr 4, 11:07Xam, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? X"Empty of data" and "blank" are two different
> things, in my opinion. XEmpty of data means NULL (and you have the correct
> syntax, of that were the case). XBlank means an empty string, e.g. ''. XIn
> which case,
> WHERE CompanyID = ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) = ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptual
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!
|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,[vbcol=seagreen]
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...

Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.|||On Apr 4, 11:07=A0am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? =A0"Empty of data" and "blank" are two different
> things, in my opinion. =A0Empty of data means NULL (and you have the corre=ct
> syntax, of that were the case). =A0Blank means an empty string, e.g. ''. ==A0In
> which case,
> WHERE CompanyID =3D ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) =3D ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptu=al
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
> > Hey guys,
> > I am trying to run a query that will return all rows where a column is
> > completely empty of data.
> > I try this query:
> > select * from patients_visitInsurers
> > where CompanyID is =A0NULL
> > But it does not return anything for me. =A0If I try the reverse
> > select * from patients_visitInsurers
> > where CompanyID is =A0not NULL
> > It returns rows that contain CompanyId values and rows that are blank.
> > What am I missing here? =A0 Thanks!- Hide quoted text -
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...sql

Having Multiple ranges in the query

Hi All,
I have a problem to get this work done. I want to optimize this by
using multiple ranges in having cluase..
for the understanding i'm writing down the whole Query here ...
----
--
SELECT
DISTINCT VAR2 AS TotalCount,
Max(NetTime) as Time
FROM
XYZ
WHERE
(Talk > 0) AND (DateTime = '2006-05-15')
GROUP BY
VAR2, NetTime
Having
((NetTime > 0) AND (NetTime < 11))
----
--
I want to have data in different ranges like this in below:
0-10 11-20 21-30 31-40 41-50 51-60
61-120 Above 120
-- -- -- -- -- --
-- -- -- --
1779 1410 1109 633 569 560
1013 798
I would highly appriciate for any quick response
TIA
-- Atif Iqbal --Hi
Probably you will be better off doing such reports in the client side
"Atif Iqbal" <aatif.iqbal@.gmail.com> wrote in message
news:1147777689.050841.290500@.g10g2000cwb.googlegroups.com...
> Hi All,
> I have a problem to get this work done. I want to optimize this by
> using multiple ranges in having cluase..
> for the understanding i'm writing down the whole Query here ...
> ----
--
> SELECT
> DISTINCT VAR2 AS TotalCount,
> Max(NetTime) as Time
> FROM
> XYZ
> WHERE
> (Talk > 0) AND (DateTime = '2006-05-15')
> GROUP BY
> VAR2, NetTime
> Having
> ((NetTime > 0) AND (NetTime < 11))
> ----
--
> I want to have data in different ranges like this in below:
> 0-10 11-20 21-30 31-40 41-50 51-60
> 61-120 Above 120
> -- -- -- -- -- --
> -- -- -- --
> 1779 1410 1109 633 569 560
> 1013 798
> I would highly appriciate for any quick response
>
> TIA
> -- Atif Iqbal --
>|||This is best done client side, but something along these lines may help, if
you insist on doing it on the database. Dividing the nettime by 10 and
rounding down will give you a row for each range, although the ranges will
run from 0 to 9 for values ranging from 0 to less than 10, 10 to 19, etc.
It is not the entire solution, but may get you most of the way there. I
don't think you want the distinct in there, since the group by and count
should be handling that.
SELECT
VAR2 AS TotalCount
, count(var2)
, floor(NetTime/10) as TimeRangeLow
, floor(NetTime/10)+9 as TimeRangeHigh
FROM
XYZ
WHERE
(Talk > 0) AND (DateTime = '2006-05-15')
GROUP BY
VAR2, floor(NetTime/10)
"Atif Iqbal" <aatif.iqbal@.gmail.com> wrote in message
news:1147777689.050841.290500@.g10g2000cwb.googlegroups.com...
> Hi All,
> I have a problem to get this work done. I want to optimize this by
> using multiple ranges in having cluase..
> for the understanding i'm writing down the whole Query here ...
> ----
--
> SELECT
> DISTINCT VAR2 AS TotalCount,
> Max(NetTime) as Time
> FROM
> XYZ
> WHERE
> (Talk > 0) AND (DateTime = '2006-05-15')
> GROUP BY
> VAR2, NetTime
> Having
> ((NetTime > 0) AND (NetTime < 11))
> ----
--
> I want to have data in different ranges like this in below:
> 0-10 11-20 21-30 31-40 41-50 51-60
> 61-120 Above 120
> -- -- -- -- -- --
> -- -- -- --
> 1779 1410 1109 633 569 560
> 1013 798
> I would highly appriciate for any quick response
>
> TIA
> -- Atif Iqbal --
>

Having major problems with my insert query logic

I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.

Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.

Here is some example code from my program:
sub Test{
foreach my $table(keys %$HoH){
foreach my $field(keys %{$HoH->{$table}}){
if($table eq "CPU"){
my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values ('$field',
'$HoH->{CPU}{$field}{Name}',
'$HoH->{CPU}{$field}{MaxClockSpeed}' ,
'$HoH->{Host}{SystemNetName}')";
print "$CPUstatement\n";
if ($db->Sql($CPUstatement))
{
print "Error on SQL Statement\n";
Win32::ODBC::DumpError();
}
else
{
print "successful\n";
}
}
}


}
}

Thanks,
LauraI'm assuming that your hash values are printing as expected. The construction looks strange (but it could be fine) to me.

Is the CPUNumber the primary key for the CPU table? If so, you could use something like:my $CPUstatement = "IF EXISTS (SELECT * FROM CPU WHERE CPUNUMBER = '$field')
THEN UPDATE CPU
SET Name = '$HoH->{CPU}{$field}{Name}'
, MaxClockSpeed = '$HoH->{CPU}{$field}{MaxClockSpeed}'
, SystemNetName = '$HoH->{Host}{SystemNetName}'
WHERE CPUNumber = '$field'
ELSE INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values (
'$field'
, '$HoH->{CPU}{$field}{Name}'
, '$HoH->{CPU}{$field}{MaxClockSpeed}'
, '$HoH->{Host}{SystemNetName}')";-PatP|||I tried your code and I am getting an error -> Incorrect Systax near the keyword 'THEN'

What do you suppose that means? I copied and pasted the code as is.

Thanks,
Laura|||That error means that I don't proofread very well ;) I was composing as I typed, and simply got ahead of myself then didn't clean up afterwards. Just remove the word THEN from that statement. Sorry.

-PatP|||oh wow. That's so cool, it worked.

Thanks Pat for your help. I learn new things everyday.

-Laura|||I just love it when I can make a lovely lady happy!

-PatP

Having difficulty creating a stored procedure

I am trying to create stored procedure i Query analyzer in visual studio 2005. I am having
difficulty though. Whenever I press the execute button, here is the error message I get:

Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21
Incorrect syntax near 'MarketName'.

Here is the stored procedure. Note that the very first column in named "MarketId" but I did not
include it in the stored procedure since it should be auto generated.

USE [StockWatch]
GO
/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MarketCreate]

(
@.MarketCode nvarchar(20),
@.MarketName nvarchar(100),
@.LastUpdateDate nvarchar(2),
@.MarketDescription nvarchar(100)
)

AS
INSERT INTO Market
(
MarketCode
MarketName
LastUpdateDate
MarketDescription
)
VALUES
(
@.MarketCode
@.MarketName
@.LastUpdateUser
@.MarketDescription
)

You need to use comma's to separate the column names.

USE[StockWatch]

GO

/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATEPROCEDURE [dbo].[MarketCreate]

(

@.MarketCodenvarchar(20),

@.MarketNamenvarchar(100),

@.LastUpdateDatenvarchar(2),

@.MarketDescriptionnvarchar(100)

)

AS

INSERTINTO Market

(

MarketCode,

MarketName,

LastUpdateDate,

MarketDescription

)

VALUES

(

@.MarketCode,

@.MarketName,

@.LastUpdateUser,

@.MarketDescription

)

|||

Thanks !

Having clause without GROUP BY clause?

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.those queries actually run in oracle? i rather doubt it

without a GROUP BY, the entire table is considered a single group

the individual col2 values would not necessarily all be the same, therefore the HAVING condition in the first query would not necessarily give you the results you want, assuming it even runs, which i doubt

in the second query you will surely get a syntax error even in oracle

perhaps what you want for the two queries is:

SELECT SUM(col1) from test where col2 < 5

SELECT SUM(col1) from test WHERE x=y AND col2 < 5|||Sorry for the typos. Actually the queries are as follows.

SELECT SUM(col1) from test HAVING SUM(col2) < 5

SELECT SUM(col1) from test WHERE x=y HAVING SUM(col2) < 5

Thanks,
Gopi.|||do you have sql server? if so, why don't you test those queries and see what you get

HAVING clause is a case statement??

i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:

(simple ex)

Select order_no
from table1
inner join table2
on table1.order_no = table2.order_no
group by order_no
having table1.Qty<> table2.Qty

BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:

CASE WHEN table1.Qty<> table3.Qty
THEN table3.Qty<> table2.Qty
ELSE table1.Qty<> table2.Qty END

but how do i actually write this?perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions|||perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions

Sorry I am trying to compare Sum(qty) for each product in an order (product maybe in the order more than 1ce) I am trying to retrieve any product lines where Sum qties in table1 and table2 are not the same.

However, if stock was not found, then an allocated qty is recorded in table 3...so in this case I want to compare qtyies in table3 and table2
?|||select t1.order_no
, t1.sumqty
, t2.order_no
, t2.sumqty
from (
select order_no
, sum(qty) as sumqty
from table1
group
by order_no
) as t1
full outer
join (
select order_no
, sum(qty) as sumqty
from table2
group
by order_no
) as t2
on t2.order_no = t1.order_no
and t2.sumqty <> t1.sumqtythat's the general strategy -- do your sums in derived tables

for table 3, you're on your own :)

having a select statement with no output

i have the following select query "select myCol from myTbl"

i cant modify the query but i can add to it;
what i want is to cancel the output of this query so it has no output( just like an update or insert query). a way for doing this is using 'select into' a #temp table but i cant do this becoz adding the 'into' at the end wont work after the 'from'.

i can use 'union all' to add another select statement but i cant use 'into' in the second statement becoz it has to be in the first one.

adding 'where 1=2' is not what i want becoz it still gives an empty output

any way to do this ?

thxCan you only add to the end of it? If you just add "--" to the front of it it won't execute at all, and you won't get any output.

This is certainly one of the strange requests I've seen on this forum. Why do you wan't a select statements with no output? Are you doing debugging or some sort of iterative process automation?

blindman|||thx for ur reply

i cant add to the front becoz it's a written query that i cant modify but can only add a string to the end before it is executed.

my prob is solved now after i found my source files and now i can modify the query..

thx|||I STILL have no idea...

WOAC radio

HAVING a problem

Hi NG,
I have a problem with a query. I have three columns with relations from
A to B and the number of eg. Orders:
C1 | C2 | Number
A B 17
A C 4
A E 23
B A 22
B G 19
B J 21
What I want is to get from each C1 element (A, B) the relation to the
C2 with the lowest number, in this example:
C1 | C2 | Number
A C 4
B G 19
How can I do this?
Thank you very much,
RudiSELECT c1, c2, number
FROM your_table AS T
WHERE number =
(SELECT MIN(number)
FROM your_table
WHERE c1 = T.c1) ;
David Portas
SQL Server MVP
--

Have a query run at specific times

Please be patient I am a noobie
We have a Query created in Query Analyzer that we would like to run nightly.
How do we get this query to run on a schedule and update tables?
Hi
Wrong newsgroup. This is the group for data replication.
Look in BOL for "jobs".
If you want to use Enterprise Manager, you can create and schedule a job, it
is under SQL Server Agent.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mueller" <Mueller@.discussions.microsoft.com> wrote in message
news:BA6E0722-D733-4708-B7D6-A972125F3AB9@.microsoft.com...
> Please be patient I am a noobie
> We have a Query created in Query Analyzer that we would like to run
nightly.
> How do we get this query to run on a schedule and update tables?
|||What you need to create is a 'Job'. This is available under Management, SQL
Server Agent, Jobs and there are plenty of details n Books-On-Line (BOL).
Basically right click the jobs node, select new job and the rest is pretty
intuitive as long as you ensure the SQL Server Agent is started and
scheduled to restart on bootup. Please post back after giving it a go.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Have a better UPDATE statement than what I wrote?

/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an Update statement.

I have a sample code to reproduce my problem.
To simplify the scenario I am trying to use Order related
tables to explain a little better the tables i have to work with.
Please don't bother looking at the wrong relationship and how
the tables are designed. That's not my current problem. My
job is to correct the wrong data either using code or manually.

Here are the tables I have created:

TBLORDERS where two fields I am interested in are:
ORDERTYPENO linking to TBLORDERTYPE
LASTSTATUSNO linking to TBLSTATUS

TBLORDERTYPE where one field I am interested in is
ORDERPROCESSINGNO

TBLORDERPROCESSING

Each order has a link to OrderTypeNo and each
OrderTypeNo has a link to OrderProcessingNo.

TBLORDERSTATUSES where one field I am
interested in is
STATUSNO

TBLSTATUS where one field I am interested in is
ORDERPROCESSINGNO

I have the sample code here:
*/

--DROP DATABASE TestDB

CREATE DATABASE TestDB
GO

USE TestDB

CREATE TABLE TBLORDER
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERNUMBER VARCHAR(50),
ORDERTYPENO INT,
LASTSTATUSNO INT
)

INSERT INTO TBLORDER (IDNO, ORDERNUMBER, ORDERTYPENO, LASTSTATUSNO)
SELECT 1, 'ORDERTEST1', 1, 3 UNION ALL
SELECT 2, 'ORDERTEST2', 1, 3 UNION ALL
SELECT 3, 'ORDERTEST3', 2, 16 UNION ALL
SELECT 4, 'ORDERTEST4', 2, 16 UNION ALL
SELECT 5, 'ORDERTEST5', 2, 16 UNION ALL
SELECT 6, 'ORDERTEST6', 2, 16 UNION ALL
SELECT 7, 'ORDERTEST7', 4, 5 UNION ALL
SELECT 8, 'ORDERTEST8', 4, 5 UNION ALL
SELECT 9, 'ORDERTEST9', 6, 22 UNION ALL
SELECT 10, 'ORDERTEST10', 6, 22 UNION ALL
SELECT 11, 'ORDERTEST11', 7, 20

CREATE TABLE TBLORDERSTATUSES
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERNO INT,
STATUSNO INT
)

INSERT INTO TBLORDERSTATUSES (IDNO, ORDERNO, STATUSNO)
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 1, 3 UNION ALL
SELECT 4, 1, 4 UNION ALL
SELECT 5, 2, 1 UNION ALL
SELECT 6, 2, 2 UNION ALL
SELECT 7, 2, 3 UNION ALL
SELECT 8, 2, 4 UNION ALL
SELECT 9, 3, 15 UNION ALL
SELECT 10, 3, 16 UNION ALL
SELECT 11, 3, 17 UNION ALL
SELECT 12, 4, 15 UNION ALL
SELECT 13, 4, 16 UNION ALL
SELECT 14, 4, 17 UNION ALL
SELECT 15, 5, 15 UNION ALL
SELECT 16, 5, 16 UNION ALL
SELECT 17, 5, 17 UNION ALL
SELECT 18, 6, 15 UNION ALL
SELECT 19, 6, 16 UNION ALL
SELECT 20, 6, 17 UNION ALL
SELECT 21, 7, 5 UNION ALL
SELECT 22, 7, 6 UNION ALL
SELECT 23, 8, 5 UNION ALL
SELECT 24, 8, 6 UNION ALL
SELECT 25, 9, 22 UNION ALL
SELECT 26, 9, 23 UNION ALL
SELECT 27, 9, 24 UNION ALL
SELECT 28, 9, 25 UNION ALL
SELECT 29, 10, 22 UNION ALL
SELECT 30, 10, 23 UNION ALL
SELECT 31, 10, 24 UNION ALL
SELECT 32, 10, 25 UNION ALL
SELECT 33, 11, 18 UNION ALL
SELECT 34, 11, 19 UNION ALL
SELECT 35, 11, 20 UNION ALL
SELECT 36, 11, 21

CREATE TABLE TBLORDERTYPE
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERTYPE VARCHAR(50),
ORDERPROCESSINGNO INT
)

INSERT INTO TBLORDERTYPE (IDNO, ORDERTYPE, ORDERPROCESSINGNO)
SELECT 1, 'CATEGORY 100', 1 UNION ALL
SELECT 2, 'CATEGORY 200', 5 UNION ALL
SELECT 3, 'CATEGORY 300', 3 UNION ALL
SELECT 4, 'CATEGORY 400', 2 UNION ALL
SELECT 5, 'CATEGORY 500', 4 UNION ALL
SELECT 6, 'CATEGORY 600', 9 UNION ALL
SELECT 7, 'CATEGORY 700', 8 UNION ALL
SELECT 8, 'CATEGORY 800', 7 UNION ALL
SELECT 9, 'CATEGORY 900', 6

CREATE TABLE TBLORDERPROCESSING
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERPROCESSING VARCHAR(50)
)

INSERT INTO TBLORDERPROCESSING (IDNO, ORDERPROCESSING)
SELECT 1, 'ORDER PROCESSING A1' UNION ALL
SELECT 2, 'ORDER PROCESSING A9' UNION ALL
SELECT 3, 'ORDER PROCESSING Z5' UNION ALL
SELECT 4, 'ORDER PROCESSING 76' UNION ALL
SELECT 5, 'ORDER PROCESSING 98' UNION ALL
SELECT 6, 'ORDER PROCESSING AB' UNION ALL
SELECT 7, 'ORDER PROCESSING 11' UNION ALL
SELECT 8, 'ORDER PROCESSING T7' UNION ALL
SELECT 9, 'ORDER PROCESSING ZX'

CREATE TABLE TBLSTATUS
(
IDNO INT PRIMARY KEY NOT NULL,
STATUS VARCHAR(50),
ORDERPROCESSINGNO INT
)

INSERT INTO TBLSTATUS (IDNO, STATUS, ORDERPROCESSINGNO)
SELECT 1, 'ABC', 1 UNION ALL
SELECT 2, 'DEF', 1 UNION ALL
SELECT 3, 'GHI', 1 UNION ALL
SELECT 4, 'JKL', 1 UNION ALL
SELECT 5, 'MNO', 2 UNION ALL
SELECT 6, 'PQR', 2 UNION ALL
SELECT 7, 'STU', 3 UNION ALL
SELECT 8, 'VWX', 3 UNION ALL
SELECT 9, 'YZ', 3 UNION ALL
SELECT 10, '123', 3 UNION ALL
SELECT 11, '456', 3 UNION ALL
SELECT 12, '789', 3 UNION ALL
SELECT 13, '0AA', 3 UNION ALL
SELECT 14, '0BB', 3 UNION ALL
SELECT 15, '0CC', 5 UNION ALL
SELECT 16, '0DD', 5 UNION ALL
SELECT 17, '0EE', 5 UNION ALL
SELECT 18, '0FF', 8 UNION ALL
SELECT 19, '0GG', 8 UNION ALL
SELECT 20, '0HH', 8 UNION ALL
SELECT 21, '0II', 8 UNION ALL
SELECT 22, '0JJ', 9 UNION ALL
SELECT 23, '0KK', 9 UNION ALL
SELECT 24, '0LL', 9 UNION ALL
SELECT 25, '0MM', 9

/*
If you run the above, the data is CORRECT and the way
it normally should be.

Basically, each Order is linked to an OrderTypeNo. Each
OrderTypeNo is linked to an OrderProcessingNo.
Each Order has MANY OrderStatuses. Each
OrderProcessingNo has MANY Statuses.
So both TBLORDERTYPE and TBLSTATUS is pointing
to TBLORDERPROCESSING. I will mess up an Order
record for example to point to a wrong OrderType and
leave its LASTSTATUSNO and all its CHILD
TBLORDERSTATUSES STATUS records point to
the CORRECT ORDERPROCESSINGNO.
*/

UPDATE TBLORDER
SET ORDERTYPENO = 3
WHERE IDNO = 5 OR IDNO = 10

/*
So now both Order IDNO 5 & 10 are basically messed
up as they are pointing to ORDERTYPENO 3 (i.e.
ORDERPROCESSINGNO 3) whereas their
LASTSTATUSNO and all its TBLORDERSTATUS
STATUS records are pointing to ......
ORDERPROCESSINGNO 5 & 9

Now I will mess up both TBLORDER and
TBLORDERDETAILS in order for my code
NOT to fix it since this I will have to deal and
decide what to correct manually.
*/

UPDATE TBLORDER
SET ORDERTYPENO = 2, LASTSTATUSNO = 15
WHERE IDNO = 8

SELECT * FROM TBLORDER
GO

CREATE VIEW VIEW1
-- This VIEW1 returns all TBLORDER records that have the problem
AS
SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO
FROM TBLORDER
INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO
INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO
AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO
GO

CREATE VIEW VIEW2
-- This VIEW2 does a GROUP BY of all TBLORDER.IDNO &
TBLSTATUS.ORDERPROCESSINGNO
AS
SELECT TOP 100 PERCENT TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
FROM TBLORDERSTATUSES INNER JOIN
TBLORDER ON TBLORDERSTATUSES.ORDERNO = TBLORDER.IDNO
INNER JOIN
TBLSTATUS ON TBLORDERSTATUSES.STATUSNO =
TBLSTATUS.IDNO INNER JOIN
VIEW1 ON TBLORDER.IDNO = VIEW1.IDNO
GROUP BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
ORDER BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
GO

CREATE VIEW VIEW3
-- This VIEW3 checks to see if TBLORDERSTATUS records have more than one
ORDERPROCESSINGNO
AS
SELECT IDNO
FROM VIEW2
GROUP BY IDNO
HAVING (COUNT(*) > 1)
GO

CREATE TABLE TMPORDERS
(
IDNO INT,
OLDORDERTYPENO INT,
NEWORDERTYPENO INT,
LASTSTATUSNO INT
)

INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO
FROM TBLORDER
INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO
INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO
AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO
LEFT JOIN VIEW3 ON TBLORDER.IDNO = VIEW3.IDNO AND VIEW3.IDNO IS NULL

SELECT * FROM TMPORDERS

UPDATE TMPORDERS
SET NEWORDERTYPENO = TBLORDERTYPE.IDNO
FROM TBLORDERTYPE
INNER JOIN TBLORDERPROCESSING ON TBLORDERTYPE.ORDERPROCESSINGNO =
TBLORDERPROCESSING.IDNO
INNER JOIN TBLSTATUS ON TBLORDERPROCESSING.IDNO =
TBLSTATUS.ORDERPROCESSINGNO
WHERE TBLSTATUS.IDNO = TMPORDERS.LASTSTATUSNO

UPDATE TBLORDER
SET ORDERTYPENO = NEWORDERTYPENO
FROM TMPORDERS
WHERE TMPORDERS.IDNO = TBLORDER.IDNO

SELECT * FROM TBLORDER

/*
Is there a better to write my Update statement? As you can see that
I am using 3 views, 1 temp table and 2 update statements to
fix my problem.
I am not even sure if i'll need to add more update statements
to handle other corrections. If that is the case I am trying to
see if my code can be simplified in order for it to be easily
modifiable to handle other scenarios.

Thank you for your time.
*/Hello, Serge

1. To complete your DDL, you should also add the foreign keys and
unique constraints:

ALTER TABLE TBLORDERTYPE ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLSTATUS ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (ORDERTYPENO)
REFERENCES TBLORDERTYPE (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (LASTSTATUSNO)
REFERENCES TBLSTATUS (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (ORDERNO)
REFERENCES TBLORDER (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (STATUSNO)
REFERENCES TBLSTATUS (IDNO)

ALTER TABLE TBLSTATUS ADD UNIQUE (STATUS)
ALTER TABLE TBLORDERTYPE ADD UNIQUE (ORDERTYPE)
ALTER TABLE TBLORDERPROCESSING ADD UNIQUE (ORDERPROCESSING)
ALTER TABLE TBLORDER ADD UNIQUE (ORDERNUMBER)
ALTER TABLE TBLORDERSTATUSES ADD UNIQUE (ORDERNO, STATUSNO)

2. Your "INSERT INTO TMPORDERS [...]" does not perform as you expect,
because the condition "AND VIEW3.IDNO IS NULL" is in the "LEFT JOIN"
clause, not in the WHERE clause (and therefore it's ignored). To
exclude from the INSERT any rows that are in VIEW3 you need to move the
condition "VIEW3.IDNO IS NULL" to the WHERE clause.

3. This corrected "INSERT INTO TMPORDERS [...]" can be rewritten
(without using views and with a slight performance improvement) as:

INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
SELECT O1.IDNO, O1.ORDERTYPENO, O1.LASTSTATUSNO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)

4. The "UPDATE TMPORDERS" statement, can be rewritten (by eliminating
the join with the TBLORDERPROCESSING) as:

UPDATE TMPORDERS SET NEWORDERTYPENO = T.IDNO
FROM TBLORDERTYPE T INNER JOIN TBLSTATUS S
ON T.ORDERPROCESSINGNO = S.ORDERPROCESSINGNO
WHERE S.IDNO = TMPORDERS.LASTSTATUSNO

5. The whole story can be written in a single UPDATE statement, like
this:

UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO
FROM TBLORDER O INNER JOIN (
SELECT O1.IDNO, (
SELECT T4.IDNO FROM TBLORDERTYPE T4
INNER JOIN TBLSTATUS S4
ON T4.ORDERPROCESSINGNO = S4.ORDERPROCESSINGNO
WHERE S4.IDNO = O1.LASTSTATUSNO
) AS NEWORDERTYPENO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)
) X ON O.IDNO=X.IDNO

I have to admit that this is a rather complex UPDATE statement and,
while it's performance is better than your solution, maintainability
may be less. So you may prefer using some views to improve readability
(for example VIEW1, which can be used in two places in the above
statement). However, there are some interesting points that you can
learn from this complex statement:
a) avoiding the use of temporary tables, when subqueries can be used
b) the use of table aliases, to improve readability
c) the use of "COUNT(DISTINCT something)" instead of two "GROUP BY"-s;
d) the use of "NOT EXISTS" subqueries, instead of "LEFT JOIN ... WHERE
... IS NULL";
e) the use of "WHERE ... IN" subqueries, instead of joins (when there
is no column used from the joined subquery);
f) the use of subqueries in the SELECT clause, instead of joins (when
there is only one column used from the joined subquery).
I think that these usages of subqueries (points d,e,f) improve
readability (and may, in rare cases, even improve performance), but
that's for you to decide, in each particular case.

Razvan|||Hello Razvan,

Thank you for your detailed explanation.

It will take me a little bit of time to go through the points
and understand them.

Thanks again.

> 1. To complete your DDL, you should also add the foreign keys and
> unique constraints:
> ALTER TABLE TBLORDERTYPE ADD FOREIGN KEY (ORDERPROCESSINGNO)
> REFERENCES TBLORDERPROCESSING (IDNO)
> ALTER TABLE TBLSTATUS ADD FOREIGN KEY (ORDERPROCESSINGNO)
> REFERENCES TBLORDERPROCESSING (IDNO)
> ALTER TABLE TBLORDER ADD FOREIGN KEY (ORDERTYPENO)
> REFERENCES TBLORDERTYPE (IDNO)
> ALTER TABLE TBLORDER ADD FOREIGN KEY (LASTSTATUSNO)
> REFERENCES TBLSTATUS (IDNO)
> ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (ORDERNO)
> REFERENCES TBLORDER (IDNO)
> ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (STATUSNO)
> REFERENCES TBLSTATUS (IDNO)
> ALTER TABLE TBLSTATUS ADD UNIQUE (STATUS)
> ALTER TABLE TBLORDERTYPE ADD UNIQUE (ORDERTYPE)
> ALTER TABLE TBLORDERPROCESSING ADD UNIQUE (ORDERPROCESSING)
> ALTER TABLE TBLORDER ADD UNIQUE (ORDERNUMBER)
> ALTER TABLE TBLORDERSTATUSES ADD UNIQUE (ORDERNO, STATUSNO)
> 2. Your "INSERT INTO TMPORDERS [...]" does not perform as you expect,
> because the condition "AND VIEW3.IDNO IS NULL" is in the "LEFT JOIN"
> clause, not in the WHERE clause (and therefore it's ignored). To
> exclude from the INSERT any rows that are in VIEW3 you need to move the
> condition "VIEW3.IDNO IS NULL" to the WHERE clause.
> 3. This corrected "INSERT INTO TMPORDERS [...]" can be rewritten
> (without using views and with a slight performance improvement) as:
> INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
> SELECT O1.IDNO, O1.ORDERTYPENO, O1.LASTSTATUSNO
> FROM TBLORDER O1
> INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
> INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
> WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
> AND NOT EXISTS (
> SELECT O2.IDNO
> FROM TBLORDER O2
> INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
> INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
> WHERE O2.IDNO IN (
> SELECT O3.IDNO
> FROM TBLORDER O3
> INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
> INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
> WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
> )
> GROUP BY O2.IDNO
> HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
> )
> 4. The "UPDATE TMPORDERS" statement, can be rewritten (by eliminating
> the join with the TBLORDERPROCESSING) as:
> UPDATE TMPORDERS SET NEWORDERTYPENO = T.IDNO
> FROM TBLORDERTYPE T INNER JOIN TBLSTATUS S
> ON T.ORDERPROCESSINGNO = S.ORDERPROCESSINGNO
> WHERE S.IDNO = TMPORDERS.LASTSTATUSNO
> 5. The whole story can be written in a single UPDATE statement, like
> this:
> UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO
> FROM TBLORDER O INNER JOIN (
> SELECT O1.IDNO, (
> SELECT T4.IDNO FROM TBLORDERTYPE T4
> INNER JOIN TBLSTATUS S4
> ON T4.ORDERPROCESSINGNO = S4.ORDERPROCESSINGNO
> WHERE S4.IDNO = O1.LASTSTATUSNO
> ) AS NEWORDERTYPENO
> FROM TBLORDER O1
> INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
> INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
> WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
> AND NOT EXISTS (
> SELECT O2.IDNO
> FROM TBLORDER O2
> INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
> INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
> WHERE O2.IDNO IN (
> SELECT O3.IDNO
> FROM TBLORDER O3
> INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
> INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
> WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
> )
> GROUP BY O2.IDNO
> HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
> )
> ) X ON O.IDNO=X.IDNO
> I have to admit that this is a rather complex UPDATE statement and,
> while it's performance is better than your solution, maintainability
> may be less. So you may prefer using some views to improve readability
> (for example VIEW1, which can be used in two places in the above
> statement). However, there are some interesting points that you can
> learn from this complex statement:
> a) avoiding the use of temporary tables, when subqueries can be used
> b) the use of table aliases, to improve readability
> c) the use of "COUNT(DISTINCT something)" instead of two "GROUP BY"-s;
> d) the use of "NOT EXISTS" subqueries, instead of "LEFT JOIN ... WHERE
> ... IS NULL";
> e) the use of "WHERE ... IN" subqueries, instead of joins (when there
> is no column used from the joined subquery);
> f) the use of subqueries in the SELECT clause, instead of joins (when
> there is only one column used from the joined subquery).
> I think that these usages of subqueries (points d,e,f) improve
> readability (and may, in rare cases, even improve performance), but
> that's for you to decide, in each particular case.
> Razvan

hash warning, hash recursion errors

sql2000 sp3.
strange problem here. i've got an extremely complex query written by a
developer. the query works and it comes back in a reasonable amount of
time. however, the query generates a "hash warning/hash recursion"
error when it runs. after reading bol, these errors don't really seem
to be very bad. i decided to investigate it further by restoring the
production db to a test server and try it there. guess what. no hash
warning errors on the test server.
prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
allocated for sql server).
test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
allocated for sql server).
the query runs slower on the test server (as expected) even with little
to no traffic and no hash warnings.
my next guess was that on the prod server, all of sql server's ram was
being used by other objects. i did a dropcleanbuffers and a
freeproccache. query still generates hash warnings.
any ideas as to what would cause the hash warnings on prod server but
not test server?
i can't reboot the prod server and i can't stop sqlserver on prod
server. are there any things i can try in sqlserver to free up
resources other than dropcleanbuffers and freeproccache?Does SQL choose the same execution plan on the production and test server?
It's possible that the plans aren't the same...
also... hashing algorithtms take amount of memory into consideration. So
it's entirely possible that the prod server is making a mistake when it
guesses how much memory will ultimately be available for it.
You might also want to make sure statistics are up to date on prod. Out of
date stats might make the optimizer come up with bad hashing (and other)
decisions...
--
Brian Moran
"chxxx" <chxxx@.dontemailme.com> wrote in message
news:3FBB6FB8.C5FF3195@.dontemailme.com...
> sql2000 sp3.
> strange problem here. i've got an extremely complex query written by a
> developer. the query works and it comes back in a reasonable amount of
> time. however, the query generates a "hash warning/hash recursion"
> error when it runs. after reading bol, these errors don't really seem
> to be very bad. i decided to investigate it further by restoring the
> production db to a test server and try it there. guess what. no hash
> warning errors on the test server.
> prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> allocated for sql server).
> test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> allocated for sql server).
> the query runs slower on the test server (as expected) even with little
> to no traffic and no hash warnings.
> my next guess was that on the prod server, all of sql server's ram was
> being used by other objects. i did a dropcleanbuffers and a
> freeproccache. query still generates hash warnings.
> any ideas as to what would cause the hash warnings on prod server but
> not test server?
> i can't reboot the prod server and i can't stop sqlserver on prod
> server. are there any things i can try in sqlserver to free up
> resources other than dropcleanbuffers and freeproccache?
>
>|||I have a bizarre "Hash Warning" performance issue I am trying to
resolve - any help, explanations or thoughts appreciated:
Configuration:
sql2000 sp3 & as2000 sp3
1Gb RAM, Single 1GHz CPU
Note: All queries/testing described below is performed on the same
database on the same server. Statistics are up to date for this
database.
When drilling through on a cube, Analysis Server generates a T-SQL
SELECT and executes it via a call to sp_prepexec.
For a specific drill-through I am testing (see below for actual
T-SQL), this code takes between 28 and 40 seconds to execute
(regardless of caching).
If I copy the exact query (captured via SQL Profiler) into Query
Analyzer, the same query executes in less than 5 seconds - sub-second
on subsequent executions (i.e. with cached data).
The result is the same slow execution using either Analysis Server's
cube browser or a web-based OLAP client application.
I ran SQL Profiler to capture the execution of this code from both
Analysis Server on Drill-Through and Query Analyzer to determine any
differences, and the only differences I can find are:
1) Query Analyzer event is captured as "SQL:BatchCompleted", whereas
the Analysis Server event is captured as "RPC:Completed"
2) No Warning or Error events are generated by Query Analyzer
executing the code, however the Analysis Server-based execution
generated 3 "HASH WARNING" events. The ObjectIDs captured by SQL
Profiler for these 3 Hash Warnings are 1, 12 and 12 again for the
third event, which map to the "sysobjects" and "sysdepends" tables!!
The specific code being executed for this test case is as follows:
================================================================declare @.P1 int
set @.P1=1
exec sp_prepexec @.P1 output, N'@.P1 tinyint,@.P2 char(3),@.P3 char(3),@.P4
char(3),@.P5 char(3),@.P6 char(3),@.P7 char(3),@.P8 char(3),@.P9
char(3),@.P10 char(3),@.P11 char(3),@.P12 char(3),@.P13 char(3),@.P14
char(3),@.P15 char(3),@.P16 char(3),@.P17 char(3),@.P18 char(3),@.P19
char(3),@.P20 char(3),@.P21 char(3),@.P22 char(3),@.P23 char(3),@.P24
char(3),@.P25 char(3),@.P26 char(3),@.P27 char(3),@.P28 char(3),@.P29
char(3),@.P30 char(3),@.P31 char(3),@.P32 char(3),@.P33 char(3),@.P34
char(3),@.P35 char(3),@.P36 char(3),@.P37 char(3),@.P38 char(3),@.P39
char(3),@.P40 char(3),@.P41 char(3),@.P42 char(3),@.P43 char(3),@.P44
char(3),@.P45 char(3),@.P46 char(3),@.P47 char(3),@.P48 char(3),@.P49
char(3),@.P50 char(3),@.P51 char(3),@.P52 char(3),@.P53 char(3),@.P54
char(3),@.P55 char(3),@.P56 char(3),@.P57 char(3),@.P58 char(3),@.P59
char(3),@.P60 varchar(13),@.P61 int,@.P62 int,@.P63 varchar(7)', N'SELECT
"dbo"."mr_Activity"."DetectionDate",
"dbo"."mr_Activity"."ReversalFlag", "dbo"."mr_Activity"."VenueID",
"dbo"."mr_Activity"."ProductCode",
"dbo"."mr_Activity"."ActivityAmount", "dbo"."mr_Venue"."VenueName",
"dbo"."mr_ProductStructure"."ProductHierarchyName",
"dbo"."mr_AccountRef"."XrefNumber", "dbo"."mr_CaseRef"."WIN" FROM
"dbo"."mr_Activity", "dbo"."mr_ActivityReason", "dbo"."mr_Calendar",
"dbo"."mr_ProductStructure", "dbo"."mr_AccountRef",
"dbo"."mr_CaseRef", "dbo"."mr_Venue" WHERE
(mr_ProductStructure.ProductHierarchyID=mr_Activity.ProductCode AND
mr_AccountRef.DataWarehouseAccountID=mr_Activity.DataWarehouseAccountID
AND mr_CaseRef.DataWarehouseCaseID=mr_Activity.DataWarehouseCaseID)
AND (("dbo"."mr_Venue"."VenueID"="dbo"."mr_Activity"."VenueID")) AND
("dbo"."mr_Activity"."ActivityTypeID"=@.P1) AND
("dbo"."mr_Activity"."ProductCode" IN
(@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10,@.P11,@.P12,@.P13,@.P14,@.P15,@.P16,@.P17,@.P18,@.P19,@.P20,@.P21,@.P22,@.P23,@.P24,@.P25,@.P26,@.P27,@.P28,@.P29,@.P30,@.P31,@.P32,@.P33,@.P34,@.P35,@.P36,@.P37,@.P38,@.P39,@.P40,@.P41,@.P42,@.P43,@.P44,@.P45,@.P46,@.P47,@.P48,@.P49,@.P50,@.P51,@.P52,@.P53,@.P54,@.P55,@.P56,@.P57,@.P58,@.P59))
AND ("dbo"."mr_ActivityReason"."BusinessType"=@.P60) AND
("dbo"."mr_Activity"."ReasonID"="dbo"."mr_ActivityReason"."ReasonID")
AND ("dbo"."mr_Activity"."ActivityTypeID"="dbo"."mr_ActivityReason"."ActivityTypeID")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 100) +
DatePart(month,"dbo"."mr_Calendar"."CalendarDate")=@.P61) AND
("dbo"."mr_Calendar"."CalendarDate"="dbo"."mr_Activity"."DetectionDate")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 10) +
"dbo"."mr_Calendar"."FiscalQtr"=@.P62) AND (''Measure''=@.P63)', 1,
'-36', '-39', '261', '222', '-40', '042', '-42', '259', '221', '-43',
'141', '-45', '030', '025', '026', '027', '028', '029', '-46', '035',
'032', '024', '-47', '282', '283', '284', '285', '286', '287', '-11',
'288', '289', '290', '291', '292', '293', '-11', '294', '295', '296',
'297', '298', '299', '-11', '-11', '-48', '-51', '220', '215', '216',
'217', '218', '219', '-53', '257', '214', '139', '-54', 'New
Business', 200307, 20031, 'Measure'
select @.P1
================================================================
The fact that Hash Warnings are appearing against sysobjects and
sysdepends in the database I find quite bizarre. I suspect these hash
warnings are related to the performance discrepancy betweeen the two
"modes" of execution (1 second vs. 30 seconds).
Any help on resolving (or even explaining) this performance
discrepancy issue is greatly appreciated.
Piquet.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message news:<epmCOPqrDHA.536@.tk2msftngp13.phx.gbl>...
> Does SQL choose the same execution plan on the production and test server?
> It's possible that the plans aren't the same...
> also... hashing algorithtms take amount of memory into consideration. So
> it's entirely possible that the prod server is making a mistake when it
> guesses how much memory will ultimately be available for it.
> You might also want to make sure statistics are up to date on prod. Out of
> date stats might make the optimizer come up with bad hashing (and other)
> decisions...
> --
> Brian Moran
>
> "chxxx" <chxxx@.dontemailme.com> wrote in message
> news:3FBB6FB8.C5FF3195@.dontemailme.com...
> > sql2000 sp3.
> >
> > strange problem here. i've got an extremely complex query written by a
> > developer. the query works and it comes back in a reasonable amount of
> > time. however, the query generates a "hash warning/hash recursion"
> > error when it runs. after reading bol, these errors don't really seem
> > to be very bad. i decided to investigate it further by restoring the
> > production db to a test server and try it there. guess what. no hash
> > warning errors on the test server.
> >
> > prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> > allocated for sql server).
> > test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> > allocated for sql server).
> > the query runs slower on the test server (as expected) even with little
> > to no traffic and no hash warnings.
> >
> > my next guess was that on the prod server, all of sql server's ram was
> > being used by other objects. i did a dropcleanbuffers and a
> > freeproccache. query still generates hash warnings.
> >
> > any ideas as to what would cause the hash warnings on prod server but
> > not test server?
> >
> > i can't reboot the prod server and i can't stop sqlserver on prod
> > server. are there any things i can try in sqlserver to free up
> > resources other than dropcleanbuffers and freeproccache?
> >
> >
> >sql

Hash join

Can any one explain me this "Option [hash join]"...
what that Option [hash join] will effect in this query.
select * from [group] inner join patientgroup on PA_PatientID = PG_PatientID
Option [hash join]
Thanks
Noor
Noor wrote:
> Can any one explain me this "Option [hash join]"...
> what that Option [hash join] will effect in this query.
> select * from [group] inner join patientgroup on PA_PatientID =
> PG_PatientID Option [hash join]
>
> Thanks
> Noor
It will force SQL Server to use a hash join on the tables instead of a
nested loop join or a merge join. Generally, you want to let SQL Server
make up its own mind about what the best join operation is. Essentially,
a hash join forces SQL Server to create hash values on both tables in
order to determine the matching values.
To see how it affects your query test it in Query Analyzer with and
without the hint.
See Understanding Hash Joins in the BOL.
David G.

Hash join

In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regards
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards

Hash join

In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regardssql

Hash join

In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards

has parallelism helped any of you ?

It seems to hurt more than help that we like to turn it off on all servers
by default. Has anyone seen advantages of a parallised query vs one that
doesnt go through one
Using SQL 2000Yes. It has helped. However, if a Query is processed with Parellelism, we
check
each of the Query and make sure that it does help indeed. (On a Case to Case
Basis)
In very rare cases, did we have to use OPTION (MAXDOP 1). But later we
realised that "Exec SPname WITH RECOMPILE" actually helped remove
Parellelism.
Gopi
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23nzTh5FUFHA.752@.TK2MSFTNGP10.phx.gbl...
> It seems to hurt more than help that we like to turn it off on all servers
> by default. Has anyone seen advantages of a parallised query vs one that
> doesnt go through one
> Using SQL 2000
>|||Hassan
I'd not change a deafult configiration of SQL Server instead I'd tune the
queries and see if it hurts perfomance by using parallelism try to opotimize
it perhaps by using MAXDOP(1) hint
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23nzTh5FUFHA.752@.TK2MSFTNGP10.phx.gbl...
> It seems to hurt more than help that we like to turn it off on all servers
> by default. Has anyone seen advantages of a parallised query vs one that
> doesnt go through one
> Using SQL 2000
>