Dataset Manipulation

Mar 1, 2007

Have an XML file that i load in to a Dataset. works fine, it builds its own scheme perfectly.

I loop through that data to load a check list which also works wonderfully.

 two questions based on that.

1) can i add a column after the fact? I want to basically update the the info in the dataset to store if the record was checked in the check list. if not, i can manipulate one of the already defined columns, but i would rather not.

2) what is the best way to update data with in the dataset? Never really done anything but pull data from one. how do i locate the correct row to update ("select name from tbl where name = " + checklist.items[index].tostring(); update row)

 

sorry for the fairly basic question. i appreciate the help.

 Justin

 

View 2 Replies


ADVERTISEMENT

SQL Server 2008 :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 26, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 0 Replies View Related

Integration Services :: Perform Lookup On Large Dataset Based On A Small Dataset

Oct 1, 2015

I have a small number of rows in a dataset, Table 1.  There is a CLOB on a large dataset, Table 2.  They join on a PK.  I would like to retrieve this CLOB and add it to the data flow for Table1.  In short I want to emulate the following:

Table 1:  Small table without CLOB, 10 rows. 
Table 2: Large table with CLOB, 10,000,000 rows

select CLOB
from table2
where pk = (select pk from table1)

I want this to return the CLOBs for the small number of rows in Table 1.  The PK is indexed obviously so it should be a fast look up.

Table 1 and Table 2 live on different Oracle databases.  How do I perform this operation efficiently in SSIS?  It seems the Lookup and Merge Join wont do this.

View 2 Replies View Related

Reporting Services :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 27, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.

I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 3 Replies View Related

How Can I Use SQL Reporting Services To Get A Dynamic Dataset From Another Web Service As My Reports Dataset?

May 21, 2007

I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking

View 2 Replies View Related

Listing Datasets In Report (dataset Name, Dataset's Commands)

Oct 12, 2007



Is there any way to display this information in the report?

Thanks

View 3 Replies View Related

Dataset.Tables.Count=0 Where There Are 2 Rows In The Dataset.

May 7, 2008

Hi,
I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0.
if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in
if @Status=0 even if I pass @status=0. What am I doing wrong?
Any help is appreciated.


ALTER PROCEDURE [dbo].[MyStorProc]

(

@Status smallint,

@RowCount int = NULL,

@FacilityId numeric(10,0) = NULL,

@QueueID numeric (10,0)= NULL,

@VendorId numeric(10, 0) = NULL

)

AS

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF



If @Status = 0

BEGIN

SELECT ......
END
If @Status = 1
BEGIN
SELECT......
END



View 4 Replies View Related

Row Manipulation

May 29, 2008

col1 col2 col3
1IN NULL
2FOR 1
3Small 4
4World 1
5Hj 1
6NJ 1
7Welcome 1
8So 4
9We 1
10Are 1
11Resp 3
12OIJIOJPOJPJO7
13OIJOIJ 1
14LKJ 2
15IJ 1

Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1
for eg., my 1st set would be
1IN NULL
2FOR1
2nd set would look something like this
3Small 4
4World1
5Hj 1
6NJ 1
7Welcome1
3rd set
8So4
9We1
10Are1
4th Set
12OIJIOJPOJPJO7
13OIJOIJ1

and the last set
14LKJ2
15IJ1

Any help is appreciated!!

View 1 Replies View Related

How To Transfer Data From One Dataset To Other Dataset

Apr 11, 2008

i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer.
but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
 

View 4 Replies View Related

Filter One One Dataset With Values In Another Dataset?

Dec 19, 2006

Hi,

I have two datasets in my report, D1 and D2.

D1 is a list of classes with classid and title

D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.

I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.

Is there any way to do this in RS2005?

View 2 Replies View Related

String Manipulation

Apr 2, 2001

I am totaly confused please help

I am trying to change a tring of 7 characters of the format "XXYZZZZ" to be
"XX0YZZZZ" I wonder if any body has any idea

Also how can get an out of a dattime field in the format of DDMMYYYY and converted into text.

You help is highly appreciated

View 1 Replies View Related

STRING MANIPULATION

May 15, 2001

Hi!

I am using the follwing query for extracting the country name and city in a COLUMN [Destination Name] in Destinations table. The Data in the table looks like:

CANADA - Toronto
United States- ARIZONA
France
Argentina
United States (USA)- ARIZONA
........
........

The folowing query is producing the required results upto soem extent but without using -1 in subtracting the one value of CHARINDEX. The error is:

Server: Msg 536, Level 16, State 4, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

QUERY
-----
select

Left(
Destinations.[Destination Name],
charindex("-", Destinations.[Destination Name])-1
)
as test
into temp2
from destinations

CAN ANYBODY HELP me in extracting the city an dcoutry name. I also want to delete the name in () like (USA).

View 1 Replies View Related

Record Manipulation

Feb 3, 2003

Hi,

I have a table that has about 12 fields and 700000 records. My client is going to send me 4 fields out of the 12 fields (1 of them will be primary key) in a text format. What is the best way I can get them updated in the table?

Please help!!

Thanks!!

View 1 Replies View Related

String Manipulation ?

Nov 30, 2004

I was given a script that was supposed to take a name field that was separated by commas and normalize it into last, first and middle name. My data looks like below in one fieldname called longname

crab,mike,Allen
Lota Weilly,Eric,M

My script to do this looks like

update ailoca
set last_name = substring (longname, 1, patindex( '%,%' , longname) -1 ),
first_name = substring (longname, patindex( '%,%' , longname) + 1, patindex( '% %', longname)-patindex( '%,%' , longname)),
middle_name = substring (longname, patindex( '% %', longname) + 1, len(longname)-patindex( '%,%' , longname))

My problem is that some people actually have 2 last names, not hyphenated, but 2. Whenever I have 2 names I get the following error

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

It seems to be related to the first name, I can comment out that update and it works

Thanks
Thanks

View 11 Replies View Related

String Manipulation

Jul 24, 2007

Hi All,

I am trying to break the string that looks like this


2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.

into three separate strings to look like this

col1 col2 col3
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.


I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.

Any help is appreciated.

Thanks.

View 2 Replies View Related

String Manipulation

Nov 13, 2005

Field1 = Dominguez Public Transport Division 03 9320 4326

how do i remove these strings in T-SQL

Field1 = Dominguez
Field2 = Public Transport Division
Field3 = 03 9320 4326

View 3 Replies View Related

String Manipulation

Jul 24, 2007

Hi All,

I am trying to break the string that looks like this


2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.

into three separate strings to look like this

col1 col2 col3
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-06 07:36:21.28 server All rights reserved.
2007-05-06 07:36:21.28 server Server Process ID is 292.


I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.

Any help is appreciated.

Thanks.

View 3 Replies View Related

Text Manipulation

Apr 30, 2008

Hi, is there a way i can replace multiple white spaces & tabs into one , on a TEXT column ?

I am using replace function and trying to store the result into a varchar variable and the TEXT being truncated after 255 charecters.

for example, I tried this :

declare @str varchar(4000)
select @str = replace(cast(textcolumn as varchar(4000)), ' ', NULL)

I understand t-SQL dml statement(s)
will automatically truncate after 255 chars in a long string. But, seeking for other options here pls.

thanks for ur help..

View 6 Replies View Related

String Manipulation

May 3, 2008

Sir

How i m Manipulate String , I have
(ABC,XYZ,EFG) this string
know i want to break this string 1 by 1 and modify and then rearrange in same form ang Get

like (ABCWE,XYZRT,EF)
pls help me out

Yaman

View 2 Replies View Related

String Manipulation

May 29, 2008

Hi
I have a field which is a file path
like
'C:avde8393948.txt'

I want to separate them into folder and filename
now the filename is always the same length,
so i can use RIGHT to get the filename,
but i prefer a method that from the right detects the
1st occurance of and everything after is the filename,

View 2 Replies View Related

Using Cursor For Manipulation

Oct 17, 2014

ALTER procedure [dbo].[sp_debit_credit]

as begin
SET NOCOUNT ON
DECLARE @DEBIT_ID INT
DECLARE @CREDIT_ID INT
DECLARE @servicedamount decimal(18,2)
declare @bal_Serviced_Amt decimal(18,2)

[Code] ....

table is like this
------------------------
DEBIT.ID, DEBIT.ACCOUNT_NO,DEBIT.SERVICED_AMT,CREDIT.ID,CREDIT.TRAN_AMT,CREDIT.SERVICED_FLAG,credit.Serviced_Amt
1456050866223983.0006418110.000300000.00
1456050866223983.0006419110.000500000.00
1456050866223983.0006447110.0002800000.00
1456050866223983.0006510110.0003100303.00

[Code] ....

I need result, I have to subtract

(DEBIT.SERVICED_amt - CREDIT.TRAN_AMT) and set CREDIT.SERVICED_FLAG =1 ,
if DEBIT.SERVICED_amt = CREDIT.TRAN_AMT then set debit.SERVICED_FLAG =1

Using with cursor. It will check row by row , i have written stored procedure , but flag is not updated .

View 1 Replies View Related

Date Manipulation

Jul 20, 2007

I have two tables TableA and TableB as shown in the example below. I will have to Update TableA based on the data in TableB.
If a member in TableB has an EmployerId different from the one in TableA where the EffectiveDate in TableB between the EffectiveDate and ExpirationDate in TableA, then it should void the row in TableA and create rows as shown in the example below (Please refer to the 1st record and the last three records).

I would like to get some suggestions on how to do this efficiently. I am not looking for queries, but I need some ideas...

Thanks in advance.

TableA

MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 0
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0


TableB

MemberId EmployerId EffectiveDate
12345 444 4/1/2006


TargetTable (TableA Updated)

MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 1
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0
12345 111 1/1/2006 3/31/2006 0
12345 444 4/1/2006 4/30/2006 0
12345 111 5/1/2006 10/31/2006 0



Thanks
Suresh

View 4 Replies View Related

Date Manipulation

Jul 23, 2007

I have a table with one column as a date field in the form of mm/dd/yyyy and I would like to create a new column on a report as 'Days Open' using the column with random dates in the past and subtracting it from the current system date. Can anyone provide any assistance. I'm very new to SQL Server. I know in Oracle u can use 'sysdate'

View 2 Replies View Related

String Manipulation

Aug 29, 2007

Hi If i have a string like :

AX4030303022

in one field and need to split it into

AX 030303022

I can use LEFT(field_name,2) for the 1st one, but
what can i use for the 2nd ?
and how can i deal with NULLS ?
if its NULL i want a blank space..

View 16 Replies View Related

Data Manipulation

Dec 26, 2007

Hello,

I have to perform some data manipulation based on ID1, ID2 with respect to Effective dates (below represented as effdt)
Source Code:

create table #source (id1 int, id2 int, effdt datetime)
insert into #source values (111, 123, '1/1/2007')
insert into #source values (111, 123, '3/1/2007')
insert into #source values (111, 123, '4/1/2007')
insert into #source values (111, 124, '5/1/2007')
insert into #source values (111, 125, '6/1/2007')
insert into #source values (111, 123, '7/1/2007')

Source Code Data:

ID1 ID2 Effdt
111 123 1/1/2007
111 123 3/1/2007
111 123 4/1/2007
111 124 5/1/2007
111 125 6/1/2007
111 123 7/1/2007

Target Data should look like:

ID1 ID2 Effdt Expdt
111 123 1/1/2007 4/30/2007
111 124 5/1/2007 5/31/2007
111 125 6/1/2007 6/30/2007
111 123 7/1/2007 7/31/2007


The logic is like the min(effdt) as effdt, min(effdt) - 1 as expdt of the next id2 from the source. Nut the problem comes when the same ID2 comes later after a different id2, so just a group by on id1, id2 does not suffice.
If it is the last record then the expdt is that month's end date.
Can someone throw me some light on what kind of logic I should be using to accomplish this.

Thanks in advance.
Greg

View 2 Replies View Related

Binary Value Manipulation

May 1, 2007

I would like to drop the leading 0x on a binary value so I can do abitwise operation.Here is simplified code:select right(0x88186000,8)I expected to get back 88186000, this was not the case. The commandreturned some wierd characters.Am I missing something?

View 3 Replies View Related

String Manipulation SQL

Jul 20, 2005

We have some rows that we need to do some tricky string manipulationon.We have a UserID column which has userid entries in the formatfirstname.lastname and i need to change each entry tolastname.firstnameCan this be done by some script?Thanks so much for your help.Sid

View 3 Replies View Related

Date Manipulation

Jul 17, 2006

I need to to get the result of the function GETDATE and converted to a simpler "mm/dd/yyyy" format in order to compare the results to another date in a table. In ACCESS the function DATE returns the format of 'mm/dd/yyyy' since I need to work with date ranges without a need for this application 'HH:MM:SS'

I have try 'TRANSFORM(GETDATE,'mm/dd/yyyy') but I keep getting errors.

I am not sure what I am doing wrong? Any help is appreciated since I need to work in SQL Server 2000.



Gratefull

Neil

View 3 Replies View Related

String Manipulation

Aug 13, 2007

Hi all,

I am having problem in string manipulation in SSIS - Derived Column Transformation.
I am trying to extract the OU names from Active Directory objects into a SQL table.

Assume that a distinguish name (DN) of an object as below:
CN=John, Doe,OU=Users,OU=SubOU,OU=ParentOU,DC=domain,DC=company,DC=com

How can I manipulate the above string so that I get:
ParentOU/SubOU/Users

Thanks in advance. Help is much appreciated!

View 8 Replies View Related

Row Manipulation TSQL

May 29, 2008

col1 col2 col3
1 IN NULL
2 FOR 1
3 Small 4
4 World 1
5 Hj 1
6 NJ 1
7 Welcome 1
8 So 4
9 We 1
10 Are 1
11 Resp 3
12 OIJIOJPOJPJO 7
13 OIJOIJ 1
14 LKJ 2
15 IJ 1

Is there any way, to query the above table which returns me all the 1's inside col3 until it finds any other number than 1 + the previous row preceding the 1

for eg., my 1st set would be
1 IN NULL
2 FOR 1

2nd set would look something like this
3 Small 4
4 World 1
5 Hj 1
6 NJ 1
7 Welcome 1

3rd set
8 So 4
9 We 1
10 Are 1

4th Set
12 OIJIOJPOJPJO 7
13 OIJOIJ 1

and the last set
14 LKJ 2
15 IJ 1

Any help is appreciated!!

View 1 Replies View Related

DATA MANIPULATION HELP

May 16, 2008


Table1:

Id column1
--------------------
251 a
251 b
251 c

What I need to accomplish:
Table2:

Id column1 column2 column3
--------------------------------------------------------
251 a b c


Using SSIS i need to extract data from table1 and combine records with same id into one record in order to map to my destination table2

View 5 Replies View Related

Manipulation In The Front End

Sep 18, 2007



Hi

I have a scenario while creating a report where I need to maintain the same number of rows for a column, even if the data grows or shrinks for that column

example

Each Scenario is grouped by ID

Scenario 1




columnA

Row1 X X
Row2 X
Row3 X
Row4 XYZ



Scenario 2




columnA

Row1 XA
Row2 XYZ


In both scenario's I need to control from front end

the number of row should remain 4

I am grouping by row field say Type

This field Type varies for each ID group

View 1 Replies View Related

Date Manipulation

Feb 20, 2008



I have a column(dts) in the database that will retun the date in this format:
2008-02-19 15:10:59:840

I would like it to ve convert it the this format:
Feb -19-08
or
Feb -19-2008


Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved