Hi Guys. I have been handed a database to run a few queries on. For one query I have to find the top 10 applications, from two different tables with hundreds of records. then on row (11) I will need to SUM or Count the remaining records and name the row "Other". I have worked out the following code so far. SELECT TOP 10 ApplicationTb...
We are using Power Designer for Database Modelling and whenever something is changed in model (column name, datatypes etc) have go back and test all depended stored procedures and fix them accordingly. What would be the best tool to do unit testing of database. Thanks ...
Hello. I have a query with a long chain of CTEs which ends with SELECT RegionName, AreaName, CityName, SubCityName, StreetName FROM tDictionaryStreets UNION ALL SELECT RegionName, AreaName, CityName, SubCityName, StreetName FROM tDictionaryRegions The execution time of this query is 1450 ms. When I execute these 2 SELECTs separatl...
In phpmyadmin there is a field length which you may specify, usually set to 11 on INT if nothing is specified. Field Name: ID Field Type: INT(11) // This is what I need to figure out! How can I calculate the length, and what does the length actually stand for? Does it mean how many digits? Bytes? And what is Unsigned and Signed g...
I have this query in MS SQL which is acting very weird (at least from my perspective). I've user defined function called: dbo.NajblizszaDataWyceny(3, '2010-02-05') which is simple check for TOP 1 entry in one table joined with couple others. The query itself takes like milliseconds so it's not a big problem, but i show the function any...
Hi Everyone, I have 2 tables from which i need to run a query to display number of views a user had in the last 3 months from now. So far I have come up with: all the field types are correct. SELECT dbo_LU_USER.USERNAME , Count(*) AS No_of_Sessions FROM dbo_SDB_SESSION INNER JOIN dbo_LU_USER ON dbo_SDB_SESSION.FK_USERID =...
I'm getting the following error whenever I try to post something with an apostrophe in it: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near... For example when I'm trying to post/using INSERT something like "I'm working hard".It's getting me an error....
Aloha, I'm trying to populate a treeview on a windows form app with Hierarchical data from a SQL db. The structure from the database is: id_def id_parent description 1 NULL Multidificiência 2 NULL Síndrome 3 NULL Outros 4 1 Surdez de Transmissão 5 2 Surdez Neurossensorial Ligeira 6 3 ...
How can I both set rowcount and then execute a query in a Statement.executeQuery() function? note I don't want to use Statement.setMaxRows() to do this ...
I have a classified webs. Users may put classifieds in, and each category has sub options. Ex: User picks CAR, and has to fill in options such as color, mileage, fuel, gearbox etc. Here are my tables for this: Category table: cat_id (PK) cat_name // "Cars" for example Category Options table: option_id (PK) cat_id (FK) option_name //...
I currently have two fairly long select statements, identical apart from the fact that the first is looking to see if the row exists via count(*) and the second selecting the row into the rowtype variable based on an if statement. This is because the data is required for further manipulation I know it is possible, but I am having a com...
I have string with many & and | operators . If I try to update such a string into database it asks me for a value . For e.g if i try to update a string A&B oracle asks me for a value . I want to store the string as A&B ...
Hi, I am using SQL 2000, and I am running a simple select statement on a table containing about 30 million rows. The select query looks like: select col1, col2, col3 from Table1 where col4=@col4 and col5=@col5 and col6=@col6 The table has a clustered index in it (i.e. a primary key), but that is not being used as a where criteria. All...
I am generating some test-data and use dbms_random. I encountered some strange behavior when using dbms_random in the condition of the JOIN, that I can not explain: ------------------------# test-data (ids 1 .. 3) With x As ( Select Rownum id From dual Connect By Rownum <= 3 ) ------------------------# end of test-data Select x.id, ...
Hello, I need to know true tablespace size in Oracle. I have some tablespace and I need to know how many space it uses now and how many space is free (and maybe percent of free space). I found in web some sqls but all of them showed size based on water mark... which is not true space allocated now but as far as I know the highest value...
Hello, I have created user-defined function that converts a comma-delimited string into a table. I execute this function like so: select [String] as 'ID' from dbo.ConvertStringToTable('1,2,3,4') The results from this query look like the following: ID -- 1 2 3 4 In reality, I want to iterate through each of the rows in this table. ...
I have the following SQL statement: SELECT * FROM cars car LEFT JOIN (SELECT * FROM cars auto LEFT JOIN steeringwheels sw ON auto.steeringwheelid = sw.ID WHERE material = 'leather') innertable ON innertable.ID = car.ID LE...
I'm looking at some SQL code which has a WHERE clause like this: WHERE 'USD' = CCY I asked the writer why he's putting the value on the left hand side, and he said it's best practice to do so, stemming from C++ where people could mistakenly assign the value instead of comparing equality by forgetting the second equals sign. I've neve...
I need help in indexing in MySQL. I have a table in MySQL with following rows: ID Store_ID Feature_ID Order_ID Viewed_Date Deal_ID IsTrial The ID is auto generated. Store_ID goes from 1 - 8. Feature_ID from 1 - let's say 100. Viewed Date is Date and time on which the data is inserted. IsTrial is either 0 or 1. You can ignore Order_ID an...
Hello! I have a table in a mysql-database with the fields "name", "title", "cd_id", "tracks" The entries look like this: Schubert | Symphonie Nr 7 | 27 | 2 Brahms | Symphonie Nr 1 | 11 | 4 Brahms | Symphonie Nr 2 | 27 | 4 Shostakovich | Jazz Suite Nr 1 | 19 | 3 To get the tracks per cd (cd_id) I have written this script: #!/usr/bin/...