|
It has the unacceptable disadvantage of not being SQL92 compliant... otherwise it's just fine. 
|
|
|
|
|
There are lots of Difference are there..
Oracle support varchar2, Sqlserver dont
Oracle support dual , Sqlserver Dont
Oracle Support P/L Sql where as Sqlserver Support TSQL
And many more.. These are the main difference.
|
|
|
|
|
Timothy CIAN wrote: how does the performance compare, generally speaking?
In my experience that all depends on the DBA, some DBAs are good and can make anything sing, others not so much.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Current we have a database that has two tables "order status" and "lead_free_certs". For each record in "order status" there may be 0 to many records in the "lead_free_certs" table. I'm trying to load a specific record from "order status" and if there is a matching record in "lead_free_certs" (based on project number), the most recent record in "lead_free_certs" based on the certificate number. I'm using VS2008 and I'm connecting to 2005 SQL Server. The SQL statement I have is:
SELECT * FROM [Order Status] LEFT JOIN Lead_Free_Certs ON [Order Status].[Project #] = Lead_Free_Certs.[Project Num] WHERE ([Order Status].[Project #] = " & projectnumber & ") ORDER BY Lead_Free_Certs.[Cert Num] DESC
The "projectnumber" variable is passed as an argument to the function. When I run this command no rows are returned. However if I want to load records only from the "order status" table then the following SQL statement works:
"SELECT * FROM [Order Status] WHERE [Project #] = " & projectnumber
So I know it is a problem with my LEFT JOIN statement. Any help would be greatly appreciated since this is the first time I'm trying to use JOIN statements.
|
|
|
|
|
Just a guess, but is it possible that the two columns of "Project #" and "Project Num" are different types and some implicit cast causes the JOIN to fail. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Didn't think of that; yes they are different types. Project Num is nvarchar(255) and Project # is int. Would it be better to convert them to the same type or is there a way to do the conversion in the SQL statement. There might be a problem with the conversion because some of the records in Lead_Free_Certs table are not strictly numerical. Some of the numbers have hyphens or slashes in them.
|
|
|
|
|
Add an int column called Project # to the Lead_Free_Certs table and figure out how to populate it based upon the value from the Project Num column. But it sounds like you are trying to join two tables that were'nt to be joined together. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
The LEFT OUTER JOIN or simply LEFT JOIN return all rows from the first table listed after the FROM clause or left of JOIN keyword , no matter if they have matches in the right table of the JOIN keyword.
Syntax :
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name ;
Check here lot of examples : http://www.w3resource.com/sql/joins/perform-a-left-join.php
|
|
|
|
|
Okay I tried casting and it worked. The new statement that works is:
SELECT * FROM [Order Status] LEFT JOIN Lead_Free_Certs ON (CAST([Order Status].[Project #] AS nvarchar(255)) = Lead_Free_Certs.[Project Num]) WHERE ([Order Status].[Project #] = " & projectnumber & ") ORDER BY Lead_Free_Certs.[Cert Num] DESC
Thanks for the help!
|
|
|
|
|
You're welcome.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Hi all.
I have a massive SSIS job that transfers data between databases. Each of these tables has a separate data flow task. When I change the connection string for the source database, SSIS wants to refresh the metadata, so I have to go into each task individually and refresh the metadata, which is pointless and time-consuming.
My question is... Is there any way I can do this more efficiently? Is there a select all/refresh metadata anywhere which I can use?
Thanks in advance for any help.
|
|
|
|
|
Hi all,
Is anyone of you know a better way to cross-tabulate within multiple tables.
At the moment what I'm going is based on the table selections, select a hard-coded query, which seems lots of work. Because I've to think about all the possibilities.
I appreciate your help all the time...
CodingLover
|
|
|
|
|
As I barely understood the question, it does need a lot of clarification - what is a cross tab, why are you joing multiple tables and your last sentence does not compute.
I'll take a guess and recommend you look at using dynamic sql to build your query as a string and then execute it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply.
Actually hard-coded sense I'm designing SQL queries dynamically. But the combination is huge. For example, say I've 2 tables and I wants to consider two columns, one from each. I can cross tabulate column_1 against column_2 or vise verse. So I'm confusing how to handle all the possibilities.
I appreciate your help all the time...
CodingLover
|
|
|
|
|
This article[^] has helped a number of people, you need to do the thinking to apply the logic to your specific requirement. One of the beuties of CP is that there may be an article out there with, or close to your specific requirement which makes it dramatically easier to relate it to your problem.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'll hazard another guess at what you are looking for and suggest a search for pivot tables.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Thanks for the reply.
I'll look at the pivot tables and let you know.
If you could look at my previous post I really appreciate too.
I appreciate your help all the time...
CodingLover
|
|
|
|
|
Hy. I'm trying to do a select from multiple databases and tables for an occurence.
let's say i have databases db1, db2 and db3. Each have the following tables t1, t2, t3. And in each table there is a field named id which i want to select and a field and p_id which i want to use as a condition. To give a better idea this is an example of what results i want to get:
---------------------------------------
|id_t1 | id_t2 | id_t3 | database_name|
---------------------------------------
| 100 | 123 | 356 | db1 |
| 252 | 156 | 566 | db2 |
|... | ... | ... | db3 |
---------------------------------------
How can i pass the p_id condition to each table and each database. I tried this:
select t1.id as id_t1, t2.id as id_t2, t3.id as id_t3, (select database()) as database_name from db1.t1, db1.t2, db1.t3 where ****can't make the condition work because if i only say p_id = 100 it says it's ambiguous****
union select *** all the above *** from db2 and db3
Thanks
|
|
|
|
|
A couple of relevant assumptions, you are using SQL Server and all the databases are on the same server.
If you are getting the database name from a variable then you need to use dynamic sql.
Select idt1,idt2,idt3
from db1.dbo.tablename
Select idt1,idt2,idt3
from db2.dbo.tablename
Select idt1,idt2,idt3
from db3.dbo.tablename
Now insert UNION between the 3 queries to get them in 1 result set
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey. Thanks for the reply, but what you suggested does not apply for me. I'm trying to select from 3 different tables from each of the 3 databases the rows which contain a certain value. In the mean time i have completed the query selecting the rows from one table, union them with the other tables in one database, then union the whole select with the other two databases.
Here is what i did
SELECT * FROM (SELECT id, "t1", "db1" FROM db1.t1 WHERE p_id = ""
UNION SELECT id, "t2", "db1" FROM db1.t2 WHERE p_id = ""
UNION SELECT id, "t3", "db1" FROM db1.t3 WHERE p_id = "") s1
UNION
SELECT * FROM (SELECT id, "t1", "db2" FROM db1.t1 WHERE p_id = ""
UNION SELECT id, "t2", "db2" FROM db2.t2 WHERE p_id = ""
UNION SELECT id, "t3", "db2" FROM db2.t3 WHERE p_id = "") s2
UNION
SELECT * FROM (SELECT id, "t1", "db3" FROM db1.t1 WHERE p_id = ""
UNION SELECT id, "t2", "db3" FROM db3.t2 WHERE p_id = ""
UNION SELECT id, "t3", "db3" FROM db3.t3 WHERE p_id = "") s3
If you have a better option for this I'm looking forward to see it, because this gets really crowded when i have more tables and databases. And yes, I am using MySql and all the databases are on the same server. The db-s are the same with db1 being main and the other are backups which differ only in the content not in fields and tables
|
|
|
|
|
To be clear - your database is MySQL.
To use different databases in the same SQL requires that the SQL that the database uses provides a way to specify the database itself as part of the name for the table.
Far as I can tell MySQL does not allow that.
The following is the documentation for what a 'table' can be.
http://dev.mysql.com/doc/refman/5.0/en/join.html[^]
This doesn't totally eliminate the possibility since this might just not specify it. Finding the similar information for MS SQL Server is difficult.
If it doesn't allow it then you must query each database independently and then merge them together yourself.
|
|
|
|
|
of course you can use the database.table.field syntax on mysql. the query i wrote works i only asked if someone has a better approach because the query gets difficult to follow once i use more databases and tables.
The join operation joins tables on certain values. I don't need the joined. I just need to see where in those tables (the row id) a value is found.
|
|
|
|
|
Hi,
I'm running SQL Server and I have an application that is creating a new database. However I'm not sure what directory I should store the database. Any suggestions?
Thank you,
Glenn
|
|
|
|
|
If the database is not server based then I would put it in either the users appdata folder or the applications install folder, although this may no longer be valid b/c of the UAC requirements.
I think the appdata folder is where it should be.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I like the idea of using the application's install directory, somewhere far from the Windows default structures. That way, when Windows Update crashes the system and Windows has to be reinstalled, it isn't erased and replaced by empty folders. 
Will Rogers never met me.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
