We have a massive amount of stored procedures to convert from sql-server 2000 to oracle 10g. Does anyone know of any tool that would achieve this? We used Oracle SQL developer for this but it seems like it is creating extra cursors for each IF/ELSE condition that was on sql server side. Has anyone successfully used a tool that would ...
Due to a variety of design decisions, we have a table, 'CustomerVariable'. CustomerVariable has three bits of information--its own id, an id to Variable (a list of possible settings the customer can have), and the value for that variable. The Variable table, on the other hand, has the information on a default--in case the CustomerVaria...
How can I create two temporary tables with the same structure without write twice? Something like that: DECLARE @TEST_TABLE1, @TEST_TABLE2 TABLE ( FIELD1 INT, FIELD2 INT ) and NO: DECLARE @TEST_TABLE1 TABLE ( FIELD1 INT, FIELD2 INT ) DECLARE @TEST_TABLE2 TABLE ( FIELD1 INT, FIELD2 INT ) ...
I have a function that has a very useful name: has_useful_state(param). I have a second function that will be returning a SETOF RECORDs of these results: CREATE OR REPLACE FUNCTION set_of_useful_things(param TEXT, OUT has_useful_state) RETURNS SETOF RECORD AS $_$ BEGIN SELECT some_key, COUNT(has_useful_state(some_key)) FROM .... ...
What is the best way to design the schema for the following requirements? Need to store Countries, States, Counties, and counties can be divided into regions. Regions can then have people with various data points to report on. Regions can also be further divided into divisions which is like grouping people. So Region 1 can have Divis...
I have a table, which consists of 3 fields: id name status Every time I get the results, it should give me 5 names whose status = 1. Suppose the db contains following: id name status 1 A 1 2 B 1 3 C 0 4 D 1 5 E 0 6 F 0 7 H 1 8 I 1 9 J 1 10 K 1 11 L 1 12 M 0 1st time, fetch ...
I have a Transaction table (transId, sn, customerId, date) that lists item transactions between customers. Some item has sn (serial number) and travels from one customer to another. For some customer (12345678), I need to find out who was the last previous owner of customer's items. Here's my query: SELECT c.*, p.transId,...
We can see schema for all tables and views by: SELECT * FROM INFORMATION_SCHEMA.TABLES SELECT * FROM INFORMATION_SCHEMA.VIEWS Can we view schema for stored procedures or functions through tsql? ...
I would like to copy parts of an Oracle DB to a SQL Server DB. I need to move the data because the Oracle box is being decommissioned. I only need the data for reference purposes so don't need indexes or stored procedures or contstaints, etc. All I need is the data. I have a link to the Oracle DB in SQL Server. I have tested the f...
This is from SQL Server 2008, ssms When I create a table, it creates under dbo. I would like to create it under a different schema, but when I use the 'New Table' dialog, I can never find the field where to specify this. ...
If I have the following table: CREATE TABLE #temp ( id int, num int, question varchar(50), qversion int ); INSERT INTO #temp VALUES(1, 1, 'Question 1 v1', 1); INSERT INTO #temp VALUES(2, 1, 'Question 1 v2', 2); INSERT INTO #temp VALUES(3, 2, 'Question 2 v1', 1); INSERT INTO #temp VALUES(4, 2, 'Question 2 v2', 2); INSERT...
I have a column in a non-partitioned Oracle table defined as VARCHAR2(50); the column has a standard b-tree index. I was wondering if there is an optimal way to query this column to determine whether it contains a given value. Here is the current query: SELECT * FROM my_table m WHERE m.my_column LIKE '%'||v_value||'%'; I looked at Ora...
I have 2 tables: TOPICS (id, title) ANSWERS (id, id_topic, id_user, answer) ...and i want to do a select to detect all the questions belonging to a user in one select. I tried to do a join, but that doesn't work since if a user answered a topic twice, it will return 2 rows with the same topic. I tried to do a SELECT DISTINCT, but th...
Supposed I have the following tables: Sailor(sid, sname, age) Boat(bid, sid) Each boat can have many sailors, and each individual sailor can serve on many boats. What I want to do is to find the boat with the highest average age of sailors. I can find the average age of the sailor on each boat with this subquery: SELECT b.bid, A...
I am having a few issues with the below SQL. SELECT * FROM (SELECT tbrm_Article.ArticleID, tbrm_Article.CountryID, tbrm_Article.CategoryID, tbrm_Article.Title, tbrm_Article.ArticleDetail, tbrm_Article.Source, tbrm_Article.ArticleDateTimeAdded, ...
Hi I've heard that on stackoverflow questions and answers are stored in the same DB table. If you were to build a twitter like service that only would allow 1 level of commenting. ie 1 tweet and then comments/replies to that tweet but no re-comments or re-replies. would you use two tables for tweets and retweets? or just one table whe...
Suppose I have a table like the following: tblNumbers Numbers 4 5 3 6 Using SET BASED approach how can I perform a multiplication so the output will be: Output 360 N.B~ There is no hard and fast rule that there will be only four numbers, but I'd prefer the answer to be using a CTE and/or correlated subquery. ...
We are about to deploy some code that truncates tables in our mysql 4 database. We are doing this to get around some replication 'weirdness' that we are seeing (and is widely known) with temp tables. My knee-jerk reaction when I saw this in a code review was "no", but I'm having trouble backing it up. So, the question is: am I just ov...
Using SQL server 2000. If the Start date is 06/23/2008 and End date is 06/30/2008 Then I need the Output of query as 06/23/2008 06/24/2008 06/25/2008 . . . 06/30/2008 I Created a Table names as Integer which has 1 Column, column values are 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 then I used the below mentioned query Tried Query SELECT DATEADD...
This probably sounds like a really stupid question, but how do I declare a variable for use in a PostgreSQL 8.3 query? In MS SQL Server I can do this: DECLARE @myvar INT SET @myvar = 5 SELECT * FROM somewhere WHERE something = @myvar How do I do the same in PostgreSQL? According to the documentation variables are declared simply as ...