Database

FirstPrevNext

Re: data type for saving long decimal Krish - KP11-Jul-07 1:11 Krish - KP 11-Jul-07 1:11  I've checked followig table CREATE TABLE TblC (Col1 int, Col2 varchar(5), Col3 decimal(38, 38)) INSERT INTO TblC VALUES(1 'AAA', '0.12345678901234567890123456789012345678') Here max of 38 digits are inserted after decimal. having no integer portion. Regards KP SQL WHERE > for text Pseudocaine10-Jul-07 10:01 Pseudocaine 10-Jul-07 10:01  is it possible to use > operator for text fields in a WHERE statement ie: SELECT * FROM tblmain WHERE tblmain.[Should Grade] > tblmain.[Week 1] Where should grade and week 1 are single letter (A, B, C, D, E and U) fields and tblmain is a table containing these fields note i have tried this already in vba... and it doesn't work just checking really if it is what i suspect it is if not, any reasonable (by reasonable anything that can be done in access & vba) suggestions for a work around? only way i can think of is having A=1 B=2 C=3 etc Re: SQL WHERE > for text _Damian S_10-Jul-07 14:24 _Damian S_ 10-Jul-07 14:24  It sure is possible, you just need to watch out for what happens if either field is NULL, and also be aware that "A" is not the same as "a" so you might want to force to upper case, of course, this might not be an issue depending on your constraints when entering the results. ------------------------------------------- Damian - Insert snappy one-liner here. Re: SQL WHERE > for text Krish - KP10-Jul-07 17:46 Krish - KP 10-Jul-07 17:46  Try comparing ASCII values of the characters Regards KP Data modeling - file-system type of hierarchy dandy7210-Jul-07 7:11 dandy72 10-Jul-07 7:11  Hi all, I'm trying to create a "generic" hierarchical model to store simple parent/child relationships. The closest approximation to what I'm trying to end up with is strongly based on the file system model. In SQL, I've defined the following table: - ID (auto-generated, unique, non-nullable) - NAME (just a string to be displayed to the user) - PARENT_ID The one fundamental constraint above all others I want to enforce at the database level is that any PARENT_ID value, if it has a non-NULL value at all, must exist somewhere in the ID field in this table. There are other restrictions that would be nice to have, but I can live with having this one only as a starting point. Ultimately, I want to be able to store something like this: C:\TEST1\FOLDER1 C:\TEST1\FOLDER2 C:\TEST1\FOLDER2\SUBFOLDER1 C:\TEST2\FOLDER1 ...as: 1, "TEST1", NULL 2, "FOLDER1", 1 3, "FOLDER2", 1 4, "SUBFOLDER1", 3 5, "TEST2", NULL 6, "FOLDER1", 5 For some reason, suggesting this sort of thing in primarily SQL-oriented newsgroups seems to make the natives want to break out the tar and feathers...I'm apparently "thinking too much like an older developer used to linked lists and pointers". Fine, maybe I can't see the forest for the trees, but this "simple" model (in my eyes), if I can get it to work, would provide me with all the flexibility I need. Ultimately, I'm NOT interested in writing any convoluted recursive queries to walk back up the PARENT_ID chain for a given node/row. The most complicated thing I might do is draw a tree (in ASP.NET), starting at the top level only (listing those entries whose PARENT_ID is NULL), and fetch a node's children when that node is clicked (select those entries whose PARENT_ID matches the ID of the node clicked). Is this approach going to work, or do I have to scrap this entirely and put myself through hundreds of pages of data modeling theory, as the SQL gurus elsewhere seem to be suggesting? If this is workable, could some kind soul *please* show me a SQL script to create this table with the appropriate constraints in place--or at least tell me what to click in SQL Server 2005 Management Studio to establish the constraints. I can't figure that part out (defining the table is easy, it's defining the "must exist in the ID column" rule for the PARENT_ID column I'm struggling with). I'll save the "what's with the animosity among SQL developers towards old-fashioned software developers" question for another thread... Re: Data modeling - file-system type of hierarchy dandy7210-Jul-07 10:44 dandy72 10-Jul-07 10:44  Pretty UIs to click through be damned. I managed to define my table as: CREATE TABLE CATEGORIES ( CAT_ID int IDENTITY(1,1) NOT NULL, PARENT_ID int NULL, CAT_NAME VARCHAR(50) NOT NULL, PRIMARY KEY(CAT_ID), FOREIGN KEY(PARENT_ID)REFERENCES CATEGORIES (CAT_ID) ) Thoughts welcomed anyway. Retrieve random rows in Access Red Lake10-Jul-07 5:56 Red Lake 10-Jul-07 5:56  I am trying to retrieve random rows from a database that I have in access. I got the sql statement to give me the database in random order, but I want to retrieve only 3 random rows from the database. THis is what I have so far: SELECT * FROM Jurors ORDER BY rnd(); Please Help Thank you Steve Vondras Red Lake Nation MIS Tech svondras@redlakenation.org Re: Retrieve random rows in Access Paul Conrad10-Jul-07 7:21 Paul Conrad 10-Jul-07 7:21  Describe the Jurors table in terms of the fields that are used. Not describing the how the table is set up, isn't going to allow anyone to really help you. "That's no moon, it's a space station." - Obi-wan Kenobi Re: Retrieve random rows in Access Red Lake10-Jul-07 7:25 Red Lake 10-Jul-07 7:25  The jurors table has only two fields and I want both to appear. THere are about 20 different jurors, and I want to be able to randomly select 2 of them. Steve Vondras Red Lake Nation MIS Tech svondras@redlakenation.org Re: Retrieve random rows in Access Paul Conrad10-Jul-07 8:01 Paul Conrad 10-Jul-07 8:01  What are the two fields? Are you trying to do all of this from within Access or do you have an app tied into the database? "Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus Re: Retrieve random rows in Access Red Lake10-Jul-07 8:06 Red Lake 10-Jul-07 8:06  The two fields are FIRST and LAST name, and I am doing this from within access. Steve Vondras Red Lake Nation MIS Tech svondras@redlakenation.org Re: Retrieve random rows in Access Paul Conrad10-Jul-07 8:10 Paul Conrad 10-Jul-07 8:10  You should have an id field that is a primary key. You can query by the id field with the randomly generated number. "Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus Re: Retrieve random rows in Access sam#10-Jul-07 8:22 sam# 10-Jul-07 8:22  if you are able to get random row.then why don't you pick TOP 3 rows from the output Re: Retrieve random rows in Access Red Lake11-Jul-07 3:30 Red Lake 11-Jul-07 3:30  Where do I put TOP 3 in at on this statement: SELECT * FROM jurors ORDER BY rnd(); I am very new sql thank you for your help Steve Vondras Red Lake Nation MIS Tech svondras@redlakenation.org Re: Retrieve random rows in Access Paul Conrad13-Jul-07 6:12 Paul Conrad 13-Jul-07 6:12  Replace the * with TOP 3... "Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus Re: Retrieve random rows in Access Red Lake16-Jul-07 2:36 Red Lake 16-Jul-07 2:36  Thank you for the help. Steve Vondras Red Lake Nation MIS Tech svondras@redlakenation.org Re: Retrieve random rows in Access Paul Conrad16-Jul-07 7:31 Paul Conrad 16-Jul-07 7:31  No problem, Steve. "The clue train passed his station without stopping." - John Simmons / outlaw programmer Datasets and varaible table name joebaas10-Jul-07 3:27 joebaas 10-Jul-07 3:27  Hi all! I've a SQL (2005) database that is structured as follows: - a schema called "salary" - the table name is given by the year and the effective table name. Example 2007.employees - that gives the table name salary.[2007.employees] In my project I've created a typed dataset with the wizard. I've then renamed the table name in the dataset without the year (ex. 2007.employees -> employees). In the application I can choose in which year to work and should load the data from the choosen year in the dataset. I should have the possibiltiy to pass to the tabeladapter the year I want. How can I do that? Thanks for suggestions! Jörg Re: Datasets and varaible table name Paul Conrad13-Jul-07 11:50 Paul Conrad 13-Jul-07 11:50  I think you look in the properties of the data adapter and include parameters. "Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus Re: Datasets and varaible table name joebaas16-Jul-07 5:14 joebaas 16-Jul-07 5:14  Yes it's possible the add parameters in the table adapter, but not for the table names. When you try to add a paramter for the table name, the query builder gives an error. I'm now thinking to add a column "Year" in each table, so that each row is allocated in a year and all data is in one table and database. cancel inner join Amr M. K.10-Jul-07 1:02 Amr M. K. 10-Jul-07 1:02  Hello How can I cancel inner join between two tables if the value in second table not exists .. to be more clear TableA -Col1 -Col2 -Col3 TableB -Col1 -Col2 -Col3 -Col9 select TableA.col1 ,TableA.col2 ,TableB.Col1 where TableA.col3=TableB.Col9 the result will diplay if the match happend I have tried to do like this in TableA.col3=isNull(TableB.Col9,TableA.col3) or something like that.. the problem that I face in sqlDataSource in asp.net aj123 Re: cancel inner join gauthee10-Jul-07 2:04 gauthee 10-Jul-07 2:04  I did not understand your question but i guess you are looking for: 'coalesce' - try this out Gautham Re: cancel inner join Pete O'Hanlon10-Jul-07 2:11 Pete O'Hanlon 10-Jul-07 2:11  Use the INNER JOIN syntax. SELECT a.Col1, a.Col2 FROM TableA a INNER JOIN TableB b ON b.FK_Field = a.PK_Field This will return you rows where items are present in both tables only. Please visit http://www.readytogiveup.com/ and do something special today. Deja View - the feeling that you've seen this post before. Re: cancel inner join WoutL10-Jul-07 2:15 WoutL 10-Jul-07 2:15  Use a left join: select TableA.col1 ,TableA.col2 ,TableB.Col1<br /> From TableA<br /> inner join TableB on TableA.col3=TableB.Col9 Wout Louwers Re: cancel inner join Amr M. K.10-Jul-07 2:48 Amr M. K. 10-Jul-07 2:48  yes that right.. I forget that... aj123

Last Visit: 31-Dec-99 18:00     Last Update: 22-Sep-24 18:34 Refresh ᐊ Prev1...1496149714981499150015011502150315041505 Next ᐅ

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.