I have a query with a hard coded value which is also hard coded into my application (which has a comment saying that the value should be the same as the value hard coded into the query).  What would be considered the best way to make that value discoverable outside of the single query it is being used in?  A UDF immediately jumps to mind, but prior to 2019 it kills performance (don’t have 2019 so I can’t verify it, but it seems inlined UDFs should fix the performance problem).    A view or TFV might be a good alternative, but both seem overly complex and a misleading.  I don’t have a table/view of constants/lookup values.  Is there a good solution for this situation or just stick with comments in the relevant code?
123106
I want to know how to efficiently store query-affecting settings in a large analytical database (MS SQL Server). By efficiency I mean no hardcode (1) and fast/optimal query processing (2).  My example  I've got a massive table of sales orders. Let's simplify it to two columns:  SalesOrder  OrderID      int      Primary Key  OrderDate    date   I need to retrieve the list of orders for the last X days and this X should be configurable. That's why I introduce a table of settings where I define it:  Admin_Configuration  SettingName   (PK)|      SettingValue  ------------------|------------------  DaysBack          |          30      It allows me not to hardcode this "30" in query definitions. Then I would run queries like:  SELECT OrderId   FROM SalesOrder as S   JOIN Admin_Configuration as A     ON A.SettingName = 'DaysBack'   WHERE [OrderDate] &gt; DATEADD(DAY, -A.SettingValue, GETDATE());   Performance  It avoids hardcoding, but leads to a performance problem. This query runs much slower than it would've, had I simply hard-coded 30 in the WHERE clause.  My understanding is that optimizer doesn't know how large my SettingValue is and assumes the worst - that all rows would be retrieved from the SalesOrders table, thus leading to a suboptimal query plan.  Trying a scalar function yielded even worse results. I'm desperate enough to have a separate view which stores these hardcoded settings but I've been refraining from it with hopes for a better solution.  Here is the original query plan (with a join): 4 seconds elapsed   Here is the plan with subquery, as :  SELECT orderid FROM [SalesOrder] as s WHERE s.OrderDate &gt; dateadd(DAY,    (SELECT -NumValue    FROM Admin_Configuration where SettingName = 'DaysBack'   )     , getdate())   7 seconds elapsed, slightly slower   As for the ideal performance: I just hardcoded 20, the query took 2 seconds. It needs mentioning that the Admin_Configuration table contains only 18 rows, hence it shouldn't affect performance.  SELECT OrderId   FROM SalesOrder as S   JOIN Admin_Configuration as A     ON A.SettingName = 'DaysBack'   WHERE [OrderDate] &gt; DATEADD(DAY, -20, GETDATE());   Using parameters would mean that I'd have to replace views (that I use now) with stored procedures/table-valued functions, which is unacceptable in my case
76549
For example  Drupal\Core\Entity\Entity::id() returns $this-&gt;id(or NULL) Drupal\Core\Entity\ContentEntityBase::id() returns $this-&gt;id-&gt;value Drupal\Core\Entity\ContentEntityBase::uuid() returns $this-&gt;get('uuid')-&gt;value Drupal\node\Entity\Node::id() returns $this-&gt;get('nid')-&gt;value  Also in some methods there are setValue() and getValue() utilised but sometimes there are just $this-&gt;field-&gt;value. So what is the reason for this inconsistent behavior? From my understanding all the &quot;fields&quot; are TypedData fields hence the setValue() and getValue() methods should be used instead of directly access $this-&gt;field-&gt;value. Besides it's strange because $value is private.
58103
I was answering a question and this little inconsistency popped up.  Just wondering if anyone has come across it before, or if anyone knows why this happens.  A variable that is declared within some form of scoped element (if, for loop, etc.) shouldn't be available outside that scope.  i.e.  {     // accountNames variable is not available outside this scope     Set&lt;String&gt; accountNames = new Set&lt;String&gt; {'Test Account'}; }  system.debug(accountNames);   This debug statement fails with the message:      Variable does not exist: accountNames   However, if this is part of a query string being generated, the Database.query() call somehow has access to it.  String queryString = 'SELECT Id, Name FROM Account';  if(true) {     // accountNames variable is not available outside the `if` statement     Set&lt;String&gt; accountNames = new Set&lt;String&gt; {'Test Account'};     queryString += ' WHERE Name IN :accountNames'; }  // this debug will fail if you uncomment it // system.debug(accountNames);  // however the query somehow has access to it. // create an account called Test Account to prove it.  system.debug('queryString: ' + queryString); system.debug('queryString result: ' + database.query(queryString));   But, Salesforce is smart enough to know that it's not just some random string, so the following will fail:  // Alternatively, use a random string as the variable name, and salesforce  // is smart enough to know it's gibberish String queryString2 = 'SELECT Id, Name FROM Account';  if(true) {     queryString2 += ' WHERE Name IN :randomString'; }  try {     system.debug('queryString2 result: ' + database.query(queryString2));     system.assert(false); } catch (QueryException qe)  {     system.debug(':randomString is gibberish: ' + qe.getMessage()); }     Edit:  Interestingly, the :randomString is definitely calculated only when the query is executed.  String queryString3 = 'SELECT Id, Name FROM Account'; String queryString4 = 'SELECT Id, Name FROM Account';  if(true) {     // assign to query 3 before it has a value     queryString3 += ' WHERE Name IN :randomString';      // assign to query 4 after it has been declared     Set&lt;String&gt; randomString = new Set&lt;String&gt;{'Test Account'};     queryString4 += ' WHERE Name IN :randomString'; }  // both queries return correct results, regardless of when the variable was declared system.debug('queryString3 result: ' + database.query(queryString3)); system.debug('queryString4 result: ' + database.query(queryString4));   But it's not an open slather.  The following throws an error:  String queryString = 'SELECT Id, Name FROM Account WHERE Name = :someField'; someMethod(); system.debug('queryString result: ' + database.query(queryString));  void someMethod() {     String someField = 'Test Account';     system.debug('someField: ' + someField); }     Does anyone know why this is the case, or have an explanation why this would work?  As far as I can tell, at the time that the accountNames variable is in scope, no part of the code knows that it's anything other than a string.
16849
Is it possible to retrieve the values of a Global Picklist/GlobalValueSet using Schema class without a SOQL ?
117983
I am trying to remove duplicate and creating a set and list with non duplicate values related to field ord__c . Trying to minimize the code as far as possible. Let me know if I can handle the same using the below piece of code I am using :   Set &lt; Id &gt; OrId = new Set &lt; Id &gt; ();  Integer j = 0;         while (j &lt; ListR.size())         {           OrId.add(ListR.get(j).Ord__c);           if(OrId.contains(ListR.get(j).Ord__c))           {             ListR.remove(j);           }           else           {             j++;           }         }
9044
I am coding in ColdFusion, but trying to stay in cfscript, so I have a function that allows me to pass in a query to run it with   &lt;cfquery blah >   #query# &lt;/cfquery>   Somehow though, when I construct my queries with sql = "SELECT * FROM a WHERE b='#c#'"  and pass it in, ColdFusion has replaced the single quotes with 2 single quotes.  so it becomes  WHERE b=''c'' in the final query.  I have tried creating the strings a lot of different ways, but I cannot get it to leave just one quote.  Even doing a string replace has no effect.   Any idea why this is happening?  It is ruining my hopes of living in cfscript for the duration of this project
115908
Suppose the following function:  CREATE FUNCTION [dbo].[ufnTest]() RETURNS TABLE AS RETURN SELECT 1 AS Nr   My actual function will select actual data from many different tables. I (mostly) understand the risks of using nolock hints, and decided that in this case I actually want them. So I try to call above function like this:  SELECT * FROM [dbo].[ufnTest]() WITH(NOLOCK)   However,  with the following message:     Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.   The tips for fixing this error are not relevant. Perhaps this table hint isn't available for table valued functions then?  There are several alternatives I'd consider. One is to use the hint on all tables in my select query, but I'd prefer not to as (a) I'll have to remember this whenever I change the function around and (b) it's repetitive. Another alternative would be to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, but a downside is that I don't quite know how to set it back to wat it was before (and statements after the select from my TVF should have the original isolation level).  So, I'd prefer to make WITH (NOLOCK) for a select from a table valued function work. Is this possible?
13332
I am trying to use a  as input and then creating a query with it - so I need a .    I was surprised to find that it couldn't do the conversion implicitly or explicitly - but I don't even know how I would do this, as the Java API is still fairly new to me.
92996
So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results.  For example, the following query doesn't return rows where value IS NULL but I want it to:  SELECT * FROM table WHERE   (value != 26)   AND date IS NULL   AND last_modified &gt;= '5/21/2014'   I understand I can do the following as a workaround but seriously? Having to add parentheses and check if is null for every single field every single time I want to include it? Seems ugly, not intuitive and crazy.  SELECT * FROM table WHERE   (value != 26 OR value is null)   AND date IS NULL   AND last_modified &gt;= '5/21/2014'   I mean I know NULL is not a value and thus can't be compared but can't you infer that it is, in fact, definitely not 26? If 26 is something and NULL is nothing and nothing is not something then NULL is not 26. Seems logical to me.  Does anyone know how I can, in a cleaner way, include nulls in my results when using comparisons without having to include an explicit check each and every time. Also turning off nulls on my tables is definitely not an option.  Edit:  My real problem of why I don't want to do it the way I showed was not exposed. So here goes. I am writing a program that let's you build queries to database tables and let's the user dynamically create filters and such which essentially at the end of the day constructs a SQL statement and gets the results to display to the user. The fields chosen by the user can be any and/or all fields in a given database and if I have to literally put that ISNULL check on every single field that would be really inefficient and make looking at the SQL super ugly. My program is table-definition-agnostic meaning I don't care what's in your table and don't want to know what your table's definition is. I just want you to pick a table, choose some fields to filter on with equals, not equals, in, not in, etc ... and then click a button to view the results of that request.
47691
I've heard that "everyone" is using parameterized SQL queries to protect against SQL injection attacks without having to vailidate every piece of user input.  How do you do this?  Do you get this automatically when using stored procedures?  So my understanding this is non-parameterized:    cmdText = String.Format("SELECT foo FROM bar WHERE baz = '{0}'", fuz)   Would this be parameterized?  cmdText = String.Format("EXEC foo_from_baz '{0}'", fuz)   Or do I need to do somethng more extensive like this in order to protect myself from SQL injection?  With command     .Parameters.Count = 1     .Parameters.Item(0).ParameterName = "@baz"     .Parameters.Item(0).Value = fuz End With   Are there other advantages to using parameterized queries besides the security considerations?  Update:  This great article was linked in one of the questions references by Grotok.
63506
This is a part of my .  I have the below logic to split a string value from a column in table tblProjects as below:  SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID  SET @Docs = @Docs + '$' SET @pos = 0 SET @len = 0  WHILE CHARINDEX('$', @Docs, @pos + 1)&gt;0 BEGIN   SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos   SET @value = SUBSTRING(@Docs, @pos, @len)    IF CHARINDEX('|', @value) &gt;0   BEGIN     SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),             @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)     INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)   END       SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1 END   A sample input would be   insert into tblProjects(ID, Documents)  values('P_1', 'gdgfg|jkkgk$bkgkkj|kjgkgk$vjffj|Khkgjhg$jkgkvghv|kbkghhkgk');   I need to split the Documents string and insert the values into a separate table. Values delimited by | should go into separate columns of the same row, whereas values for a particular row are delimited by $.   Is there a single query method to complete this operation, for all rows in the table, in the minimum possible time?
103863
I'm trying to get the ID of a lookup field when using search api. My query looks something like this:  /_api/Search/query?querytext='ListId:12345678-1234-1234-1234-123456789012'&amp;selectproperties='Title,Company'   Where Company is the lookup field, in a specific list.  The result looks like this:  &lt;d:element m:type="SP.KeyValue"&gt;   &lt;d:Key&gt;Company&lt;/d:Key&gt;   &lt;d:Value&gt;Some company&lt;/d:Value&gt;   &lt;d:ValueType&gt;Edm.String&lt;/d:ValueType&gt; &lt;/d:element&gt;   I have created a managed property which looks like this:
91411
I'm trying to write a key:value environment which would allow me to create a dual columns tables where the columns are separated by colons.  Typically, I've implemented this using parbox in my own class file:  \makeatletter \newenvironment{keylist}{\global\setlength{\parindent}{0pt}}{} \newlength{\@keyvalueXposition} \setlength{\@keyvalueXposition}{0.5\textwidth} \newcommand{\keyvalue}[2]{     \parbox[t]{\@keyvalueXposition}{#1}     :     \parbox[t]{\@keyvalueXposition}{#2}     \\ } \makeatother   Which is typically used like this on the user-side:  \begin{keylist}     \keyvalue{Foo que lorem ipsum          } {Foofoo                           }     \keyvalue{Bar foobar                   } {Bar bar ipsum                    }\\     \keyvalue{Baz norf                     } {Bazbaz                           }     \keyvalue{Qux lorem ipsum              } {Qux lorem qux                    } \end{keylist}   Soon I realized my implementation was lacking this use case: allow lines to have only one element without printing the colon. Also, I began to use, in my class file, tabularx with booktabs to have great spaced lines in my tables.  \begin{tabularx}{\textwidth}{ X@{ : }X }\toprule     Foo que lorem ipsum &amp; Foofoo\\\midrule     Bar foobar &amp; Bar bar ipsum\\[\baselineskip]     Baz norf &amp; Bazbaz\\     Qux lorem ipsum &amp; Qux lorem qux\\\bottomrule \end{tabularx}   * where [\baselineskip] is used to simulate linebreaks, implemented simply using \\ in the previous implementation using parbox.  While this was working nice up to now, this was quite a hassle to use. I decided to replace the above solution by an environment alias, defined like this:  \newenvironment{keylisting} {\tabularx{\textwidth}{ X@{ : }X }} {\endtabularx}   which would give me  \begin{keylisting}\toprule     Foo que lorem ipsum &amp; Foofoo\\\midrule     Bar foobar &amp; Bar bar ipsum\\[\baselineskip]     Baz norf &amp; Bazbaz\\     Qux lorem ipsum &amp; Qux lorem qux\\\bottomrule \end{keylisting}   In the case where a line has only one item, we need to create an empty item for the second column, like this  Foo bar &amp;\\   However, a colon is printed between both columns. According to what I found in the documentation, using  \multicolumn{2}{l}{Foo bar}\\ is the solution. However, for a reason I don't know when we prefix that statement by [\baselineskip], the latter isn't interpreted by the LaTeX kernel. If you use the following example, you will see the differences between the two lines with [\baselineskip]]: the first one is not interpreted. I prefixed the statement by 10 to clearly see it isn't interpreted.      \documentclass{report}     \usepackage[margin=2cm]{geometry}     \linespread{1.2}     \usepackage{tabularx}     \usepackage{booktabs}     \usepackage{float}     \newenvironment{keyval}     { \tabularx{\textwidth}{ X@{ : }X } }     { \endtabularx }     \begin{document}     \begin{keyvalue}         Foo bar lorem ipsum           &amp; Lorem ipusm que \\         Some number                   &amp; 12 3456 789  8  \\         \multicolumn{2}{l}{This is some comment}  \\[10\baselineskip]         Yet some number               &amp; 987 65432 1     \\         Foo bar                       &amp; 163883495778    \\[\baselineskip]         Bar lorem ipsum               &amp; 2013-04-12      \\     \end{keyvalue}     \end{document}   Thanks for your help.
82651
I have an output of a query as follows:  bid  | code | amount        | ----------------------------- 2915 | BF   |   -10700.00   | 2915 | YQ   |   -300.00     | 2915 | YR   |   0.00        | 2915 | YM   |   0.00        | 2915 | WO   |   -153.00     | 2915 | IN   |   -329.00     | 2915 | K3   |   0.00        | 2915 | CUTE |   -50.00      | -----------------------------   I need to convert the values in code column to individual columns with column name as corresponding code and amount shall be the value.  bid  | BF       | YQ     | ... | CUTE  | ---------------------------------------- 2915 | -10700.00| -300.00| ... | -50.00|   The issue is that the result of my initial query has dynamic codes and hence need to generate columns accordingly.  Already tried the following logic but couldn't achieve the goal:   , ,   All the above links use some expression or the other to generate the columns.   My original resulting rows are result of simple select on multiple joined tables, and has no calculation or expression scope.  Kindly suggest some pointer or hint on how to achieve this.  Many thanks in advance.
78923
I'm dealing with nullable values in a column of a table, i'm wondering why this:  with cte as (     select 'c1'::text as c1,null::text as c2     union     select 'c2'::text as c1,null::text as c2 ) select * from cte where c2 is null   returns a result set with the two "rows" having a null value in c2; comparting to this query:  with cte as (     select 'c1'::text as c1,null::text as c2     union     select 'c2'::text as c1,null::text as c2 ) select * from cte where c2 = null   that returns an empty result set!!  so it looks like that field is null is actually different from field = null? the question is... is there "null-compatible operator" to use? so i can write the comparision like: column_name = $1 and argument $1 can be a not null or null value. IF $1 is a null value then it will semantically equals to write as IS NULL, ELSE (if $1 is not a null value) then it would be semantically equals to = $1.  P.D: This question also applies to the case is NOT NULL versus &lt;&gt; NULL
114888
I'm in the process of creating a class that stores metadata about a particular data source.  The metadata is structured in a tree, very similar to how XML is structured.  The metadata values can be integer, decimal, or string values.  I'm curious if there is a good way in C++ to store variant data for a situation like this.  I'd like for the variant to use standard libraries, so I'm avoiding the COM, Ole, and SQL VARIANT types that are available.  My current solution looks something like this:  enum MetaValueType {     MetaChar,     MetaString,     MetaShort,     MetaInt,     MetaFloat,     MetaDouble };  union MetaUnion {     char cValue;     short sValue;     int iValue;     float fValue;     double dValue; };  class MetaValue { ... private:     MetaValueType ValueType;     std::string StringValue;     MetaUnion VariantValue; };   The MetaValue class has various Get functions for obtaining the currently stored variant value, but it ends up making every query for a value a big block of if/else if statements to figure out which value I'm looking for.  I've also explored storing the value as only a string, and performing conversions to get different variant types out, but as far as I've seen this leads to a bunch of internal string parsing and error handling which isn't pretty, opens up a big old can of precision and data loss issues with floating point values, and still doesn't eliminate the query if/else if issue stated above.  Has anybody implemented or seen something that's cleaner to use for a C++ variant data type using standard libraries?
118306
I'm trying to convert a single-columned subquery into a command-separated VARCHAR-typed list of values.  This is identical to , but for Oracle rather than SQL Server or MySQL.