|
You need to pivot[^] the "Additions" table, before joining it to the "MainTable" on emp_code.
|
|
|
|
|
I m a new bee i have used sql server 2000 before my question is when creating two tables in sql server 2000 say
location and projects table projects having a foreign key referencing the location table when inserting values in
location the projects is also updated thats is no need to insert the similar value in the foreign key in projects table
why is it not possible in mysql when i insert values in location using insert command and when using select command on
projects it does not shows the value in foreign key please check the below code
mysql> create table location(
-> id int not null,
-> primary key(id))
-> engine=innodb;
Query OK, 0 rows affected (0.11 sec)
mysql> create table projects(
-> id int,
-> location_id int,
-> foreign key(location_id) references location(id) on update cascade on del
ete cascade)
-> engine=innodb;
Query OK, 0 rows affected (0.31 sec)
mysql> insert into location values('1')
Query OK, 1 row affected (0.34 sec)
mysql> select * from location;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> select * from projects;
Empty set (0.00 sec)
as u see in the above code it was possible in sql server 2000 that the value was reflected in the child table why is
it not possible in MySql Why should the value be insert in both the tables isn't it possible in MySql that when i insert
in the location table the projects table pick the value automatically and show it in select Query when i query the projects table
|
|
|
|
|
Do not create duplicate posts, you can edit your previous post and make your additions.
|
|
|
|
|
I m a new bee in mysql i have used sql server 2000 before my question is when creating two tables in sql server 2000 say location and projects table projects having a foreign key referencing the location table when inserting values in location the projects is also updated thats is no need to insert the similar value in the foreign key in projects table why is it not possible in mysql when i insert values in location using insert command and when using select command on projects it does not shows the value in foreign key why should the value be insert in both the tables isn't it possible that when i insert in the location table the projects table pick the value automatically and show it in select result when i query the projects table
|
|
|
|
|
There is nothing it TSQL (Microsoft SQL Server "Transactional SQL") that is going to automatically insert foreign key values.
You might have used some tool that created TSQL for you, but then it generated TSQL which explicitly inserted the key value.
|
|
|
|
|
Hi there,
i have a sql server table there i have thousands of record and when i call it from Stored procedure then it will take very huge time to display records how can i make Fast.
Thanks in Advance
|
|
|
|
|
Returning thousands of rows is usually not the problem.
The problem is usually in the query (your stored procedure).
It might help if you were to post the Stored Procedure?
|
|
|
|
|
Without your query I can only suggest things to try as I had to do something similar just recently
1. Break your query down into sections and find what part is slow
2. Indexing your data.
or like the other poster suggested, post your query
Nagy Vilmos wrote: And eat bacon. Bacon's real important for 'puters.
|
|
|
|
|
The general answer is write more efficient code. Since you haven't posted your code, we can't tell you where to improve it.
|
|
|
|
|
In general you shouldn't be attempting to display "thousands" of records in the first place.
This typically occurs in list box type displays for users who are interested in looking at the records. But those users don't want 'thousands" what they want is a very small subset very likely one record. And they know what they are looking for. So only display the single record they are looking for.
|
|
|
|
|
Posting your query will help us determine your problem, but here are some general tips:
1. Index your table(s) properly.
2. Select only the required rows using a properly constructed WHERE clause.
3. Select only the required columns.
|
|
|
|
|
I managed to duplicate a single record. However I intended to display the duplicate record with different values. For example:
A ABC 065 001 A10111 Sell 54.847500 0 0
T ABC 065 001 A10111 Sell 50.847500 0 1
SELECT
case i.ordStatus
when 'Queued' then 'N'
when 'Filled' then 'A'
when 'Partial Filled' then 'A'
when 'Cancel' then 'C'
end AS 'ordStatus',
f.code AS 'exchCode',
g.code AS 'companyCode',
h.code AS 'branchCode',
c.code AS 'clientCode',
i.transType AS 'transType',
case i.ordStatus
when 'Queued' then ((i.quantity * i.price * i.exchRate) / j.denomination)
when 'Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
when 'Partial Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
when 'Cancel' then ((i.adjust * i.price * i.exchRate) / j.denomination)
end AS 'ordAmount',
case i.ordStatus
when 'Queued' then 0
when 'Filled' then 0
when 'Partial Filled' then 0
when 'Cancel' then 0
end AS 'matchAmount'
FROM TM_TradingProfile a
LEFT JOIN TM_clientTradingProfile b
ON a.id = b.id
LEFT JOIN TM_Client c
ON c.id = b.TM_client_fk
LEFT JOIN BKL_User d
ON d.id = c.id
JOIN BKL_Authentication e
ON c.id = e.BKL_user_fk
LEFT JOIN RM_Exchange f
ON a.RM_exchange_fk = f.id
LEFT JOIN BKL_Company g
ON a.BKL_company_fk = g.id
LEFT JOIN BKL_Branch h
ON a.BKL_branch_fk=h.id
LEFT JOIN RM_MarketTransaction i
ON a.tradingAccountNumber = i.tradingAccNo
LEFT JOIN bkl_forexexch j
ON i.tradCurr = j.currencyCodeFrom
CROSS JOIN (SELECT 1 UNION ALL SELECT 1) AS T(x)
WHERE a.tradingAccountNumber='ST3273'
Besides, I should be able to display a duplicate record when the ordStatus is filled or partial filled.
Your help is kindly appreciated.
Thank You.
|
|
|
|
|
Can't tell exactly what's wrong but you seem to have too many joins. That cross join... hmmm... I am nos sure what you want to do. Personally, I would use a union operator to duplicate records.
SELECT a.col1, a.col2,... 0 As Flag
FROM myTable a
WHERE ...
UNION
SELECT b.col1,b.col2,... 1 as Flag
FROM myTable b
WHERE ....
|
|
|
|
|
Hi,
I am using dot net and sql server. I am sending my data from the database table to the pdf file. It looks using lots of tables to get data slowing down the program and so I tend to use the view instead of tables. Will it be the good idea to use view and if yes, i have few more questions.
1. When are we inserting data to the view. Is that when we are inserting data to the actual table or anytime ?
2. If I insert data in the view, will the data be there once i close my program?( I am assuming view as the cache table)
3. what is the difference between using view and actual table ?
4. How the use of view and table different?
Thanks in advance
suchita
|
|
|
|
|
SayamiSuchi wrote: 1. When are we inserting data to the view. Is that when we are inserting data to the actual table or anytime ?
You can't insert data into View.
SayamiSuchi wrote: 3. what is the difference between using view and actual table ?
Difference between View and table[^]
Check this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
|
Blue_Boy wrote: You can't insert data into View.
Actually, most modern databases support what is called "updatable views". If your view does not contain calculated columns and the base tables have a default defined on the columns not selected in the view, then the view must be updatable.
|
|
|
|
|
|
A "view" is a way of looking at data stored in table(s), therefore using a view instead of the underlying base table will not solve your performance issue. If your query is slow, consider these tips:
1. Index your base table(s).
2. Use a WHERE clause to filter only those rows that is needed.
3. Select only the required columns (don't use a * in the SELECT statement).
|
|
|
|
|
i have create a function in pubs data base
create function show(@temp varchar(11))
returns table
return(select *from author where au_id=@temp)
now i wants to access this function from aspx page using c#
please give me an applicable answer so that i will satisfied
thanks
regards
(manoj bhatt)
|
|
|
|
|
String inputValue;
String sqlText;
SqlConnection connection;
SqlCommand command;
SqlDataReader dataReader;
inputValue="123ABC";
sqlText="SELECT dbo.show(" + inputValue +")";
connection=new SqlConnection(" ...whatever your connection stuff is ");
try
{
connection.Open();
command=new SqlCommand(sqlText,connection);
SqlDataReader dataReader = command.ExecuteReader();
if(dataReader != null)
{
while(dataReader.Read())
{
--get values from dataReader
}
}
}
catch
{
}
|
|
|
|
|
oooh! shiny! SQL injection, here we come!!!!
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
I know.... personally I would have preferred a stored procedure.
|
|
|
|
|
I have a couple of newbie questions for SQL Server. On the SQL Server Management Studio, I used the following script to delete and create a table on the master databse:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PARTNOTE]
GO
CREATE TABLE [dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I can run this as many times as I want. Both deletion and creation work well. My first question is "After the creation, where is the table creates?" I couldn't find where the table object from Object Explorer.
Now I modify the script to perform the same tasks not on the master, but on "MyDatabase":
if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [MyDatabase].[dbo].[PARTNOTE]
GO
CREATE TABLE [MyDatabase].[dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The creation still works but the deletion does not. It couldn't find where the table was creates.
Could someone please explain what goes wrong in the modified script? Thanks!
Best,
Jun
|
|
|
|
|
After you execute script, refresh Tables node by right click on it and click refresh, it should shows created tables.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
