Search Results

Search found 2750 results on 110 pages for 'recursive subquery factor'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 23/110 | < Previous Page | 19 20 21 22 23 24 25 26 27 28 29 30  | Next Page >

  • Yet another use of OUTER APPLY in defensive programming

    - by Alexander Kuznetsov
    When a SELECT is used to populate variables from a subquery, it fails to change them if the subquery returns nothing - and that can lead to subtle bugs. We shall use OUTER APPLY to eliminate this problem. Prerequisites All we need is the following mock function that imitates a subquery: CREATE FUNCTION dbo.BoxById ( @BoxId INT ) RETURNS TABLE AS RETURN ( SELECT CAST ( 1 AS INT ) AS [Length] , CAST ( 2 AS INT ) AS [Width] , CAST ( 3 AS INT ) AS [Height] WHERE @BoxId = 1 ) ; Let us assume that this...(read more)

    Read the article

  • Why isn't my query using any indices when I use a subquery?

    - by sfussenegger
    I have the following tables (removed columns that aren't used for my examples): CREATE TABLE `person` ( `id` int(11) NOT NULL, `name` varchar(1024) NOT NULL, `sortname` varchar(1024) NOT NULL, PRIMARY KEY (`id`), KEY `sortname` (`sortname`(255)), KEY `name` (`name`(255)) ); CREATE TABLE `personalias` ( `id` int(11) NOT NULL, `person` int(11) NOT NULL, `name` varchar(1024) NOT NULL, PRIMARY KEY (`id`), KEY `person` (`person`), KEY `name` (`name`(255)) ) Currently, I'm using this query which works just fine: select p.* from person p where name = 'John Mayer' or sortname = 'John Mayer'; mysql> explain select p.* from person p where name = 'John Mayer' or sortname = 'John Mayer'; +----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | p | index_merge | name,sortname | name,sortname | 767,767 | NULL | 3 | Using sort_union(name,sortname); Using where | +----+-------------+-------+-------------+---------------+---------------+---------+------+------+----------------------------------------------+ 1 row in set (0.00 sec) Now I'd like to extend this query to also consider aliases. First, I've tried using a join: select p.* from person p join personalias a where p.name = 'John Mayer' or p.sortname = 'John Mayer' or a.name = 'John Mayer'; mysql> explain select p.* from person p join personalias a on p.id = a.person where p.name = 'John Mayer' or p.sortname = 'John Mayer' or a.name = 'John Mayer'; +----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+ | 1 | SIMPLE | a | ALL | ref,name | NULL | NULL | NULL | 87401 | Using temporary | | 1 | SIMPLE | p | eq_ref | PRIMARY,name,sortname | PRIMARY | 4 | musicbrainz.a.ref | 1 | Using where | +----+-------------+-------+--------+-----------------------+---------+---------+-------------------+-------+-----------------+ 2 rows in set (0.00 sec) This looks bad: no index, 87401 rows, using temporary. Using temporary only appears when I use distinct, but as an alias might be the same as the name, I can't really get rid of it. Next, I've tried to replace the join with a subquery: select p.* from person p where p.name = 'John Mayer' or p.sortname = 'John Mayer' or p.id in (select person from personalias a where a.name = 'John Mayer'); mysql> explain select p.* from person p where p.name = 'John Mayer' or p.sortname = 'John Mayer' or p.id in (select id from personalias a where a.name = 'John Mayer'); +----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+ | 1 | PRIMARY | p | ALL | name,sortname | NULL | NULL | NULL | 540309 | Using where | | 2 | DEPENDENT SUBQUERY | a | index_subquery | person,name | person | 4 | func | 1 | Using where | +----+--------------------+-------+----------------+------------------+--------+---------+------+--------+-------------+ 2 rows in set (0.00 sec) Again, this looks pretty bad: no index, 540309 rows. Interestingly, both queries (select p.* from person ... or p.id in (4711,12345) and select id from personalias a where a.name = 'John Mayer') work extremely well. Why doesn't MySQL use any indices for both of my queries? What else could I do? Currently, it looks best to fetch person.ids for aliases and add them statically as an in(...) to the second query. There certainly has to be another way to do this with a single query. I'm currently out of ideas though. Could I somehow force MySQL into using another (better) query plan?

    Read the article

  • What is the proper way to create a recursive entity in the Entity Framework?

    - by Orion Adrian
    I'm currently using VS 2010 RC, and I'm trying to create a model that contains a recursive self-referencing entity. Currently when I import the entity from the model I get an error indicating that the parent property cannot be part of the association because it's set to 'Computed' or 'Identity', though I'm not sure why it does it that way. I've been hand-editing the file to get around that error, but then the model simply doesn't work. What is the proper way to get recursive entities to work in the Entity Framework. CREATE TABLE [dbo].[Appointments]( [AppointmentId] [int] IDENTITY(1,1) NOT NULL, [Description] [nvarchar](1024) NULL, [Start] [datetime] NOT NULL, [End] [datetime] NOT NULL, [Username] [varchar](50) NOT NULL, [RecurrenceRule] [nvarchar](1024) NULL, [RecurrenceState] [varchar](20) NULL, [RecurrenceParentId] [int] NULL, [Annotations] [nvarchar](50) NULL, [Application] [nvarchar](100) NOT NULL, CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED ( [AppointmentId] ASC ) ) GO ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_ParentAppointments] FOREIGN KEY([RecurrenceParentId]) REFERENCES [dbo].[Appointments] ([AppointmentId]) GO ALTER TABLE [dbo].[Appointments] CHECK CONSTRAINT [FK_Appointments_ParentAppointments] GO

    Read the article

  • Can you make a PHP function recursive without repeating it's name?

    - by alex
    It's always bugged me a recursive function needs to name itself, when a instantiated class can use $this and a static method can use self etc. Is there a similar way to do this in a recursive function without naming it again (just to cut down on maintenance)? Obviously I could use call_user_func or the __FUNCTION__ constant but I would prefer something less ugly. Update Thanks for your answers. I might stick to including the function name for simple functions, and make take the other approaches for anything more complicated.

    Read the article

  • Change a Recursive function that has a for loop in it into an iterative function?

    - by Bill
    So I have this function that I'm trying to convert from a recursive algorithm to an iterative algorithm. I'm not even sure if I have the right subproblems but this seems to determined what I need in the correct way, but recursion can't be used you need to use dynamic programming so I need to change it to iterative bottom up or top down dynamic programming. The basic recursive function looks like this: Recursion(i,j) { if(i>j) return 0; else { //This finds the maximum value for all possible subproblems and returns that for this problem for(int x = i; x < j; x++) { if(some subsection i to x plus recursion(x+1,j) is > current max) max = some subsection i to x plus recursion(x+1,j) } } } This is the general idea, but since recursions typically don't have for loops in them I'm not sure exactly how I would convert this to iterative. Does anyone have any ideas?

    Read the article

  • How can I make a recursive version of my iterative method?

    - by user247679
    Greetings. I am trying to write a recursive function in Java that prints the numbers one through n. (n being the parameter that you send the function.) An iterative solution is pretty straightforward: public static void printNumbers(int n){ for(int i = 1; i <= n; i++){ System.out.println(i); i++; } As a novice programmer, I'm having troubles figuring out how a recursive version of this method would work. Any bright ideas? Thanks for reading my problem!

    Read the article

  • Java - StackOverflow Error on recursive 2D boolean array method that shouldn't happen.

    - by David W.
    Hey everyone, I'm working on a runnable java applet that has a fill feature much like the fill method in drawing programs such as Microsoft Paint. This is how my filling method works: 1.) The applet gets the color that the user clicked on using .getRGB 2.) The applet creates a 2D boolean array of all the pixels in the window, with the value "true" if that pixel is the same color as the color clicked on or "false" if not. The point of this step is to keep the .getRGB method out of the recursive method to hopefully prevent this error. 3.) The applet recursively searches the 2D array of booleans where the user clicked, recording each adjacent point that is "true" in an ArrayList. The method then changes each point it records to false and continues. 4.) The applet paints every point stored in the ArrayList to a user selected color. All of the above steps work PERFECTLY if the user clicks within a small area, where only a few thousand pixels or so have their color changed. If the user selects a large area however (such as about 360,000 / the size of the applet window), the applet gets to the recursive stage and then outputs this error: Exception in thread "AWT-EventQueue-1" java.lang.StackOverflowError at java.util.ArrayList.add(ArrayList.java:351) at paint.recursiveSearch(paint.java:185) at paint.recursiveSearch(paint.java:190) at paint.recursiveSearch(paint.java:190) at paint.recursiveSearch(paint.java:190) at paint.recursiveSearch(paint.java:190) at paint.recursiveSearch(paint.java:190) at paint.recursiveSearch(paint.java:190) (continues for a few pages) Here is my recursive code: public void recursiveSearch(boolean [][] list, Point p){ if(isValid(p)){ if(list[(int)p.y][(int)p.x]){ fillPoints.add(p); list[(int)p.y][(int)p.x] = false; recursiveSearch(list, new Point(p.x-1,p.y));//Checks to the left recursiveSearch(list, new Point(p.x,p.y-1));//Checks above recursiveSearch(list, new Point(p.x+1,p.y));//Checks to the right recursiveSearch(list, new Point(p.x,p.y+1));//Checks below } } } Is there any way I can work around an error like this? I know that the loop will never go on forever, it just could take a lot of time. Thanks in advance.

    Read the article

  • How can I implement a tail-recursive list append?

    - by martingw
    A simple append function like this (in F#): let rec app s t = match s with | [] -> t | (x::ss) -> x :: (app ss t) will crash when s becomes big, since the function is not tail recursive. I noticed that F#'s standard append function does not crash with big lists, so it must be implemented differently. So I wondered: How does a tail recursive definition of append look like? I came up with something like this: let rec comb s t = match s with | [] -> t | (x::ss) -> comb ss (x::t) let app2 s t = comb (List.rev s) t which works, but looks rather odd. Is there a more elegant definition?

    Read the article

  • Global variable in a recursive function how to keep it at zero?

    - by Grammin
    So if I have a recursive function with a global variable var_: int var_; void foo() { if(var_ == 3) return; else var_++; foo(); } and then I have a function that calls foo() so: void bar() { foo(); return; } what is the best way to set var_ =0 everytime foo is called thats not from within itself. I know I could just do: void bar() { var_ =0; foo(); return; } but I'm using the recursive function a lot and I don't want to call foo and forget to set var_=0 at a later date. Does anyone have any suggestions on how to solve this? Thanks, Josh

    Read the article

  • I'm trying to implement 2 factor authentication on the cheap. How would I do that?

    - by Biff MaGriff
    Ok so I need 2 of the 3. Something the user knows. Something the user has. Something the user is. I have a system that is exposed to the internet and we need clients to connect in a secure manner to satisfy our security standards. I'm thinking when a user registers to use our system we send them an application that they install on their home system. The application generates a key based on a timed randomness algorithm. Our application server has the same algorithm so when the user submits their credentials with the key we know that they are a legitimate user. Is this a valid method of 2 factor authentication? What is another way of doing this? Are there any pitfalls that I should be aware of? Thanks for your help!

    Read the article

  • Error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAV

    - by brz dot net
    I have to find the indentid from the status table based on below two conditions: 1. If there are more than one record having same indentid in status table and the same indentID has count1 in feasibilitystatus table then I don't want to display the record. 2. If there is only one record of indentid in status table and the same indentID has count0 in feasibilitystatus table then I don't want to display the record. Query: select distinct s.indentid from status s where s.status='true' and s.indentid not in( select case when count(s.indentid)>1 then (select indentid from feasibilitystatus group by indentid having count(indentid)>1) else (select indentid from feasibilitystatus group by indentid having count(indentid)>0) end as indentid from status) Error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

    Read the article

  • How to create a column containing a string of stars to inidcate levels of a factor in a data frame i

    - by PaulHurleyuk
    (second question today - must be a bad day) I have a dataframe with various columns, inculding a concentration column (numeric), a flag highlighting invalid results (boolean) and a description of the problem (character) dput(df) structure(list(x = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), rawconc = c(77.4, 52.6, 86.5, 44.5, 167, 16.2, 59.3, 123, 1.95, 181), reason = structure(c(NA, NA, 2L, NA, NA, NA, 2L, 1L, NA, NA), .Label = c("Fails Acceptance Criteria", "Poor Injection"), class = "factor"), flag = c("False", "False", "True", "False", "False", "False", "True", "True", "False", "False" )), .Names = c("x", "rawconc", "reason", "flag"), row.names = c(NA, -10L), class = "data.frame") I can create a column with the numeric level of the reason column df$level<-as.numeric(df$reason) df x rawconc reason flag level 1 1 77.40 <NA> False NA 2 2 52.60 <NA> False NA 3 3 86.50 Poor Injection True 2 4 4 44.50 <NA> False NA 5 5 167.00 <NA> False NA 6 6 16.20 <NA> False NA 7 7 59.30 Poor Injection True 2 8 8 123.00 Fails Acceptance Criteria True 1 9 9 1.95 <NA> False NA 10 10 181.00 <NA> False NA and here's what I want to do to create a column with 'level' many stars, but it fails df$stars<-paste(rep("*",df$level)sep="",collapse="") Error: unexpected symbol in "df$stars<-paste(rep("*",df$level)sep" df$stars<-paste(rep("*",df$level),sep="",collapse="") Error in rep("*", df$level) : invalid 'times' argument rep("*",df$level) Error in rep("*", df$level) : invalid 'times' argument df$stars<-paste(rep("*",pmax(df$level,0,na.rm=TRUE)),sep="",collapse="") Error in rep("*", pmax(df$level, 0, na.rm = TRUE)) : invalid 'times' argument It seems that rep needs to be fed one value at a time. I feel that this should be possible (and my gut says 'use lapply' but my apply fu is v. poor) ANy one want to try ?

    Read the article

  • How to Display result of subquery rows as one column in MySQL?

    - by SIA
    Hi I have three tables Category, Movies and RelCatMov Category-table categoryid, categoryName 1 thriller 2 supsense 3 romantic 4 action 5 sci-fi Movies-table movieid, movieName 1 Avataar 2 Titanic 3 NinjaAssassin RelCatMov-table categoryid, MovieID 1 1 2 2 3 2 4 2 5 2 Now i Want to display a the record as MovieName Categories Titanic Suspense,Romantic,Sci-fi,action How to do this. I am writing a query select MovieName,(select categoryname from category b,relcatmov c where b.categoryid=c.categoryid and c.movieid=a.movieid) as categories from movies a; Error: Subquery returns more than one row!!! How to display the result of rows in one column? Please help!!!

    Read the article

  • Why did the following linq to sql query generate a subquery?

    - by Xaisoft
    I did the following query: var list = from book in books where book.price > 50 select book; list = list.Take(50); I would expect the above to generate something like: SELECT top 50 id, title, price, author FROM Books WHERE price > 50 but it generates: SELECT [Limit1].[C1] as [C1] [Limit1].[id] as [Id], [Limit1].[title] as [title], [Limit1].[price] as [price], [Limit1].[author] FROM (SELECT TOP (50) [Extent1].[id] as as [Id], [Extent1].[title] as [title], [Extent1].[price] as [price], [Extent1].[author] as [author] FROM Books as [Extent1] WHERE [Extent1].[price] > 50 ) AS [Limit1] Why does the above linq query generate a subquery and where does the C1 come from?

    Read the article

  • An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING cla

    - by brz dot net
    I have to find the indentid from the status table based on below two conditions: 1. If there are more than one record having same indentid in status table and the same indentID has count1 in feasibilitystatus table then I don't want to display the record. 2. If there is only one record of indentid in status table and the same indentID has count0 in feasibilitystatus table then I don't want to display the record. Query: select distinct s.indentid from status s where s.status='true' and s.indentid not in(select case when count(s.indentid)>1 then (select indentid from feasibilitystatus group by indentid having count(indentid)>1) else (select indentid from feasibilitystatus group by indentid having count(indentid)>0) end as indentid from status) Error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

    Read the article

  • How to deal with recursive dependencies between static libraries using the binutils linker?

    - by Jack Lloyd
    I'm porting an existing system from Windows to Linux. The build is structured with multiple static libraries. I ran into a linking error where a symbol (defined in libA) could not be found in an object from libB. The linker line looked like g++ test_obj.o -lA -lB -o test The problem of course being that by the time the linker finds it needs the symbol from libA, it has already passed it by, and does not rescan, so it simply errors out even though the symbol is there for the taking. My initial idea was of course to simply swap the link (to -lB -lA) so that libA is scanned afterwards, and any symbols missing from libB that are in libA are picked up. But then I find there is actually a recursive dependency between libA and libB! I'm assuming the Visual C++ linker handles this in some way (does it rescan by default?). Ways of dealing with this I've considered: Use shared objects. Unfortunately this is undesirable from the perspective of requiring PIC compliation (this is performance sensitive code and losing %ebx to hold the GOT would really hurt), and shared objects aren't needed. Build one mega ar of all of the objects, avoiding the problem. Restructure the code to avoid the recursive dependency (which is obviously the Right Thing to do, but I'm trying to do this port with minimal changes). Do you have other ideas to deal with this? Is there some way I can convince the binutils linker to perform rescans of libraries it has already looked at when it is missing a symbol?

    Read the article

  • Recursive function for copy of multilevel folder is not working.

    - by OM The Eternity
    Recursive function for copy of multilevel folder is not working. I have a code to copy all the mulitilevel folder to new folder. But in between I feel there is problem of proper path recognition, see the code below.. <?php $source = '/var/www/html/pranav_test'; $destination = '/var/www/html/parth'; copy_recursive_dirs($source, $destination); function copy_recursive_dirs($dirsource, $dirdest) { // recursive function to copy // all subdirectories and contents: if(is_dir($dirsource)) { $dir_handle=opendir($dirsource); } if(!is_dir($dirdest)) { mkdir($dirdest, 0777); } while($file=readdir($dir_handle)) {/*echo "<pre>"; print_r($file);*/ if($file!="." && $file!="..") { if(!is_dir($dirsource.DIRECTORY_SEPARATOR.$file)) { copy ($dirsource.DIRECTORY_SEPARATOR.$file, $dirdest.DIRECTORY_SEPARATOR.$dirsource.DIRECTORY_SEPARATOR.$file); } else{ copy_recursive_dirs($dirsource.DIRECTORY_SEPARATOR.$file, $dirdest); } } } closedir($dir_handle); return true; } ?> from the above code the if loop has a copy function as per requirement, but the path applied for destination here is not proper, I have tried with basename function as well.. but it didnt got the expected result.. below is the if loop i am talking about with comment describing the output... if(!is_dir($dirsource.DIRECTORY_SEPARATOR.$file)) { $basefile = basename($dirsource.DIRECTORY_SEPARATOR.$file);//it gives the file name echo "Pranav<br>".$dirdest.DIRECTORY_SEPARATOR.$dirsource.DIRECTORY_SEPARATOR.$file;//it outputs for example "/var/www/html/parth//var/www/html/pranav_test/media/system/js/caption.js" which is not correct.. copy ($dirsource.DIRECTORY_SEPARATOR.$file, $dirdest.DIRECTORY_SEPARATOR.$dirsource.DIRECTORY_SEPARATOR.$file); } as shown above the i cannot get the files and folders copied to expected path.. please guide me to place proper path in the function....

    Read the article

  • Problem with JavaScript arithmetic

    - by Lynn
    I have a form for my customers to add budget projections. A prominent user wants to be able to show dollar values in either dollars, Kila-dollars or Mega-dollars. I'm trying to achieve this with a group of radio buttons that call the following JavaScript function, but am having problems with rounding that make the results look pretty crummy. Any advice would be much appreciated! Lynn function setDollars(new_mode) { var factor; var myfield; var myval; var cur_mode = document.proj_form.cur_dollars.value; if(cur_mode == new_mode) { return; } else if((cur_mode == 'd')&&(new_mode == 'kd')) { factor = "0.001"; } else if((cur_mode == 'd')&&(new_mode == 'md')) { factor = "0.000001"; } else if((cur_mode == 'kd')&&(new_mode == 'd')) { factor = "1000"; } else if((cur_mode == 'kd')&&(new_mode == 'md')) { factor = "0.001"; } else if((cur_mode == 'md')&&(new_mode == 'kd')) { factor = "1000"; } else if((cur_mode == 'md')&&(new_mode == 'd')) { factor = "1000000"; } document.proj_form.cur_dollars.value = new_mode; var cur_idx = document.proj_form.cur_idx.value; var available_slots = 13 - cur_idx; var td_name; var cell; var new_value; //Adjust dollar values for projections for(i=1;i<13;i++) { var myfield = eval('document.proj_form.proj_'+i); if(myfield.value == '') { myfield.value = 0; } var myval = parseFloat(myfield.value) * parseFloat(factor); myfield.value = myval; if(i < cur_idx) { document.getElementById("actual_"+i).innerHTML = myval; } }

    Read the article

  • Private Java class properties mysteriously reset between method calls....

    - by Michael Jones
    I have a very odd problem. A class property is mysteriously reset between method calls. The following code is executed so the constructor is called, then the parseConfiguration method is called. Finally, processData is called. The parseConfiguration method sets the "recursive" property to "true". However, as soon as it enters "processData", "recursive" becomes "false". This problem isn't isolated to a single class -- I have several examples of this in my code. How can this possibly be happening? I've tried initialising properties when they're declared outside any methods, I've tried initialising them in constructors... nothing works. The only complication I can think of here is that this class is invoked by an object that runs in a thread -- but here is one instance per thread, so surely no chance that threads are interfering. I've tried setting both methods to "synchronized", but this still happens. Please help! /** * This class or its superclasses are NOT threaded and don't extend Thread */ public class DirectoryAcquirer extends Manipulator { /** * @var Whether to recursively scan directories */ private boolean recursive = false; /** * Constructor */ public DirectoryAcquirer() { } /** * Constructor that initialises the configuration * * @param config * @throws InvalidConfigurationException */ public DirectoryAcquirer(HierarchicalConfiguration config) throws InvalidConfigurationException { super(config); } @Override protected void parseConfiguration() throws InvalidConfigurationException { // set whether to recurse into directories or not if (this.config.containsKey("recursive")) { // this.recursive gets set to "true" here this.recursive = this.config.getBoolean("recursive"); } } @Override public EntityCollection processData(EntityCollection data) { // here this.recursive is "false" this.logger.debug("processData: Entered method"); } }

    Read the article

  • Unique prime factors using HashSet

    - by theGreenCabbage
    I wrote a method that recursively finds prime factors. Originally, the method simply printed values. I am currently trying to add them to a HashSet to find the unique prime factors. In each of my original print statements, I added a primes.add() in order to add that particular integer into my set. My printed output remains the same, for example, if I put in the integer 24, I get 2*2*2*3. However, as soon as I print the HashSet, the output is simply [2]. public static Set<Integer> primeFactors(int n) { Set<Integer> primes = new HashSet<Integer>(); if(n <= 1) { System.out.print(n); primes.add(n); } else { for(int factor = 2; factor <= n; factor++) { if(n % factor == 0) { System.out.print(factor); primes.add(factor); if(factor < n) { System.out.print('*'); primeFactors(n/factor); } return primes; } } } return primes; } I have tried debugging via putting print statements around every line, but was unable to figure out why my .add() was not adding some values into my HashSet.

    Read the article

  • Oracle command hangs when using view for "WHERE x IN..." subquery

    - by Calvin Fisher
    I'm working on a web service that fetches data from an oracle data source in chunks and passes it back to an indexing/search tool in XML format. I'm the C#/.NET guy, and am kind of fuzzy on parts of Oracle. Our Oracle team gave us the following script to run, and it works well: SELECT ROWID, [columns] FROM [table] WHERE ROWID IN ( SELECT ROWID FROM ( SELECT ROWID FROM [table] WHERE ROWID > '[previous_batch_last_rowid]' ORDER BY ROWID ) WHERE ROWNUM <= 10000 ) ORDER BY ROWID 10,000 rows is an arbitrary but reasonable chunk size and ROWID is sufficiently unique for our purposes to use as a UID since each indexing run hits only one table at a time. Bracketed values are filled in programmatically by the web service. Now we're going to start adding views to the indexing, each of which will union a few separate tables. Since ROWID would no longer function as a unique identifier, they added a column to the views (VIEW_UNIQUE_ID) that concatenates the ROWIDs from the component tables to construct a UID for each union. But this script does not work, even though it follows the same form as the previous one: SELECT VIEW_UNIQUE_ID, [columns] FROM [view] WHERE VIEW_UNIQUE_ID IN ( SELECT VIEW_UNIQUE_ID FROM ( SELECT VIEW_UNIQUE_ID FROM [view] WHERE ROWID > '[previous_batch_last_view_unique_id]' ORDER BY VIEW_UNIQUE_ID ) WHERE ROWNUM <= 10000 ) ORDER BY VIEW_UNIQUE_ID It hangs indefinitely with no response from the Oracle server. I've waited 20+ minutes and the SQLTools dialog box indicating a running query remains the same, with no progress or updates. I've tested each subquery independently and each works fine and takes a very short amount of time (<= 1 second), so the view itself is sound. But as soon as the inner two SELECT queries are added with "WHERE VIEW_UNIQUE_ID IN...", it hangs. Why doesn't this query work for views? In what important way are they not interchangeable here?

    Read the article

  • Oracle command hangs when using view for "WHILE x IN..." subquery

    - by Calvin Fisher
    I'm working on a web service that fetches data from an oracle data source in chunks and passes it back to an indexing/search tool in XML format. I'm the C#/.NET guy, and am kind of fuzzy on parts of Oracle. Our Oracle team gave us the following script to run, and it works well: SELECT ROWID, [columns] FROM [table] WHERE ROWID IN ( SELECT ROWID FROM ( SELECT ROWID FROM [table] WHERE ROWID > '[previous_batch_last_rowid]' ORDER BY ROWID ) WHERE ROWNUM <= 10000 ) ORDER BY ROWID 10,000 rows is an arbitrary but reasonable chunk size and ROWID is sufficiently unique for our purposes to use as a UID since each indexing run hits only one table at a time. Bracketed values are filled in programmatically by the web service. Now we're going to start adding views to the indexing, each of which will union a few separate tables. Since ROWID would no longer function as a unique identifier, they added a column to the views (VIEW_UNIQUE_ID) that concatenates the ROWIDs from the component tables to construct a UID for each union. But this script does not work, even though it follows the same form as the previous one: SELECT VIEW_UNIQUE_ID, [columns] FROM [view] WHERE VIEW_UNIQUE_ID IN ( SELECT VIEW_UNIQUE_ID FROM ( SELECT VIEW_UNIQUE_ID FROM [view] WHERE ROWID > '[previous_batch_last_view_unique_id]' ORDER BY VIEW_UNIQUE_ID ) WHERE ROWNUM <= 10000 ) ORDER BY VIEW_UNIQUE_ID It hangs indefinitely with no response from the Oracle server. I've waited 20+ minutes and the SQLTools dialog box indicating a running query remains the same, with no progress or updates. I've tested each subquery independently and each works fine and takes a very short amount of time (<= 1 second), so the view itself is sound. But as soon as the inner two SELECT queries are added with "WHERE VIEW_UNIQUE_ID IN...", it hangs. Why doesn't this query work for views? In what important way are they not interchangeable here?

    Read the article

  • Subquery with multiple results combined into a single field?

    - by Todd
    Assume I have these tables, from which i need to display search results in a browser: Table: Containers id | name 1 Big Box 2 Grocery Bag 3 Envelope 4 Zip Lock Table: Sale id | date | containerid 1 20100101 1 2 20100102 2 3 20091201 3 4 20091115 4 Table: Items id | name | saleid 1 Barbie Doll 1 2 Coin 3 3 Pop-Top 4 4 Barbie Doll 2 5 Coin 4 I need output that looks like this: itemid itemname saleids saledates containerids containertypes 1 Barbie Doll 1,2 20100101,20100102 1,2 Big Box, Grocery Bag 2 Coin 3,4 20091201,20091115 3,4 Envelope, Zip Lock 3 Pop-Top 4 20091115 4 Zip Lock The important part is that each item type only gets one record/row in the return on the screen. I accomplished this in the past by returning multiple rows of the same item and using a scripting language to limit the output. However, this makes the ui overly complicated and loopy. So, I'm hoping I can get the database to spit out only as many records as there are rows to display. This example may be a bit extreme because of the 2 joins needed to get to the container from the item (through the sale table). I'd be happy for just an example query that outputs this: itemid itemname saleids saledates 1 Barbie Doll 1,2 20100101,20100102 2 Coin 3,4 20091201,20091115 3 Pop-Top 4 20091115 I can only return a single result in a subquery, so I'm not sure how to do this.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 19 20 21 22 23 24 25 26 27 28 29 30  | Next Page >