Sql Public Role: columns

Showing posts with label columns. Show all posts

Wednesday, March 21, 2012

Purge data dynamically

I want to delete data from all the tables dynamically. I have a table that
consists of
columns:
RecID, -- Identity(1,1)
Table_Name,
Column_Name, -- Date column to use for purging in that table
NumberOfDays, -- Number of days to keep the data and delete before this day.
How can I write a dynamic SQL to include in a procedure? Thanks for your
response.Never mind. I got it. If anyone needs the script, I can post.
"David" wrote:
> I want to delete data from all the tables dynamically. I have a table that
> consists of
> columns:
> RecID, -- Identity(1,1)
> Table_Name,
> Column_Name, -- Date column to use for purging in that table
> NumberOfDays, -- Number of days to keep the data and delete before this day.
> How can I write a dynamic SQL to include in a procedure? Thanks for your
> response.
>sql

Tuesday, March 20, 2012

Pulling row values from a dataset using embedded code?

I am trying to figure out a way to pull the values of one of my report
columns once the report has been rendered to create a comma separated list of
those values to pass as a paramater to another report. I figure i will have
to do this in the code part of RS. Can anyone help me with the proper
functions and syntax to walk through the rows returned by my report and pull
the values from the column I need.
I imagine it would look like this
function GetItemList()
Dim rtnstr As String
Dim x As Interger
Set rtnstr = ""
for x = 0 to (RowNumber("DatasetName")-1)
rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
next
return rtnstr
end function
What I am stuck on is how to pull the values from the column in the
particular dataset I need and also how to determine the Scope of the dataset
to know how long th loop needs to run for.
ThanksOut of the box, you can only base a report on the following data sources: SQL
Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
into one of these categories; in other words, there is no data processing
extension that allows you to read data from one report into another report.
You should re-design your solution. What about writing a stored procedure
that writes the data to a second table, appending commas as required to
create the csv format you want? You could then report off this second table?
Charles Kangai, MCDBA, MCT
"jordang" wrote:
> I am trying to figure out a way to pull the values of one of my report
> columns once the report has been rendered to create a comma separated list of
> those values to pass as a paramater to another report. I figure i will have
> to do this in the code part of RS. Can anyone help me with the proper
> functions and syntax to walk through the rows returned by my report and pull
> the values from the column I need.
> I imagine it would look like this
> function GetItemList()
> Dim rtnstr As String
> Dim x As Interger
> Set rtnstr = ""
> for x = 0 to (RowNumber("DatasetName")-1)
> rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> next
> return rtnstr
> end function
> What I am stuck on is how to pull the values from the column in the
> particular dataset I need and also how to determine the Scope of the dataset
> to know how long th loop needs to run for.
> Thanks|||Hi Charles
My Report is already driven by a stored procedure, however, there are local
filters the user can apply to the report through the report viewer as well.
So As far as the stored proc generates the dataset, I'm good to handle those
results and create the list i need. The problem is when the user applies a
filter that is specific to that report and thus not sent back to the stored
proc. I was hoping there was a way I could avoid rewriting my stored proc to
include all my report parameters, or furthermore, writing another stored proc
to generate my list.
Thanks for your help.
"Charles Kangai" wrote:
> Out of the box, you can only base a report on the following data sources: SQL
> Server, Oracle, OLE DB and ODBC. The data output by a report does not fall
> into one of these categories; in other words, there is no data processing
> extension that allows you to read data from one report into another report.
> You should re-design your solution. What about writing a stored procedure
> that writes the data to a second table, appending commas as required to
> create the csv format you want? You could then report off this second table?
> Charles Kangai, MCDBA, MCT
>
> "jordang" wrote:
> > I am trying to figure out a way to pull the values of one of my report
> > columns once the report has been rendered to create a comma separated list of
> > those values to pass as a paramater to another report. I figure i will have
> > to do this in the code part of RS. Can anyone help me with the proper
> > functions and syntax to walk through the rows returned by my report and pull
> > the values from the column I need.
> >
> > I imagine it would look like this
> >
> > function GetItemList()
> > Dim rtnstr As String
> > Dim x As Interger
> > Set rtnstr = ""
> > for x = 0 to (RowNumber("DatasetName")-1)
> > rtnstr = rtnstr & DatasetName(x).ColumnName.Value & ","
> > next
> > return rtnstr
> > end function
> >
> > What I am stuck on is how to pull the values from the column in the
> > particular dataset I need and also how to determine the Scope of the dataset
> > to know how long th loop needs to run for.
> >
> > Thanks

Pulling out table and column descriptions

When I run the following query:
Select * From Information_Schema.columns Where TABLE_NAME = 'Answers'
I see that, for my table "Answers", I have 9 rows returned, showing each
field in my table.
The sysobjects query shows where I can get the Id for Answers and use it for
getting the field descriptions from sysproperties (along with a table
description):
Select * From sysobjects Where name = 'Answers'
Select * From sysproperties Where id = 859150106
The trouble I am having is I want to join the the column and sysproperties
tables together, but I get back 38 rows showing a lot of duplicate data when
I only want the 9 rows that define my Answers table. Can someone show and
explain to me what I am doing wrong? Below is the query I tried that returne
d
38 rows instead of the 9 I wanted. Thank you.
Select isc.table_name, isc.column_name, sp.value
From sysproperties sp
Join Information_Schema.columns isc On sp.smallid = isc.ordinal_position
Where isc.table_name = 'Answers'Hello, Mike
Try something like this:
SELECT c.name as ColumnName, p.value as Description
FROM syscolumns c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysproperties p
ON p.smallid=c.colid AND p.id=o.id AND p.name='MS_Description'
WHERE o.name='YourTable' ORDER BY c.colid
Razvan|||Exactly what I wanted. Thanks.
"Razvan Socol" wrote:

> Hello, Mike
> Try something like this:
> SELECT c.name as ColumnName, p.value as Description
> FROM syscolumns c
> INNER JOIN sysobjects o ON c.id=o.id
> LEFT JOIN sysproperties p
> ON p.smallid=c.colid AND p.id=o.id AND p.name='MS_Description'
> WHERE o.name='YourTable' ORDER BY c.colid
> Razvan
>

Pulling data from tables using ColumnID

My problem is I need to loop through columns in a table and refer to each column using its ColumnID, all this I can do, I can even pull back the Cloumn Name from the system tables, but I can't find a way of pulling the data from this into a variable...

The code below will give me the data I need, but I don't know how to put the result from the exec statement into a variable?

select @.sql = 'select ' + name + ' from Test'
from syscolumns
where colid = @.i and id = object_id('Test')
exec(@.sql)

Please help

thanks,

ConanHi,

you might use
exec sp_executesql <cmd>, <paramlist>, <parameters>

this will allow you to get the result from some cmd like
'select @.Data='+@.columname+' from '+@.tablename

have a look into bol|||Hi,

Unfortunately this didn't work...

It has the result of only returning the column name again, rather than its data, have been stumped now on this little thing for a couple of days. If you can help please do, thanks,

DECLARE @.i int
DECLARE @.colname nvarchar(200)
SET @.i = 5

--Gets the column name of the column we want using its Column ID
select @.colname = name from syscolumns where colid = @.i and id = object_id('Test')

--Why does the below merely return the column name rather than its data
select @.colname from test

--The below ruturns the correct value, but I can't/don't know how to store it.
DECLARE @.sql nvarchar(1000)
DECLARE @.data int
select @.sql = 'select ' + @.colname + ' from Test'
EXEC sp_executesql @.sql
go

thanks,

Conan|||Hi,

as i said before, you can use sp_executesql. read bol for details...
you have to distinguish between vars and their values. your "select @.colname from test" will select whatever is the value of colname, regardless the table you specify! even without a table name the cmd will show your var value.

declare @.Table sysname, @.ID int, @.ColName nvarchar(200), @.CMD nvarchar(1000), @.ColData varchar(20)

select @.Table='test', @.ID=5

-- get column name for table / colid
select @.ColName=name from syscolumns where colid=@.ID and id= object_id(@.Table)

-- build up cmd string for pulling data
select @.CMD='select @.Param = '+@.ColName+' from '+@.Table

-- show what we have so far
print '@.Table = >'+@.Table+'<'
print '@.COLNAME = >'+@.ColName+'<'
print '@.CMD= >'+@.CMD+'<'

-- execute the cmd string pulling data from @.colname into @.ColData via @.Param
exec sp_executesql @.CMD, N'@.Param varchar(20) output', @.Param = @.ColData output

-- here we are
print '@.COLDATA= >'+@.COLDATA+'<'

Friday, March 9, 2012

Pull data from rows to make columns

I have a table with three columns:
AcctNbr, Type, CodeValue
Listed below is an example of the database.
AcctNbr Type CodeValue
1 MAILCODE 99
2 MAILCODE 99
3 MAILCODE 99
4 MAILCODE 90
4 MAILCODE 99
4 SEG1 O
5 MAILCODE 99
6 MAILCODE 99
7 MAILCODE 99
8 MAILCODE 99
9 MAILCODE 99
10 MAILCODE 90
11 MAILCODE 99
12 MAILCODE 99
13 MAILCODE 99
14 MAILCODE 99
15 LIST DS1
15 MAILCODE 99
There are multiple Type's for some AcctNbr's and what I want to do is
run a query on the database so that if the AcctNbr has multiple Type's
and CodesValue's it takes them and creates new columns like so:
AcctNbr MailCode_90 MailCode_99 SEG1
4 90 99 O
So on and so forth. There are multiple Type's and multiple codes that
I need to do this with for each account number. If someone could give
me a base code to try I could start somewhere. I am an SQL novice.
Thanks.
JoshGoogle for pivot in t-sql
MC
"cypherus" <fbsdguy@.gmail.com> wrote in message
news:9bc69bb7-1996-4d07-a0c2-fdace73e4c4f@.y21g2000hsf.googlegroups.com...
>I have a table with three columns:
> AcctNbr, Type, CodeValue
> Listed below is an example of the database.
> AcctNbr Type CodeValue
> 1 MAILCODE 99
> 2 MAILCODE 99
> 3 MAILCODE 99
> 4 MAILCODE 90
> 4 MAILCODE 99
> 4 SEG1 O
> 5 MAILCODE 99
> 6 MAILCODE 99
> 7 MAILCODE 99
> 8 MAILCODE 99
> 9 MAILCODE 99
> 10 MAILCODE 90
> 11 MAILCODE 99
> 12 MAILCODE 99
> 13 MAILCODE 99
> 14 MAILCODE 99
> 15 LIST DS1
> 15 MAILCODE 99
> There are multiple Type's for some AcctNbr's and what I want to do is
> run a query on the database so that if the AcctNbr has multiple Type's
> and CodesValue's it takes them and creates new columns like so:
> AcctNbr MailCode_90 MailCode_99 SEG1
> 4 90 99 O
> So on and so forth. There are multiple Type's and multiple codes that
> I need to do this with for each account number. If someone could give
> me a base code to try I could start somewhere. I am an SQL novice.
> Thanks.
> Josh|||Your best bet is to do this with the Rac utility. It will minimize the ugly
sql coding necessary. If your interested post back and I'll hook you up with
the Rac execute statement for this.
www.rac4sql.net
www.beyondsql.blogspot.com|||On Apr 10, 5:56 am, "steve dassin" <stevenos...@.rac4sql.net> wrote:
> Your best bet is to do this with the Rac utility. It will minimize the ugly
> sql coding necessary. If your interested post back and I'll hook you up with
> the Rac execute statement for this.www.rac4sql.net
> www.beyondsql.blogspot.com
I don't really want to download any new programs just for that. I did
post to a couple other boards and somebody gave me some test code that
throws up errors. Can someone either help me fix the errors or help
me with some new test code? Here's the code that I was given:
SELECT AcctNbr FROM [KMAi_V8].[dbo].[A04CODE],
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='90' THEN CodeValue ELSE
NULL END) MailCode_90,
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='99' THEN CodeValue ELSE
NULL END) MailCode_99,
MAX(CASE WHEN Type='SEG1' AND CodeValue='O' THEN Type ELSE NULL END)
SEG1
FROM Acct
WHERE AcctNbr IN (SELECT AcctNbr FROM Acct GROUP BY AcctNbr HAVING
COUNT(DISTINCT(Type+CodeValue))>1)
GROUP BY AcctNbr
And it tosses up this error:
TITLE: SQL Server Import and Export Wizard
--
The statement could not be parsed.
--
ADDITIONAL INFORMATION:
Deferred prepare could not be completed.
Statement(s) could not be prepared.
Incorrect syntax near the keyword 'GROUP'.
Incorrect syntax near the keyword 'CASE'. (Microsoft SQL Native
Client)
--
BUTTONS:
OK
--
Any ideas?
Josh|||Answer here: http://forums.devshed.com/showthread.php?p=2022538&mode=linear#post2022538
SELECT AcctNbr,
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='90' THEN CodeValue ELSE
NULL END) MailCode_90,
MAX(CASE WHEN Type='MAILCODE' AND CodeValue='99' THEN CodeValue ELSE
NULL END) MailCode_99,
MAX(CASE WHEN Type='SEG1' AND CodeValue='O' THEN Type ELSE NULL END)
SEG1
FROM TableNm
WHERE AcctNbr IN (SELECT AcctNbr FROM TableNm GROUP BY AcctNbr HAVING
COUNT(DISTINCT(Type+CodeValue))>1)
GROUP BY AcctNbr
On Apr 16, 4:26 pm, cypherus <fbsd...@.gmail.com> wrote:
> On Apr 10, 5:56 am, "steve dassin" <stevenos...@.rac4sql.net> wrote:
> > Your best bet is to do this with the Rac utility. It will minimize the ugly
> > sql coding necessary. If your interested post back and I'll hook you up with
> > the Rac execute statement for this.www.rac4sql.net
> >www.beyondsql.blogspot.com
> I don't really want to download any new programs just for that. I did
> post to a couple other boards and somebody gave me some test code that
> throws up errors. Can someone either help me fix the errors or help
> me with some new test code? Here's the code that I was given:
> SELECT AcctNbr FROM [KMAi_V8].[dbo].[A04CODE],
> MAX(CASE WHEN Type='MAILCODE' AND CodeValue='90' THEN CodeValue ELSE
> NULL END) MailCode_90,
> MAX(CASE WHEN Type='MAILCODE' AND CodeValue='99' THEN CodeValue ELSE
> NULL END) MailCode_99,
> MAX(CASE WHEN Type='SEG1' AND CodeValue='O' THEN Type ELSE NULL END)
> SEG1
> FROM Acct
> WHERE AcctNbr IN (SELECT AcctNbr FROM Acct GROUP BY AcctNbr HAVING
> COUNT(DISTINCT(Type+CodeValue))>1)
> GROUP BY AcctNbr
> And it tosses up this error:
> TITLE: SQL Server Import and Export Wizard
> --
> The statement could not be parsed.
> --
> ADDITIONAL INFORMATION:
> Deferred prepare could not be completed.
> Statement(s) could not be prepared.
> Incorrect syntax near the keyword 'GROUP'.
> Incorrect syntax near the keyword 'CASE'. (Microsoft SQL Native
> Client)
> --
> BUTTONS:
> OK
> --
> Any ideas?
> Josh

Wednesday, March 21, 2012

Purge data dynamically

Tuesday, March 20, 2012

Pulling row values from a dataset using embedded code?

Pulling out table and column descriptions

Pulling data from tables using ColumnID

Friday, March 9, 2012

Pull data from rows to make columns

Pull data from rows to make columns

Sql Public Role

Blog Archive

About Me