Showing posts with label records. Show all posts
Showing posts with label records. Show all posts

Monday, March 26, 2012

How to use the Top N in Filters

Hi,
I have created one report which uses the stored proc. it returns the data
more than 100 rows but i want to show only top 20 records in the graph( I
have to use that stored proc only. not possible to create another stored
proc)..
How can i achieve that? I found one property at filters sections i.e Top
N. Could any one let me know the usage of TOP N.
Regards,
SriHope you have used table control, just right click for properties. select the
filter tab In the expressions select the first column of your dataset. Select
the "Top N" from the operator. now in the value just type =20. You get first
20 rows.
Amarnath
"Sriman" wrote:
> Hi,
> I have created one report which uses the stored proc. it returns the data
> more than 100 rows but i want to show only top 20 records in the graph( I
> have to use that stored proc only. not possible to create another stored
> proc)..
> How can i achieve that? I found one property at filters sections i.e Top
> N. Could any one let me know the usage of TOP N.
> Regards,
> Sri

Monday, March 19, 2012

How to use procs records

example:
select * from sp_who
but it cannot use in QATry:

EXECUTE sp_who|||Stored procedures require you to "execute" using either of the following formats:

exec sp_who
execute sp_who

in query analyzer if you are only executing a stored procedure this is not necessary - you can just use sp_who.

If you have not installed sql server books online, you can download it from ms.|||Originally posted by rnealejr
Stored procedures require you to "execute" using either of the following formats:

exec sp_who
execute sp_who

in query analyzer if you are only executing a stored procedure this is not necessary - you can just use sp_who.

If you have not installed sql server books online, you can download it from ms.
I am a chinese.I am not good at english.
I want use only a column from sp_who .
because I create some procedures,but some query only need some part of the procedures.
example:
select spid,ecid,status from sp_who
because I only need the three columns

thanks for answer my question!!!|||If you know a little chinese please you refor this:
(I)(want)(use)洢(procedure)(retu rn)?(records)(I)?_(already)(have) (tow)洢(procedures)?(records) (but)(I)(now)(want)?(use)select (records)?join

Monday, March 12, 2012

How to use nested CASE statemenet?

Hi all,

I have a Sproc. to get records and I was looking to use a nested CASE statement

The thing is that first I want to check the availability of single quote in my where statement and replace it with double quote. The second case is to check for a given condition and make some calculation. The following statement is not running. So would you mind helping me in this regard?

Thank you in advance:

(CASE WHEN CHARINDEX(''ca.caCode'',''' + replace(@.WhereStmt, char(39), char(39) + char(39)) + ''') > 0 THEN ca.ClusterAmount ELSE dbo.vwGrantsMaster.CurrentValueTotalCost END CASE WHEN dbo.vwGrantsMaster.StatusCode IN (3) THEN (IsNull(dbo.vwGrantsMaster.CurrentValueTotalCost, 0))-(IsNull(dbo.vwGrantsMaster.AwardedTotalCostAmount,0)) ELSE dbo.vwGrantsMaster.CurrentValueTotalCost END) AS CurrentValueTotalCost,

I'm confused about what you are attempting to do here. Is this building a command string? I don't see a need to 'nest' CASE statements. You may have multiple actions that need to happen separately.

To replace a single quote with two single quotes (I don't think that you really mean 'double quote'.) use the following:

Code Snippet


DECLARE @.MyString varchar(50)
SET @.MyString = 'This isn''t a test.'


SELECT replace( @.MyString, '''', '''''' )

To put a single quote in a string, which is enclosed in single quotes, you have to 'escape' the single quote. That is done by doubling it up. So to check for a single quote in a string, you look for '''' (4 single quotes, the doubled up one, inside a pair enclosing a string.) And then you just replace that single quote (doubled up) with two single quotes (doubled up), inside a pair eclosing a string. It's mind boggling at first, but really not that difficult to understand.

Why bother to first check to see if a single quote exists, just do the REPLACE(), and if there are single quotes, they are doubled up, and if not, nothing happens.

Besides that, in order to help you with this 'mess', you are need to provide the entire command string building process. I can't make any sense of this...

Friday, March 9, 2012

How to use For Each loop to Read one record and insert the same into destination

Hi,

My procedure to implement a task is like this

I will be using execute SQL task to fetch the records from source,after this wanna use For each loop to access each record one at a time,perform some trnsformations and insert that record into destination.

Help me in accessing the data stored in the Variable(SQL task) in Dataflow task of foreach loop.

Why do you need to do this one record at a time - is the transformation different for each record ?

Can you explain the transformations you need to do?

|||

In the Exec SQL task Editor:

1. General Tab - Result should be of type Full Resultset.

2. ResultSet tab - Set Result Name =0 , Variable Name= your variable of type object (you can create a new variable from the Variable Name dropdown itself, or in the main Variables Pane). Variable type =Object.

http://technet.microsoft.com/en-us/library/ms141689.aspx has details.

3. Add a For Each Loop Container.

4. In the forEach Loop editor, Collection Tab, Set Enumerator=For Each ADO Enumerator

5. In the Variable Mapping, map your column variables to index 0,1,2 (assuming you have three columns in your resultset). You can create your own variables for each column. For each iteration i.e. for each row of the resultset, these variables will hold the value of the respective columns for the current row.

6. Perform your transformations

7. Use a OLEDB Command destination to insert/update your row to the table.

HTH

Kar

|||The requriment from is in that way,I have proposed for a bulk process.But m not sure if i will get the approval.

|||

The index value to be set are in tenh order of retreival from source.

How to access those variables inTansformations can u be specific using Derived column.

|||

You'll get much better performance if you use an OLE DB Source inside a data flow, rather than iterating through a resultset with a For Each. You might want to revisit your design, or post more specifics so we can help you a little more.

|||

Hi,

I will be having a SQL query to retreive data from source,incorporated in Execute SQL task.

Result will be stored in a Result Set.

After this inside a ForEach loop i have to access single record contained in the result set apply some transformations and send the data into destination.

So my requirement is to process one row of data at a time and whole data processing must take place in a loop.

My problem is,How do i process this data in Data Flow task inside For Each loop.

|||

Hi,

You can achive this by this method(i have tried it out)

Use source query and populate the Recordset destination(variable must be of type object)

For each loop(enumerte through adonet)

Based on number of columns declare those many variables and access them in a script componet(source)

and output those variables as columns,

Do the transformations and insert into destination finally.

Any queries post it here.I will help u out.

|||Sorry, but I'm still not understanding why you have to loop through the recordset rather than using an OLEDB Source. However, if it works for you, stick with it.

Friday, February 24, 2012

How to use cartesian join to get multiple records

Hello !
How to use correctly catesian join to get follwing result
Source table
Col1, Col2, KOKKU
1 1 4
1 2 2
1 3 1
And result must be (the column KOKKU defines how much every row must by
multiplied)
Col1, Col2
1 1
1 1
1 1
1 1
1 2
1 2
1 3
There must be come cartesian(CROSS) join solution
Kuido
Message posted via http://www.webservertalk.comI would use a Numbers table for this:
SELECT col1, col2
FROM YourTable AS T
INNER JOIN Numbers AS N
ON N.num BETWEEN 1 AND T.kokku
(BTW an INNER JOIN is a subset of a Cartesian Product)
There are plenty of ways to build a numbers table, but since you only ever
have to do it once the method isn't very important. I actually prefer to use
a loop:
CREATE TABLE Numbers
(num INTEGER PRIMARY KEY)
INSERT INTO Numbers VALUES (1)
WHILE (SELECT MAX(num) FROM Numbers)<65536
INSERT INTO Numbers (num)
SELECT num+(SELECT MAX(num) FROM Numbers)
FROM Numbers
Some other suggestions here:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
David Portas
SQL Server MVP
--

How to use a Wildcard with the value for the parameter?

I have a report that selects certain records based on what the user enters for the parameter but I'm trying to allow the user to enter a wildcard and show the relevant records. I used like and had the user add an * or even a % but it still returns nothing. Records are returned only when the user enters the full value with no wildcards. Help.

SELECT DISTINCT
PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID, PDFBM.REQUIRED_QTY, ICFPM.PART_DESC, ICFPM.DWG_REV, ICFPM.PENDING_ECN,
dbo.ICFSS.STORES_CODE, dbo.ICFSS.STOCK_LOCATION
FROM dbo.ICFPM ICFPM INNER JOIN
dbo.PDFBM PDFBM ON ICFPM.PART_ID = PDFBM.ASSEMBLY_ID INNER JOIN
dbo.ICFSS ON ICFPM.PART_ID = dbo.ICFSS.PART_ID
WHERE (PDFBM.COMPONENT_ID LIKE @.componentid) AND (PDFBM.ASSEMBLY_ID LIKE '350%')
ORDER BY PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID

A

I'm not sure but try converting the query to a dynamic query:

="SELECT DISTINCT
PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID, PDFBM.REQUIRED_QTY, ICFPM.PART_DESC, ICFPM.DWG_REV, ICFPM.PENDING_ECN,
dbo.ICFSS.STORES_CODE, dbo.ICFSS.STOCK_LOCATION
FROM dbo.ICFPM ICFPM INNER JOIN
dbo.PDFBM PDFBM ON ICFPM.PART_ID = PDFBM.ASSEMBLY_ID INNER JOIN
dbo.ICFSS ON ICFPM.PART_ID = dbo.ICFSS.PART_ID
WHERE (PDFBM.COMPONENT_ID LIKE '" + Parameters!componentid.Value + "') AND (PDFBM.ASSEMBLY_ID LIKE '350%')
ORDER BY PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID"

Regards,

Telmo Moreira

|||

try:

LIKE @.parameter + '%')

drop the user adding the wildcard char

Sunday, February 19, 2012

How to use a parameter to return all records

I am using the example from the Microsoft Official Course 2030A.
I want the option for a user to select valeu from a drop down to return all records or to choose individual ones from the multi-value check box.

My query is taking forever and as you see I just want the top ten

Select TOP 10 * from PRH_EOB

WHERE (MemberId = @.MemberId OR @.MemberId = 'ALL')

and (disenr_st = @.Disenroll or @.Disenroll = 'ALL')

and (LOB in (Select * from SplitList(',',@.LOB) as ListItem) or @.LOB = 'ALL')

and (Groupid in (Select * from SplitList(',',@.GroupList) as ListItem) or @.Grouplist = 'ALL')

and paydate between @.From and @.TO

order by Slastname, sFirstname, claimid, linenum

Thanks,
Phil

Come to find out, I have to put an index hint in the query to get it to work.

ex

Select * from PRH_EOB (INDEX = IX_PayDate)

WHERE (MemberId = @.MemberId OR @.MemberId = 'ALL')

and (disenr_st = @.Disenroll or @.Disenroll = 'ALL')

and (LOB in (Select * from SplitList(',',@.LOB) as ListItem) or @.LOB = 'ALL')

and (Groupid in (Select * from SplitList(',',@.GroupList) as ListItem) or @.Grouplist = 'ALL')

and paydate between @.From and @.TO

order by Slastname, sFirstname, claimid, linenum