Query works in QA, but NOT in SQL Server stored proc?!

Ok...below is a simple query that inserts some records into a temp
table then updates another table using the temp table. It works great
in Query Analyzer, but refuses to save in SQL Servers' stored procedure
area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
I'm banging my head against a wall here, please help! I've tried
placing single and double quotes around fvd_count to no avail...
CREATE PROCEDURE [Update_Counts]
AS
--counts the number of times that distinct doc/poe combo exists
SELECT
doc,
poe,
COUNT(equipment) AS fvd_count <<<<<--ERROR
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND
a.poe = b.poe
GOAre you creating the sp in EM?. Try creating the sp from SQL Query Analyzer.
AMB
"roy.anderson@.gmail.com" wrote:

> Ok...below is a simple query that inserts some records into a temp
> table then updates another table using the temp table. It works great
> in Query Analyzer, but refuses to save in SQL Servers' stored procedure
> area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
> I'm banging my head against a wall here, please help! I've tried
> placing single and double quotes around fvd_count to no avail...
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exists
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count <<<<<--ERROR
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = b.fvd_count
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>|||Did you cut/paste the error message? If so, then you have a typo somewhere
in your code because the error message references field 'fvd_cnt', while the
sp that you show us names it 'fvd_count'.
If that is just a typo in your post...then...another thought is to use
[square brackets] around the field name. It shouldn't be necessary in this
case, but worth a try.
<roy.anderson@.gmail.com> wrote in message
news:1107276839.749893.138630@.c13g2000cwb.googlegroups.com...
> Ok...below is a simple query that inserts some records into a temp
> table then updates another table using the temp table. It works great
> in Query Analyzer, but refuses to save in SQL Servers' stored procedure
> area. The error it gives is "Error 207: Invalid column name 'fvd_cnt'"
> I'm banging my head against a wall here, please help! I've tried
> placing single and double quotes around fvd_count to no avail...
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exists
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count <<<<<--ERROR
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = b.fvd_count
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>|||I dont think this is the full sproc, i have just tested this in QA & EM
without any error
USE Northwind
GO
CREATE TABLE firstvd (doc varchar(10), poe varchar(10), equipment varchar(10
))
CREATE TABLE lla (doc varchar(10), poe varchar(10), fvd_count int)
GO
CREATE PROCEDURE [Update_Counts]
AS
SELECT doc, poe, COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND a.poe = b.poe
GO
EXEC Update_Counts
GO
DROP TABLE firstvd
DROP TABLE lla
DROP PROCEDURE [Update_Counts]
GO
Instead of using a temp table why not do it in the query
UPDATE a
SET a.fvd_count = (SELECT COUNT(equipment)
FROM firstvd b
WHERE b.doc = a.doc AND b.poe = a.doc)
FROM lla a, firstvd
Andy
"CPK" wrote:

> Did you cut/paste the error message? If so, then you have a typo somewher
e
> in your code because the error message references field 'fvd_cnt', while t
he
> sp that you show us names it 'fvd_count'.
> If that is just a typo in your post...then...another thought is to use
> [square brackets] around the field name. It shouldn't be necessary in thi
s
> case, but worth a try.
> <roy.anderson@.gmail.com> wrote in message
> news:1107276839.749893.138630@.c13g2000cwb.googlegroups.com...
>
>|||Hey all, thanks for the input. I tried using QA to load it in and it
works like that... the first time... but when I call it after that it
ends up producing nothing (called from my asp.net page) or erroring out
in QA or EM.
Tried the square brackets, no go (and yes, it was a typo on my part).
I'll try your query idea next Andy, I don't know what to say regarding
your experiment except to say it just doesn't work in my EM. I did
clarify where the error occurs though. It happens at b.fvd_count below:
CREATE PROCEDURE [Update_Counts]
AS
--counts the number of times that distinct doc/poe combo exi=ADsts
SELECT
doc,
poe,
COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count =3D >>>>>>> b.fvd_count <<<<<<--ERROR HERE
FROM #FVD_Temp b, lla a
WHERE a.doc =3D b.doc AND
a=2Epoe =3D b.poe
GO=20
****************************************
***********************|||Roy
Just try this in QA first, forget about EM its a GUI and should be just
treated that way, i spend 90% of my time using QA
CREATE PROCEDURE [Update_Counts]
AS
SELECT doc, poe, COUNT(equipment) AS fvd_count
INTO #FVD_Temp
FROM firstvd
GROUP BY doc, POE
UPDATE a
SET a.fvd_count = b.fvd_count
FROM #FVD_Temp b, lla a
WHERE a.doc = b.doc AND a.poe = b.poe
GO
Oh just one more thing does this field "fvd_count" exist in table lla as we
know it exists in the temp table #FVD_Temp
Andy
"roy.anderson@.gmail.com" wrote:

> Hey all, thanks for the input. I tried using QA to load it in and it
> works like that... the first time... but when I call it after that it
> ends up producing nothing (called from my asp.net page) or erroring out
> in QA or EM.
> Tried the square brackets, no go (and yes, it was a typo on my part).
> I'll try your query idea next Andy, I don't know what to say regarding
> your experiment except to say it just doesn't work in my EM. I did
> clarify where the error occurs though. It happens at b.fvd_count below:
>
> CREATE PROCEDURE [Update_Counts]
> AS
> --counts the number of times that distinct doc/poe combo exi_sts
> SELECT
> doc,
> poe,
> COUNT(equipment) AS fvd_count
> INTO #FVD_Temp
> FROM firstvd
> GROUP BY doc, POE
> UPDATE a
> SET a.fvd_count = >>>>>>> b.fvd_count <<<<<<--ERROR HERE
> FROM #FVD_Temp b, lla a
> WHERE a.doc = b.doc AND
> a.poe = b.poe
> GO
>
> ****************************************
***********************
>|||Yes, the create proc above works in QA and yes, fvd_count exists in
lla...|||Wild guess: Try adding SET NOCOUNT ON in the beginning of your proc code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<roy.anderson@.gmail.com> wrote in message
news:1107286986.218999.296680@.f14g2000cwb.googlegroups.com...
> Yes, the create proc above works in QA and yes, fvd_count exists in
> lla...
>|||Roy
have you sorted this out.
If the CREATE PROC statement returned "The command completed successfully"
then the procedure has been created. But does it execute & do what you
expect'
Andy
"roy.anderson@.gmail.com" wrote:

> Yes, the create proc above works in QA and yes, fvd_count exists in
> lla...
>|||Yes, the query proc works! Thanks much. Additonally, the suggestion to
create it in QA (the original stored proc) was accurate. The SP works
when called from my asp.net page. Even though both ways work (outside
of EM), I'm sticking with the query method because it seems less cpu
intensive.
The weird thing is still that the original SP won't save and the syntax
check fails when I open it (the original SP) in EM. But no matter, at
least I have a working now process now. Thanks everyone for all the
great suggestions!

Query work in sql 200 fails in sql 2005

Hello all,
The query below, has been succesfully working in sql 2000 for months.
While I recongnize that the isnumeric attribute is reference twice
(this has since been corrected).
My concern is why did this generate an error in sql 2005 and not sql
2000.
Error = (Duplicate column names are not allowed in result sets obtained
through OPENQUERY and OPENROWSET.)
I am concerned that I've missed a server setting.
If this is just a case where it should of failed in 2000 I would feel
much better.
Any insight into this would be greatly appreciated.
Thanks,
Henry Lovera
--
insert into @.deal_properties (deal_property_id,
parent_deal_property_id, is_numeric, property_name, property_format,
display_order, string_value, numeric_value)
select
deal_property_id
,parent_deal_property_id
,is_numeric
,property_name
,property_format
,display_order
,string_value = case when is_numeric = 0 then value
end
,numeric_value = case when is_numeric = 1 then convert(float,
value) end
from
openxml(@.xml_doc, '//deal_property', 1)
with( deal_property_id int
,parent_deal_property_id int
,is_numeric int
,property_name varchar(32)
,property_format varchar(16)
,display_order int
,is_numeric bit
,value varchar(100))Henry
I was not able to test it because the script throws lots of errors.
Please post proper DDL+ sample data.
<hanklvr@.yahoo.com> wrote in message
news:1143684314.846726.121030@.t31g2000cwb.googlegroups.com...
> Hello all,
> The query below, has been succesfully working in sql 2000 for months.
> While I recongnize that the isnumeric attribute is reference twice
> (this has since been corrected).
> My concern is why did this generate an error in sql 2005 and not sql
> 2000.
> Error = (Duplicate column names are not allowed in result sets obtained
> through OPENQUERY and OPENROWSET.)
> I am concerned that I've missed a server setting.
> If this is just a case where it should of failed in 2000 I would feel
> much better.
> Any insight into this would be greatly appreciated.
> Thanks,
> Henry Lovera
> --
> insert into @.deal_properties (deal_property_id,
> parent_deal_property_id, is_numeric, property_name, property_format,
> display_order, string_value, numeric_value)
> select
> deal_property_id
> ,parent_deal_property_id
> ,is_numeric
> ,property_name
> ,property_format
> ,display_order
> ,string_value = case when is_numeric = 0 then value
> end
> ,numeric_value = case when is_numeric = 1 then convert(float,
> value) end
> from
> openxml(@.xml_doc, '//deal_property', 1)
> with( deal_property_id int
> ,parent_deal_property_id int
> ,is_numeric int
> ,property_name varchar(32)
> ,property_format varchar(16)
> ,display_order int
> ,is_numeric bit
> ,value varchar(100))
>

Query Without Cursor

I gave read a lot about cursors being inefficent and i would like to re-write
the below query without using them, but not sure how i would do this, sure
there will be a way to do this, but not sure how, can anyone help:
The query baisically sets the the linenum on a hoiday itinerary, based on
date:
declare @.cnt integer
set @.cnt = 2
declare cursorItin cursor fast_forward
FOR SELECT [id]
FROM dbo.Itinerary
WHERE bkref=@.bkref AND linenum<>1
Order By itinDateTime
open cursorItin
fetch next from cursorItin into @.id
while @.@.fetch_status=0
begin
UPDATE dbo.Itinerary
SET linenum=@.cnt
WHERE id=@.id
set @.cnt = @.cnt + 1
fetch next from cursorItin into @.id
end
close cursorItin
deallocate cursorItin
On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
> I gave read a lot about cursors being inefficent and i would like to re-write
> the below query without using them, but not sure how i would do this, sure
> there will be a way to do this, but not sure how, can anyone help:
> The query baisically sets the the linenum on a hoiday itinerary, based on
> date:
> ----
> declare @.cnt integer
> set @.cnt = 2
> declare cursorItin cursor fast_forward
> FOR SELECT [id]
> FROM dbo.Itinerary
> WHERE bkref=@.bkref AND linenum<>1
> Order By itinDateTime
> open cursorItin
> fetch next from cursorItin into @.id
> while @.@.fetch_status=0
> begin
> UPDATE dbo.Itinerary
> SET linenum=@.cnt
> WHERE id=@.id
> set @.cnt = @.cnt + 1
> fetch next from cursorItin into @.id
> end
> close cursorItin
> deallocate cursorItin
> ---
Try:
WITH t AS
(SELECT id, linenum,
ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
AS new_linenum
FROM Itinerary)
UPDATE t SET linenum = new_linenum;
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/ms130214(en-US,SQL.90).aspx
|||Apologies, should have mentioned before am running on SQL 2000, sp4.
I am getting error:
"'ROW_NUMBER' is not a recognized function name"
Is ROW_NUMBER a 2005 only function?
"David Portas" wrote:

> On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
> Try:
> WITH t AS
> (SELECT id, linenum,
> ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
> AS new_linenum
> FROM Itinerary)
> UPDATE t SET linenum = new_linenum;
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>
|||Yes, ROW_NUMBER doesn't seems to exist in SQL2000
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Billy wrote:[vbcol=seagreen]
> Apologies, should have mentioned before am running on SQL 2000, sp4.
> I am getting error:
> "'ROW_NUMBER' is not a recognized function name"
> Is ROW_NUMBER a 2005 only function?
> "David Portas" wrote:
|||On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> Apologies, should have mentioned before am running on SQL 2000, sp4.
> I am getting error:
> "'ROW_NUMBER' is not a recognized function name"
> Is ROW_NUMBER a 2005 only function?
>
ROW_NUMBER() was introduced in 2005.
You didn't post any CREATE TABLE statement so I'll have to make a
guess at what the key of your table is. I'll assume that the
combination of (bkref, itinDateTime, ID) is unique:
UPDATE Itinerary SET linenum =
(SELECT COUNT(*)
FROM Itinerary AS I
WHERE bkref = Itinerary.bkref
AND
(itinDateTime < Itinerary.itinDateTime
OR
(id <= Itinerary.id
AND itinDateTime = Itinerary.itinDateTime)
)
);
If you need more help, please post a CREATE TABLE statement (including
constraints), some INSERT statements consisting of some sample data
and then show what end result you want from that data.
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/ms130214(en-US,SQL.90).aspx
|||that works great, thanks david, i just need to understand it now! (scratches
head)
I guess the subquery almost works as a loop returning the number of rows
whose date is less than the previous row?
"David Portas" wrote:

> On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> ROW_NUMBER() was introduced in 2005.
> You didn't post any CREATE TABLE statement so I'll have to make a
> guess at what the key of your table is. I'll assume that the
> combination of (bkref, itinDateTime, ID) is unique:
> UPDATE Itinerary SET linenum =
> (SELECT COUNT(*)
> FROM Itinerary AS I
> WHERE bkref = Itinerary.bkref
> AND
> (itinDateTime < Itinerary.itinDateTime
> OR
> (id <= Itinerary.id
> AND itinDateTime = Itinerary.itinDateTime)
> )
> );
> If you need more help, please post a CREATE TABLE statement (including
> constraints), some INSERT statements consisting of some sample data
> and then show what end result you want from that data.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>
|||I must say that this appears to definitely be a case where the CTE produces
code that is much cleaner and easier to understand. I haven't felt that is
the case much (yet) since I haven't worked with CTEs prior to SQL 2005.
Perhaps they are starting to rub off on me!! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1181818331.032779.128580@.g37g2000prf.googlegr oups.com...
> On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> ROW_NUMBER() was introduced in 2005.
> You didn't post any CREATE TABLE statement so I'll have to make a
> guess at what the key of your table is. I'll assume that the
> combination of (bkref, itinDateTime, ID) is unique:
> UPDATE Itinerary SET linenum =
> (SELECT COUNT(*)
> FROM Itinerary AS I
> WHERE bkref = Itinerary.bkref
> AND
> (itinDateTime < Itinerary.itinDateTime
> OR
> (id <= Itinerary.id
> AND itinDateTime = Itinerary.itinDateTime)
> )
> );
> If you need more help, please post a CREATE TABLE statement (including
> constraints), some INSERT statements consisting of some sample data
> and then show what end result you want from that data.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>
sql

Query Without Cursor

I gave read a lot about cursors being inefficent and i would like to re-write
the below query without using them, but not sure how i would do this, sure
there will be a way to do this, but not sure how, can anyone help:
The query baisically sets the the linenum on a hoiday itinerary, based on
date:
----
declare @.cnt integer
set @.cnt = 2
declare cursorItin cursor fast_forward
FOR SELECT [id]
FROM dbo.Itinerary
WHERE bkref=@.bkref AND linenum<>1
Order By itinDateTime
open cursorItin
fetch next from cursorItin into @.id
while @.@.fetch_status=0
begin
UPDATE dbo.Itinerary
SET linenum=@.cnt
WHERE id=@.id
set @.cnt = @.cnt + 1
fetch next from cursorItin into @.id
end
close cursorItin
deallocate cursorItin
---On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
> I gave read a lot about cursors being inefficent and i would like to re-write
> the below query without using them, but not sure how i would do this, sure
> there will be a way to do this, but not sure how, can anyone help:
> The query baisically sets the the linenum on a hoiday itinerary, based on
> date:
> ----
> declare @.cnt integer
> set @.cnt = 2
> declare cursorItin cursor fast_forward
> FOR SELECT [id]
> FROM dbo.Itinerary
> WHERE bkref=@.bkref AND linenum<>1
> Order By itinDateTime
> open cursorItin
> fetch next from cursorItin into @.id
> while @.@.fetch_status=0
> begin
> UPDATE dbo.Itinerary
> SET linenum=@.cnt
> WHERE id=@.id
> set @.cnt = @.cnt + 1
> fetch next from cursorItin into @.id
> end
> close cursorItin
> deallocate cursorItin
> ---
Try:
WITH t AS
(SELECT id, linenum,
ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
AS new_linenum
FROM Itinerary)
UPDATE t SET linenum = new_linenum;
--
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/ms130214(en-US,SQL.90).aspx
--|||Apologies, should have mentioned before am running on SQL 2000, sp4.
I am getting error:
"'ROW_NUMBER' is not a recognized function name"
Is ROW_NUMBER a 2005 only function?
"David Portas" wrote:
> On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
> > I gave read a lot about cursors being inefficent and i would like to re-write
> > the below query without using them, but not sure how i would do this, sure
> > there will be a way to do this, but not sure how, can anyone help:
> >
> > The query baisically sets the the linenum on a hoiday itinerary, based on
> > date:
> >
> > ----
> > declare @.cnt integer
> > set @.cnt = 2
> >
> > declare cursorItin cursor fast_forward
> > FOR SELECT [id]
> > FROM dbo.Itinerary
> > WHERE bkref=@.bkref AND linenum<>1
> > Order By itinDateTime
> >
> > open cursorItin
> > fetch next from cursorItin into @.id
> > while @.@.fetch_status=0
> > begin
> > UPDATE dbo.Itinerary
> > SET linenum=@.cnt
> > WHERE id=@.id
> > set @.cnt = @.cnt + 1
> > fetch next from cursorItin into @.id
> > end
> > close cursorItin
> > deallocate cursorItin
> > ---
> Try:
> WITH t AS
> (SELECT id, linenum,
> ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
> AS new_linenum
> FROM Itinerary)
> UPDATE t SET linenum = new_linenum;
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> Apologies, should have mentioned before am running on SQL 2000, sp4.
> I am getting error:
> "'ROW_NUMBER' is not a recognized function name"
> Is ROW_NUMBER a 2005 only function?
>
ROW_NUMBER() was introduced in 2005.
You didn't post any CREATE TABLE statement so I'll have to make a
guess at what the key of your table is. I'll assume that the
combination of (bkref, itinDateTime, ID) is unique:
UPDATE Itinerary SET linenum =(SELECT COUNT(*)
FROM Itinerary AS I
WHERE bkref = Itinerary.bkref
AND
(itinDateTime < Itinerary.itinDateTime
OR
(id <= Itinerary.id
AND itinDateTime = Itinerary.itinDateTime)
)
);
If you need more help, please post a CREATE TABLE statement (including
constraints), some INSERT statements consisting of some sample data
and then show what end result you want from that data.
--
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/ms130214(en-US,SQL.90).aspx
--|||Yes, ROW_NUMBER doesn't seems to exist in SQL2000
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Billy wrote:
> Apologies, should have mentioned before am running on SQL 2000, sp4.
> I am getting error:
> "'ROW_NUMBER' is not a recognized function name"
> Is ROW_NUMBER a 2005 only function?
> "David Portas" wrote:
>> On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
>> I gave read a lot about cursors being inefficent and i would like to re-write
>> the below query without using them, but not sure how i would do this, sure
>> there will be a way to do this, but not sure how, can anyone help:
>> The query baisically sets the the linenum on a hoiday itinerary, based on
>> date:
>> ----
>> declare @.cnt integer
>> set @.cnt = 2
>> declare cursorItin cursor fast_forward
>> FOR SELECT [id]
>> FROM dbo.Itinerary
>> WHERE bkref=@.bkref AND linenum<>1
>> Order By itinDateTime
>> open cursorItin
>> fetch next from cursorItin into @.id
>> while @.@.fetch_status=0
>> begin
>> UPDATE dbo.Itinerary
>> SET linenum=@.cnt
>> WHERE id=@.id
>> set @.cnt = @.cnt + 1
>> fetch next from cursorItin into @.id
>> end
>> close cursorItin
>> deallocate cursorItin
>> ---
>> Try:
>> WITH t AS
>> (SELECT id, linenum,
>> ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
>> AS new_linenum
>> FROM Itinerary)
>> UPDATE t SET linenum = new_linenum;
>> --
>> 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/ms130214(en-US,SQL.90).aspx
>> --
>>|||that works great, thanks david, i just need to understand it now! (scratches
head)
I guess the subquery almost works as a loop returning the number of rows
whose date is less than the previous row?
"David Portas" wrote:
> On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> > Apologies, should have mentioned before am running on SQL 2000, sp4.
> >
> > I am getting error:
> > "'ROW_NUMBER' is not a recognized function name"
> >
> > Is ROW_NUMBER a 2005 only function?
> >
> >
> ROW_NUMBER() was introduced in 2005.
> You didn't post any CREATE TABLE statement so I'll have to make a
> guess at what the key of your table is. I'll assume that the
> combination of (bkref, itinDateTime, ID) is unique:
> UPDATE Itinerary SET linenum => (SELECT COUNT(*)
> FROM Itinerary AS I
> WHERE bkref = Itinerary.bkref
> AND
> (itinDateTime < Itinerary.itinDateTime
> OR
> (id <= Itinerary.id
> AND itinDateTime = Itinerary.itinDateTime)
> )
> );
> If you need more help, please post a CREATE TABLE statement (including
> constraints), some INSERT statements consisting of some sample data
> and then show what end result you want from that data.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||I must say that this appears to definitely be a case where the CTE produces
code that is much cleaner and easier to understand. I haven't felt that is
the case much (yet) since I haven't worked with CTEs prior to SQL 2005.
Perhaps they are starting to rub off on me!! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1181818331.032779.128580@.g37g2000prf.googlegroups.com...
> On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
>> Apologies, should have mentioned before am running on SQL 2000, sp4.
>> I am getting error:
>> "'ROW_NUMBER' is not a recognized function name"
>> Is ROW_NUMBER a 2005 only function?
>>
> ROW_NUMBER() was introduced in 2005.
> You didn't post any CREATE TABLE statement so I'll have to make a
> guess at what the key of your table is. I'll assume that the
> combination of (bkref, itinDateTime, ID) is unique:
> UPDATE Itinerary SET linenum => (SELECT COUNT(*)
> FROM Itinerary AS I
> WHERE bkref = Itinerary.bkref
> AND
> (itinDateTime < Itinerary.itinDateTime
> OR
> (id <= Itinerary.id
> AND itinDateTime = Itinerary.itinDateTime)
> )
> );
> If you need more help, please post a CREATE TABLE statement (including
> constraints), some INSERT statements consisting of some sample data
> and then show what end result you want from that data.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>

Query Without Cursor

I gave read a lot about cursors being inefficent and i would like to re-writ
e
the below query without using them, but not sure how i would do this, sure
there will be a way to do this, but not sure how, can anyone help:
The query baisically sets the the linenum on a hoiday itinerary, based on
date:
----
declare @.cnt integer
set @.cnt = 2
declare cursorItin cursor fast_forward
FOR SELECT [id]
FROM dbo.Itinerary
WHERE bkref=@.bkref AND linenum<>1
Order By itinDateTime
open cursorItin
fetch next from cursorItin into @.id
while @.@.fetch_status=0
begin
UPDATE dbo.Itinerary
SET linenum=@.cnt
WHERE id=@.id
set @.cnt = @.cnt + 1
fetch next from cursorItin into @.id
end
close cursorItin
deallocate cursorItin
---On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
> I gave read a lot about cursors being inefficent and i would like to re-wr
ite
> the below query without using them, but not sure how i would do this, sure
> there will be a way to do this, but not sure how, can anyone help:
> The query baisically sets the the linenum on a hoiday itinerary, based on
> date:
> ----
> declare @.cnt integer
> set @.cnt = 2
> declare cursorItin cursor fast_forward
> FOR SELECT [id]
> FROM dbo.Itinerary
> WHERE bkref=@.bkref AND linenum<>1
> Order By itinDateTime
> open cursorItin
> fetch next from cursorItin into @.id
> while @.@.fetch_status=0
> begin
> UPDATE dbo.Itinerary
> SET linenum=@.cnt
> WHERE id=@.id
> set @.cnt = @.cnt + 1
> fetch next from cursorItin into @.id
> end
> close cursorItin
> deallocate cursorItin
> ---
Try:
WITH t AS
(SELECT id, linenum,
ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
AS new_linenum
FROM Itinerary)
UPDATE t SET linenum = new_linenum;
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/ms130214(en-US,SQL.90).aspx
--|||Apologies, should have mentioned before am running on SQL 2000, sp4.
I am getting error:
"'ROW_NUMBER' is not a recognized function name"
Is ROW_NUMBER a 2005 only function?
"David Portas" wrote:

> On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
> Try:
> WITH t AS
> (SELECT id, linenum,
> ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
> AS new_linenum
> FROM Itinerary)
> UPDATE t SET linenum = new_linenum;
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||Yes, ROW_NUMBER doesn't seems to exist in SQL2000
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Billy wrote:[vbcol=seagreen]
> Apologies, should have mentioned before am running on SQL 2000, sp4.
> I am getting error:
> "'ROW_NUMBER' is not a recognized function name"
> Is ROW_NUMBER a 2005 only function?
> "David Portas" wrote:
>|||On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> Apologies, should have mentioned before am running on SQL 2000, sp4.
> I am getting error:
> "'ROW_NUMBER' is not a recognized function name"
> Is ROW_NUMBER a 2005 only function?
>
ROW_NUMBER() was introduced in 2005.
You didn't post any CREATE TABLE statement so I'll have to make a
guess at what the key of your table is. I'll assume that the
combination of (bkref, itinDateTime, ID) is unique:
UPDATE Itinerary SET linenum =
(SELECT COUNT(*)
FROM Itinerary AS I
WHERE bkref = Itinerary.bkref
AND
(itinDateTime < Itinerary.itinDateTime
OR
(id <= Itinerary.id
AND itinDateTime = Itinerary.itinDateTime)
)
);
If you need more help, please post a CREATE TABLE statement (including
constraints), some INSERT statements consisting of some sample data
and then show what end result you want from that data.
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/ms130214(en-US,SQL.90).aspx
--|||that works great, thanks david, i just need to understand it now! (scratches
head)
I guess the subquery almost works as a loop returning the number of rows
whose date is less than the previous row?
"David Portas" wrote:

> On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> ROW_NUMBER() was introduced in 2005.
> You didn't post any CREATE TABLE statement so I'll have to make a
> guess at what the key of your table is. I'll assume that the
> combination of (bkref, itinDateTime, ID) is unique:
> UPDATE Itinerary SET linenum =
> (SELECT COUNT(*)
> FROM Itinerary AS I
> WHERE bkref = Itinerary.bkref
> AND
> (itinDateTime < Itinerary.itinDateTime
> OR
> (id <= Itinerary.id
> AND itinDateTime = Itinerary.itinDateTime)
> )
> );
> If you need more help, please post a CREATE TABLE statement (including
> constraints), some INSERT statements consisting of some sample data
> and then show what end result you want from that data.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||I must say that this appears to definitely be a case where the CTE produces
code that is much cleaner and easier to understand. I haven't felt that is
the case much (yet) since I haven't worked with CTEs prior to SQL 2005.
Perhaps they are starting to rub off on me!! :-)
TheSQLGuru
President
Indicium Resources, Inc.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1181818331.032779.128580@.g37g2000prf.googlegroups.com...
> On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> ROW_NUMBER() was introduced in 2005.
> You didn't post any CREATE TABLE statement so I'll have to make a
> guess at what the key of your table is. I'll assume that the
> combination of (bkref, itinDateTime, ID) is unique:
> UPDATE Itinerary SET linenum =
> (SELECT COUNT(*)
> FROM Itinerary AS I
> WHERE bkref = Itinerary.bkref
> AND
> (itinDateTime < Itinerary.itinDateTime
> OR
> (id <= Itinerary.id
> AND itinDateTime = Itinerary.itinDateTime)
> )
> );
> If you need more help, please post a CREATE TABLE statement (including
> constraints), some INSERT statements consisting of some sample data
> and then show what end result you want from that data.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>

Query with a left outer join is not working as expected

The query below is suppose to join customer information with sales and vendo
r
information. An outer join and a union is used to insure I get all customer
s
and vendors within the selection criteria even though either entity may not
have sales. The query is:
SELECT
cs.customer_ident,
ss.Total_Cases,
ss.Total_Pounds,
ss.Total_Sales,
ss.Total_Actual,
ss.Total_Rep,
ss.Total_Profit,
ss.Total_Items,
cs.customer_num,
cs.customer_name,
cs.ship_to_address1,
cs.ship_to_county,
cs.ship_to_state,
cs.customer_phone1,
op.opco_desc,
ss.opco_vendor_num,
ss.opco_vendor_desc,
rp.salesrep_num,
rp.salesrep_name
FROM
dim_customer cs
LEFT OUTER JOIN
(SELECT
sh.customer_ident,
vn.opco_vendor_num,
vn.opco_vendor_desc,
vn.vendor_ident,
sum(sd.extended_cases) AS Total_Cases,
sum(sd.extended_sales) AS Total_Sales,
sum(sd.extended_cost) AS Total_Actual,
sum(sd.extended_sales_rep_cost) AS Total_Rep,
sum(sd.extended_pounds) AS Total_Pounds,
Count(DISTINCT sd.item_ident) As Total_Items,
sum(sd.extended_sales - sd.extended_Cost) As Total_Profit
FROM
fact_sales_header sh
INNER JOIN
fact_sales_detail sd
ON
sh.header_ident = sd.header_ident
INNER JOIN
dim_item it
ON
sd.item_ident = it.item_ident
INNER JOIN
dim_time tm
ON
sh.time_ident = tm.time_ident
INNER JOIN
dim_vendor vn
ON
it.vendor_ident = vn.vendor_ident
INNER JOIN
dim_customer cs1
ON
cs1.customer_ident = sh.customer_ident
INNER JOIN
dim_salesRep rp1
ON
cs1.sales_rep_ident = rp1.salesRep_ident
WHERE
(((tm.gl_year = 2004 AND tm.gl_period >= 4) OR tm.gl_year > 2004) and
((tm.gl_year =2005 AND tm.gl_period <=3) OR tm.gl_year < 2005)) AND
sh.opco_num = 125
and it.subCategory_num in (3932)
and cs1.abc_rating = 'A'
GROUP BY
vn.vendor_ident,
vn.opco_vendor_num,
sh.customer_ident,
vn.opco_vendor_desc
) ss
ON
cs.customer_ident = ss.customer_ident
INNER JOIN
dim_opco op
ON
cs.opco_num = op.opco_num
INNER JOIN
dim_salesRep rp
ON
cs.sales_rep_ident = rp.salesRep_ident
WHERE
cs.opco_num = 125 AND NOT exists
(SELECT
sh1.customer_ident,
sum(sd1.extended_cases) AS Cases_Test
FROM
fact_sales_header sh1
INNER JOIN
fact_sales_detail sd1
ON
sh1.header_ident = sd1.header_ident
INNER JOIN
dim_item it1
ON
sd1.item_ident = it1.item_ident
INNER JOIN
dim_time tm1
ON
sh1.time_ident = tm1.time_ident WHERE
sh1.customer_ident = cs.customer_ident
AND (((tm1.gl_year = 2004 AND tm1.gl_period >= 4) OR tm1.gl_year > 2004)
and ((tm1.gl_year =2005 AND tm1.gl_period <=3) OR tm1.gl_year < 2005)) AND
sh1.opco_num = 125
and it1.subCategory_num in (3932)
GROUP BY
sh1.customer_ident
HAVING
sum(sd1.extended_cases) > 10000)
and cs.abc_rating = 'A'
UNION ALL
SELECT Distinct
NULL as customer_ident,
NULL as Total_Cases,
NULL as Total_Pounds,
NULL as Total_Sales,
NULL as Total_Actual,
NULL as Total_Rep,
NULL as Total_Profit,
NULL as Total_Items,
NULL as customer_num,
NULL as customer_name,
NULL as ship_to_address1,
NULL as ship_to_county,
NULL as ship_to_state,
NULL as customer_phone1,
op.opco_desc,
vn.opco_vendor_num,
vn.opco_vendor_desc,
NULL as salesrep_num,
NULL as salesrep_name
FROM
dim_item it
INNER JOIN
dim_vendor vn
ON
it.vendor_ident = vn.vendor_ident
INNER JOIN
dim_opco op
ON
it.opco_num = op.opco_num
WHERE
it.opco_num = 125
and it.subCategory_num in (3932)
ORDER BY
cs.customer_ident,
opco_vendor_desc,
salesRep_name,
salesRep_num,
customer_num,
customer_name
Even though the result set from the left outer join query produces rows with
customer idents that match customer idents from the main select statement,
the sales data is not joined in (It looks like the query from the LEFT OUTER
JOIN returned no matching rows).
The interesting thing is if I comment out or remove the UNION ALL portion of
the query, the LEFT OUTER JOIN seems to work as I would expect.
I also change the LEFT OUTER JOIN to a FULL OUTER JOIN but did not make any
difference.
Does anyone have any idea what is happening here? Below are the DDL
statements for the tables involved:
CREATE TABLE [fact_sales_header] (
[header_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[time_ident] [int] NOT NULL ,
[customer_ident] [int] NOT NULL ,
[order_source] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[credit_invoice_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[invoice_count] [int] NOT NULL ,
[invoice_line_count] [int] NOT NULL ,
[routed_orders] [int] NOT NULL ,
[published_ident] [int] NOT NULL ,
PRIMARY KEY CLUSTERED
(
[header_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_fact_sales_header_01] UNIQUE NONCLUSTERED
(
[opco_num],
[time_ident],
[customer_ident],
[order_source],
[credit_invoice_flag]
) ON [PRIMARY] ,
FOREIGN KEY
(
[customer_ident]
) REFERENCES [dim_customer] (
[customer_ident]
),
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
),
FOREIGN KEY
(
[published_ident]
) REFERENCES [meta_published] (
[published_ident]
),
FOREIGN KEY
(
[time_ident]
) REFERENCES [dim_time] (
[time_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [fact_sales_detail] (
[detail_ident] [int] IDENTITY (1, 1) NOT NULL ,
[header_ident] [int] NOT NULL ,
[item_ident] [int] NOT NULL ,
[original_item_ident] [int] NULL ,
[special_handling_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[credit_ident] [int] NULL ,
[extended_sales] [numeric](15, 2) NULL ,
[extended_cost] [numeric](15, 2) NULL ,
[extended_sales_rep_cost] [numeric](15, 2) NULL ,
[extended_cases] [numeric](19, 5) NULL ,
[extended_pounds] [numeric](15, 2) NULL ,
[market_cost] [numeric](15, 4) NULL ,
[invoice_line_count] [int] NOT NULL ,
CONSTRAINT [pk_fact_sales_detail] PRIMARY KEY NONCLUSTERED
(
[detail_ident]
) ON [PRIMARY] ,
FOREIGN KEY
(
[credit_ident]
) REFERENCES [dim_creditCode] (
[credit_ident]
),
FOREIGN KEY
(
[header_ident]
) REFERENCES [fact_sales_header] (
[header_ident]
),
FOREIGN KEY
(
[item_ident]
) REFERENCES [dim_item] (
[item_ident]
),
FOREIGN KEY
(
[original_item_ident]
) REFERENCES [dim_item] (
[item_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_item] (
[item_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[opco_item_num] [int] NOT NULL ,
[item_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__dim_item__item_d__15A53433] DEFAULT ('Unknown'),
[item_desc2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_item__item_d__1699586C] DEFAULT ('Unknown'),
[category_num] [numeric](4, 0) NOT NULL CONSTRAINT
[DF__dim_item__catego__178D7CA5] DEFAULT (0),
[category_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_item__catego__1881A0DE] DEFAULT ('Unknown'),
[subCategory_num] [numeric](4, 0) NOT NULL CONSTRAINT
[DF__dim_item__subCat__1975C517] DEFAULT (0),
[subCategory_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_item__subCat__1A69E950] DEFAULT ('Unknown'),
[brand_num] [numeric](5, 0) NOT NULL CONSTRAINT
[DF__dim_item__brand___1B5E0D89] DEFAULT (0),
[brand_key_word] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_item__brand___1C5231C2] DEFAULT ('Unknown'),
[brand_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_item__brand___1D4655FB] DEFAULT ('Unknown'),
[brand_type_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_item__brand___1E3A7A34] DEFAULT ('Unknown'),
[brand_type_num] [numeric](2, 0) NOT NULL CONSTRAINT
[DF__dim_item__brand___1F2E9E6D] DEFAULT (0),
[vendor_ident] [int] NULL CONSTRAINT [DF__dim_item__vendor__2116E6DF]
DEFAULT (0),
[pack] [numeric](4, 0) NULL ,
[size] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[common_item_num] [numeric](6, 0) NOT NULL CONSTRAINT
[DF__dim_item__common__220B0B18] DEFAULT (0),
[non_inv_item_code_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF__dim_item__non_in__22FF2F51] DEFAULT (''),
[common_item_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__dim_item__common__3AD6B8E2] DEFAULT ('Unknown'),
PRIMARY KEY CLUSTERED
(
[item_ident]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
),
FOREIGN KEY
(
[vendor_ident]
) REFERENCES [dim_vendor] (
[vendor_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_time] (
[time_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NULL ,
[gl_w] [int] NOT NULL ,
[gl_period] [int] NOT NULL ,
[gl_quarter] [int] NOT NULL ,
[gl_year] [int] NOT NULL ,
[begin_w_date] [datetime] NULL ,
PRIMARY KEY CLUSTERED
(
[time_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_dim_time_01] UNIQUE NONCLUSTERED
(
[opco_num],
[gl_w],
[gl_year]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_vendor] (
[vendor_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[opco_vendor_num] [int] NOT NULL ,
[opco_vendor_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_vendo__opco___11D4A34F] DEFAULT ('Unknown'),
PRIMARY KEY CLUSTERED
(
[vendor_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_dim_vendor_01] UNIQUE NONCLUSTERED
(
[opco_num],
[opco_vendor_num]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_customer] (
[customer_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL CONSTRAINT [DF__dim_custo__opco___73501C2F]
DEFAULT (0),
[customer_num] [int] NOT NULL ,
[customer_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__custo__74444068] DEFAULT ('Unknown'),
[sales_rep_ident] [int] NOT NULL CONSTRAINT
[DF__dim_custo__sales__762C88DA] DEFAULT (0),
[abc_rating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__abc_r__7720AD13] DEFAULT ('0'),
[type_num] [int] NOT NULL CONSTRAINT [DF__dim_custo__type___7814D14C]
DEFAULT (0),
[type_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__type___7908F585] DEFAULT ('Unknown'),
[major_type_num] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__major__79FD19BE] DEFAULT (0),
[major_type_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__major__7AF13DF7] DEFAULT ('Unknown'),
[chain_code] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__chain__7BE56230] DEFAULT ('INDEP'),
[chain_code_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__chain__7CD98669] DEFAULT ('Not a Chain'),
[chain_code_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__chain__7DCDAAA2] DEFAULT ('U'),
[ship_to_county] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__ship___7EC1CEDB] DEFAULT ('Unknown'),
[ship_to_state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__ship___7FB5F314] DEFAULT ('Un'),
[ship_to_address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__ship___00AA174D] DEFAULT ('Unknown'),
[customer_phone1] [numeric](10, 0) NOT NULL CONSTRAINT
[DF__dim_custo__custo__019E3B86] DEFAULT (0),
PRIMARY KEY CLUSTERED
(
[customer_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_dim_customer_01] UNIQUE NONCLUSTERED
(
[opco_num],
[customer_num]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
),
FOREIGN KEY
(
[sales_rep_ident]
) REFERENCES [dim_salesRep] (
[salesRep_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_salesRep] (
[salesRep_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[salesRep_num] [int] NOT NULL ,
[salesRep_name] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_sales__sales__6ABAD62E] DEFAULT ('Unknown'),
[salesManager_code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_sales__sales__6BAEFA67] DEFAULT ('Unk'),
[salesManager_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_sales__sales__6CA31EA0] DEFAULT ('Unknown'),
[salesTerritory] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_sales__sales__6D9742D9] DEFAULT ('Unk'),
[salesTerritory_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF__dim_sales__sales__6E8B6712] DEFAULT ('Unknown'),
[Rep_Net_Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mgr_Net_Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[salesRep_ident]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_opco] (
[opco_num] [int] NOT NULL ,
[opco_desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[region_num] [int] NOT NULL ,
[region_desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Dim_OPCO] PRIMARY KEY CLUSTERED
(
[opco_num]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GOHere is a suggestion that may solve your problem: move the two INNER
JOIN-s (with dim_opco and with dim_salesRep) BEFORE the LEFT JOIN.
Razvan|||Thanks for the suggestion. However, moving the INNER JOINS up made no
effect on the result set.
"Razvan Socol" wrote:

> Here is a suggestion that may solve your problem: move the two INNER
> JOIN-s (with dim_opco and with dim_salesRep) BEFORE the LEFT JOIN.
> Razvan
>sql

Query Where clause not working quite correct

My query below should only do an insert if these 2 evaluate to true:

a) The customer number is not currently in the DR table

b) The customer number is in the list check (where customer in)

a has to be true in order for b to be checked. The insert cannot happen for a customer that's already in the DCR whose number is in the IN clause

So far, it is close however I'm finding a few numbers being inserted that are already in the DR table.

I would also like to insert one record for the customer. It will find multiple entries per customer number since this is really a transactiosn table but I need to insert like the Top 1 o something since these inserts are going to be customers who have no transactions...thus is why you see blanks or zeros for all the values. These inserts are part of a larger picture but are needed...so there is no n eed to explain why I'd want to insert nothing ('' and 0 values) for those customers...just leave it at that

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

AND c.customer NOT IN (select customernumber FROM DR)

I think your problem lies in the fact that you are making your customer not in the DR table comparison as part or the join criteria. Try moving it to a WHERE clause instead or actually join the DR table using left join, see below

1st, instead of "AND c.customer NOT IN (select customernumber FROM DR)" Replace with WHERE c.customer NOT IN (SELECT customernumber FROM DR) Also move your check for customers in the provided list, into a where clause instead of the join criteria.

2nd, use the join as below with a where clause

Code Snippet

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

LEFT OUTER JOIN DR d ON c.customer = d.customernumber

WHERE d.customernumber is null

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

Either method should provide the results you need.

|||

Try re-writing your statement to use NOT EXISTS instead NOT IN to check for existence. If for any reason, there is a row in [DR] where customernumber is NULL, then you will have some trouble.

Example:

Code Snippet

select

*

from

(select 1 as c1 union all select 2) as a

where

c1 not in (1, NULL)

It should looks like:

...

where

c.customer IN (

'0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113',

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133'

)

AND NOT exists (

select *

from DR as d

where d.customernumber = c.customer

)

AMB

Query Where clause not working quite correct

My query below should only do an insert if these 2 evaluate to true:

a) The customer number is not currently in the DR table

b) The customer number is in the list check (where customer in)

a has to be true in order for b to be checked. The insert cannot happen for a customer that's already in the DCR whose number is in the IN clause

So far, it is close however I'm finding a few numbers being inserted that are already in the DR table.

I would also like to insert one record for the customer. It will find multiple entries per customer number since this is really a transactiosn table but I need to insert like the Top 1 o something since these inserts are going to be customers who have no transactions...thus is why you see blanks or zeros for all the values. These inserts are part of a larger picture but are needed...so there is no n eed to explain why I'd want to insert nothing ('' and 0 values) for those customers...just leave it at that

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

AND c.customer NOT IN (select customernumber FROM DR)

I think your problem lies in the fact that you are making your customer not in the DR table comparison as part or the join criteria. Try moving it to a WHERE clause instead or actually join the DR table using left join, see below

1st, instead of "AND c.customer NOT IN (select customernumber FROM DR)" Replace with WHERE c.customer NOT IN (SELECT customernumber FROM DR) Also move your check for customers in the provided list, into a where clause instead of the join criteria.

2nd, use the join as below with a where clause

Code Snippet

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

LEFT OUTER JOIN DR d ON c.customer = d.customernumber

WHERE d.customernumber is null

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

Either method should provide the results you need.

|||

Try re-writing your statement to use NOT EXISTS instead NOT IN to check for existence. If for any reason, there is a row in [DR] where customernumber is NULL, then you will have some trouble.

Example:

Code Snippet

select

*

from

(select 1 as c1 union all select 2) as a

where

c1 not in (1, NULL)

It should looks like:

...

where

c.customer IN (

'0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113',

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133'

)

AND NOT exists (

select *

from DR as d

where d.customernumber = c.customer

)

AMB

Query using DATEPART

Hello.

The purpose of the code below is to produce graphs in a report (thus the SELECT DATEPART so the month and year appear on the graphs using COUNT in the report.)I have two challenges:

1. How should the code be modified so that it returns the previous two months at the start of a new year?(I.e. In Jan. 2008, returning Nov. & Dec. 2007, and in Feb. 2008, returning Dec. 2007 & Jan 2008, etc.)

2. How should the code be modified so that the DatePart and DateName data are treated as dates yet still appear as the Month Name and Year (June 2007)? Currently it is treating them as alphanumeric so that on the chart, July is coming before June.

Thank you.

SELECT DISTINCT TBL_01.id, TBL_01.url, TBL_01.source, Tbl_02.type, TBL_01.time, Tbl_03.time, DATENAME(month, TBL_01.time) + ' ' + DATENAME(year,TBL_01.time) as Month_Year

FROMTbl_03 INNER JOIN

Tbl_04 ON Tbl_03.id = Tbl_04.endsTBL_01 LEFT OUTER JOIN

Tbl_02 ON Tbl_04.endsTBL_01 = Tbl_02.id LEFT OUTER JOIN

Tbl_05 ON Tbl_04.ursid = Tbl_05.SubId INNER JOIN

Tbl_03 AS TBL_01 ON TBL_01.id = Tbl_04. EFP

WHERE(Tbl_04. EFP IS NOT NULL) AND (Tbl_05.grade IS NULL OR

Tbl_05.grade = 'NC')

AND (DATEPART(month, TBL_01.time) < (SELECT DATEPART(month, getutcdate()))

AND DATEPART(month, TBL_01.time) > (SELECT DATEPART(month, getutcdate()))-3

AND DATEPART(year, TBL_01.time) = (SELECT DATEPART(year, getutcdate()))

AND (Tbl_02.type IS NULL OR Tbl_02.type = 'CC'

OR Tbl_02.type = 'MK'))

ORDER BY Tbl_02.Type, Tbl_03.time DESC

For Q2, you might be able to fix the ordering with a tiny change to the 'ORDER BY ' clause:

ORDER BY TBL_01.time desc, Tbl_02.Type, Tbl_03.time

You don't always have to have the <ORDER BY> field in the select list.

|||

For Q1, I would do something like this:

Code Snippet

declare @.dtStartOfThisMonth datetime, @.dtStartOfTimePeriod datetime

set @.dtStartOfThisMonth = ('01 ' + right(convert(varchar, getdate(),106),9))

set @.dtStartOfTimePeriod = Dateadd(m,-2, @.dtStartOfThisMonth)

THE REST OF THE QUERY HERE

Then, in the WHERE clause put something like:

Code Snippet

AND Tbl_01.Time >= @.dtStartOfTimePeriod and Tbl_01.Time < @.dtStartOfThisMonth

|||

This worked in putting things in the proper order. I just took out the "desc" and everything fell into place. The one field that was in the select list that didn't need to be was merely a leftover from when I was experimenting with an aggregate. I had forgotten to take it out.

Thank you so much.

|||

This worked beautifully! Thank you so much!

query to trace all parents

Hi,

I have a table with filled out below data:

+--+--+
|parent|child|
+--+--+
|A |B |
|B |C |
|B |E |
|C |D |
|E |F |
|E |G |
+--+--+

So I have to make a query which get all 'parent' values values for
given child value.

For example :
------
If I have to get all parent values for 'D' child., query must get this
values : C, B, A.

If I have to get all parent values for 'F' child., query must get this
values : E, B, A.

If I have to get all parent values for 'C' child., query must get this
values : B, A.

If I have to get all parent values for 'B' child., query must get this
values : A only.
------

Is it possible to create a query which will covers all above conditions
or not using only sql statement without UDF or stored procedures.

Any solutiuons?

Sincerely,
Rustam BogubaevPYCTAM (rbogubaev@.bookinturkey.com) writes:
> So I have to make a query which get all 'parent' values values for
> given child value.
> For example :
> ------
> If I have to get all parent values for 'D' child., query must get this
> values : C, B, A.
> If I have to get all parent values for 'F' child., query must get this
> values : E, B, A.
> If I have to get all parent values for 'C' child., query must get this
> values : B, A.
> If I have to get all parent values for 'B' child., query must get this
> values : A only.
> ------
> Is it possible to create a query which will covers all above conditions
> or not using only sql statement without UDF or stored procedures.

In SQL2000, no.

In SQL 2005, slated for release in November, there is support for
recursive queries.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||in this hopeless situation problem is solved using UDF :

CREATE FUNCTION getAllParents(
@.child NVARCHAR(1)
) RETURNS @.PARENTS TABLE (
[parent] NVARCHAR(1)
) AS BEGIN
DECLARE @.parent NVARCHAR(1)

SELECT @.parent = parent FROM table WHERE child = @.child

WHILE @.@.ROWCOUNT = 1 BEGIN
INSERT @.PARENTS SELECT @.parent

SELECT @.child = @.parent
SELECT @.parent = parent FROM table WHERE child = @.child
END

RETURN
END|||Again, get a copy of TREES & HIERARCHIES IN SQL and look up the Nested
Sets Model for trees.

Query to get Linked List kind of data from the Table

hi Experts,

I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of

ID

Duplicate_of

State

77637

65702

Duplicate

65702

42217

Duplicate

42217

-

Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.

Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.

/soni

This is a common problem, what you will find is that you actually have a tree structure where 42217 is the root of the tree, 65702 is a branch, and 77637 is a leaf. Have a search around Google for Celko's "nested set" which has a solution which should help you here.

Query to get Linked List kind of data from the Table

hi Experts,

I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of

ID

Duplicate_of

State

77637

65702

Duplicate

65702

42217

Duplicate

42217

-

Verified

i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.

Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate

i appreciate if somebody can help me think in some line to give me some idea.

/soni

Assuming you are using 2005 you can use a common table expression (CTE) and use the following syntax:

DECLARE @.ID int

SET @.ID = 77637

;WITH Dupes (ID, Duplicate_of, State) AS

(

SELECT

ID, Duplicate_of, State

FROM

dbo.test

WHERE

ID = @.ID

UNION ALL

SELECT

T.ID, T.Duplicate_of, T.State

FROM

dbo.test T

INNER JOIN Dupes D ON D.Duplicate_of = T.ID

)

SELECT *

FROM Dupes

|||

hi Weaver,

Many Thanks for looking at my problem.

i forgot to mention i use SQL Server 2000 as of now. cannot upgrade to SQL Server 2005. :(

/Soni

|||

You can use this function to return the root of the tree (assuming you wanted to do this one at a time :)

set nocount on
drop table issue
drop function issue$getRoot
go
create table issue
(
issueId int primary key,
duplicateOf int references issue(issueId)
)
insert into issue
select 1, NULL
insert into issue
select 2, 1
insert into issue
select 3, 2
insert into issue
select 4, NULL
insert into issue
select 5, 4
go
create function issue$getRoot
(
@.issueId int
)
returns int
as
begin
while(1=1)
begin
select @.issueId = issue.issueId
from issue
join issue as dup
on dup.duplicateOf = issue.issueId
where dup.issueId = @.issueId

if @.@.rowcount = 0
break
end
return @.issueId
end
go

select dbo.issue$getRoot (5)

|||

hi Louis

Thanks a million for yr expert logic. its working!!!. :)

/Soni

query to find the top 3 in each type

Hi

I need help in finding the query which will provide the following resultset from the below table..

Table :
create table product_stocks(product_id int , product_type varchar(20) , no_of_units int)

Data:
insert into product_stocks values(1,'A',30)
insert into product_stocks values(2,'A',70)
insert into product_stocks values(3,'A',60)
insert into product_stocks values(4,'A',40)
insert into product_stocks values(1,'B',90)
insert into product_stocks values(2,'B',60)
insert into product_stocks values(3,'B',70)
insert into product_stocks values(4,'B',40)
insert into product_stocks values(1,'C',40)
insert into product_stocks values(2,'C',50)
insert into product_stocks values(3,'C',80)
insert into product_stocks values(4,'C',90)

Result Set:
product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 3 70
B 2 60
C 4 90
C 3 80
C 2 50

i.e The result set gives the top 3 products in each product_type based on the no_of_units.

thanksselect * from product_stocks where product_id in (select top 3 product_id from product_stocks group by product_id )order by product_type,no_of_units desc|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework|||Hi harshal,

Thanks for providing the query.
It was very helpful and met my requirement.

thanks|||harshal, fortunately for you, your solution has a wee flaw

by the way, did you not notice that this was another RFH post?

:)

RFH = request for homework

yeah I thought it would be a RFH..:mad: .

can u please enlighten me on the flaw part please...|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!|||take a look at the subquery

you are grouping on product_id and then taking the top 3 of them

the top three based on what? there's no ORDER BY!!!!

OHH!! :confused:
I m getting lazy day by day.. need to spend more time on the forums i guess..;)

thanks for pointing out..

harshal|||Hi harshal

I tested the query , but the result is not correct .
It provides the result set for the product_id 1 , 2 , 3 in each product_type and not the
top 3 in each product_type based on no_of_units

thanks|||arjun, try this --select one.product_type
, one.product_id
, one.no_of_units
from daTable as one
inner
join daTable as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units
group
by one.product_type
, one.product_id
, one.no_of_units
having count(*) <= 3 and be sure you can explain it when your teacher asks you how you got it|||Hi

Even if order by is used in the sub query, it will give the top 3 product_id across all the product_type
But what i need is the top 3 from each of the product_type .

thanks|||Even if order by is used in the sub query, it will give the top 3 product_id across all the product_typeno, not if it's a correlated subquery

But what i need is the top 3 from each of the product_typedid you try my query?|||Hi r937 ,

I tried your query. It returns the top 3 product_id from all product_types.
I need another help.
In the result set , the order of the result set varies for each product_type.

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

How to modify this so that the no_of_units for each product_type is in the descending order.

thanks.|||I tried your query. It returns the top 3 product_id from all product_types.you could not possibly have tried it

here is what it produces:A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90this is exactly what you asked for|||Hi r937,

I am getting the same result as you have posted.

The Result set is here :

product_type product_id no_of_units
----- ---- -----
A 2 70
A 3 60
A 4 40
B 1 90
B 2 60
B 3 70
C 2 50
C 3 80
C 4 90

In this result set , the no_of_units for product_type 'A' is in descending order,
but the no_of_units for product_type 'B' and 'C' is not in descending order.

What i seek is to get the no_of_units in descending order for each of the product_types.

thanks|||look up ORDER BY in your manual

:)|||look up ORDER BY in your manual

:)
Firstly you must look up ORDER By in manual as r937 said ,thats for your knowledge..
well,this time just try this to get your results..

select one.product_type
, one.product_id
, one.no_of_units
from product_stocks as one
inner
join product_stocks as two
on two.product_type = one.product_type
and two.no_of_units >= one.no_of_units

group
by one.product_type
, one.product_id
, one.no_of_units

having count(*) <= 3 order by one.product_type,one.no_of_units desc

Joydeep|||This is coming pretty close to baby-sitting. arjun, you need to become familiar with books online. If you can't find your answer there, or you don't understand something, then post a question.