Problems in SQL, Updating many rows at once

Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNameSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so. Still takes a long time. I then notice what is happening. The SQL is taking the SOUNDEX of the LastName of the first record, apply it to ALL the records, then taking the SOUNDEX of the LastName of the second records, then updating it to ALL the records, etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!
> It takes forever. I start tweeking the SQL to commit every 500 records or
so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.
>
How did you notice this? This is really strange, I've never heard of
something like this.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problems in SQL, Updating many rows at once

Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNam
eSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so
. Still takes a long time. I then notice what is happening. The SQL is takin
g the SOUNDEX of the LastName of the first record, apply it to ALL the recor
ds, then taking the SOUNDEX
of the LastName of the second records, then updating it to ALL the records,
etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!

quote:

---

> It takes forever. I start tweeking the SQL to commit every 500 records or

---

so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.

quote:

---

>

---

How did you notice this? This is really strange, I've never heard of
something like this.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problems exporting to Excel

I am having a problem exporting a linked SQL server table from Access 2003
to
Excel. The file is about 2,000 records with several columns. The problem
is that when the table is imported into excel some of the records are not
coming across with the correct data and when numeric fields are summed the
totals are wrong.
When I export directly to Excel from the SQL server using DTS, I don't have
this problem. Has anyone else had this problem? Anyone have ideas of how to
fix it? I need to be able to allow the users to create queries in access
and export the data they need, so setting up a DTS as a solution is not
really workable (or at least I don't think it is).Hi Jim,
Being that this works fine from SQL Server but you are
having problems from MS Access, it's likely an MS Access
issue that you may want to post in one of the MS Access
newsgroups. Maybe try the following group:
microsoft.public.access.externaldata
-Sue
On Mon, 21 Aug 2006 14:52:39 GMT, "JIM" <jcrisp1@.kc.rr.com>
wrote:

>I am having a problem exporting a linked SQL server table from Access 2003
>to
>Excel. The file is about 2,000 records with several columns. The problem
>is that when the table is imported into excel some of the records are not
>coming across with the correct data and when numeric fields are summed the
>totals are wrong.
>When I export directly to Excel from the SQL server using DTS, I don't have
>this problem. Has anyone else had this problem? Anyone have ideas of how t
o
>fix it? I need to be able to allow the users to create queries in access
>and export the data they need, so setting up a DTS as a solution is not
>really workable (or at least I don't think it is).
>

Problems creating "such" query

Hi guys,

There is a problem now. I'm currently using Access2000, two tables A & B respectively. The both tables contain one columns of data. What I am needed to do now is a query that results the difference in the data.

Eg. Table A contains 1,2,3 | Table B contains 1,3,5

The similar numbers: 1, 3
The different numbers: 2, 5

My query results need to show 2 & 5 as they are not in both the tables.

Can anyone enlighten me on this? I've tried Unmatch Query Wizard. But my results only shows 2 but not 5 because the left join only applies on one table - A.

Thanks for help in advance! Hope to hear from you guys real soon. This is a urgent problem. = )Here are 2. One that uses outer joins and the other uses a combination of union, minus and intersect.

1)
select a.a
from A LEFT OUTER JOIN B on a.a = b.b
where b.b is null
union
select b.b
from A RIGHT OUTER JOIN B on a.a = b.b
where a.a is null;

2)
(union)
minus
(intersection*)

* - difference in other DBMS's|||hey, thanks r123456

i really got what i wan, but that was a trial for me, may i know is it possible for the query to work if there are ten tables?

same thing applies, i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j.

Is it possible? can help me on this? thanks a lot anyway!|||You're talking about an EXTREMELY intensive query there. Is it necessary to do this in SQL?

Essentially you need to ask access to compare A to B, A to C, A to D, A to E, A to F, B to C, B to D, B to E, C to E, C to D, E to D

etc etc

This will be obscene to do in SQL, I would highly recommend scripting a small function to do it for you, depending on how you intend to use the data.|||Your comment ->
"i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j."

I have assumed you wish to return a single set containing all numbers from accross all tables that do not appear in the intersection of all tables.

If this is the case then for more than say 2-3 tables I would personally write a recursive code block. Essentially for a given number of tables any value in table1 that does not appear in table2 can be immediately disregarded as it instantly fails the intersection.

Thus instead of constructing a 10-way join or 10-way union minus 10-way intersection accross all tables, by using a recursive procedure, one is able to compare a continously decreasing set of tuples with each remaining table, providing the majority of numbers do not form the intersection.

Example
----

Table 1 - Table 2 returns a set of 6 matching tuples. Now these 2 tables can be disregarded, and only the 6 resulting tuples need be continued. After comparing to the next table, only 4 rows are returned. As the procedure continues this set decreases in size.|||But can i know how do i go about doing a recursive code block? can i run it using a macro? i'm really new to Access2000, hope u guys will help me. i'm willing to try though.

r123456,
i understand what you are trying to say, basically, compare table 1 & 2 first, then get the different numbers from these two tables, then get the result to compare with other tables am i right?

regards,
wensheng|||As you are using Access not SQL Server I don't know to what extent Access supports these types of queries.

For example a typical example of recursion may be:

List all parts and subparts along with all their prices required to make a specific device. Ie. Device requires 5 main parts and each main part requires 10-20 sub parts etc. This query can not be written in pure SQL hence external code is required, on ORACLE this can be PL/SQL. SQL Server would probably contain a similar concept.

Access however, I could not tell you. If it does not, then the above queries can be modified. The outer join needs to be altered sligtly whereas the (union) / (intersect) can simply be duplicated. Consider the following,

TableA Table B
--- ----
1 1
3 3
5 4

The intersection can be constructed as an INNER JOIN instead (note, intersection compares all columns not just a candidate key, which in this example is suitable), giving the first 2 rows. An obvious next step

would be:
select (a, b)
from a, b
where (a, b) NOT IN (the intersection returned set), this however would return a set of unwanted combinations that remained from the CP.

To resolve this one of the following can be done,
1) Union the tables instead of a cartesian product to eliminate combinations, then where union column NOT IN any of the columns returned by the inner join.
2) Perform an inner join on all tables with the join condition being the collection of 10 NOT IN / NOT EXISTS operators to check their column with any column in the intersection.

Clearly the 2nd option is not an option. Regarding option 1, the (minus and intersection) clauses have essentially been re-written. The question can now be simplified.

Is it faster to use (minus & intersect) as opposed to where NOT IN (INNER JOIN). I would suggest INTERSECT.|||Hi r1234567 again, thanks for replying promptly.. thanks a lot. appreciated. but somehow i tried something strange.. i think this kind of stupid idea can only come from my brain, wanna take a look? just see below Query Statement:

select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN B on [A].[Object ID]=[B].[Object ID]
where [B].[Object ID] is null;

UNION select [B].[Object ID], [B].[Object Description]
from A RIGHT OUTER JOIN B on [A].[Object ID] = [B].[Object ID]
where [A].[Object ID] is null;

UNION select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN C on [A].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;

UNION select [C].[Object ID], [C].[Object Description]
from A RIGHT OUTER JOIN C on [A].[Object ID] = [C].[Object ID]
where [A].[Object ID] is null;

UNION select [B].[Object ID], [B].[Object Description]
from B LEFT OUTER JOIN C on [B].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;

UNION select [C].[Object ID], [C].[Object Description]
from B RIGHT OUTER JOIN C on [B].[Object ID] = [C].[Object ID]
where [B].[Object ID] is null;

Somehow, this gives the result of what i want man! thanks a lot! i really appreciate u guys for helping.. especially r1234567! really.|||That is a valid solution for only three tables, please be cautioned that for each table you add, the number of select statements you will need to include will grow exponentially.|||Hi Teddy,

i know the number of sql statements are going to grow exponentially.
However the few solutions other than sql queries i ain't sure. I'm not a programmer or what, that's why i dun understand.

I'm sorry. I did this Union Queries till the 7th table. when i reach 8th table, there was an error, " Query too complex! "

So i stopped at 7th table. i think that should be enough. = )|||I'm not up to speed on my vb lately as I've been working primarily in delphi lately, but I can outline a basic concept. I would do this using an array and a for loop. Essentially declare an array to store your final value list. Open your first data set with SELECT * FROM A and scroll through the record set to assign values to the array...

Essentially you would need to declare an array, assign the first table to the array, then compare it to the next table. Scroll through each record in the next table, if a match is found in the array, delete the matching field in the array, otherwise add the field to the array from the dataset. Then compare to the next table and do the same.

basically you'd have a for loop that would iterate and either append or remove depending on a match. I hope that makes any sort of sense heh..

Problems Concatenating Columns

Hi All,
I am getting some rather strange results when concatenating columns.
Please see my inline comments below...
SELECT
'http://www.oursite.co.uk/scripts/prodview.asp?idproduct=' +
CAST(products.idProduct AS Char(4)) AS product_url,
('Replacement '
+
Case When CharIndex(';',Products.description) > 0 Then
Left(Products.description,CharIndex(';',Products.description)-1)
Else
Products.description
End
+
' for '
+
Case When CharIndex('[',allProducts.CatDescMake) > 0 Then
Left(allProducts.CatDescMake,CharIndex('[',allProducts.CatDescMake) - 1)
+'' -- If I dont add this empty string, the concatenation seems to stop
after this point (not so much of a problem, but why?)
Else
allProducts.CatDescMake + '' -- Ditto!
End
--+ allProducts.CatDescModel + products.description -- This fails, only
the
first column ends up in the concatenated column
+ products.description + allProducts.CatDescModel -- This works OK, both
columns are in the concatenated column
) AS name,
products.DescriptionLong as Description,
'http://www.oursite.co.uk/prodimages/' + products.imageURL as image_url,
products.Price as price
FROM Categories_Products
INNER JOIN allProducts ON Categories_Products.idCategory =
allProducts.CatIDModel
INNER JOIN products ON Categories_Products.idProduct = products.idProduct
WHERE products.idProduct IN (select idProduct from products where
description like '%;%')
Any help will be appreciated! :)
Simon.
PS: I know that string concatnation should be done on the
client/application
end, but ASP is not happy about doing with with the amount of records we
have (Times out) where as from what I have seen so far, SQL Server does
the
job ALOT faster!
---
I am using the free version of SPAMfighter for private users.
It has removed 2988 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!Hi
Check that those columns you are having problems with do not contain nulls.
string + NULL results in NULL
You might need to add to your code ISNULL(column, '') replaces a null with
an empty string.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:eEeSzzvAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am getting some rather strange results when concatenating columns.
> Please see my inline comments below...
> SELECT
> 'http://www.oursite.co.uk/scripts/prodview.asp?idproduct=' +
> CAST(products.idProduct AS Char(4)) AS product_url,
> ('Replacement '
> +
> Case When CharIndex(';',Products.description) > 0 Then
> Left(Products.description,CharIndex(';',Products.description)-1)
> Else
> Products.description
> End
> +
> ' for '
> +
> Case When CharIndex('[',allProducts.CatDescMake) > 0 Then
> Left(allProducts.CatDescMake,CharIndex('[',allProducts.CatDescMake) - 1)
> +'' -- If I dont add this empty string, the concatenation seems to stop
> after this point (not so much of a problem, but why?)
> Else
> allProducts.CatDescMake + '' -- Ditto!
> End
> --+ allProducts.CatDescModel + products.description -- This fails, only
> the
> first column ends up in the concatenated column
> + products.description + allProducts.CatDescModel -- This works OK, both
> columns are in the concatenated column
> ) AS name,
> products.DescriptionLong as Description,
> 'http://www.oursite.co.uk/prodimages/' + products.imageURL as image_url,
> products.Price as price
> FROM Categories_Products
> INNER JOIN allProducts ON Categories_Products.idCategory =
> allProducts.CatIDModel
> INNER JOIN products ON Categories_Products.idProduct = products.idProduct
> WHERE products.idProduct IN (select idProduct from products where
> description like '%;%')
> Any help will be appreciated! :)
> Simon.
> PS: I know that string concatnation should be done on the
> client/application
> end, but ASP is not happy about doing with with the amount of records we
> have (Times out) where as from what I have seen so far, SQL Server does
> the
> job ALOT faster!
>
> ---
> I am using the free version of SPAMfighter for private users.
> It has removed 2988 spam emails to date.
> Paying users do not have this message in their emails.
> Try www.SPAMfighter.com for free now!
>sql

PROBLEM: Report column is spilling over to another page

Hi all,
I'm trying to resolve a problem with my report. I am using a Table element,
and I'm using fixed width columns. When I preview the report in my
development environment, I get a normal looking report... 4 pages with all
the proper headers and columns in their correct locations. When I run the
report through my ASP.NET application and stream the rendered PDF through my
web application, the report rendering is wrong. On the 1st page, where the
final column on the right should be visible on the first page, spills onto a
2nd page, where it's all by itself. The 3rd page of the report shows all of
the columns, but no header, and then the 4th and 5th pages resemble the 1st
and 2nd page, and the 6th and 7th pages render like the 1st and 2nd pages.
I have tried to reasonably squish the columns, to no avail, and the only way
I could have all the columns show on one page would be to squish the first
column very small so it's unusable. My report is 8" wide, and I have 0.25"
margins on the left and right hand sides. My main table is 8" wide, and when
I make it smaller, there's no change... I still get the problem. I have
tried everything I can think of to try to make this work, but I am now
officially stumped.
I am using SQL Server 2000 Reporting Services.
Can anyone help? Any thoughts?
Here's the code I use to render the report:
Private Function GetReport(ByVal strReportPath As String, ByVal
strReportFormat As String, ByVal parameters As ParameterValue())
Dim data As Byte()
rs = New ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.Url = Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings.Item("payerdirectories.ReportingServicesWS.ReportingService"))
' create parameters
Dim encoding As String
Dim mimeType As String
Dim parametersUsed As ParameterValue()
Dim warnings As Warning()
Dim streamIDs As String()
data = rs.Render(strReportPath, strReportFormat, Nothing, Nothing,
parameters, Nothing, Nothing, encoding, mimeType, parametersUsed, warnings,
streamIDs)
Return data
End Function
I can upload my report design XML if need be.
Thanks in advance,
Cory KoskiClick on the page, Go into properties, size and make sure that it is less
than 8 (8.5 - .25+.25), I think what happend is this, when you were making
your report, at some point the page width became large than 8, although you
squished everything back to your requirement of 8, this setting did not
adjust. Unless you manually reset this it wont fix itself
"Cory Koski" wrote:
> Hi all,
> I'm trying to resolve a problem with my report. I am using a Table element,
> and I'm using fixed width columns. When I preview the report in my
> development environment, I get a normal looking report... 4 pages with all
> the proper headers and columns in their correct locations. When I run the
> report through my ASP.NET application and stream the rendered PDF through my
> web application, the report rendering is wrong. On the 1st page, where the
> final column on the right should be visible on the first page, spills onto a
> 2nd page, where it's all by itself. The 3rd page of the report shows all of
> the columns, but no header, and then the 4th and 5th pages resemble the 1st
> and 2nd page, and the 6th and 7th pages render like the 1st and 2nd pages.
> I have tried to reasonably squish the columns, to no avail, and the only way
> I could have all the columns show on one page would be to squish the first
> column very small so it's unusable. My report is 8" wide, and I have 0.25"
> margins on the left and right hand sides. My main table is 8" wide, and when
> I make it smaller, there's no change... I still get the problem. I have
> tried everything I can think of to try to make this work, but I am now
> officially stumped.
> I am using SQL Server 2000 Reporting Services.
> Can anyone help? Any thoughts?
> Here's the code I use to render the report:
> Private Function GetReport(ByVal strReportPath As String, ByVal
> strReportFormat As String, ByVal parameters As ParameterValue())
> Dim data As Byte()
> rs = New ReportingService
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> rs.Url => Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings.Item("payerdirectories.ReportingServicesWS.ReportingService"))
> ' create parameters
> Dim encoding As String
> Dim mimeType As String
> Dim parametersUsed As ParameterValue()
> Dim warnings As Warning()
> Dim streamIDs As String()
> data = rs.Render(strReportPath, strReportFormat, Nothing, Nothing,
> parameters, Nothing, Nothing, encoding, mimeType, parametersUsed, warnings,
> streamIDs)
> Return data
> End Function
> I can upload my report design XML if need be.
> Thanks in advance,
> Cory Koski|||Ok this is strange. I did as you suggested, and no go.
But I did notice something interesting that I'm not sure that was there
before, but I'm betting it was. The Page Header and Footer are cut off on
the right-hand side, so the text that I have there is not displaying
completely.
Is there some setting in the device settings for the renderer to tell it to
use the entire 8.5" width instead of the default? What should I do to check?
I'm in Canada, so is it possible that the server I am testing this report on
is rendering it to A4 instead of Letter by accident?
Any suggestions?
"Seidel1" wrote:
> Click on the page, Go into properties, size and make sure that it is less
> than 8 (8.5 - .25+.25), I think what happend is this, when you were making
> your report, at some point the page width became large than 8, although you
> squished everything back to your requirement of 8, this setting did not
> adjust. Unless you manually reset this it wont fix itself
> "Cory Koski" wrote:
> > Hi all,
> >
> > I'm trying to resolve a problem with my report. I am using a Table element,
> > and I'm using fixed width columns. When I preview the report in my
> > development environment, I get a normal looking report... 4 pages with all
> > the proper headers and columns in their correct locations. When I run the
> > report through my ASP.NET application and stream the rendered PDF through my
> > web application, the report rendering is wrong. On the 1st page, where the
> > final column on the right should be visible on the first page, spills onto a
> > 2nd page, where it's all by itself. The 3rd page of the report shows all of
> > the columns, but no header, and then the 4th and 5th pages resemble the 1st
> > and 2nd page, and the 6th and 7th pages render like the 1st and 2nd pages.
> >
> > I have tried to reasonably squish the columns, to no avail, and the only way
> > I could have all the columns show on one page would be to squish the first
> > column very small so it's unusable. My report is 8" wide, and I have 0.25"
> > margins on the left and right hand sides. My main table is 8" wide, and when
> > I make it smaller, there's no change... I still get the problem. I have
> > tried everything I can think of to try to make this work, but I am now
> > officially stumped.
> >
> > I am using SQL Server 2000 Reporting Services.
> >
> > Can anyone help? Any thoughts?
> >
> > Here's the code I use to render the report:
> >
> > Private Function GetReport(ByVal strReportPath As String, ByVal
> > strReportFormat As String, ByVal parameters As ParameterValue())
> >
> > Dim data As Byte()
> >
> > rs = New ReportingService
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> > rs.Url => > Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings.Item("payerdirectories.ReportingServicesWS.ReportingService"))
> >
> > ' create parameters
> > Dim encoding As String
> > Dim mimeType As String
> > Dim parametersUsed As ParameterValue()
> > Dim warnings As Warning()
> > Dim streamIDs As String()
> >
> > data = rs.Render(strReportPath, strReportFormat, Nothing, Nothing,
> > parameters, Nothing, Nothing, encoding, mimeType, parametersUsed, warnings,
> > streamIDs)
> >
> > Return data
> >
> > End Function
> >
> > I can upload my report design XML if need be.
> >
> > Thanks in advance,
> >
> > Cory Koski

Problem with WHERE clause when column length > 255

Hello, I am using the JDCB-ODBC driver for my app, but one of the columns in my WHERE clause is 255 characters long. Because of this no rows are returned even though the statement should return 1 or 2 rows. I've tried other JDBC-ODBC drivers too and they have the same problem. Additionally, I've tried using RTrim(), Substring(), etc and it still will not work...any ideas? Is it a driver bug? Anyone know of a driver that will work?

Sample code...

ResultSet rs = dbRetrieve.getStatement().executeQuery( sql ) ;
if (rs.next()) {
// Never gets here
}

Thanx!Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?

-PatP|||Originally posted by Pat Phelan
Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?

-PatP

Yes, I have and it returns results. Additionally, I've removed the column from the where clause in my java file and it returns data, but of course its uselys because I need that column in the where clause...

Thanks...|||Hmm... Microsoft's JDBC driver says it will support upt to 4000 character strings. Have you used profiler to see exactly what's being sent to your SQL server?|||Since the SQL code works ok from Query Analyzer, we can assume that SQL Server to the MDAC on your client works fine. The problem must be in the JDBC driver or beyond it.

Which JDBC driver are you using? Does it have any length limitations that you are aware of (or does it keep them as surprises for the unwary)? As Peter pointed out, the Microsoft JDBC driver handles big strings (I've run 2 Kb columns out through it).

Just as another thought, are you certain that your Java app is behaving and actually submitting what you expect it to.

-PatP

Problem with Union ALl

I want to combine 4 columns and map those 4 to respective fileds in the target database.For this..when i used "Union All " its going for cross join and filling the columns with Null vlaues..
Like If each column has 5 rows ,iam getting (4* 5 =20) 20 rows

Is there any solution to avoid for this and to get the 5 rows?

Thanks
Kumar
sorry small mistake . when using UnionAll getting output like this

Like If each column has 5 rows ,iam getting (5+5+5+5)= 20 rows instaead of 5

|||

That's what its supposed to do. What exactly is your requirement?

-Jamie

|||

Sounds like you should be using the Merge Join transformation

from BOL:

The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin

|||I need only 5 rows with out any null values (making sure that there is no null values in the source)

when i combine ..iam getting ouput like

ID FirstName LastName
1 Null Null
2 Null Null
3 Null Null
Null Kumar Null
Null Jackson Null
Null Tom Null
Null Null Niranjan
Null Null McDonald
Null Null Harry

But I want like this ..is there any solution

ID FirstName LastName

1 Kumar Niranjan
2 Jackson McDonald
3 Tom Harry

I hope you understand what i want to say
|||
I can't use Merge Join Cynic ,Bcoz i have 4 inputs to combine...it will help only when your dealing with 2.
To use Merge join for this scenario i have to sort 4 columns indvidually and then again i have to use 3 merge joins which will be heavy work

|||

Niru wrote:

I need only 5 rows with out any null values (making sure that there is no null values in the source)

when i combine ..iam getting ouput like

ID FirstName LastName
1 Null Null
2 Null Null
3 Null Null
Null Kumar Null
Null Jackson Null
Null Tom Null
Null Null Niranjan
Null Null McDonald
Null Null Harry

But I want like this ..is there any solution

ID FirstName LastName

1 Kumar Niranjan
2 Jackson McDonald
3 Tom Harry

I hope you understand what i want to say

That makes no sense. For example, where is it defined that "1", "Kumar" & "Niranjan" form a single record? Nowhere that I can see.

Perhaps you are implying that the order in which the values appear is important. i.e. RecordX from dataset1 joins with RecordX from dataset2 and RecordX from dataset3. If this is the case you will have to give each row a number (here is how: http://www.sqlis.com/default.aspx?37) and then use the MERGE JOIN component.

-Jamie

|||Thanks Jamie for the hint ..got it solved with that idea.

Thats what i need

Thanks
Niru