I have a super-simple query in a star schema. One fact, two dimensions. I have verified that I am doing the joins properly. But when I execute the query plan, I get a merge cartesian join right before the steps to add the dimensions. explain plan for select * from fact f inner join dim1 d1 on d1.id = f.d1_id inner join dim2 d2 ...
I am using Oracle SQL Developer, but I am having an issue seeing results from a package that returns a ref cursor. Below is the package definition: CREATE OR REPLACE package instance.lswkt_chgoff_recov as type rec_type is record ( source_cd lswk_tpr.gltrans.tpr_source_cd%TYPE, ...
As a newbie to Postgresql (I'm moving over because I'm moving my site to heroku who only support it, I'm having to refactor some of my queries and code. Here's a problem that I can't quite understand the problem with: PGError: ERROR: column "l_user_id" does not exist LINE 1: ...t_id where l.user_id = 8 order by l2.geopoint_id, l_user_i...
First, from BOL: Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Que...
Hi!, I´m having trouble with bulk insert a file that contains a field in data type text, and it holds enters and the delimiter for the fields are pipes "|" and the row terminator is "|\n" I get an error of truncation for the fields next to the text field. I think that the bulk insert thinks that the enters in the second field are the n...
Given the following table 'foo' ID | First Name | Last Name ---------------------------- 67 John Smith ---------------------------- 67 Bill Jacobs What first_name and last_name will the following query return and why? SELECT * FROM foo WHERE ID = 67 GROUP BY ID ...
I'm trying to find an integer number of days between two dates in Oracle 11g. I can get close by doing select sysdate - to_date('2009-10-01', 'yyyy-mm-dd') from dual but this returns an interval, and I haven't been successful casting this to an integer. Edit: Apparently in 10g, this returns the number of days as an integer. ...
Can we use constraint like check constraints, not null constraints on MyISAM Storage Engines in MySQL? ...
I have a SQL Table UDF That gets a Standard Deviation from a 20 day moving average of values... The table its computing from is: Tickers([date] datetime, [close] numeric(7,2)) The Function computes a Table GetStDev([date] datetime, stddev numeric(7,2). The last row of the stddev column is always NULL (due to STDEV calc?)... I need to r...
The site I am working on as a student will be redesigned and released in the near future and I have been assigned the task of manually searching through every table in the DB the site uses to find tables we can consider for deletion. I'm doing the search through every HTML files source code in dreamweaver but I was hoping there is an aut...
I have a ASP.NET web page that needs to make a SQL Server call from the client side in a script sectipn of my aspx file. I'm calling a stored proc which takes one parm. This sp works fine in SQL Server Management Studio returning records as expected. When I try to fill a dataset from a call to this sp the ds gets filled with zero records...
I have 2 tables containing information that i want to search for, a main table, and a comments table. The main table contains timestamps, subjects, etc. While the comments table holds comments for the individual records in the main table. Its basically a simple ticket system. I need to be able to search for things in the main table and ...
Hi, i need Add Row Numbers To a SELECT Query without using Row_Number() function. and without using user defined functions or stored procedures. Select (obtain the row number) as [Row], field1, field2, fieldn from aTable UPDATE i am using SAP B1 DIAPI, to make a query , this system does not allow the use of rownumber() function in th...
The environment is Oracle 9 & 10. I do not have DBA level access. The problem is to verify that a specific column exists in a specific table, in another schema. There are two cases to deal with. Another schema in the same instance A schema in a different instance, using a db_link Given my schema FRED and another schema BARNEY...
Probably about 300K rows. Using SQL and/or PL/SQL. Would it be best to copy/convert into an interim format in a temporary table, work on it and then import it back in? The updated data would need to remain as a 'long' datatype because we don't have the resources (or inclination) to fix the DB. Thanks. ...
I don't know if "variadic" is actually the right word, but I'm talking about things that can take a list of values, like IN(). If you've been working with DBI for long, you've probably tried to do this: (Note: All examples extremely simplified for brevity) my $vals = join ', ', @numbers; my $sth = $dbh->prepare( "SELECT * FROM mytbl WH...
I have an SQLite database, eventually will be a MySQL database and I'm using Zend Framework. I'm trying to fetch all the rows in a table where the 'date_accepted' column is empty/null/doesn't have a value. This is what I have so far: public function fetchAllPending() { $select = $this->getDbTable()->select(); $select->where('date_acce...
I´m sorting by Price, look: SELECT Price FROM re2_listings ORDER BY Price asc Result: 1.200.000,00 1.500.000,00 200,00 3.000,00 ...but the correct way is: 200,00 3.000,00 1.200.000,00 1.500.000,00 Understand? How to do this? ...
postgres has an array data type, in this case a numeric array: CREATE TABLE sal_emp (name text, pay_by_quarter integer[]); INSERT INTO sal_emp VALUES ('one', '{1,2,3}'); INSERT INTO sal_emp VALUES ('two', '{4,5,6}'); INSERT INTO sal_emp VALUES ('three', '{2,4,6}'); SELECT * FROM sal_emp; Result: one, {1,2,3} two, {4,5,6} three, {2,4,6}...
I work with Entity Framework - add records in the database using classes that map to the DB. But i need to make sure I dont add duplicate records in the database. I assumed that when SaveChanges() is called, it sorts out all the duplicates, but it doesnt. Is there a way to eliminate creation of the duplicate records? ...