If I am populating a Prepared Statement  SELECT CODE FROM MY_TABLE WHERE COMPANY_NO IN (?)   How could I populate it correctly?  When I do   String sqlResult = "SELECT CODE FROM MY_TABLE WHERE COMPANY_NO IN (?)"      try {         PreparedStatement ps = con.prepareStatement(sqlResult, ResultSet.TYPE_SCROLL_INSENSITIVE,                 ResultSet.CONCUR_READ_ONLY);           ps.setString(1,"4,5");    }   then I get  Arithmetic overflow error converting varchar to data type numeric.   which is because it is being populated as below:  SELECT CODE FROM MY_TABLE WHERE COMPANY_NO IN ('4,5')
6749
What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? placeholder represents one value, rather than a list of values.  Consider the following SQL statement:  SELECT my_column FROM my_table where search_column IN (?)   Using preparedStatement.setString( 1, "'A', 'B', 'C'" ); is essentially a non-working attempt at a workaround of the reasons for using ? in the first place.    What workarounds are available?
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.
25865
I'd like to increment fields based on the quote field, so when a quote is created, a custom field will increment depend on the region (each field will have format of "ZO - {000}). Each region should have it's own series of Unique numbers. For example: if it's a "US" region quote, "Quote Number1" custom field will increment (. If it's a "ROW" region quote, "Quote Number2" custom field will increment.  Standard "Autonumber" field works on any record creation, and not customized by record types..  UPDATE: I modified a trigger posted as an answer (@user2590, see below your modified code). I want the autonumbers to work only for "Invoice" record type in Quotes ("Quote record type should be affected"). I assume it is simpler than the original code but somehow it doesn't work...I'm getting: "System.NullPointerException: Attempt to de-reference a null object". Can you have a look and see what's not working?  trigger SampleAutoNumberRegion on Quote (before insert){ String[] name = new String[]{'Invoice'};  Map&lt;Id, RecordType&gt; regions = new Map&lt;Id,RecordType&gt;([SELECT Name from RecordType WHERE sObjectType = 'Quote' AND Name IN :name]);  AggregateResult res = [SELECT MAX(US_Invoice__c) max1, MAX(ROW_Invoice__c) max2 FROM Quote WHERE RecordType.Name IN :name ];   Integer max1 = Integer.valueOf(((String)res.get('max1')).substringAfter('- ') ); Integer max2 = Integer.valueOf(((String)res.get('max2')).substringAfter('- ') );   for(Quote theQuote : trigger.new){          if(thequote.Opportunity.Account.Region__c.contains('US')){   //region corresponds to 'US'         String num1 = String.valueOf( (max1 != null) ? ++max1 : 1 );//will use the incremented value of max unless max was null (else use 1 instead)          for(Integer i=0; i&lt; (3 - num1.length() ); i++){ //should pad with leading zeros to ensure format of {000}             num1 = '0' + num1;         }         theQuote.US_Invoice__c = 'ZO - ' + num1;      }       else if(!thequote.Opportunity.Account.Region__c.contains('US')) {          String num2 = String.valueOf( (max2 != null) ? ++max2 : 1 );         for(Integer i=0; i&lt; (3 - num2.length() ); i++){ //should pad with leading zeros to ensure format of {000}             num2 = '0' + num2;         }         theQuote.ROW_Invoice__c = 'ZO - ' + num2;    }  } }
105601
Since varchar anyways allocate space dynamically, my question is whether using varchar(255) be more efficient or save more space compared to using varchar(5000).  If yes, why?
45046
I've started a new job and it involves looking at a bunch of big numbers. Is there an easy way to add commas to an int or decimal field to make it readable?  For example, SQL Server outputs the column on the left, but for my own sanity, I need it to look like the one on the right:  2036150 -&gt; 2,036,150     ...or would I have to write some heinous   left(right(vandalized_data),6),3) + ',' + right(left(vandalized_data),6),3)   function?  The perfect thing would be commas in the display grid, then plain integers in the output.
28022
I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case.  As I understand the SQL specifications, ' ' is not the same as NULL -- one is a valid datum, and the other is indicating the absence of that same information.  Feel free to speculate, but please indicate if that's the case.  If there's anyone from Oracle who can comment on it, that'd be fantastic!
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.
4667
Getting error with this code but I can't figure it out  trigger LeadConvert on Lead (after insert) {     list leadsToConvert = new list();      Database.LeadConvert lc;     string convertedStatus = '';     list convertStatusList = new list();     convertStatusList = [Select Id, MasterLabel                          from LeadStatus where IsConverted=true limit 1];     if(convertStatusList.size() &gt; 0){         convertedStatus = convertStatusList[0].MasterLabel;         }     else{              convertedStatus = 'Closed - Converted';          }      for(Lead l : trigger.New){         if(l.AutoConvert__c == true){             lc = new database.LeadConvert();             lc.setLeadId(l.ID);             lc.setDoNotCreateOpportunity(true);              lc.setConvertedStatus(convertedStatus);             leadsToConvert.add(lc);         }     }      list lcrList = new list();     lcrList = Database.convertLead(leadsToConvert); }
58206
How can I have a dynamic variable setting the amount of rows to return in SQL Server? Below is not valid syntax in SQL Server 2005+:  DECLARE @count int SET @count = 20  SELECT TOP @count * FROM SomeTable
74479
I am not as familiar with Oracle as I would like to be.  I have some 250k records, and I want to display them 100 per page.  Currently I have one stored procedure which retrieves all quarter of a million records to a dataset using a data adapter, and dataset, and the dataadapter.Fill(dataset) method on the results from the stored proc.  If I have "Page Number" and "Number of records per page" as integer values I can pass as parameters, what would be the best way to get back just that particular section.  Say, if I pass 10 as a page number, and 120 as number of pages, from the select statement it would give me the 1880th through 1200th, or something like that, my math in my head might be off.    I'm doing this in .NET with C#, thought that's not important, if I can get it right on the sql side, then I should be cool.   Update:  I was able to use Brian's suggestion, and it is working great.  I'd like to work on some optimization, but the pages are coming up in 4 to 5 seconds rather than a minute, and my paging control was able to integrate in very well with my new stored procs.
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.
82706
What is the equivalent of varchar(max) in MySQL?
18011
Is there a way to get the raw SQL string executed when calling PDOStatement::execute() on a prepared statement? For debugging purposes this would be extremely useful.
60098
I want to format an SQL table the right way in a Stack&nbsp;Overflow question.  ID Name  1 Tom  I know the example above is not the right way. I even tried an HTML table approach, but it's still wrong. Please help, because I need to include SQL tables later...
40938
After I posted this question, I found a similar post that seemed to answer it: . I'll leave this in place for now, as I am not sure how to mark my own question as a duplicate  In attempting to debug a SOQL query error, I encountered a strange behavior with Database.query and bind variables. Specifically, when I run this code:  String userQuery = 'SELECT Id FROM User'; if (true) {     List&lt;String&gt; firstNames = new List&lt;String&gt;{'James','Joe'};     userQuery += ' WHERE FirstName IN :firstNames'; } Database.query(userQuery);   the Database.query statement executes with no errors, despite the fact that the variable firstNames does not exist in its context. However, when I add an else clause:  String userQuery = 'SELECT Id FROM User'; if (true) {     List&lt;String&gt; firstNames = new List&lt;String&gt;{'James','Joe'};     userQuery += ' WHERE FirstName IN :firstNames'; } else {     List&lt;String&gt; firstNames; } Database.query(userQuery);   I receive a null object reference exception. It seems like Salesforce is checking to see if the variable is only declared once beforehand and, if so, uses its value in the context it was declared. This seems strange to me, so I was wondering if anybody here has any insight into what is going on under the hood.
111051
How would you write a prepared MySQL statement in PHP that takes a differing number of arguments each time? An example such query is:  SELECT `age`, `name` FROM `people` WHERE id IN (12, 45, 65, 33)   The IN clause will have a different number of ids each time it is run.  I have two possible solutions in my mind but want to see if there is a better way.  Possible Solution 1 Make the statement accept 100 variables and fill the rest with dummy values guaranteed not to be in the table; make multiple calls for more than 100 values.  Possible Solution 2 Don't use a prepared statement; build and run the query checking stringently for possible injection attacks.
34123
My problem (or at least the error message) is very similar to .  My customer is working with an SQL select-query, containing a where-clause with exactly 100,000 entries.  The query is failing with error 8632 and error message      Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.)   I find it very peculiar that this error message is thrown, exactly at 100,000 entries, so I wonder if this is a configurable value. Is this the case and in case yes, how can I increase this value to a higher one?  On , there is the proposal to re-rewrite the query, but I'd like to avoid this.  Meanwhile I've found out that the list of entries I'm talking about contains natural numbers, quite some of them seem to be sequential (something like (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20).  This makes the SQL where-clause something like:  where entry in (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20)   I could transform this into:  where (entry between 1 and 3) OR       (entry between 6 and 10) OR       (entry between 12 and 13) OR       (entry between 15 and 20)   Can this be shortened by:  where entry in (1,...,3,6,...,10,12,13,15,...,20)   ...or something similar? (I know it's a long shot, but it would make software updates easier and more readable)  For your information: the data in the where-clause are the result of a calculation, done on another table: first the entries of that table are read and filtered at the beginning, then some extra processing is done (which is impossible to do using SQL), the result of that extra processing is more filtering and the result of that is used in the where-clause. As it was impossible to write the complete filtering in SQL, the mentioned method has been used. Obviously the content of the where-clause might change at every processing, hence the need of a dynamic solution.