query equivalent: order

Showing posts with label order. Show all posts

Wednesday, March 28, 2012

Loops and building comma delimited strings

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @.NumberingType varchar(10)
DECLARE @.TotalSum int
DECLARE @.DoorLineItemID int
DECLARE @.Quantity int
DECLARE @.SeedInt int

SET @.SeedInt = 1

SELECT @.TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity
DECLARE @.RefNumberLine varchar(1024)
SET @.RefNumberLine = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN

WHILE @.SeedInt <= @.Quantity
BEGIN

SET @.RefNumberLine = @.RefNumberLine + CONVERT(varchar, @.SeedInt, 101) + ', '
SET @.SeedInt = @.SeedInt + 1

END
PRINT @.RefNumberLine

SET @.SeedInt = @.Quantity + @.SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @.SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @.Quantity + @.SeedInt, 101)

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity

END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor

This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3

When I really want the results described above.

What am I doing wrong?

Thanks!

You really need to post a table structure and some data for us to use to try this out. That's a lot of variables with no data to reference to try out.|||solved. Thanks for your input.

looping to drop temp tables

Hello,

I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

declare @.n as nvarchar(3),
@.table as nvarchar(10),
@.str nvarchar(1000)
set @.n = 1
while @.n <= 5 begin
set @.table = '#temp'+@.n
set @.str = 'if object_id('+''''+'tempdb..'+@.table+''''+') is not null drop table '+ @.table
print @.str

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

The following code will print the "DROP TABLE <Your Temp Tables> list of commands in your message tab", and you need to run those commands by copying all of them into your query editor and excute them all no need to write any loop statements.

Use TempDb

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

immediately following the "is not null" statement as written in my original attempt?
|||Thanks to everyone who replied and provided code snippets. It is very helpful to see those. It is also reassuring to know there is more than one possible solution.
|||

Because the DDL statements do not allow variables for commands. It is really kind of annoying at times, but DDL is really not made to execute dynamically like this, as it is expected that these are management constructs, not programming ones.

You don't actually have to drop temp tables, but if I was going to do it, I would use your initial form. It will likely be a little bit faster as you already have to do the same amount of work against the database, and easier to test as there is no looping variable based code that could go wrong (not that it has a large chance to, but the other method is safer.

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

looping to drop temp tables

Hello,

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

Use TempDb

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

sql

looping to drop temp tables

Hello,

Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null drop table @.table

set @.n = @.n + 1

end

Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.table'.

I have also tried:
declare @.n as nvarchar(3), @.dropstmt as nvarchar(25)

set @.n = 1

while @.n <= 5 begin

set @.dropstmt = 'drop table #temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null @.dropstmt

set @.n = @.n + 1

end

This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@.dropstmt'.

Does anyone know how to get this to work?

Thanks.

Code Snippet

exec sp_executesql @.str
set @.n = @.n + 1
end

|||

Here it is,

Code Snippet

declare @.n as nvarchar(3), @.table as nvarchar(10)

set @.n = 1

while @.n <= 5 begin

set @.table = '#temp'+@.n

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table ' + @.table)

set @.n = @.n + 1

end

|||

Hi,

Use TempDb

DECLARE @.RETURN_VALUE int

DECLARE @.command1 nvarchar(2000)

DECLARE @.whereand nvarchar(2000)

SET @.command1 = 'Print ''drop table ?'''

SET @.whereand = 'AND O.Name LIKE ''#Temp[0-9]%'''

EXEC @.RETURN_VALUE = sp_MSforeachtable @.command1=@.command1,

@.whereand = @.whereand

Reference:

http://www.dbazine.com/sql/sql-articles/larsen5

Regards,

Kiran.Y

|||Dear Moderator,

Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use

exec('drop table '+@.table)?

Why doesn't it recognize the plain old

drop table @.table

If you know that all of the tables are created (and really you should usually know what tables you are going to create in your code that is using a temp table,) then you could just do:

drop table table1, table2, table3, etc.

|||

One further note about about this -

The final code I arrived at is as follows:

Code Snippet

declare @.n as nvarchar(3)

set @.n = 1

while @.n <= 5 begin

if object_id('tempdb..#temp'+@.n) is not null

exec('drop table #temp'+@.n)

set @.n = @.n + 1

end

This is simplified one more step by removing the @.table variable and it seems to work fine.

Thanks for everyone's help on this.

Monday, March 26, 2012

Looping through Column to Insert

Attempting to insert a new Item for each Vendor into a Vendor Item Catalog. However, the Vendors are in no particular order.

ie... sample data

CompanyID,VendorID,ItemID

Comp1,1004,Event1

Comp1,3433,Event2

Comp1,45343,Event3

I need to be able to loop through to the next Vendor that doesnt already have this ItemID.

Any ideas?

Maybe something like this:

declare @.source table
( CompanyID varchar(8),
VendorID integer,
ItemID varchar(8)
)
insert into @.source
select 'Comp1', 1004, 'Event1' union all
select 'Comp1', 3433, 'Event2' union all
select 'Comp1',45343, 'Event3'

declare @.target table
( CompanyID varchar(8),
VendorID integer,
ItemID varchar(8)
)
insert into @.target
select 'Comp1', 1004, 'Event1'

insert into @.target

select CompanyID,
VendorID,
ItemID
from @.source a
where not exists
( select 0 from @.target b
where a.companyId = b.companyId
and a.vendorId = b.vendorId
and a.ItemId = b.ItemId
)

select * from @.target
/*
CompanyID VendorID ItemID
-- --
Comp1 1004 Event1
Comp1 3433 Event2
Comp1 45343 Event3
*/

|||

Are you trying to INSERT or UPDATE?

Adamus

|||

I am attempting to INSERT. I tried using the suggested code WHERE NOT EXISTS but it returned nothing.

|||It would be good if you post the code you are executing with a description of the tables involved.|||

CODE:

declare @.CompID as varchar(5),

@.VendID as varchar(15)

SET @.CompID = SELECT CompanyID FROM ARIVendorCatalog WHERE NOT EXISTS

(SELECT TOP 1 CompanyID FROM ARIVendorCatalog

WHERE VendorItemID = 'Events - Internal Meetings')

SET @.VendID = SELECT VendorID FROM ARIVendorCatalog WHERE NOT EXISTS

(SELECT TOP 1 VendorID FROM ARIVendorCatalog

WHERE VendorItemID = 'Events - Internal Meetings')

INSERT INTO ARIVendorCatalog

VALUES (@.CompID, @.VendID, 'Events - Internal Meetings', 'Events - Internal Meetings', '0', 'Z-US$', '','','')

I Figure that once inserted, the TOP vendor will change every time until there is none left.

|||

Ilana:

You might be able to accomplish the insert with something like this:

create table ARIVendorCatalog
( CompID varchar(5),
VendorID varchar(15),
VendorItemID varchar(30),
Column_04 varchar(30),
Column_05 varchar(10),
Column_06 varchar(10),
Column_07 varchar(10),
Column_08 varchar(10),
Column_09 varchar(10)
)
go

insert into ARIVendorCatalog
select 'Comp1', 'Vend1', 'Events - Internal Meetings', 'Events - Internal Meetings', '0', 'Z-US$', '', '', '' union all
select 'Comp1', 'Vend2', 'Bricka Bracka Firecracker', 'Bugs Bunny, Bugs Bunny', '0', 'Z-US$', '', '', '' union all
select 'Comp2', 'Vend3', 'Help Richard Starr', 'Bingo Night', '0', 'Z-US$', '', '', ''

insert into ARIVendorCatalog
select CompID,
VendorID,
'Events - Internal Meetings',
'Events - Internal Meetings',
'0', 'Z-US$', '', '', ''
from ( select CompID,
VendorID,
max ( case when VendorItemID = 'Events - Internal Meetings'
then 1 else 0 end
) as hasTargetEvent
from ARIVendorCatalog
group by CompID, VendorID
having max ( case when VendorItemID = 'Events - Internal Meetings'
then 1 else 0 end
) = 0
) source

select * from ARIVendorCatalog

/*
CompID VendorID VendorItemID Column_04 Column_05 Column_06 Column_07 Column_08 Column_09
- - - - -
Comp1 Vend1 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp1 Vend2 Bricka Bracka Firecracker Bugs Bunny, Bugs Bunny 0 Z-US$
Comp2 Vend3 Help Richard Starr Bingo Night 0 Z-US$
Comp1 Vend2 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp2 Vend3 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
*/

|||

Ilana:

There is a much better way of doing this if you happen to also have a "VENDOR" table that lists all unique active combinations of CompID and VendorID. If that is true you will not need to compute the aggregate in my last example and you might be able to do something like this:

create table ARIVendorCatalog
( CompID varchar(5),
VendorID varchar(15),
VendorItemID varchar(30),
Column_04 varchar(30),
Column_05 varchar(10),
Column_06 varchar(10),
Column_07 varchar(10),
Column_08 varchar(10),
Column_09 varchar(10)
)
go

insert into ARIVendorCatalog
select 'Comp1', 'Vend1', 'Events - Internal Meetings', 'Events - Internal Meetings', '0', 'Z-US$', '', '', '' union all
select 'Comp1', 'Vend2', 'Bricka Bracka Firecracker', 'Bugs Bunny, Bugs Bunny', '0', 'Z-US$', '', '', '' union all
select 'Comp2', 'Vend3', 'Help Richard Starr', 'Bingo Night', '0', 'Z-US$', '', '', ''

create table Vendor
( CompID varchar(5),
VendorID varchar(15),
vendorName varchar(20)
)
go

insert into Vendor
select 'Comp1', 'Vend1', 'Jaba Vendor' union all
select 'Comp1', 'Vend2', 'Blue Vendor' union all
select 'Comp2', 'Vend3', 'Gold Vendor' union all
select 'Comp2', 'Vend4', 'New Vendor'

insert into ARIVendorCatalog
select CompID,
VendorID,
'Events - Internal Meetings',
'Events - Internal Meetings',
'0', 'Z-US$', '', '', ''
from Vendor a
where not exists
( select 0 from ARIVendorCatalog b
where a.CompID = b.CompID
and a.VendorID = b.VendorID
and VendorItemId = 'Events - Internal Meetings'
)

select * from ARIVendorCatalog

/*
CompID VendorID VendorItemID Column_04 Column_05 Column_06 Column_07 Column_08 Column_09
- - - - -
Comp1 Vend1 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp1 Vend2 Bricka Bracka Firecracker Bugs Bunny, Bugs Bunny 0 Z-US$
Comp2 Vend3 Help Richard Starr Bingo Night 0 Z-US$
Comp1 Vend2 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp2 Vend3 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp2 Vend4 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
*/

Wednesday, March 21, 2012

loop through few table in sp

Hi,
I have a lot monthly tables, in order to query some data for last few month,
I have to check a few tables. And the table names are determined by today's
date. If I find the record, I don't need to query other tables anymore. how
can I do it in store procedure? ThanksIf the table names are based on the current date, you'll need to use dynamic
SQL for your query.
Like this:
declare @.today datetime
set @.today = getdate()
declare @.qry nvarchar(1000)
set @.qry = 'select * from sometable_' +
replace(convert(nvarchar(10),@.today,120)
,'-','') + ' where col = 1'
sp_executesql @.qry
But of course, if you want to look through a bunch of tables and stop
searching, you might want to consider populating a temporary table with the
tables you want to check (search through sysobjects for them perhaps), and
then grab the top record, delete it out of your temporary table, search
through the table, and if you find the data, drop out of your while loop. If
you run out of tables to check, you're done and you haven't found it.
But if it's a fixed list of tables, you could just write it out in full,
with return statements appropriately placed.
Hope this helps,
Rob
"Jen" wrote:

> Hi,
> I have a lot monthly tables, in order to query some data for last few mont
h,
> I have to check a few tables. And the table names are determined by today'
s
> date. If I find the record, I don't need to query other tables anymore. ho
w
> can I do it in store procedure? Thanks|||Jen
Can you create a view with an UNION ALL clause to combine those tables and
then query the view BETWEEN required dates?
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:26AB2E6F-CED2-4559-B48E-1F8FDF6123EB@.microsoft.com...
> Hi,
> I have a lot monthly tables, in order to query some data for last few
> month,
> I have to check a few tables. And the table names are determined by
> today's
> date. If I find the record, I don't need to query other tables anymore.
> how
> can I do it in store procedure? Thanks|||thanks. I need to query myTable20060523 first, if record found then I am
done; otherwise I need to continue query myTable200604, myTable200603...,
etc. up to 6 tables. how can I loop through these tables? Thanks
"Rob Farley" wrote:
> If the table names are based on the current date, you'll need to use dynam
ic
> SQL for your query.
> Like this:
> declare @.today datetime
> set @.today = getdate()
> declare @.qry nvarchar(1000)
> set @.qry = 'select * from sometable_' +
> replace(convert(nvarchar(10),@.today,120)
,'-','') + ' where col = 1'
> sp_executesql @.qry
>
> But of course, if you want to look through a bunch of tables and stop
> searching, you might want to consider populating a temporary table with th
e
> tables you want to check (search through sysobjects for them perhaps), and
> then grab the top record, delete it out of your temporary table, search
> through the table, and if you find the data, drop out of your while loop.
If
> you run out of tables to check, you're done and you haven't found it.
> But if it's a fixed list of tables, you could just write it out in full,
> with return statements appropriately placed.
> Hope this helps,
> Rob
>
> "Jen" wrote:
>|||This code will update only tables name like cust and update value of id
to 100.
create table cust (id int)
GO
create table cust1 (id int)
GO
insert into cust values(10)
go
insert into cust1 values(10)
go
select * from cust
go
select * from cust1
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname
set @.db = DB_NAME()
Declare @.tabname sysname
set @.tabname= '%cust%'
set @.sql ='select ''update '' + QUOTENAME(table_SCHEMA) + ''.'' +
QUOTENAME(table_NAME) + '' set id = 100 '' FROM
INFORMATION_SCHEMA.tables ' +
'WHERE table_type = ''base table'''
if @.tabname is not null
set @.sql = @.sql + N' AND table_NAME LIKE ''' + @.tabname+ ''''
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
print @.ret
select * from cust
select * from cust1
You can specify name pattern for your table like '%cust%' here in
sample and it will update or do other operation only on that tables.
Regards
Amish Shah|||This code will update only tables name like cust and update value of id
to 100.
create table cust (id int)
GO
create table cust1 (id int)
GO
insert into cust values(10)
go
insert into cust1 values(10)
go
select * from cust
go
select * from cust1
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname
set @.db = DB_NAME()
Declare @.tabname sysname
set @.tabname= '%cust%'
set @.sql ='select ''update '' + QUOTENAME(table_SCHEMA) + ''.'' +
QUOTENAME(table_NAME) + '' set id = 100 '' FROM
INFORMATION_SCHEMA.tables ' +
'WHERE table_type = ''base table'''
if @.tabname is not null
set @.sql = @.sql + N' AND table_NAME LIKE ''' + @.tabname+ ''''
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
print @.ret
select * from cust
select * from cust1
You can specify name pattern for your table like '%cust%' here in
sample and it will update or do other operation only on that tables.
Regards
Amish Shah|||can I create view in the store procedure? Is there any side effect or
performance issue? How about more than one user is executing the same
procedure?Thanks
"Uri Dimant" wrote:

> Jen
> Can you create a view with an UNION ALL clause to combine those tables and
> then query the view BETWEEN required dates?
>
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:26AB2E6F-CED2-4559-B48E-1F8FDF6123EB@.microsoft.com...
>
>|||--Try something like this:
declare @.qry nvarchar(2000)
declare @.found bit
set @.found = 0
declare @.done bit
set @.done = 0
declare @.tablenames table (id int identity(1,1), name varchar(128));
insert into @.tablenames
select top 6 name
from sysobjects
where name like 'mytable%'
order by 1 desc
declare @.tablename varchar(128)
declare @.tableid int
while (@.found = 0 and @.done = 0)
begin
select top 1 @.tablename = name, @.tableid = id
from @.tablenames
order by id
if (@.@.rowcount = 0)
begin
set @.done = 1
end
else --search through the table
begin
delete from @.tablenames where id = @.tableid
set @.qry = 'declare @.misc int; select @.misc = id from ' + @.tablename + '
where somecol = 15' --This won't return a value, but will set @.@.rowcount
exec sp_executesql @.qry
if (@.@.rowcount > 0)
begin
set @.found = 1
end
end
end
-- Look at the values of @.found and @.tablename to see if you found it, and
what table you found it in
if (@.found = 1)
begin
select @.tablename
end
"Jen" wrote:
> thanks. I need to query myTable20060523 first, if record found then I am
> done; otherwise I need to continue query myTable200604, myTable200603...,
> etc. up to 6 tables. how can I loop through these tables? Thanks
> "Rob Farley" wrote:
>sql

Monday, March 12, 2012

Lookup including looking up on null values possible?

In order to insert datekey values in I lookup datekey in the datedimension table. I join on the 'Date' column that contains dates. The datedimension contains one record for 'unknown date' for which the value of the 'Date' column is null.

The behavior that I desire from my lookup transformation is that for input records with a date the corresponding datekey from the datedimension is looked up and for records with date = null, the datekey for 'unknown date' is looked up.

The first part works well but the looking up on null fails, apparently because you can not say null == null. Does anyone know whether there is a setting in the lookup transformation to include null lookups?

Thnx,
HenkThe lookup transform can not do this. You would need to put a derived column in the flow and if the value is NULL then set it to the appropriate 'unknown date' value.

Thanks,|||Thanks Matt.|||In fact it can and it is quite easy! I found out in the documentation:

"A Lookup transformation that has been configured to use partial or no caching will fail if a lookup operation matches columns that contain null values, unless you manually update the SQL statement to include an OR ISNULL(ColumnName) condition. If full precaching is used, the lookup operation succeeds."

|||So by selecting the full precaching option for the lookup, you eliminate the need to modify the SQL with the ISNULL function?|||While this can work as described I would recommend against it and is, therefore, why I didn't mention it. You need to be careful if you do lookups in this way because unless you guarrantee that there is only one such value you will get the first one lookup happens to find with no warning.

Full precaching will not work because the cache is fully charged and doesn't issue the SQL statement again. The reason why partial or no cache works is because the SQL statement is issued if a match isn't found and will return success due to the ISNULL statement as long as there is a NULL in the table.

There are too many ifs and caveats to make this a good solution, IMHO.

Thanks,

Lookup Arrays

I need to load an array of order numbers.. Then later I need to be able to
'lookup' in that area for a specific order number (for example one that user
keys into text box).
how is this accomplished? ive researched Array and Arraylist and cannot
figure how without a bunch of work.
There must be something simple for this.
Can you help?
Thanks.
jrHello Jr,
Welcome to the MSDN newsgroup.
From your description, you have an order number list which want to be
stored in a list container and you'll lookup some certain order numbers
from that container later in your SQL Reporting service's report, correct?
Based on my experience, since these code should be in your custom code
period or an separate assembly, so you're using them as custom code or
custom assembly in your report , right? If this is the case, what
available to us are all those utility or collection classes in the .net
framework system.collections namespace(Such as the Array, ArrayList you
mentioned).
For your scenario, I'm wondering how will you identify a certain order
number? Just through the orderID or will you give each order number another
identity(a key value) for lookup? Here are two generic cases:
1. If you just want to get the order item's index in the container(such as
Array) through the order number(suppose its of string type), you can just
Array.IndexOf method to locate a certain item in the Array:
#Array.IndexOf, m¨¦thode (Array, Object)
http://msdn2.microsoft.com/fr-fr/library/7eddebat.aspx
Here is a simple test class which intializing the Array(orderList) in the
type's static initializer and expose a public static method for lookup item
index in the array:
==================public class ArrayUtil
{
public static string[] OrderArray;
public ArrayUtil()
{
}
static ArrayUtil()
{
OrderArray = new string[10];
for (int i = 0; i < OrderArray.Length; i++)
{
OrderArray[i] = "Order_" + i;
}
}
public static int FindOrder(string num)
{
return Array.IndexOf(OrderArray, num);
}
}
=====================
2. If each order nunber will be stored with an identity value as the lookup
key, I think the "HashTable" class is the reasonable choice. HashTable
class support storing multiple key/value pairs and have method for looking
up a certain item in it via its key value:
#Hashtable Class
http://msdn2.microsoft.com/en-us/library/system.collections.hashtable.aspx
Hope this helps. If there is any paricular concerns in your scenario or if
you have any other ideas ,please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Jr,
How are doing on this issue, have you got any progress or does my last
reply helps you a little on this? If you have anything unclear or still
anything else we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Friday, March 9, 2012

looking to collect distinct date part out of datetime field

from this, circdate being a datetime field:
SQLQuery = "select distinct circdate from circdata order by circdate"

I need the distinct date portion excluding the time part.

this has come about when I discovered
I am inserting and updating some datetime values with the same value,
but for some reason, the values are always off by a few seconds. I set
a variable called SetNow assigned to NOW and then set the datetime
fields to this SetNow variable. Then when I collect the distinct date
time I am assuming they will have the same values recorded in
circdate, but no, they are off by several seconds. Makes no sense to me
at all. I tried renaming the variable several times but it makes no
difference at all.
any help appreciated, thanks.SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"

I think I solved it
any one see a problem with this?
thanks
how does the '1' parameter affect the output as I know there are
several choices|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"
> I think I solved it
> any one see a problem with this?
> how does the '1' parameter affect the output as I know there are
> several choices

1 is a format parameter that controls how the datetime value is formatted.
You can read about these in the topic CAST and CONVERT in Books Online.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||http://www.aspfaq.com/show.asp?id=2464
shows all the outputs
Was wondering though if it wont use an index on a convert.|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> http://www.aspfaq.com/show.asp?id=2464
> shows all the outputs
> Was wondering though if it wont use an index on a convert.

For the query you gave,

SELECT distinct CONVERT(char,circdate,1) from circdata

this is not an issue. If there is an index on cricdate, SQL Server will
use that index in the most effective, that is to scan the index, because
that is what the query calls for, with or without the convert().

On the other hand

SELECT col1, col2, col3 FROM circdata
WHERE CONVERT(char, circdate, 1) = @.val

will probably not use the index, and in any case the query will not seek
the index, that is lookup the value through the B-tree. This is because
the index is sorted on the datetime value, not on a character value.

To list all rows for a given date you can do:

SELECT col1, col2, col3 FROM circdata
WHERE circdate >= @.val AND circdate < dateadd(DAY, @.val, 1)

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

Madhivanan

Saturday, February 25, 2012

looking for non similar orders

System is "MFG" Custom jobs.
Tracking customer's last order and looking for changes they make in the job
detail table as compared to order before.
Table schema:
OrderID, line#, Item, QtyReq
I need to verify the last order and if it changed with the clients prior
order.
I have 6 line items per order, and the rows 1,2,3 are the critical check
point I need to verify.
If it returned int for # of rows diff that would be fine.
http://www.webhost4life.com/hosting.asp shows monthly fees for 5,10,20 USD
per month.Hi
Check out http://www.aspfaq.com/etiquett_e.asp?id=5006 on how to post DDL
and example data. It is also useful to post your current attempt and the
information you expect from the example data.
John
"Stephen Russell" wrote:

> System is "MFG" Custom jobs.
> Tracking customer's last order and looking for changes they make in the jo
b
> detail table as compared to order before.
> Table schema:
> OrderID, line#, Item, QtyReq
> I need to verify the last order and if it changed with the clients prior
> order.
> I have 6 line items per order, and the rows 1,2,3 are the critical check
> point I need to verify.
> If it returned int for # of rows diff that would be fine.
> http://www.webhost4life.com/hosting.asp shows monthly fees for 5,10,20 US
D
> per month.
>
>
>
>

Looking for ideas

Hi everyone

We've got currenlty around 500 dts 2000 in production.

In order to know in what ETL processes we have Oracle connections, or FTP tasks or whatever, we did a VB6 app using dtspkg.dll which load all the properties for each DTS into Sql server tables. So that, then you could see from a specific DTS how many connections, Sql Tasks it had and so on..

How to accomplish the same with SSIS? I know, doing the same but using .Net, of course, but is there any else approximation? I am little bit concerned when we will have hundreds of them up.

Maybe 2005 is offering this feature automatically, I don't know.

Thanks in advance for your time/advices/ideas,

Try this -

Download details: SQL Server 2005 Business Intelligence Metadata Samples Toolkit
(http://www.microsoft.com/downloads/details.aspx?FamilyID=11daa4d1-196d-4f2a-b18f-891579c364f4&DisplayLang=en)

Even if the tools are visualisation is not what you want, it will have populated some tables with details of packages and objects that you can probably use. And if that is still not good enough the source code included will give you a start in writing your own tool.

Monday, February 20, 2012

Looking for duplicate and consecutive records in a table

I'm trying to get the LastcallResult = 'Not Home' result that happen more than once but that happen in consecutive order. This cursor displays the records but doesn't grab the occurance one after the other , consecutive order.

If there's a way I can modify this cursor to get the records I want or If there's another process to get the records that have the "not home' filter more than once in consecutive order .

Thanks for any suggestions in advance....

DECLARE get_lastCallresult CURSOR FOR

select count(*), lastCallresult, fn, ln from contacts

where lastCallresult = 'Not Home'

and contactType = 'referral'

and system = 'lead'

--and callback ='Not Home'

group by lastCallresult,fn, ln

having COUNT(*) > 2

OPEN get_lastCallresult

FETCH NEXT FROM get_lastCallresult

WHILE @.@.FETCH_STATUS = 0

FETCH NEXT FROM get_lastCallresult

CLOSE get_lastCallresult

DEALLOCATE get_lastCallresult

Try adding identity() to your select

Code Snippet

select count(*), lastCallresult, fn, ln from contacts, IDENTITY(int, 1,1) as seq

where lastCallresult = 'Not Home'

and contactType = 'referral'

and system = 'lead'

--and callback ='Not Home'

group by lastCallresult,fn, ln

having COUNT(*) > 2

|||

For this kind of questions, it is better to post some DDL, including constraints and indexes, sample data and expected result.

AMB

|||

Are you sure that you require a CURSOR based operation?

I suggest that you consider dropping the CURSOR and just create a #temp TABLE with the query results. Then you can cycle through the temp table if your needs require rowwise operations.

However, if you provided the code that you use on the cursor, we might be able to help you revise the code to do it all in one operation. SQL

Server is best when used for SET based operations -not 'recordset' type operations.

|||

Try adding an ORDER BY clause to your statement. Cursor operations are ordered, but you have to specify the order by for ordering to be guaranteed.

Without more info, no idea if you actually need a cursor, but look into the ROW_NUMBER() function if this is SQL Server 2005. It will let you do a lot of cool things in regards to gettting the last row.

DaleJ, this isn't valid syntax, certainly not in the FROM clause, and not in the SELECT clause without and INTO:

select count(*), lastCallresult, fn, ln

from contacts, IDENTITY(int, 1,1) as seq

where lastCallresult = 'Not Home'

and contactType = 'referral'

and system = 'lead'

--and callback ='Not Home'

group by lastCallresult,fn, ln

having COUNT(*) > 2

|||

Oops, yup, thanks Louis.

I didn't think that all the way through along with not paying attention to where I pasted...

|||

The only reason the I'm trying a cursor is because I was trying to scan thru the rows and find all those rows that are repeated and happen one after the other

i.e. ( this is not the actual table, is something to demostrate)

Name Address Phone DateCalled CalledReason

John 123 Main St 123-1234 01/02/06 Survey

John 123 Main St 123-1234 02/25/06 Survey

The column the has the called reason is the one that I want, is repeated and happen one after the oher.

|||

druiz,

The solution would be finding existing groups, of consecutive rows, with more than one element. If you are using SQL Server 2005, then you can try using the new ranking function ROW_NUMBER.

Code Snippet

create table dbo.t1 (

[Name] varchar(50),

[Address] varchar(50),

Phone char(8),

DateCalled datetime,

CalledReason varchar(25)

)

insert into dbo.t1 values('John', '123 Main St', '123-1234', '01/01/06', 'Reason 1')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '01/02/06', 'Survey')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/25/06', 'Survey')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/26/06', 'Reason 2')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/27/06', 'Reason 3')

insert into dbo.t1 values('John', '123 Main St', '123-1234', '02/28/06', 'Reason 3')

insert into dbo.t1 values('Paul', '124 Main St', '123-5678', '01/02/06', 'Reason 1')

insert into dbo.t1 values('Paul', '124 Main St', '123-5678', '01/03/06', 'Reason 2')

;with cte

(

select

[name], [address], phone, datecalled, calledreason,

row_number() over(partition by [name], [address], phone order by datecalled) -

row_number() over(partition by [name], [address], phone, calledreason order by datecalled) as grp

from

dbo.t1

)

select

[name],

[address],

phone,

calledreason,

min(datecalled) as min_datecalled,

max(datecalled) as max_datecalled

from

cte

group by

[name], [address], phone, calledreason, grp

having

count(*) > 1

drop table dbo.t1

AMB

|||

This snippet has been really helpful. I tried this same way and it gave the results I'm looking for, I'm just going to make a couple mod's to fit my tables.

Thanks

Wednesday, March 28, 2012

Loops and building comma delimited strings

looping to drop temp tables

looping to drop temp tables

looping to drop temp tables

Monday, March 26, 2012

Looping through Column to Insert

Wednesday, March 21, 2012

loop through few table in sp

Monday, March 12, 2012

Lookup including looking up on null values possible?

Lookup Arrays

Friday, March 9, 2012

looking to collect distinct date part out of datetime field

Saturday, February 25, 2012

looking for non similar orders

Looking for ideas

Monday, February 20, 2012

Looking for duplicate and consecutive records in a table

query equivalent

Blog Archive

About Me