Database Discussion Boards

First Prev Next

Re: Need Oracle Column Names

PIEBALDconsult28-Aug-11 11:56

PIEBALDconsult

28-Aug-11 11:56

No, I most certainly do not; I very much prefer a database-agnostic solution when one is available and especially when such is better (more flexible, more informative) than the database-specific one.

select statement group by

salmonraju25-Aug-11 8:42

salmonraju

25-Aug-11 8:42

Hi I have table Table1 contains SerialNumber, Name, City ,State
SerialNumber, Name, City , State
1 ABC Dallas TX
2 BCD VCDFV NY
3 ABC Dallas TX

Here I want to get the max of the serialnumber with if same combination of Name, City ,State is repeated , here 1 and 3 repeted , Please suggest how can I do this

Thanks,

Re: select statement group by

Chris Meech25-Aug-11 9:15

Chris Meech

25-Aug-11 9:15

select max(serialnumber), Name, City, State
  from YourTable
 group by Name, City, State

That's a start. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Re: select statement group by

Corporal Agarn25-Aug-11 9:51

Corporal Agarn

25-Aug-11 9:51

Building on Chris's post:
If you need only max by state you might try something like:

select yourtable.serialnumber, yourtable.name, yourtable.city, yourtable.state 
from yourtable 
Inner join (
    select Max(serialnumber), state
    from yourtable 
    group by state) as sub1
on yourtable.serialnumber = sub1.serialnumber

(NOT TESTED)

SQL Nominal Date Conversion [modified]

Member 408789025-Aug-11 3:41

Member 4087890

25-Aug-11 3:41

I currently have a table in SQL 2005 with two columns, one for Date, and one for Datetime. These are stored as nominal values, and is defined as the number of days since 12/30/1899, i.e. NOM_DATE 0 = 12/30/1899. Moment is defined as the number of minutes since 12/30/1899 12:00am (GMT), i.e. MOMENT 0 = 12/30/1899 12:00am.

My question is how would I go ahead and convert these into actual dates like dd/mm/yy,using a stored procedure?

Answer Found -

Using DATEADD(days, Date, '18991230') worked.

modified on Thursday, August 25, 2011 10:24 AM

Re: SQL Nominal Date Conversion

Shameel25-Aug-11 5:39

Shameel

25-Aug-11 5:39

Since you have already found the solution, why not post it as an answer and mark it as answered?

Re: SQL Nominal Date Conversion

Simon_Whale25-Aug-11 5:48

Simon_Whale

25-Aug-11 5:48

You can only do that on the QA forums and not the general programming forums

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: SQL Nominal Date Conversion

Shameel25-Aug-11 9:10

Shameel

25-Aug-11 9:10

But when I click 'Good Answer' on a forum reply, I get a message saying 'Marked as answer'.

SQL database

Emejulu JVT25-Aug-11 2:25

Emejulu JVT

25-Aug-11 2:25

Pls guys, i wrote some code which is supposed to be installed on other computers. but when i finished, it could only run on my system, and generates SQL database errors when installed on other systems. the code was written in C#, i used the sql compact server that comes with visual studio 2008.this is how i wrote the connection string:

private static string connectionString()
       {
           string conString = @"Data Source=|DataDirectory|\electionsoft.sdf;Password=emejulu.4190;Persist Security Info=True";
           return conString;
       }

       private void openConnection()
       {
           if(con.State == ConnectionState.Closed)
               con.Open();
       }

Re: SQL database

GenJerDan25-Aug-11 3:56

GenJerDan

25-Aug-11 3:56

What errors? What OS on the target?

Did you actually set the DataDirectory value somewhere? (Or did you forget to do that like I forgot to do that a while back?)

I thought you had to go to Pittsburgh for that.

My Mu[sic]
My Films
My Windows Programs, etc.

Re: SQL database

Emejulu JVT25-Aug-11 23:50

Emejulu JVT

25-Aug-11 23:50

The OS i'm using is Win7. How do i set the DataDirectory value

Re: SQL database

GenJerDan26-Aug-11 3:15

GenJerDan

26-Aug-11 3:15

Put this somewhere where it will run before doing any database stuff:

AppDomain.CurrentDomain.SetData("DataDirectory", System.Environment.GetFolderPath(System.Environment.SpecialFolder.CommonApplicationData));//LocalApplicationData));

Depending on whether you're installing for "everyone" or for each individual user, choose the CommonApplicationData or LocalApplicationData "SpecialFolder" there at the end.

And, of course, that's where the database file has to sit. (which makes debugging from within Visual Studio a pain in the butt.)

I thought you had to go to Pittsburgh for that.

My Mu[sic]
My Films
My Windows Programs, etc.

Re: SQL database

S Douglas25-Aug-11 13:39

S Douglas

25-Aug-11 13:39

Emejulu JVT wrote:
Source=|DataDirectory|\electionsoft.sdf

Where does the 'DataDirectory' get put at? It's likely to be a permissions issue on Vista / Windows 7 if this is in the program files directory. Just guessing here as the actual error message was not included in the post.

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

I installed Sqlce 4 but the only available connection is for v3.5 [modified]

nstk25-Aug-11 0:41

nstk

25-Aug-11 0:41

Hi,

I downloaded 'SSCERuntime_x64-ENU.exe' from http://www.microsoft.com/download/en/details.aspx?id=17876 but when I go in Visual Studio 2010 to Add Connection I only see Microsoft SQL Server Compact 3.5 and nothing for v4.

Am I missing something? Is there another download I should install? The system is Win7, 64bit.

Thanks in advance.

modified on Thursday, August 25, 2011 3:11 PM

Alter Query

ivo7524-Aug-11 3:49

ivo75

24-Aug-11 3:49

Hi I use this

SQL

ALTER TABLE table_name
ADD column_name datatype

to add column in table. How to use Alter Query to add column in query? I use MS Access 2003.

Re: Alter Query

jschell24-Aug-11 8:13

jschell

24-Aug-11 8:13

You can't add it.

Depends on what you mean by 'query'.

In terms of procs you must replace the proc either by using 'replace' or 'drop' and 'create'. Which one depends on the database.

If the query is other code then the code must be updated.

Re: Alter Query

ivo7524-Aug-11 8:45

ivo75

24-Aug-11 8:45

for example I have 1 query in who participate 3 columns in 2 table and I want to add 1 column in first table and column in query will be 4. I want do this with code.

Re: Alter Query

S Douglas24-Aug-11 11:34

S Douglas

24-Aug-11 11:34

ivo75 wrote:
I want to add 1 column in first table and column in query will be 4.

As jschell indicated, you will need to use an alter statement to replace the existing view with a new one.

Here is a site that has more detail about how you alter a veiw in Access (I didn't think it was posible, but then I havent worked with MS Access since 1997)

http://allenbrowne.com/func-DDL.html[^]

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

Re: Alter Query

jschell25-Aug-11 8:34

jschell

25-Aug-11 8:34

ivo75 wrote:

for example I have 1 query in who participate 3
columns in 2 table and I want to add 1 column in first table and column in query
will be 4. I want do this with code.

Not sure I understand what you are saying.

However you appear to be referring to that you now have the following:

select t.a, t.b, t.c from...

And you want the following:

select t.a, t.b, t.c, t.d from...

The problem with that is that SOMETHING is using the first query. And it is not going to magically use the second one. At best (depending on a number of things) it will simply do nothing at all. At worse something will fail in a way that makes it difficult to determine what happened.

So the best case has no impact - so no point in trying to change it.
In the worst case many things fail - so not point in trying to change it.

Thus it follows, what you think that you want to do will NOT work.

Re: Alter Query

Ganu Sharma15-Sep-11 21:15

Ganu Sharma

15-Sep-11 21:15

alter exists column datatype.
alter table tblname
alter column columnname datatype(variablesize)

or

add new column................

SQL

alter table tblname
add columnname datatype(variablesize)

Access: SQL export [SOLVED]

Luc Pattyn24-Aug-11 1:52

Luc Pattyn

24-Aug-11 1:52

For an existing Access database holding around 100 queries, I'm looking for a comfortable way to export the actual SQL statements that represent those queries; ideally I'd like some (C#) code that generates a DataTable, with columns queryName and SqlStatement. Alternatively, a command or script that tells Access to create a text file holding such information. Or some magical SQL snippet that creates a new table in the database itself.

To be perfectly clear, I need the SQL statements, not the results of the queries themselves. And I'll need to run it a couple of times in the next few months, so I don't plan on doing it manually, one by one!

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

modified on Wednesday, August 24, 2011 10:12 AM

Re: Access: SQL export

Geoff Williams24-Aug-11 3:16

Geoff Williams

24-Aug-11 3:16