hierarchical query - Page 261

Querying tables based on other column values

- by blcArmadillo

Is there a way to query different databases based on the value of a column in the query? Say for example you have the following columns: id part_id attr_id attr_value_ext attr_value_int You then run a query and if the attr_id is '1' is returns the attr_value_int column but if attr_id is greater than '1' it joins data from another table based on the attr_value_ext.

Read the article

Result Set Problem

- by vipul

Hello Friends, It is possible to Use two query with in single ResultSet

Read the article

How do I convert tuple of tuples to list in one line (pythonic)?

- by ThinkCode

query = 'select mydata from mytable' cursor.execute(query) myoutput = cursor.fetchall() print myoutput (('aa',), ('bb',), ('cc',)) Why is it (cursor.fetchall) returning a tuple of tuples instead of a tuple since my query is asking for only one column of data? What is the best way of converting it to ['aa', 'bb', 'cc'] ? I can do something like this : mylist = [] myoutput = list(myoutput) for each in myoutput: mylist.append(each[0]) I am sure this isn't the best way of doing it. Please enlighten me!

Read the article

exec problem in sql 2005

- by IordanTanev

Hi, i have the situation where i have two databases whith same structure. The first have some data in its datatables. I need to create a script that will transfer the data from the first database to the second. I have created this script. DECLARE @table_name nvarchar(MAX), @query nvarchar(MAX) DECLARE @table_cursor CURSOR SET @table_cursor = CURSOR FAST_FORWARD FOR Select TABLE_NAME FROM INFORMATION_SCHEMA.TABLES OPEN @table_cursor FETCH NEXT FROM @table_cursor INTO @table_name WHILE @@FETCH_STATUS = 0 BEGIN SET @query = 'INSERT INTO ' + @table_name + ' SELECT * FROM MyDataBase.dbo.' + @table_name print @query exec @query FETCH NEXT FROM @table_cursor INTO @table_name END CLOSE @table_cursor DEALLOCATE @table_cursor The problem is that when i run th script the "print @query" statement prints statement like this INSERT INTO table SELECT * FROM MyDataBase.dbo.table When i copy this and run it from Management studio it works fine. But when the script trys to run it with exec i get this error Msg 911, Level 16, State 1, Line 21 Could not locate entry in sysdatabases for database 'INSERT INTO table SELECT * FROM MPDEV090314'. No entry found with that name. Make sure that the name is entered correctly. Hope someone can tell me whot is wront with this. Best Regards, Iordan Tanev

Read the article

Calling a protected Windows executable with Perl

- by Jake

I'm trying to write a perl script that determines which users are currently logged into Windows by using query.exe (c:\Windows\system32\query.exe). Perl is unable to access this file, unable to execute it, even unable to see that it exists, as I've found with the following code: print `dir c:\\windows\\system32\\query*`; This produces the following output: 07/13/2009 05:16 PM 1,363,456 Query.dll 1 File(s) 1,363,456 bytes 0 Dir(s) 183,987,658,752 bytes free I've checked the user executing the script using perl's getlogin function, and it returns the name of a member of the local Administrators group (specifically, me). I've also tried adding read/execute permissions for "Everyone", but windows keeps giving me access denied errors when I try to modify this file's permissions. Finally, I've tried running perl.exe as an administrator but that doesn't fix the problem either. Is this something I can solve by changing some settings in Windows? Do I need to add something to my perl script? Or is there just no way to grant perl access to some of these processes?

Read the article

Perform Grouping of Resultsets in Code, not on Database Level

- by NinjaBomb

Stackoverflowers, I have a resultset from a SQL query in the form of: Category Column2 Column3 A 2 3.50 A 3 2 B 3 2 B 1 5 ... I need to group the resultset based on the Category column and sum the values for Column2 and Column3. I have to do it in code because I cannot perform the grouping in the SQL query that gets the data due to the complexity of the query (long story). This grouped data will then be displayed in a table. I have it working for specific set of values in the Category column, but I would like a solution that would handle any possible values that appear in the Category column. I know there has to be a straightforward, efficient way to do it but I cannot wrap my head around it right now. How would you accomplish it? EDIT I have attempted to group the result in SQL using the exact same grouping query suggested by Thomas Levesque and both times our entire RDBMS crashed trying to process the query. I was under the impression that Linq was not available until .NET 3.5. This is a .NET 2.0 web application so I did not think it was an option. Am I wrong in thinking that? EDIT Starting a bounty because I believe this would be a good technique to have in the toolbox to use no matter where the different resultsets are coming from. I believe knowing the most concise way to group any 2 somewhat similar sets of data in code (without .NET LINQ) would be beneficial to more people than just me.

Read the article

Split function in where clause

- by abhishek-khandelwal

hello friends I am using following query in linq In product table following type of data are stored abc-def bcd=fgh abc-xyz var query=from prod in db.Product join cat in db.category on prod.categoryId=cat.categoryID where prod.productName.split('-')[0]=="abc" but in that query it product annoumous problem Please give some suggestion to split in where caluse

Read the article

Unit testing a controller method?

- by Stefan Kendall

I have a controller method like such: def search = { def query = params.query ... render results as JSON } How do I unit test this? Specifically, how do I call search to set params.query, and how do I test the results of the method render? Is there a way to mock the render method, perhaps?

Read the article

How to improve INSERT INTO ... SELECT locking behavior

- by Artem

In our production database, we ran the following pseudo-code SQL batch query running every hour: INSERT INTO TemporaryTable (SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true) Now this query itself does not need to be fast, but I noticed it was locking up HighlyContentiousTableInInnoDb, even though it was just reading from it. Which was making some other very simple queries take ~25 seconds (that's how long that other query takes). Then I discovered that InnoDB tables in such a case are actually locked by a SELECT! http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/ But I don't really like the solution in the article of selecting into an OUTFILE, it seems like a hack (temporary files on filesystem seem sucky). Any other ideas? Is there a way to make a full copy of an InnoDB table without locking it in this way during the copy. Then I could just copy the HighlyContentiousTable to another table and do the query there.

Read the article

MySql scoping problem with correlated subqueries

- by Rolf

Hi, I'm having this Mysql query, It works: SELECT nom ,prenom ,(SELECT GROUP_CONCAT(category_en) FROM (SELECT DISTINCT category_en FROM categories c WHERE id IN (SELECT DISTINCT category_id FROM m3allems_to_categories m2c WHERE m3allem_id = 37) ) cS ) categories ,(SELECT GROUP_CONCAT(area_en) FROM (SELECT DISTINCT area_en FROM areas c WHERE id IN (SELECT DISTINCT area_id FROM m3allems_to_areas m2a WHERE m3allem_id = 37) ) aSq ) areas FROM m3allems m WHERE m.id = 37 The result is: nom prenom categories areas Man Multi Carpentry,Paint,Walls Beirut,Baalbak,Saida It works correclty, but only when i hardcode into the query the id that I want (37). I want it to work for all entries in the m3allem table, so I try this: SELECT nom ,prenom ,(SELECT GROUP_CONCAT(category_en) FROM (SELECT DISTINCT category_en FROM categories c WHERE id IN (SELECT DISTINCT category_id FROM m3allems_to_categories m2c WHERE m3allem_id = m.id) ) cS ) categories ,(SELECT GROUP_CONCAT(area_en) FROM (SELECT DISTINCT area_en FROM areas c WHERE id IN (SELECT DISTINCT area_id FROM m3allems_to_areas m2a WHERE m3allem_id = m.id) ) aSq ) areas FROM m3allems m And I get an error: Unknown column 'm.id' in 'where clause' Why? From the MySql manual: 13.2.8.7. Correlated Subqueries [...] Scoping rule: MySQL evaluates from inside to outside. So... do this not work when the subquery is in a SELECT section? I did not read anything about that. Does anyone know? What should I do? It took me a long time to build this query... I know it's a monster query but it gets what I want in a single query, and I am so close to getting it to work! Can anyone help?

Read the article

Optimization of running total calculation in SQL for multiple values per join condition

- by Kiril

I have the following table (test_table): date value --------------- d1 10.0 d1 20.0 d2 60.0 d2 10.0 d2 -20.0 d3 40.0 I calculate the running total as follows. I use the same query twice, because first I need to calculate the values for a specifi date, and afterwards I can calculate the running total. Otherwise, joining the two tables where date is not unique, I would get too many results from the join: SELECT t1.date, SUM(t2.value) AS total FROM (SELECT date, SUM(value) AS value FROM test_table GROUP BY date) AS t1 JOIN (SELECT date, SUM(value) AS value FROM test_table GROUP BY date) AS t2 ON t1.date >= t2.date GROUP BY t1.date ORDER BY t1.date This gives me (which is fine): date total ------------- d1 30.0 d2 80.0 d3 120.0 BUT, this query isn't very efficient, because I need to change conditions in two places, if necessary. In production, the test_table is a lot bigger ( 4 Mio. rows), and the query takes too much time to complete. Question: How can I avoid using the same query twice?

Read the article

SQL: Join vs. subquery

- by Col. Shrapnel

I am an old-school MySQL user and always preferred JOIN over sub-query. But nowadays everyone uses sub-query and I hate it, dunno why. Though I've lack of theoretical knowledge to judge myself if there are any difference. Well, I am curious if sub-query as good as join and there is no thing to worry about?

Read the article

Django sphinx works only after app restart.

- by Lhiash

Hi, I've set up django-sphinx in my project, which works perfectly only for some time. Later it always returns empty result set. Surprisingly restarting django app fixes it. And search works again but again only for short time (or very limiter number of queries). Heres my sphinx.conf: source src_questions { # data source type = mysql sql_host = xxxxxx sql_user = xxxxxx #replace with your db username sql_pass = xxxxxx #replace with your db password sql_db = xxxxxx #replace with your db name # these two are optional sql_port = xxxxxx #sql_sock = /var/lib/mysql/mysql.sock # pre-query, executed before the main fetch query sql_query_pre = SET NAMES utf8 # main document fetch query sql_query = SELECT q.id AS id, q.title AS title, q.tagnames AS tags, q.html AS text, q.level AS level \ FROM question AS q \ WHERE q.deleted=0 \ # optional - used by command-line search utility to display document information sql_query_info = SELECT title, id, level FROM question WHERE id=$id sql_attr_uint = level } index questions { # which document source to index source = src_questions # this is path and index file name without extension # you may need to change this path or create this folder path = /home/rafal/core_index/index_questions # docinfo (ie. per-document attribute values) storage strategy docinfo = extern # morphology morphology = stem_en # stopwords file #stopwords = /var/data/sphinx/stopwords.txt # minimum word length min_word_len = 3 # uncomment next 2 lines to allow wildcard (*) searches min_infix_len = 1 enable_star = 1 # charset encoding type charset_type = utf-8 } # indexer settings indexer { # memory limit (default is 32M) mem_limit = 64M } # searchd settings searchd { # IP address on which search daemon will bind and accept # optional, default is to listen on all addresses, # ie. address = 0.0.0.0 address = 127.0.0.1 # port on which search daemon will listen port = 3312 # searchd run info is logged here - create or change the folder log = ../log/sphinx.log # all the search queries are logged here query_log = ../log/query.log # client read timeout, seconds read_timeout = 5 # maximum amount of children to fork max_children = 30 # a file which will contain searchd process ID pid_file = searchd.pid # maximum amount of matches this daemon would ever retrieve # from each index and serve to client max_matches = 1000 } and heres my search part from views.py: content = Question.search.query(keywords) if level: content = content.filter(level=level)#level is array of integers There are no errors in any logs, it just isnt returning any results. All help would be most appreciated.

Read the article

DBLinq not generating where clause

- by sipwiz

I'm testing out DBLinq-0.18 and DBLinq from SVN Trunk with MySQL and Postgresql. I'm only using a very simple query but on both database DBLinq is not generating a Where clause. I have confirmed this by turning on statement logging on Postgresql to check exactly what request DBLinq is sending. My Linq query is: MyDB db = new MyDB(new NpgsqlConnection("Database=database;Host=localhost;User Id=postgres;Password=password")); var customers = from customer in db.Customers where customer.CustomerUserName == "test" select customer; The query works ok but the SQL generated by DBLinq is of the form: select customerusername, customerpassword .... from public.customers There is no Where clause which means DBLinq must be pulling the whole table down before running the Linq query. Has anyone had any experience with DBLinq and know what I could be doing wrong?

Read the article

How to designing a generic databse whos layout may change over time?

- by mawg

Here's a tricky one - how do I programatically create and interrogate a database who's contents I can't really foresee? I am implementing a generic input form system. The user can create PHP forms with a WYSIWYG layout and use them for any purpose he wishes. He can also query the input. So, we have three stages: a form is designed and generated. This is a one-off procedure, although the form can be edited later. This designs the database. someone or several people make use of the form - say for daily sales reports, stock keeping, payroll, etc. Their input to the forms is written to the database. others, maybe management, can query the database and generate reports. Since these forms are generic, I can't predict the database structure - other than to say that it will reflect HTML form fields and consist of a the data input from collection of edit boxes, memos, radio buttons and the like. Questions and remarks: A) how can I best structure the database, in terms of tables and columns? What about primary keys? My first thought was to use the control name to identify each column, then I realized that the user can edit the form and rename, so that maybe "name" becomes "employee" or "wages" becomes ":salary". I am leaning towards a unique number for each. B) how best to key the rows? I was thinking of a timestamp to allow me to query and a column for the row Id from A) C) I have to handle column rename/insert/delete. Foe deletion, I am unsure whether to delete the data from the database. Even if the user is not inputting it from the form any more he may wish to query what was previously entered. Or there may be some legal requirements to retain the data. Any gotchas in column rename/insert/delete? D) For the querying, I can have my PHP interrogate the database to get column names and generate a form with a list where each entry has a database column name, a checkbox to say if it should be used in the query and, based on column type, some selection criteria. That ought to be enough to build searches like "position = 'senior salesman' and salary 50k". E) I probably have to generate some fancy charts - graphs, histograms, pie charts, etc for query results of numerical data over time. I need to find some good FOSS PHP for this. F) What else have I forgotten? This all seems very tricky to me, but I am database n00b - maybe it is simple to you gurus?

Read the article

Invalid parameter number: number of bound variables does not match number of tokens

- by Alex

I have a table: 'objects' with few columns: object_id:int, object_type:int, object_status:int, object_lati:float, object_long:float My query is : $stmt = $db->query('SELECT o.object_id, o.object_type, o.object_status, o.object_lati, o.object_long FROM objects o WHERE o.object_id = 1'); $res = $stmt->fetch(); Pdo throw error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens When i remove column object_lati or object_long query is work fine.

Read the article

Masspay and MySql

- by Mike

Hi, I am testing Paypal's masspay using their 'MassPay NVP example' and I having difficulty trying to amend the code so inputs data from my MySql database. Basically I have user table in MySql which contains email address, status of payment (paid,unpaid) and balance. CREATE TABLE `users` ( `user_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(100) collate latin1_general_ci NOT NULL, `status` enum('unpaid','paid') collate latin1_general_ci NOT NULL default 'unpaid', `balance` int(10) NOT NULL default '0', PRIMARY KEY (`user_id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci Data : 1 [email protected] paid 100 2 [email protected] unpaid 11 3 [email protected] unpaid 20 4 [email protected] unpaid 1 5 [email protected] unpaid 20 6 [email protected] unpaid 15 I then have created a query which selects users with an unpaid balance of $10 and above : $conn = db_connect(); $query=$conn->query("SELECT * from users WHERE balance >='10' AND status = ('unpaid')"); What I would like to is for each record returned from the query for it to populate the code below: Now the code which I believe I need to amend is as follows: for($i = 0; $i < 3; $i++) { $receiverData = array( 'receiverEmail' => "[email protected]", 'amount' => "example_amount",); $receiversArray[$i] = $receiverData; } However I just can't get it to work, I have tried using mysqli_fetch_array and then replaced "[email protected]" with $row['email'] and "example_amount" with row['balance'] in various methods of coding but it doesn't work. Also I need it to loop to however many rows that were retrieved from the query as <3 in the for loop above. So the end result I am looking for is for the $nvpStr string to pass with something like this: $nvpStr = "&EMAILSUBJECT=test&RECEIVERTYPE=EmailAddress&CURRENCYCODE=USD&[email protected]&L_Amt=11&[email protected]&L_Amt=11&[email protected]&L_Amt=20&[email protected]&L_Amt=20&[email protected]&L_Amt=15"; Thanks

Read the article

retrieve my wall post in my native i phone application

- by hardik

hello all i want to retrieve my all the wall post that i have posted until now i am using this fql query from my native iphone application in order to fetch it but i think i am maiking some mistake please guide me how could i do that the following is my fql query to get wall post - (void)session:(FBSession*)session didLogin:(FBUID)uid { NSString *fql = [NSString stringWithFormat:@"SELECT comments, likes FROM stream WHERE post_id=100000182297319"]; NSLog(@"session key is %@",_session.sessionKey); NSLog(@"from global key is %@",[twitfacedemoAppDelegate getfacebookkey]); NSDictionary* params = [NSDictionary dictionaryWithObject:fql forKey:@"query"]; [[FBRequest requestWithDelegate:self] call:@"facebook.fql.query" params:params]; this is my other delgate method to get the wall post but this code is not working - (void)request:(FBRequest*)request didLoad:(id)result { @try { NSLog(@"result is %@",result); if(result==nil) { UIAlertView *a=[[UIAlertView alloc]initWithTitle:@"Can't login" message:@"You cant login here." delegate:nil cancelButtonTitle:@"ok" otherButtonTitles:nil]; [a show]; [a release]; } else { NSArray* users = result; NSDictionary* user = [users objectAtIndex:0]; NSString* name = [[NSString alloc]initWithString:[user objectForKey:@"name"]]; NSLog(@"name is %@",name); _label.text = [NSString stringWithFormat:@"Logged in as %@", name]; NSString *globalfacebookname; globalfacebookname=[[NSString alloc]initWithString:name]; NSLog(@"value is %@",globalfacebookname); NSString *fid=[user objectForKey:@"uid"]; NSLog(@"FACEBOOK ID WITH STRING DATATYP %@",fid); [twitfacedemoAppDelegate fetchfacebookid:fid]; } } @catch (NSException * e) { NSLog(@"Problem in dialog didFailWithError:%@",e); } } please guide me how could i retrieve my all the post from facebook to my native iphone application through fql query thanks in advance

Read the article

PHP calling PostgreSQL function - type issue?

- by CitrusTree

I have a function in PostgreSQL / plpgsql with the following signature: CREATE OR REPLACE FUNCTION user_login(TEXT, TEXT) RETURNS SETOF _get_session AS $$ ... $$ Where _get_session is a view. The function works fine when calling it from phpPgAdmin, however whan I call it from PHP I get the following error: Warning: pg_query() [function.pg-query]: Query failed: ERROR: type "session_ids" does not exist CONTEXT: compile of PL/pgSQL function "user_login" near line 2 in /home/sites/blah.com/index.php on line 69 The DECLARE section of the function contains the following variables: oldSessionId session_ids := $1; newSessionId session_ids := $2; The domain session_ids DOES exist, and other functions which use the same domain work when called from the same script. The PHP is as follows: $query = "SELECT * FROM $dbschema.user_login('$session_old'::TEXT, '$session'::TEXT)"; $result = pg_query($login, $query); I have also tried this using ::session_ids in place of ::TEXT when calling the function, however I recieve the same error. Help :o(

Read the article

NHibernate FetchMode.Lazy

- by RyanFetz

I have an object which has a property on it that has then has collections which i would like to not load in a couple situations. 98% of the time i want those collections fetched but in the one instance i do not. Here is the code I have... Why does it not set the fetch mode on the properties collections? [DataContract(Name = "ThemingJob", Namespace = "")] [Serializable] public class ThemingJob : ServiceJob { [DataMember] public virtual Query Query { get; set; } [DataMember] public string Results { get; set; } } [DataContract(Name = "Query", Namespace = "")] [Serializable] public class Query : LookupEntity<Query>, DAC.US.Search.Models.IQueryEntity { [DataMember] public string QueryResult { get; set; } private IList<Asset> _Assets = new List<Asset>(); [IgnoreDataMember] [System.Xml.Serialization.XmlIgnore] public IList<Asset> Assets { get { return _Assets; } set { _Assets = value; } } private IList<Theme> _Themes = new List<Theme>(); [IgnoreDataMember] [System.Xml.Serialization.XmlIgnore] public IList<Theme> Themes { get { return _Themes; } set { _Themes = value; } } private IList<Affinity> _Affinity = new List<Affinity>(); [IgnoreDataMember] [System.Xml.Serialization.XmlIgnore] public IList<Affinity> Affinity { get { return _Affinity; } set { _Affinity = value; } } private IList<Word> _Words = new List<Word>(); [IgnoreDataMember] [System.Xml.Serialization.XmlIgnore] public IList<Word> Words { get { return _Words; } set { _Words = value; } } } using (global::NHibernate.ISession session = NHibernateApplication.GetCurrentSession()) { global::NHibernate.ICriteria criteria = session.CreateCriteria(typeof(ThemingJob)); global::NHibernate.ICriteria countCriteria = session.CreateCriteria(typeof(ThemingJob)); criteria.AddOrder(global::NHibernate.Criterion.Order.Desc("Id")); var qc = criteria.CreateCriteria("Query"); qc.SetFetchMode("Assets", global::NHibernate.FetchMode.Lazy); qc.SetFetchMode("Themes", global::NHibernate.FetchMode.Lazy); qc.SetFetchMode("Affinity", global::NHibernate.FetchMode.Lazy); qc.SetFetchMode("Words", global::NHibernate.FetchMode.Lazy); pageIndex = Convert.ToInt32(pageIndex) - 1; // convert to 0 based paging index criteria.SetMaxResults(pageSize); criteria.SetFirstResult(pageIndex * pageSize); countCriteria.SetProjection(global::NHibernate.Criterion.Projections.RowCount()); int totalRecords = (int)countCriteria.List()[0]; return criteria.List<ThemingJob>().ToPagedList<ThemingJob>(pageIndex, pageSize, totalRecords); }

Read the article

The question about the basics of LINQ to SQL

- by Alex

I just started learning LINQ to SQL, and so far I'm impressed with the easy of use and good performance. I used to think that when doing LINQ queries like from Customer in DB.Customers where Customer.Age > 30 select Customer LINQ gets all customers from the database ("SELECT * FROM Customers"), moves them to the Customers array and then makes a search in that Array using .NET methods. This is very inefficient, what if there are hundreds of thousands of customers in the database? Making such big SELECT queries would kill the web application. Now after experiencing how actually fast LINQ to SQL is, I start to suspect that when doing that query I just wrote, LINQ somehow converts it to a SQL Query string SELECT * FROM Customers WHERE Age > 30 And only when necessary it will run the query. So my question is: am I right? And when is the query actually run? The reason why I'm asking is not only because I want to understand how it works in order to build good optimized applications, but because I came across the following problem. I have 2 tables, one of them is Books, the other has information on how many books were sold on certain days. My goal is to select books that had at least 50 sales/day in past 10 days. It's done with this simple query: from Book in DB.Books where (from Sale in DB.Sales where Sale.SalesAmount >= 50 && Sale.DateOfSale >= DateTime.Now.AddDays(-10) select Sale.BookID).Contains(Book.ID) select Book The point is, I have to use the checking part in several queries and I decided to create an array with IDs of all popular books: var popularBooksIDs = from Sale in DB.Sales where Sale.SalesAmount >= 50 && Sale.DateOfSale >= DateTime.Now.AddDays(-10) select Sale.BookID; BUT when I try to do the query now: from Book in DB.Books where popularBooksIDs.Contains(Book.ID) select Book It doesn't work! That's why I think that we can't use thins kinds of shortcuts in LINQ to SQL queries, like we can't use them in real SQL. We have to create straightforward queries, am I right?

Read the article

Hibernate HQL with interfaces

- by Benju

According to this section of the Hibernate documentation I should be able to query any java class in HQL http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-polymorphism Unfortunately when I run this query... "from Transaction trans where trans.envelopeId=:envelopeId" I get the message "Transaction is not mapped [from Transaction trans where trans.envelopeId=:envelopeId]". Transaction is an interface, I have to entity classes that implement it, I want on HQL query to return a Collection of type Transaction.

Read the article

Using SPServices & jQuery to Find My Stuff from Multi-Select Person/Group Field

- by Mark Rackley

Okay… quick blog post for all you SPServices fans out there. I needed to quickly write a script that would return all the tasks currently assigned to me. I also wanted it to return any task that was assigned to a group I belong to. This can actually be done with a CAML query, so no big deal, right? The rub is that the “assigned to” field is a multi-select person or group field. As far as I know (and I actually know so little) you cannot just write a CAML query to return this information. If you can, please leave a comment below and disregard the rest of this blog post… So… what’s a hacker to do? As always, I break things down to their most simple components (I really love the KISS principle and would get it tattooed on my back if people wouldn’t think it meant “Knights In Satan’s Service”. You really gotta be an old far to get that reference). Here’s what we’re going to do: Get currently logged in user’s name as it is stored in a person field Find all the SharePoint groups the current user belongs to Retrieve a set of assigned tasks from the task list and then find those that are assigned to current user or group current user belongs to Nothing too hairy… So let’s get started Some Caveats before I continue There are some obvious performance implications with this solution as I make a total of four SPServices calls and there’s a lot of looping going on. Also, the CAML query in this blog has NOT been optimized. If you move forward with this code, tweak it so that it returns a further subset of data or you will see horrible performance if you have a few hundred entries in your task list. Add a date range to the CAML or something. Find some way to limit the results as much as possible. Lastly, if you DO have a better solution, I would like you to share. Iron sharpens iron and all… Alright, let’s really get started. Get currently logged in user’s name as it is stored in a person field First thing we need to do is understand how a person group looks when you look at the XML returned from a SharePoint Web Service call. It turns out it’s stored like any other multi select item in SharePoint which is <id>;#<value> and when you assign a person to that field the <value> equals the person’s name “Mark Rackley” in my case. This is for Windows Authentication, I would expect this to be different in FBA, but I’m not using FBA. If you want to know what it looks like with FBA you can use the code in this blog and strategically place an alert to see the value. Anyway… I need to find the name of the user who is currently logged in as it is stored in the person field. This turns out to be one SPServices call: var userName = $().SPServices.SPGetCurrentUser({ fieldName: "Title", debug: false }); As you can see, the “Title” field has the information we need. I suspect (although again, I haven’t tried) that the Title field also contains the user’s name as we need it if I was using FBA. Okay… last thing we need to do is store our users name in an array for processing later: myGroups = new Array(); myGroups.push(userName); Find all the SharePoint groups the current user belongs to Now for the groups. How are groups returned in that XML stream? Same as the person <ID>;#<Group Name>, and if it’s a mutli select it’s all returned in one big long string “<ID>;#<Group Name>;#<ID>;#<Group Name>;#<ID>;#<Group Name>;#<ID>;#<Group Name>;#<ID>;#<Group Name>”. So, how do we find all the groups the current user belongs to? This is also a simple SPServices call. Using the “GetGroupCollectionFromUser” operation we can find all the groups a user belongs to. So, let’s execute this method and store all our groups. $().SPServices({ operation: "GetGroupCollectionFromUser", userLoginName: $().SPServices.SPGetCurrentUser(), async: false, completefunc: function(xData, Status) { $(xData.responseXML).find("[nodeName=Group]").each(function() { myGroups.push($(this).attr("Name")); }); } }); So, all we did in the above code was execute the “GetGroupCollectionFromUser” operation and look for the each “Group” node (row) and store the name for each group in our array that we put the user’s name in previously (myGroups). Now we have an array that contains the current user’s name as it will appear in the person field XML and all the groups the current user belongs to. The Rest Now comes the easy part for all of you familiar with SPServices. We are going to retrieve our tasks from the Task list using “GetListItems” and look at each entry to see if it belongs to this person. If it does belong to this person we are going to store it for later processing. That code looks something like this: // get list of assigned tasks that aren't closed... *modify the CAML to perform better!* $().SPServices({ operation: "GetListItems", async: false, listName: "Tasks", CAMLViewFields: "<ViewFields>" + "<FieldRef Name='AssignedTo' />" + "<FieldRef Name='Title' />" + "<FieldRef Name='StartDate' />" + "<FieldRef Name='EndDate' />" + "<FieldRef Name='Status' />" + "</ViewFields>", CAMLQuery: "<Query><Where><And><IsNotNull><FieldRef Name='AssignedTo'/></IsNotNull><Neq><FieldRef Name='Status'/><Value Type='Text'>Completed</Value></Neq></And></Where></Query>", completefunc: function (xData, Status) { var aDataSet = new Array(); //loop through each returned Task $(xData.responseXML).find("[nodeName=z:row]").each(function() { //store the multi-select string of who task is assigned to var assignedToString = $(this).attr("ows_AssignedTo"); found = false; //loop through the persons name and all the groups they belong to for(var i=0; i<myGroups.length; i++) { //if the person's name or group exists in the assigned To string //then the task is assigned to them if (assignedToString.indexOf(myGroups[i]) >= 0){ found = true; break; } } //if the Task belongs to this person then store or display it //(I'm storing it in an array) if (found){ var thisName = $(this).attr("ows_Title"); var thisStartDate = $(this).attr("ows_StartDate"); var thisEndDate = $(this).attr("ows_EndDate"); var thisStatus = $(this).attr("ows_Status"); var aDataRow=new Array( thisName, thisStartDate, thisEndDate, thisStatus); aDataSet.push(aDataRow); } }); SomeFunctionToDisplayData(aDataSet); } }); Some notes on why I did certain things and additional caveats. You will notice in my code that I’m doing an AssignedToString.indexOf(GroupName) to see if the task belongs to the person. This could possibly return bad results if you have SharePoint Group names that are named in such a way that the “IndexOf” returns a false positive. For example if you have a Group called “My Users” and a group called “My Users – SuperUsers” then if a user belonged to “My Users” it would return a false positive on executing “My Users – SuperUsers”.IndexOf(“My Users”). Make sense? Just be aware of this when naming groups, we don’t have this problem. This is where also some fine-tuning can probably be done by those smarter than me. This is a pretty inefficient method to determine if a task belongs to a user, I mean what if a user belongs to 20 groups? That’s a LOT of looping. See all the opportunities I give you guys to do something fun?? Also, why am I storing my values in an array instead of just writing them out to a Div? Well.. I want to pass my data to a jQuery library to format it all nice and pretty and an Array is a great way to do that. When all is said and done and we put all the code together it looks like: $(document).ready(function() { var userName = $().SPServices.SPGetCurrentUser({ fieldName: "Title", debug: false }); myGroups = new Array(); myGroups.push(userName ); $().SPServices({ operation: "GetGroupCollectionFromUser", userLoginName: $().SPServices.SPGetCurrentUser(), async: false, completefunc: function(xData, Status) { $(xData.responseXML).find("[nodeName=Group]").each(function() { myGroups.push($(this).attr("Name")); }); // get list of assigned tasks that aren't closed... *modify this CAML to perform better!* $().SPServices({ operation: "GetListItems", async: false, listName: "Tasks", CAMLViewFields: "<ViewFields>" + "<FieldRef Name='AssignedTo' />" + "<FieldRef Name='Title' />" + "<FieldRef Name='StartDate' />" + "<FieldRef Name='EndDate' />" + "<FieldRef Name='Status' />" + "</ViewFields>", CAMLQuery: "<Query><Where><And><IsNotNull><FieldRef Name='AssignedTo'/></IsNotNull><Neq><FieldRef Name='Status'/><Value Type='Text'>Completed</Value></Neq></And></Where></Query>", completefunc: function (xData, Status) { var aDataSet = new Array(); //loop through each returned Task $(xData.responseXML).find("[nodeName=z:row]").each(function() { //store the multi-select string of who task is assigned to var assignedToString = $(this).attr("ows_AssignedTo"); found = false; //loop through the persons name and all the groups they belong to for(var i=0; i<myGroups.length; i++) { //if the person's name or group exists in the assigned To string //then the task is assigned to them if (assignedToString.indexOf(myGroups[i]) >= 0){ found = true; break; } } //if the Task belongs to this person then store or display it //(I'm storing it in an array) if (found){ var thisName = $(this).attr("ows_Title"); var thisStartDate = $(this).attr("ows_StartDate"); var thisEndDate = $(this).attr("ows_EndDate"); var thisStatus = $(this).attr("ows_Status"); var aDataRow=new Array( thisName, thisStartDate, thisEndDate, thisStatus); aDataSet.push(aDataRow); } }); SomeFunctionToDisplayData(aDataSet); } }); } }); }); Final Thoughts So, there you have it. Take it and run with it. Make it something cool (and tell me how you did it). Another possible way to improve performance in this scenario is to use a DVWP to display the tasks and use jQuery and the “myGroups” array from this blog post to hide all those rows that don’t belong to the current user. I haven’t tried it, but it does move some of the processing off to the server (generating the view) so it may perform better. As always, thanks for stopping by… hope you have a Merry Christmas…

Read the article

How do you read system jobs in Dynamics CRM?

- by Dan Crowther

The CRM SDK says this is possible but the following code fails. Does anyone know why? var request = new RetrieveMultipleRequest(); var query = new QueryExpression(EntityName.asyncoperation.ToString()); query.ColumnSet = new AllColumns(); request.Query = query; var response = _connection.Execute(request); The error is: <error>\n <code>0x80040216</code> <description>An unexpected error occurred.</description> <type>Platform</type> </error> If I change the entity name to account, it works fine.

Read the article

Compiled Queries and "Parameters cannot be sequences"

- by David B

I thought that compiled queries would perform the same query translation as DataContext. Yet I'm getting a run-time error when I try to use a query with a .Contains method call. Where have I gone wrong? //private member which holds a compiled query. Func<DataAccess.DataClasses1DataContext, List<int>, List<DataAccess.TestRecord>> compiledFiftyRecordQuery = System.Data.Linq.CompiledQuery.Compile <DataAccess.DataClasses1DataContext, List<int>, List<DataAccess.TestRecord>> ((dc, ids) => dc.TestRecords.Where(tr => ids.Contains(tr.ID)).ToList()); //this method calls the compiled query. public void FiftyRecordCompiledQueryByID() { List<int> IDs = GetRandomInts(50); //System.NotSupportedException //{"Parameters cannot be sequences."} List<DataAccess.TestRecord> results = compiledFiftyRecordQuery (myContext, IDs); }

Search Results

Search found 17627 results on 706 pages for 'hierarchical query'.

Page 261/706 | < Previous Page | 257 258 259 260 261 262 263 264 265 266 267 268 | Next Page >

- by blcArmadillo

- by vipul

- by ThinkCode

- by IordanTanev

- by Jake

- by NinjaBomb

- by abhishek-khandelwal

- by Stefan Kendall

- by Artem

- by Rolf

- by Kiril

- by Col. Shrapnel

- by Lhiash

- by sipwiz

- by mawg

- by Alex

- by Mike

- by hardik

- by CitrusTree

- by RyanFetz

- by Alex

- by Benju

- by Mark Rackley

- by Dan Crowther

- by David B

< Previous Page | 257 258 259 260 261 262 263 264 265 266 267 268 | Next Page >