I have a working SQL script to show results that join multiple tables. However, I want to limit the results of the join to show only one record per a given "Chart Number" value in one of the tables. Here is the original query: SELECT MWPAT."Chart Number", MWPAT."First Name", MWCAS."Case Number", MWCAS."Marital Status" FROM MWPAT MWPAT INNER JOIN MWCAS MWCAS ON (MWCAS."Chart Number" = MWPAT."Chart Number") INNER JOIN MWINS MWINS ON (MWINS.Code = MWCAS."Insurance Carrier #1") INNER JOIN MWTRN MWTRN ON (MWTRN."Chart Number" = MWPAT."Chart Number") Where MWTRN."Date From"> '01/01/2000' GROUP BY MWPAT."Chart Number", MWPAT."First Name", MWCAS."Case Number", MWCAS."Marital Status" ORDER BY MWPAT."Chart Number" And here is the sample output for this query: | Chart Number | First Name | Case Number | Marital Status | | :----------- | :--------- | :---------- | :------------- | | 000001 | John | 2 | Single | | 000001 | John | 8 | Single | | 000001 | John | 15 | | | 000005 | Sarah | 35 | Single | | 000005 | Sarah | 42 | Married | | 000009 | Fred | 7 | Single | | 000036 | Mary | 89 | Divorced | I need to limit this output to only show one line per "Chart Number" column of the MWCAS table, based on the highest "Case Number" value that appears for the given Chart Number. For example- the output would show this instead: | Chart Number | First Name | Case Number | Marital Status | | :----------- | :--------- | :---------- | :------------- | | 000001 | John | 15 | | | 000005 | Sarah | 42 | Married | | 000009 | Fred | 7 | Single | | 000036 | Mary | 89 | Divorced | Note that each Chart Number only shows one line now, which is based on the information of the MWCAS table, and choosing the highest Case Number value for the output. I have tried different uses of MAX but I cannot find a syntax that works. I'm querying Advantage Database, so this could be limiting. Appreciate any suggestions.

For SQL SELECT queries, we often have requests to include a field name in the csv output which does not exist in the database. (to match the format of another system\database, for example) To handle this, I usually just edit the csv output file in Excel to add the extra column names as necessary. But there must be a simple method to accomplish this directly in the SQL query? I don't want to alter the database in any way, the new column name would only be used for the query output. Also, there would not be any values for the result records- all results for this added field would be NULL. Example: A database has TABLE1, with existing fields for Name, City, Phone. Name City Phone John Princeton 333-444-5555 Paul Denver 222-888-7777 Sarah Detroit 987-654-3210 So I can query: select "Name", "City", "Phone" from TABLE1 But, is there a way to query so the results include a NewColumn name inserted, with null values for that field, so that output would look like this? Name City NewColumn Phone John Princeton 333-444-5555 Paul Denver 222-888-7777 Sarah Detroit 987-654-3210

I am having a problem with syntax. I have the following statement which updates a field in a table with a value from a field in a different table. I need the statement to update the field with the text in field 142 unless field 142 is null, then update with field 17. Here is the existing code: --Defendant Address update xrji_:Usernum set DefendantAddress=trim(MemoText) from eqanswer where eqanswer.entitynum=xrji_:Usernum.DefendantEntityNum and entityrole='DEFENDANT' and fieldnum=142 ; Columns *MemoText*, *entityrole* and *fieldnum* belong to the *eqanswer* table. Let me know if this needs further clarification. Any suggestions would be appreciated. DBMS is: **Advantage**.

I am currently in the process of writing a .NET program with C# to transfer a database on a Sybase/Advantage 9.1 platform to a PostgreSQL 9.3.4 platform. I am doing this transfer in two stages - transfer all the tables/schema without the data and then transferring the data. I was able to transfer the schema without much of a problem; however, I am having issues with transferring the data (mainly performance issues as there are multiple tables with over 4 million entries). My current approach for the data transfer is as follows... - Establish an ODBC connection to the Advantage database - Create an ODBC Data Adapter - Fill a Data Table with the data entries using the method DataAdapter.Fill() - Write the contents of the Data Table to a CSV file - Establish an ODBC connection to the PostgreSQL database - Use the COPY query to upload the CSV file into the database This current method works and correctly transfers the data. However, I calculated that it would take 45 minutes to transfer a table with 5 million entries. I need this to work much faster. The longest portion of my current method is the DataAdapter.Fill(). Does anyone have any suggestions on how to do this transfer more efficiently and in less time? Either a suggestion to improve my current method or a different approach entirely. I would like to stick to using C# and the .NET platform to write a program for this transfer. EDIT: Just to add more details. Below is the portion of code that is currently taking the most time and seems to be the trouble area. string query = "SELECT * FROM \"[tableNames]\""; OdbcDataAdapter adapter = new OdbcDataAdapter(query, myConnection); tableEntries.BeginLoadData(); adapter.ResetFillLoadOption(); adapter.Fill(tableEntries); tableEntries.EndLoadData();

How to get current database name in advantage database server? I don't know which query will answer this question. I have tried select top 10 * from system.logins but I got below error message: > Error 7200: AQE Error: State = HY000; NativeError = 5004; [iAnywhere Solutions][Advantage SQL][ASA] Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: logins AdsCommand query execution failed.