Database

5-Jan-09 21:48

Shravanthi wrote:
It was throwing an error

What is the error you get?

The need to optimize rises from a bad design.My articles[^]

sql query

kadkir5-Jan-09 17:01

5-Jan-09 17:01

I have a table with the following data
Row | IssuedBankId | paidBankId | Payable | Receivable
1 | 1 | 2 | 1000 | 2000
_____________________________________________________________
2 | 1 | 3 | 1100 | 3000
_____________________________________________________________
3 | 1 | 4 | 4000 | 5000
_____________________________________________________________
4 | 2 | 1 | 2000 | 1000
_____________________________________________________________
5 | 3 | 1 | 3000 | 1100
_____________________________________________________________
6 | 4 | 2 | 5000 | Null
_____________________________________________________________
7 | 5 | 4 | 1000 | Null

I want to eliminate rows 4,5 bcoz we have the same info in row 1,2 respectively. Just payable, receivable amnts are interchanged. how can I get it. Please give me some Idea.

Thaking You,
Kiran.

Garth J Lancaster5-Jan-09 17:32

Garth J Lancaster

5-Jan-09 17:32

I don't understand - is there a constraint preventing you from deleteing the data ?

if not, I'd make a backup of the table, then

delete from <table> where row in (4,5);

you don't say which database/sql you're using, so Im assuming your sql allows the 'in (4,5)' - not sure which standard it is ...

'g'

kadkir5-Jan-09 18:03

5-Jan-09 18:03

Thank U 'g',
I am using sql server database. The data which I have given is from view not from table (sorry for mentioning it as table). What I exactly need is ,

I want to exclude redundant data while displaying, here when Bank '1' is payable "xxx" amnt to Bank '2', and receivable 'yyy' from bank '2', then bank '2' is receivable 'xxx' from bank '1', and payable 'yyy' to bank '1'.
Hence my report should consists of either bank '1' info, or bank '2' info. In the same manner all banks info in one query.And field 'Row' is not a column. That I hav given for referance purpose.

Kiran

Wendelius5-Jan-09 21:56

5-Jan-09 21:56

If I inderstood you corectly, you want to eliminate rows 4 and 5 because the transfer is in opposite order base on bank id's and amounts. If that's correct you could write something like:

select *
from tablename t1
where not exists (select 1
                  from  tablename t2
                  where t2.paidbankid = t1.issuedbankid
                  and   t2.payable    = t1.receivable
                  and   t2.receivable = t1.payable)

The need to optimize rises from a bad design.My articles[^]

kadkir5-Jan-09 23:07

5-Jan-09 23:07

Thank U Mika,
I want retain Rows(1,2). With ur query Rows (1,2,4,5) are getting eliminated. As rows 4,5 are having same data in other way I don't want to display it. If I show relation b/w Bank 'm' & 'n' I need not to Display the same relation b/w 'n' & 'm'.

------
Kiran

Wendelius5-Jan-09 23:26

5-Jan-09 23:26

Yes, that's correct. That query finds matching pairs and eliminates both of them.

Now you would have to add the logic which one of the matching rows is left in the result set. You didn't mention the logic why rows 1 and 2 are left so I wasn't able to write it to the example. Is it perhaps the issuedbankid why the rows are left or something else?

The need to optimize rises from a bad design.My articles[^]

kadkir6-Jan-09 21:23

6-Jan-09 21:23

Sorry For not responding in time.

I want to get a report of In the following way,

If the user selects a "Bank" from dropdownlist, then he must get the list of all banks' amnts (may payable,receivable) towards that selected bank

assume selected banks' Id is 1 (xyz bank)

bankname Payable to(xyz) receivable from(xyz)

aaa 1000 2000
bbb 2000 00.00
ccc 3000 4000

I have given the structure of my view. so how to generate this sort of report. what would be the query?

I tried with this

<pre>select T1.IssuedBank,T1.PaidBank,T1.Payable,T1.Receivable from
Test_MergeView as T1 Left Join
Test_MergeView as T2
ON
T1.IssuedBank = T2.PaidBank and
T1.PaidBank = T2.IssuedBank
Where
T1.PaidBank > T1.IssuedBank </pre>

but if paidbankId is < issuedbankid that info is getting eliminated in display. to avoid rows 4,5 I used where clause. but other wanted info is also getting eliminated.

------
kiran

Wendelius7-Jan-09 0:45

7-Jan-09 0:45

So if your data was:

Row | IssuedBankId | paidBankId | Payable | Receivable
1   | 1            | 2          | 1000    | 2000
2   | 1            | 3          | 1100    | 3000
3   | 1            | 4          | 4000    | 5000
4   | 2            | 1          | 2000    | 1000
5   | 3            | 1          | 3000    | 1100
6   | 4            | 2          | 5000    | Null
7   | 5            | 4          | 1000    | Null

and you said that if the user select 1 for bankid, then you would like the result to be:

Row | IssuedBankId | paidBankId | Payable | Receivable
1   | 1            | 2          | 1000    | 2000
2   | 1            | 3          | 1100    | 3000
3   | 1            | 4          | 4000    | 5000

Is that correct? Are all the rows and columns present? Since that would be simply:

SELECT *
FROM   TableName
WHERE  IssuedBankId = 1

I suppose that this isn't what you're looking for?

The need to optimize rises from a bad design.My articles[^]

kadkir7-Jan-09 19:17

7-Jan-09 19:17

Thank U Mika,
U made my job simpler , Though I was not searching for that, with ur previous sln I can proceed further.
------
Kiran

Wendelius7-Jan-09 19:41

Import Data From Other Database and Eliminate Duplicates

7-Jan-09 19:41

Glad it helped.

The need to optimize rises from a bad design.My articles[^]

polishprogrammer5-Jan-09 9:59

Re: Import Data From Other Database and Eliminate Duplicates

5-Jan-09 9:59

I need to import data from one Access database to another with virtually identitical structurers (the target database may have more columns, but it has every column that the database to be imported has). I want to do this for specific tables using SQL, but my database is protected by a database password. How do I construct the SQL statement to include the password?

Thanks.

Wendelius5-Jan-09 10:25

Re: Import Data From Other Database and Eliminate Duplicates

5-Jan-09 10:25

polishprogrammer wrote:
How do I construct the SQL statement to include the password

The SQL statement doesn't include the password, only the connection does.

The solution varies depending which tools you're about to use, but basically you take one connection to the access db, read data from there and put it to sql server using another connection. You could do this with c#, SSIS/DTS (depending on the db version), linked server etc.

The need to optimize rises from a bad design.My articles[^]

polishprogrammer5-Jan-09 10:38

Re: Import Data From Other Database and Eliminate Duplicates

5-Jan-09 10:38

Thanks, but both the databases are MS Access databases. I need to import data from one Access database into another Access database. I will be using C# to do this operation. How would I include the connection in any SQL operation I might perform? I've tried to Merge 2 data tables. It works (usually), but takes a VERY long time. That's why I was hoping to find something quicker, such as a SQL statement perform the work. If my databases were not secure, I could do this very easily, but the database password complicates things.

Thanks.

Wendelius5-Jan-09 10:58

Re: Import Data From Other Database and Eliminate Duplicates

5-Jan-09 10:58

I guess I would open a connection to the source db and then loop through the data and put the data to the target db using another connection. So I wouldn't use data tables in the middle. Also for removing duplicates I would order the source data.

It's been awhile since I last used access so there may be another ways, but I think the logic I described would get the job done.

The need to optimize rises from a bad design.My articles[^]

polishprogrammer7-Jan-09 9:47

Re: Import Data From Other Database and Eliminate Duplicates

7-Jan-09 9:47

Mika,

I found this statement worked to import the data, but it does not take care of the issue of duplicates. I need to customize this statement a bit to ensure that I'm only selecting those records from the C:\Program Files\Northwind.mdb database, say, that are not already in the target database. Sort of, perhaps, a reverse INNER JOIN. If you have any suggestions on that, I would appreciate it. Thanks.

INSERT INTO Employees
SELECT Employees.*
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees;

Wendelius7-Jan-09 10:00

Re: Import Data From Other Database and Eliminate Duplicates

7-Jan-09 10:00

Aah, they've added external sources to Access. Starts to look like SQL Server.

Anyway, I don't know how you identify a duplicate, but let's say you have a column named ID and based on that you make the decision if it exists or not. In that case you could have for example:

INSERT INTO Employees
SELECT source.*
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees source
WHERE NOT EXISTS (SELECT 1
                  FROM   Employees target
                  WHERE  target.ID = source.ID)

or even better if EXCEPT is supported by Access, you could have:

INSERT INTO Employees
SELECT *
FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].Employees
EXCEPT
SELECT *
FROM Employees

The columns must be in the same order in both tables in all of the statements in order for them to work.

The need to optimize rises from a bad design.My articles[^]

polishprogrammer8-Jan-09 10:56

Re: Import Data From Other Database and Eliminate Duplicates

8-Jan-09 10:56

After some search (and trial and error), I found this statement to work:

INSERT INTO Employees
SELECT e1.* FROM [;DATABASE=C:\Program Files\Northwind.mdb;PWD=none].as e1
LEFT JOIN Employees as e2 ON e1.Field1=e2.Field1 WHERE e2.Field1 IS NULL

The IS NULL comparison selects only those records from the left side of the join that did not already have a match in the right (target) table.

It seems to work well. When I tried to import records through code (DataTable.Merge or some iteration technique), the import process would take many minutes for moderately sized tables (30000 to 70000 records), but using just the SQL it took the application about 6 seconds do the same operation.

The only major change I had to make when implementing this in code was to insert the appropriate variable for the database path and implement a function to get the password, as appropriate, such as Marshal.PtrToStringAuto(logPointer).

Thanks again for your suggestions and I hope this follow up I provided is useful.

Wendelius8-Jan-09 11:06

SQL Server- Upload CSV file via Bulk Insert with double Quotation Issue?

8-Jan-09 11:06

No problem and the follow up was useful! Even though I rarely use Access, it's always good to have understanding how it's developping. Thanks!

The need to optimize rises from a bad design.My articles[^]

Tarini Singh5-Jan-09 4:21

Tarini Singh

5-Jan-09 4:21

Hi Team,

Needed functionality is upload csv file via bulk insert only.

1)I Created table structure

CREATE TABLE #Test_temp
(
Name	varchar(100),
Age	varchar(100),
Salary	varchar(100),
Dept	varchar(100)
)

2)
creating the format file (.fmt)
8.0
4

1	SQLCHAR	      0       100     ","			1       NAME		SQL_Latin1_General_CP1_CI_AS
2	SQLCHAR	      0	      100     ","			2	AGE		SQL_Latin1_General_CP1_CI_AS
3	SQLCHAR	      0	      100     ","			3	SALARY		SQL_Latin1_General_CP1_CI_AS
4	SQLCHAR	      0	      100     "\r\n"			4	DEPT		SQL_Latin1_General_CP1_CI_AS

3)
The comma delimited csv data file

Name,Age,Salary,Dept
Scott,21,2343,salary
Tiger,22,34343,acct

It was successfully uploaded but
the input changes is that

In csv file, some filed contains values within (")double quotes with (,) .
like in third row below

Name,Age,Salary,Dept
<code>Scott,21,"23,43",salary</code>
Tiger,22,34343,acct

for this scenario, I took the fmt file as below

1 SQLCHAR 0 100 "," 1 NAME SQL_Latin1_General_CP1_CI_AS

2 SQLCHAR 0 100 "," 2 AGE SQL_Latin1_General_CP1_CI_AS

3 SQLCHAR 0 100 "\"," 3 SALARY SQL_Latin1_General_CP1_CI_AS

4 SQLCHAR 0 100 "\r\n" 4 DEPT SQL_Latin1_General_CP1_CI_AS

Could anyone help me out from this scenario.

Tarini Singh
Sr Software Engg.
email-tksingh@zenta.com

Re: SQL Server- Upload CSV file via Bulk Insert with double Quotation Issue?

Wendelius5-Jan-09 9:18

Problem with large database fetching

5-Jan-09 9:18

I think you could also set quotation marks on the second field like:

2 SQLCHAR 0 100 ",\"" 2 AGE SQL_Latin1_General_CP1_CI_AS

However, it would be better, if the character data is always in quotation marks. Otherwise you may have problems if the data contains commas etc.

So could you get the data in following format (assuming that the number are actually characters as your table defines them):

"Scott","21","23,43","salary"
"Tiger","22","34343","acct"

The need to optimize rises from a bad design.My articles[^]

Member 40015965-Jan-09 2:40

Member 4001596

5-Jan-09 2:40

hi everyone

i have create a web page where i place two DropDownList in First i bound Country List and in second one i am bounding places list according to selected country my problem is that when place list have less items it works fine but when list goes large Ex more than 5000 or more it take so much time some times it it show time out error (Sql Server). i also try with Ajax tool kit with Cascading Dropdownlist extender with web service method but problem not solve. here is my code

// for country selection

Public Shared Function getContry() As DataTable
Dim Ds As DataSet
Dim contry_list As String
contry_list = "SELECT Country_List.CountryNo,Country_List.CountryName From Country_List"
Ds = SqlHelper.ExecuteDataset(connection, CommandType.Text, contry_list) ' "Get_Country_List")

Return Ds.Tables(0)
End Function

/// for Place List

Public Shared Function getplace(ByVal CountryNo As Integer) As DataTable
Dim Ds As DataSet
Dim getplacelist As String
getplacelist = "SELECT Place_List.PlaceNo,Place_List.PlaceName From Place_List " & " WHERE Place_List.CountryNo = " & CountryNo
Ds = SqlHelper.ExecuteDataset(connection, CommandType.Text, getplacelist)
Return Ds.Tables(0)
End Function

and on .aspx page i am using

Private Sub fillCountry()
Dim dt As DataTable
dt = getContry()
ComboBox1.DataSource = dt
ComboBox1.DataValueField = dt.Columns("CountryNo").ToString
ComboBox1.DataTextField = dt.Columns("CountryName").ToString
ComboBox1.DataBind()

End Sub

Private Sub fillPlace(ByVal CountryNo As Integer)
Dim dt As DataTable
dt = getplace(CountryNo)

ListBox1.DataSource = dt
ListBox1.DataValueField = dt.Columns("PlaceNo").ToString
ListBox1.DataTextField = dt.Columns("PlaceName").ToString
ListBox1.DataBind()
ListBox1.Enabled = True
End Sub

where i am wrong plz help me

Re: Problem with large database fetching

Ashfield5-Jan-09 2:54

Ashfield

5-Jan-09 2:54

I suspect you need to look at the indices on the tables. An index on Place_List.CountryNo would certainly help. You could also look at caching as the places per country probably do not change much.

Bob
Ashfield Consultants Ltd

Re: Problem with large database fetching

Wendelius5-Jan-09 9:22