Search Results

Search found 31606 results on 1265 pages for 'generate table'.

Page 466/1265 | < Previous Page | 462 463 464 465 466 467 468 469 470 471 472 473  | Next Page >

• Is it possible to definitively identify whether a DML command was issued from a stored procedure?

  - by Ed Harper

  I have inherited a SQL Server 2008 database to which calling applications have access through stored procedures. Each table in the database has a shadow audit table into which Insert/Update/Delete operations for are logged. Performance testing on populating the audit tables showed that inserting the audit records using OUTPUT clauses was 20% or so faster than using triggers, so this has been implemented in the stored procedures. However, because this design cannot track changes made directly to the tables through DML statements issued directly against the tables, triggers have also been implemented which use the value of @@NESTLEVEL to determine whether or not to run the trigger (the assumption being that all DML run through stored procedures will have @@NESTLEVEL 1). i.e. the body of the trigger code looks something like: IF @@NESTLEVEL = 1 -- implies call is direct sql so generate history from here BEGIN ... insert into audit table This design is flawed because it won't track updates where DML statements are executed in dynamic SQL, or any other context where @@NESTLEVEL is raised above 1. Can anyone suggest a completely reliable method we can use in the triggers to execute them only if not triggered by a stored procedure? Or is this (as I suspect) not possible?

  Read the article

• Is this a legitimate implementation of a 'remember me' function for my web app?

  - by user246114

  Hi, I'm trying to add a "remember me" feature to my web app to let a user stay logged in between browser restarts. I think I got the bulk of it. I'm using google app engine for the backend which lets me use java servlets. Here is some pseudo-code to demo: public class MyServlet { public void handleRequest() { if (getThreadLocalRequest().getSession().getAttribute("user") != null) { // User already has session running for them. } else { // No session, but check if they chose 'remember me' during // their initial login, if so we can have them 'auto log in' // now. Cookie[] cookies = getThreadLocalRequest().getCookies(); if (cookies.find("rememberMePlz").exists()) { // The value of this cookie is the cookie id, which is a // unique string that is in no way based upon the user's // name/email/id, and is hard to randomly generate. String cookieid = cookies.find("rememberMePlz").value(); // Get the user object associated with this cookie id from // the data store, would probably be a two-step process like: // // select * from cookies where cookieid = 'cookieid'; // select * from users where userid = 'userid fetched from above select'; User user = DataStore.getUserByCookieId(cookieid); if (user != null) { // Start session for them. getThreadLocalRequest().getSession() .setAttribute("user", user); } else { // Either couldn't find a matching cookie with the // supplied id, or maybe we expired the cookie on // our side or blocked it. } } } } } // On first login, if user wanted us to remember them, we'd generate // an instance of this object for them in the data store. We send the // cookieid value down to the client and they persist it on their side // in the "rememberMePlz" cookie. public class CookieLong { private String mCookieId; private String mUserId; private long mExpirationDate; } Alright, this all makes sense. The only frightening thing is what happens if someone finds out the value of the cookie? A malicious individual could set that cookie in their browser and access my site, and essentially be logged in as the user associated with it! On the same note, I guess this is why the cookie ids must be difficult to randomly generate, because a malicious user doesn't have to steal someone's cookie - they could just randomly assign cookie values and start logging in as whichever user happens to be associated with that cookie, if any, right? Scary stuff, I feel like I should at least include the username in the client cookie such that when it presents itself to the server, I won't auto-login unless the username+cookieid match in the DataStore. Any comments would be great, I'm new to this and trying to figure out a best practice. I'm not writing a site which contains any sensitive personal information, but I'd like to minimize any potential for abuse all the same, Thanks

  Read the article

• How can I update a record using a correlated subquery?

  - by froadie

  I have a function that accepts one parameter and returns a table/resultset. I want to set a field in a table to the first result of that recordset, passing in one of the table's other fields as the parameter. If that's too complicated in words, the query looks something like this: UPDATE myTable SET myField = (SELECT TOP 1 myFunctionField FROM fn_doSomething(myOtherField) WHERE someCondition = 'something') WHERE someOtherCondition = 'somethingElse' In this example, myField and myOtherField are fields in myTable, and myFunctionField is a field return by fn_doSomething. This seems logical to me, but I'm getting the following strange error: 'myOtherField' is not a recognized OPTIMIZER LOCK HINTS option. Any idea what I'm doing wrong, and how I can accomplish this? *UPDATE: * Based on Anil Soman's answer, I realized that the function is expecting a string parameter and the field being passed is an integer. I'm not sure if this should be a problem as an explicit call to the function using an integer value works - e.g. fn_doSomething(12345) seems to automatically cast the number to an string. However, I tried to do an explicit cast: UPDATE myTable SET myField = (SELECT TOP 1 myFunctionField FROM fn_doSomething(CAST(myOtherField AS varchar(1000))) WHERE someCondition = 'something') WHERE someOtherCondition = 'somethingElse' Now I'm getting the following error: Line 5: Incorrect syntax near '('.

  Read the article

• Searching with MATCH(), AGAINST() and AS score with mysqli and php

  - by Drew

  Below is the code I am using to search my table. I have made the relevant columns FULLTEXT in the table. This doesn't return me anything. Can someone tell me what it is that i'm doing wrong? Thanks in advance. $sql = 'SELECT id,uname,class,school, MATCH(uname, class, school) AGAINST(?) AS score FROM images WHERE MATCH(uname, class, school) AGAINST(? IN BOOLEAN MODE) ORDER BY score DES'; $stmt = $db_connection->prepare($sql); $stmt->bind_param('ss',$keyword,$keyword); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($id,$uname,$class,$school); $xml = "<data>".PHP_EOL; while($stmt->fetch()){ $xml .= " <person>".PHP_EOL; $xml .= " <id>$id</id>".PHP_EOL; $xml .= " <name>$uname</name>".PHP_EOL; $xml .= " <class>$class</class>".PHP_EOL; $xml .= " <school>$school</school>".PHP_EOL; $xml .= " </person>".PHP_EOL; } $xml .= "</data>"; echo $xml; Below is an image of the indexes of the table:

  Read the article

• How to do steps of an API through CLI/ batch mode without GUI

  - by Dolphin

  I'm using Audiveris API to generate xml(MusicXML) file once the sheetmusic(e.g. pdf/img file) is being input (i.e. When I give the sheet music (pdf file) location, to generate the xml file out of it (in another location)). Audiveris has its own GUI to do this. But can I do this process of sheetmusic-to-xml without using their GUI, but only from the CLI? If so how may I approach it? And also if so - is there a possibility to make it work in CLI - using Java code (say to invoke steps for API to occur in the CLI using Java Code - I've heard this is possible using shell commands)? I managed to open the GUI by opening the jar file from CLI. But I need to know whether there's a possibility to carry out the entire sheetmusic(say pdf)-to-xml process without using their GUI, but only through CLI (i.e. in batch mode)? Greatly appreciate any help or guidance Thanks in advance

  Read the article

• clarification on the concept of "web service"

  - by udit

  Im a little confused on the varying definitions and implementations of web services available as implementations. Need some clarification please. Ones I have used till now: If a vendor gives me a specific format of XML that I can send populated with data to request and I make a simple HTTP POST over the internet passing in the XML String as the payload, is this a web service call ? If so, is there a specific name to it, this kind of web service ? Because obviously, it does not use anything like Axis, WSDL or SOAP to establish this connection. A variant of this is If the vendor gives me an XSD, I use JAXB to make a java class out of it and pass in the serialized version of the object, which eventually works out to be the same as option 1. RESTful web service: Vendor gives me a URL like http://restfulservice/products and I can make HTTP Requests to the URL and depending on what HTTP verb I use, the appropropriate action is called and the response sent over the wire. Ones I have only read about\ have a vague idea about SOAP. How does this work?.. Ive read the W3Schools tutorial and I undertsand that there is a very specific form of XML that is standardized according to W3C standards that we use to pass the same kind of messages as we did in option 1. But how does this work in real life? Vendor sends me what? Do I generate classes? Do I serialize some objects and http post them over to an address? Or do the generated objects themselves have connection methods that will do them for me? What about WSDL? When does a vendor send me WSDL and what do I do with it ? I guess I can generate classes from it. If yes, then what do I do with the generated classes ? When do I need that axis jar to generate classes from something that the vendor sends ? As you can see, I have some clear and other mostly vague ideas about the different kinds of web services available. would help if someone ould clarify and\or point to more real-world resources. I've looked a little bit into Java Web Services on the internet and the numerous four letter acronyms that get thrown at me make me dizzy. Thanks

  Read the article

• TicTacToe strategic reduction

  - by NickLarsen

  I decided to write a small program that solves TicTacToe in order to try out the effect of some pruning techniques on a trivial game. The full game tree using minimax to solve it only ends up with 549,946 possible games. With alpha-beta pruning, the number of states required to evaluate was reduced to 18,297. Then I applied a transposition table that brings the number down to 2,592. Now I want to see how low that number can go. The next enhancement I want to apply is a strategic reduction. The basic idea is to combine states that have equivalent strategic value. For instance, on the first move, if X plays first, there is nothing strategically different (assuming your opponent plays optimally) about choosing one corner instead of another. In the same situation, the same is true of the center of the walls of the board, and the center is also significant. By reducing to significant states only, you end up with only 3 states for evaluation on the first move instead of 9. This technique should be very useful since it prunes states near the top of the game tree. This idea came from the GameShrink method created by a group at CMU, only I am trying to avoid writing the general form, and just doing what is needed to apply the technique to TicTacToe. In order to achieve this, I modified my hash function (for the transposition table) to enumerate all strategically equivalent positions (using rotation and flipping functions), and to only return the lowest of the values for each board. Unfortunately now my program thinks X can force a win in 5 moves from an empty board when going first. After a long debugging session, it became apparent to me the program was always returning the move for the lowest strategically significant move (I store the last move in the transposition table as part of my state). Is there a better way I can go about adding this feature, or a simple method for determining the correct move applicable to the current situation with what I have already done?

  Read the article

• copy rows before updating them to preserve archive in Postgres

  - by punkish

  I am experimenting with creating a table that keeps a version of every row. The idea is to be able to query for how the rows were at any point in time even if the query has JOINs. Consider a system where the primary resource is books, that is, books are queried for, and author info comes along for the ride CREATE TABLE authors ( author_id INTEGER NOT NULL, version INTEGER NOT NULL CHECK (version > 0), author_name TEXT, is_active BOOLEAN DEFAULT '1', modified_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (author_id, version) ) INSERT INTO authors (author_id, version, author_name) VALUES (1, 1, 'John'), (2, 1, 'Jack'), (3, 1, 'Ernest'); I would like to be able to update the above like so UPDATE authors SET author_name = 'Jack K' WHERE author_id = 1; and end up with 2, 1, Jack, t, 2012-03-29 21:35:00 2, 2, Jack K, t, 2012-03-29 21:37:40 which I can then query with SELECT author_name, modified_on FROM authors WHERE author_id = 2 AND modified_on < '2012-03-29 21:37:00' ORDER BY version DESC LIMIT 1; to get 2, 1, Jack, t, 2012-03-29 21:35:00 Something like the following doesn't really work CREATE OR REPLACE FUNCTION archive_authors() RETURNS TRIGGER AS $archive_author$ BEGIN IF (TG_OP = 'UPDATE') THEN -- The following fails because author_id,version PK already exists INSERT INTO authors (author_id, version, author_name) VALUES (OLD.author_id, OLD.version, OLD.author_name); UPDATE authors SET version = OLD.version + 1 WHERE author_id = OLD.author_id AND version = OLD.version; RETURN NEW; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $archive_author$ LANGUAGE plpgsql; CREATE TRIGGER archive_author AFTER UPDATE OR DELETE ON authors FOR EACH ROW EXECUTE PROCEDURE archive_authors(); How can I achieve the above? Or, is there a better way to accomplish this? Ideally, I would prefer to not create a shadow table to store the archived rows.

  Read the article

• How to insert an element between the two elements dynamically?

  - by Harish

  I am using a table, in which there are buttons, on button click i want the new TR element to be inserted between the two TR or at the end of the TR... my code goes here <table> <tbody> <tr> <td> <input type="submit" value="Add" onclick="addFunction()" /> </td> </tr> <tr> <td> <input type="submit" value="Add" onclick="addFunction()" /> </td> </tr> <tr> <td> <input type="submit" value="Add" onclick="addFunction()" /> </td> </tr> </tbody> </table> i want to insert new TR element next to the element which has triggered the event... NOTE: i am not using any javascript library, just plain javascript

  Read the article

• [hibernate - jpa] @OneToOne annotoation problem (i think...)

  - by blow

  Hi all, im new in hibernate and JPA and i have some problems with annotations. My target is to create this table in db (PERSON_TABLE with personal-details) ID ADDRESS NAME SURNAME MUNICIPALITY_ID First of all, i have a MUNICIPALITY table in db containing all municipality of my country. I mapped this table in this ENTITY: @Entity public class Municipality implements Serializable { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) private Long id; private String country; private String province; private String name; @Column(name="cod_catasto") private String codCatastale; private String cap; public Municipality() { } ... Then i make an EMBEDDABLE class Address containing fields that realize a simple address... @Embeddable public class Address implements Serializable { @OneToOne(cascade=CascadeType.ALL) @JoinColumn(name="id_municipality") private Municipality municipality; @Column(length=45) private String address; public Address() { } ... Finally i embedded this class into Person ENTITY @Entity public class Person implements Serializable { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) private Long id; private String name; private String surname; @Embedded private Address address; public Person() { } ... All works good when i have to save a new Person record, in fact hibernate creates a PERSON_TABLE as i want, but if i try to retrieve a Person record i have an exception. HQL is simply "from Person" The excpetion is (Entities is the package containing all classes above-mentioned): org.hibernate.AnnotationException: @OneToOne or @ManyToOne on Entities.Person.address.municipality references an unknown entity: Entities.Municipality Is the @OneToOne annotation the problem? Thanks.

  Read the article

• comma separated values in oracle function body

  - by dmitry

  I've got following oracle function but it does not work and errors out. I used Ask Tom's way to convert comma separated values to be used in select * from table1 where col1 in <> declared in package header: TYPE myTableType IS table of varchar2 (255); Part of package body: l_string long default iv_value_with_comma_separated|| ','; l_data myTableType := myTableType(); n NUMBER; begin begin LOOP EXIT when l_string is null; n := instr( l_string, ',' ); l_data.extend; l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 ) ) ); l_string := substr( l_string, n+1 ); END LOOP; end; OPEN my_cursor FOR select * from table_a where column_a in (select * from table (l_data)); CLOSE my_cursor END; above fails but it works fine when I remove select * from table (l_data) Can someone please tell me what I might be doing wrong here??

  Read the article

• Reporting on release changes when using JIRA, Ivy and SVN

  - by HaveAGuess

  Hey guys, We have an Ivy release procedure where resolve our dependancies using Ivy pre-tagging which means that we can pull in changes for releases by other members of the team that we are not expecting We want to generate reports and track changes to dependancies between releases but there doesn't seem to be an easy way of doing this without a significant effort. We also want to generate a JIRA list of all changes not only in the main project but in dependancies that we own. This is an extension of the problem above. Otherwise we simply don't know what we are releasing - any ideas?!

  Read the article

• classic asp ignore comma within speach marks CSV

  - by user2968227

  I Have a CSV File that looks like this 1,HELLO,ENGLISH 2,HELLO1,ENGLISH 3,HELLO2,ENGLISH 4,HELLO3,ENGLISH 5,HELLO4,ENGLISH 6,HELLO5,ENGLISH 7,HELLO6,ENGLISH 8,"HELLO7, HELLO7 ...",ENGLISH 9,HELLO7,ENGLISH 10,HELLO7,ENGLISH I want to step loop through the lines and write to a table using split classic asp function by comma. When Speech marks are present to ignore the comma within those speech marks and take the string. Please help. <% dim csv_to_import,counter,line,fso,objFile csv_to_import="uploads/testLang.csv" set fso = createobject("scripting.filesystemobject") set objFile = fso.opentextfile(server.mappath(csv_to_import)) str_imported_data="<table cellpadding='3' cellspacing='1' border='1'>" Do Until objFile.AtEndOfStream line = split(objFile.ReadLine,",") str_imported_data=str_imported_data&"<tr>" total_records=ubound(line) for i=0 to total_records if i>0 then str_imported_data=str_imported_data&"<td>"&line(i)&"</td>" else str_imported_data=str_imported_data&"<th>"&line(i)&"</th>" end if next str_imported_data=str_imported_data&"</tr>" & chr(13) Loop str_imported_data=str_imported_data&"<caption>Total Number of Records: "&total_records&"</caption></table>" objFile.Close response.Write str_imported_data %>

  Read the article

• Visual Studio + Database Edition + CDC = Deploy Fail

  - by Ben

  Hi All, I've got a database using change data capture (CDC) that is created from a Visual Studio database project (GDR2). My problem is that I have a stored procedure that is analyzing the CDC information and then returning data. How is that a problem you ask? Well, the order of operation is as follows. Pre-deployment Script Tables Indexes, keys, etc. Procedures Post-deployment Script Inside the post-deployment script is where I enable CDC. Here-in lies the problem. The procedure that is acting on the CDC tables is bombing because they don't exist yet! I've tried to put the call to sys.sp_cdc_enable_table in the script that creates the table, but it doesn't like that. Error 102 TSD03070: This statement is not recognized in this context. C:...\Schema Objects\Schemas\dbo\Tables\Foo.table.sql 20 1 Foo Is there a better/built-in way to enable CDC such that it's references are available when the stored procedures are created? Is there a way to run a script after tables are created but before other objects are created? How about a way to create the procedure dependencies be damned? Or maybe I'm just doing things that shouldn't be done?!?! Now, I have a work around. Comment out the sproc body Deploy (CDC is created) Uncomment sproc Deploy Everything is great until the next time I update a CDC tracked table. Then I need to comment out the 'offending' procedure. Thanks for reading my question and thanks for your help!

  Read the article

• Validating Login / Changing User settings / Php Mysql

  - by Marcelo

  Hi everyone, my questions are about login, and changing already saved data. (Q1) 'Till now I've only saved input in the tables of the database (registration steps), now I need to check if the input (login steps), are the same of my table in database, in fact I have 3 types of users, then I'll have to check 3 kind of tables. Then if the input data matches with one of those 3 tables I will redirect the user to his specific area. I'm thinking about saved the submitted data $login=$_REQUEST['login']; and $password=$_REQUEST['password']; and compare with the login column in the database. Then if the login matches, I'll compare the password submitted with the one in the row, not in the column. But I don't know how to do this search and comparison,neither what to use. Then if both matches I'll redirect the user. Else I'll send an login error message. (this I know how to do) (Q2) What if need to change an already saved user ? For example to change an email address. My changing user's data web page is exactly the same like the registration user web page. Can I load the already saved options and values of registration (table user for example). Then the user will change whatever he thinks it's necessary, and then when he submits the new information, they would not create a new row in my table, but just be overwritten the old information? How can I do this? Sorry for any mistake in English, and Thanks for the attention.

  Read the article

• Generating a Grid of NSTextField Objects From NSDictionary Items

  - by SteveStifler

  I'm trying to create an vocabulary study application using Obj-C and the Cocoa frameworks. I have about two week's experience in both areas and have reached an edge of my current knowledge. Here's where I'm stuck. When I press a checkbox, a corresponding plist is loaded into memory as an NSDictionary. I want to generate a "Label: Textfield" pair for each key:value pair, where the Label is the key. When the text typed into the Textfield matches the key's value, I want the Label's text to turn green. So how would I generate this grid, and once generated, how would I make the text green upon correct input? Thanks!

  Read the article

• Refactoring - Speed increase

  - by Michael G

  How can I make this function more efficient. It's currently running at 6 - 45 seconds. I've ran dotTrace profiler on this specific method, and it's total time is anywhere between 6,000ms to 45,000ms. The majority of the time is spent on the "MoveNext" and "GetEnumerator" calls. and example of the times are 71.55% CreateTableFromReportDataColumns - 18, 533* ms - 190 calls -- 55.71% MoveNext - 14,422ms - 10,775 calls What can I do to speed this method up? it gets called a lot, and the seconds add up: private static DataTable CreateTableFromReportDataColumns(Report report) { DataTable table = new DataTable(); HashSet<String> colsToAdd = new HashSet<String> { "DataStream" }; foreach (ReportData reportData in report.ReportDatas) { IEnumerable<string> cols = reportData.ReportDataColumns.Where(c => !String.IsNullOrEmpty(c.Name)).Select(x => x.Name).Distinct(); foreach (var s in cols) { if (!String.IsNullOrEmpty(s)) colsToAdd.Add(s); } } foreach (string col in colsToAdd) { table.Columns.Add(col); } return table; }

  Read the article

• Creating a function in Postgresql that does not return composite values

  - by celenius

  I'm learning how to write functions in Postgresql. I've defined a function called _tmp_myfunction() which takes in an id and returns a table (I also define a table object type called _tmp_mytable) -- create object type to be returned CREATE TYPE _tmp_mytable AS ( id integer, cost double precision ); -- create function which returns query CREATE OR REPLACE FUNCTION _tmp_myfunction( id integer ) RETURNS SETOF _tmp_mytable AS $$ BEGIN RETURN QUERY SELECT id, cost FROM sales WHERE id = sales.id; END; $$ LANGUAGE plpgsql; This works fine when I use one id and call it using the following approach: SELECT * FROM _tmp_myfunction(402); What I would like to be able to do is to call it, but to use a column of values instead of just one value. However, if I use the following approach I end up with all values of the table in one column, separated by commas: -- call function using all values in a column SELECT _tmp_myfunction(t.id) FROM transactions as t; I understand that I can get the same result if I use SELECT _tmp_myfunction(402); instead of SELECT * FROM _tmp_myfunction(402); but I don't know how to construct my query in such a way that I can separate out the results.

  Read the article

• Heavy Mysql operation & Time Constraints [closed]

  - by Rahul Jha

  There is a performance issue where that I have stuck with my application which is based on PHP & MySql. The application is for Data Migration where data has to be uploaded and after various processes (Cleaning from foreign characters, duplicate check, id generation) it has to be inserted into one central table and then to 5 different tables. There, an id is generated and that id has to be updated to central table. There are different sets of records and validation rules. The problem I am facing is that when I insert say(4K) rows file (containing 20 columns) it is working fine within 15 min it gets inserted everywhere. But, when I insert the same records again then at this time it is taking one hour to insert (ideally it should get inserted by marking earlier inserted data as duplicate). After going through the log file, I noticed is that there is a Mysql select statement where I am checking the duplicates and getting ID which are duplicates. Then I am calling a function inside for loop which is basically inserting records into 5 tables and updates id to central table. This Calling function is major time of whole process. P.S. The records has to be inserted record by record.. Kindly Suggest some solution.. //This is that sample code $query=mysql_query("SELECT DISTINCT p1.ID FROM table1 p1, table2 p2, table3 a WHERE p2.datatype =0 AND (p1.datatype =1 || p1.datatype=2) AND p2.ID =0 AND p1.ID = a.ID AND p1.coulmn1 = p2.column1 AND p1.coulmn2 = p2.coulmn2 AND a.coulmn3 = p2.column3"); $num=mysql_num_rows($query); for($i=0;$i<$num;$i++) { $f=mysql_result($query,$i,"ID"); //calling function RecordInsert($f); }

  Read the article

• Improve Log Exceptions

  - by Jaider

  I am planning to use log4net in a new web project. In my experience, I see how big the log table can get, also I notice that errors or exceptions are repeated. For instance, I just query a log table that have more than 132.000 records, and I using distinct and found that only 2.500 records are unique (~2%), the others (~98%) are just duplicates. so, I came up with this idea to improve logging. Having a couple of new columns: counter and updated_dt, that are updated every time try to insert same record. If want to track the user that cause the exception, need to create a user_log or log_user table, to map N-N relationship. Create this model may made the system slow and inefficient trying to compare all these long text... Here the trick, we should also has a hash column of binary of 16 or 32, that hash the message and the exception, and configure an index on it. We can use HASHBYTES to help us. I am not an expert in DB, but I think that will made the faster way to locate a similar record. And because hashing doesn't guarantee uniqueness, will help to locale those similar record much faster and later compare by message or exception directly to make sure that are unique. This is a theoretical/practical solution, but will it work or bring more complexity? what aspects I am leaving out or what other considerations need to have? the trigger will do the job of insert or update, but is the trigger the best way to do it?

  Read the article

• MySQL SELECT combining 3 SELECTs INTO 1

  - by Martin Tóth

  Consider following tables in MySQL database: entries: creator_id INT entry TEXT is_expired BOOL other: creator_id INT entry TEXT userdata: creator_id INT name VARCHAR etc... In entries and other, there can be multiple entries by 1 creator. userdata table is read only for me (placed in other database). I'd like to achieve a following SELECT result: +------------+---------+---------+-------+ | creator_id | entries | expired | other | +------------+---------+---------+-------+ | 10951 | 59 | 55 | 39 | | 70887 | 41 | 34 | 108 | | 88309 | 38 | 20 | 102 | | 94732 | 0 | 0 | 86 | ... where entries is equal to SELECT COUNT(entry) FROM entries GROUP BY creator_id, expired is equal to SELECT COUNT(entry) FROM entries WHERE is_expired = 0 GROUP BY creator_id and other is equal to SELECT COUNT(entry) FROM other GROUP BY creator_id. I need this structure because after doing this SELECT, I need to look for user data in the "userdata" table, which I planned to do with INNER JOIN and select desired columns. I solved this problem with selecting "NULL" into column which does not apply for given SELECT: SELECT creator_id, COUNT(any_entry) as entries, COUNT(expired_entry) as expired, COUNT(other_entry) as other FROM ( SELECT creator_id, entry AS any_entry, NULL AS expired_entry, NULL AS other_enry FROM entries UNION SELECT creator_id, NULL AS any_entry, entry AS expired_entry, NULL AS other_enry FROM entries WHERE is_expired = 1 UNION SELECT creator_id, NULL AS any_entry, NULL AS expired_entry, entry AS other_enry FROM other ) AS tTemp GROUP BY creator_id ORDER BY entries DESC, expired DESC, other DESC ; I've left out the INNER JOIN and selecting other columns from userdata table on purpose (my question being about combining 3 SELECTs into 1). Is my idea valid? = Am I trying to use the right "construction" for this? Are these kind of SELECTs possible without creating an "empty" column? (some kind of JOIN) Should I do it "outside the DB": make 3 SELECTs, make some order in it (let's say python lists/dicts) and then do the additional SELECTs for userdata? Solution for a similar question does not return rows where entries and expired are 0. Thank you for your time.

  Read the article

• How to figure out which record has been deleted in an effiecient way?

  - by janetsmith

  Hi, I am working on an in-house ETL solution, from db1 (Oracle) to db2 (Sybase). We needs to transfer data incrementally (Change Data Capture?) into db2. I have only read access to tables, so I can't create any table or trigger in Oracle db1. The challenge I am facing is, how to detect record deletion in Oracle? The solution which I can think of, is by using additional standalone/embedded db (e.g. derby, h2 etc). This db contains 2 tables, namely old_data, new_data. old_data contains primary key field from tahle of interest in Oracle. Every time ETL process runs, new_data table will be populated with primary key field from Oracle table. After that, I will run the following sql command to get the deleted rows: SELECT old_data.id FROM old_data WHERE old_data.id NOT IN (SELECT new_data.id FROM new_data) I think this will be a very expensive operation when the volume of data become very large. Do you have any better idea of doing this? Thanks.

  Read the article

• How to create a clip region from a path that includes the 'outline'?

  - by mackenir

  I am creating a rounded rectangle GraphicsPath (see red outline image below), and then using this as a clip region both when drawing graphics, and as the Region of a Form. Unfortunately, although the path looks good, it doesn't work well as a region (see solid black image below) Is there a way that I can generate a clipping region from the path that includes all the 'outline' pixels of the path? Do I need to generate a bitmap and then process this to create a region? The rounded rectangle path: When used as a clip region: The discrepancy (red pixels are in the path outline, but outside the region. blue pixels are in both):

  Read the article

• Mysql many to many problem (leaderborad/scoreboard)

  - by zoko2902

  Hi all! I'm working on a small project in regards of the upcoming World Cup. I'm building a roster/leaderboard/scoredboard based on groups with national teams. The idea is to have information on all upcoming matches within the group or in the knockout phase (scores, time of the match, match stats etc.). Currently I'm stuck with the DB in that I can't come up with a query that would return paired teams in a row. I have these 3 tables: CREATE TABLE IF NOT EXISTS `wc_team` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NULL , `description` VARCHAR(250) NULL , `flag` VARCHAR(45) NULL , `image` VARCHAR(45) NULL , `added` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`) , CREATE TABLE IF NOT EXISTS `wc_match` ( `id` INT NOT NULL AUTO_INCREMENT , `score` VARCHAR(6) NULL , `date` DATE NULL , `time` VARCHAR(45) NULL , `added` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`) , CREATE TABLE IF NOT EXISTS `wc_team_has_match` ( `wc_team_id` INT NOT NULL , `wc_match_id` INT NOT NULL , PRIMARY KEY (`wc_team_id`, `wc_match_id`) , I've simplified the tables so we don't go in the wrong direction. Now I've tried al kinds of joins and groupings I could think of, but I never seem to get. Example guery: SELECT t.wc_team_id,t.wc_match_id,c.id.c.name,d.id,d.name FROM wc_team_has_match AS t LEFT JOIN wc_match AS s ON t.wc_match_id = s.id LEFT JOIN wc_team AS c ON t.wc_team_id = c.id LEFT JOIN wc_team AS d ON t.wc_team_id = d.id Which returns: wc_team_id wc_match_id id name id name 16 5 16 Brazil 16 Brazil 18 5 18 Argentina 18 Argentina But what I really want is: wc_team_id wc_match_id id name id name 16 5 16 Brazil 18 Argentina Keep in mind that a group has more matches I want to see all those matches not only one. Any pointer or suggestion would be extremly appreciated since I'm stuck like a duck on this one :).

  Read the article

• What tools do you use when generating markup from mockups?

  - by Paul

  So I endeavor to spend most of time on the app server side of things but time to time I need to get my hands dirty and generate markup/css/js from a wireframe or mockup. As far as tools go, Ive found browsershots and Litmus app helpful and of course, vm's as well for checking things out live in ie-{6,7,8}. Otherwise I do the heavy lifting in vim. For generating new markup thats not tied to a target design I think some of the css frameworks & tools like sass look useful but Im skeptical of their utility when needing to generate markup to match a photoshop design. So what tips / tools do you keep in your markup generating utility belt when building solid markup from designs? My list so far: Browsershots Browserlab.adobe.com Haml / Less / Sass (not used but will probably explore)

  Read the article

< Previous Page | 462 463 464 465 466 467 468 469 470 471 472 473  | Next Page >