1. [on SQL]difference between REFERENCE and FOREIGNKEY? coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
2. SQL Question coderanch.comthanks dana I am using oracle 8i the result is something like: TYPE_ID USER_NAME 1..........A 1..........B 1..........D 2..........A while I would like to have the result like: when the typeID = 1: TYPE_ID USER_NAME 1..........A 1..........B 1..........D null.......C null.......E null.......F but it doesn't work after i put in the condition type id = 1. Any suggestion? Thank so much | |||||||||||||||||||||||||||||||||||||||||||||||||||||
3. Generate Xquery from SQL coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
4. Problem with SQL uqery or DSN coderanch.comHi, I am using two different DSN for two different tables, thats my requirement. How can I insert a value of table1 into table2 without changing another cloumns values. RateInfo First DSN) name address city xxx yyy zzz aaa bbb uuu UserInfo: (Second DSN) name address city fff iii hhh ddd Finally the table would be look like name address city ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
5. New Features of java.sql & javax.sql packages coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
6. SQL coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
7. java.sql.Type to java type mapping coderanch.comI don't believe that such a thing exists. The best you can do is look at the "java.sql.Types" API to see what's defined there. One of the difficulties in doing the mapping you propose is that there might not be one--and only one--way to map. For example, if something is "Numeric" in the database, should that to map to an "int", ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
8. SQL trace logs coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
9. How do you indent SQL? coderanch.comNot to start a holy war or anything, but is there any published guideline on how to indent SQL so it is easier to read? Mainly I'm interested in SELECT statements, but the other stuff would be nice too. I'm interested in the whole thing: indenting, whether keywords should be in upper or lower case, whether to indent with tabs or ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
10. A small Sql Question coderanch.comi am trying to insert data into sql database and the data comes from a file and gets transferred directly to the database. the problem is that one of my fields is to collect pathnames. now whenever the data reaches the databases the slashes are gone for ex if the path in the datafile is like \\rhino\users\vol1\sim then in the database ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
11. A little SQL help with SUM() coderanch.comHi guys, I have query with a sum() in it, on a DB2 7.0 box. One of my sum() records returns a null because there are no values to add, but I want it to send back a zero so that it gets printed on a report. I tired using CASE WHEN to catch the null and print the '0' but ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
12. help in sql coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
13. CONTAINS keyword in SQL coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
14. get picture using sql coderanch.comHi, I'm designing the system. So for each user's data appear , the user's picture also appear. let say John's data appear, so john's picture will appear, if Mary's data appear then Mary's picture will appear. I have no idea for that. Can anybody help me, some sample coding will help a lot! Thank u... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
15. Stored Proc w/dynamic sql coderanch.comIs it a good to write a store procedure using dynamic sql? What in ways could I optimize this SP that uses dynamic sql? The tables will contain several million rows and I need to return only 20 rows at a time. ------------------ CallableStatement cstmt = con.prepareCall("{call dbo.rc(?,?,?,?,?,?,?)}"); cstmt.setString(1,Fieldnames); cstmt.setString(2,TableName); cstmt.setString(3,PrimaryKey); cstmt.setString(4,SortField); cstmt.setInt(5,PageSize); cstmt.setInt(6,currentPage); cstmt.setString(7,QueryFilter); cstmt.execute(); ------- CREATE PROCEDURE dbo.rc @Fieldnames ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
16. SQL help! coderanch.comThere were no errors as such when I used my original statement- the only thing was that the table wasn't being displayed and thus suggesting that it had not been connected to but after I tried the ammendments (as mentioned in earlier reply) the table was displayed. I am using Access Databases. | |||||||||||||||||||||||||||||||||||||||||||||||||||||
17. SQL Stmt not working in Java App % coderanch.comHowdy Lisa, You 're not incidently querying a DATE column, are you? Wouldn't be surprised to see the VARCHAR syntax not working on those, would I! More in general: don't forget telling us what kind of database you are using. Makes it so much easier to answer. For myself, I can easily rope in the Oracle loose ends, but when you ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
18. SQL stmt Not working coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
19. Counting the number of numbers in SQL coderanch.comI have two tables, one contains a list of subject areas and another which has these subjects allocated to it. The problem I have is I need to find out which subject area has the most courses allocated to it. I am attempting to count how many times each subjectID appears in the course table and then print the name of ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
20. SQL Problem coderanch.comThis is a SQL not a java problem, if someone could point me towards a SQL website similar to this i would be happy or if you can solve this problem I would be even happier. I have two tables EMPLOYEE and WORKING, I want to display all employees mot working. I use the folling SQL Code> select emp.employeeID, emp.name from ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
21. SQL Problem coderanch.comHi, I have 2 tables, Attendee and Client_Company, and I need to display all client_companies with less than 2 female attendees. I can get the companies with 1 female to display but am stuck on how to also show those with no females. Heres what I have so far select compname from client_company, attendee where attendee.company_id = client_company.company_ID (+) and gender ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
22. Another SQL problem... coderanch.comI have an instructor table where each instructor has a unique ins_id, there is also a mentor_id column which allows instructors to mentor other instructors. This is show by having the ins_id of the mentoring instructor in the mentor_id column sort of like: ins_ID Mentor_ID 1 - 2 - 3 2 4 2 5 3 The problem is I need to ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
23. Some SQL help please coderanch.comHello, im fairly new to SQL and believe it or not I have a problem. Im trying to update the first character value of each field in a column. e.g. "AZZZ999" would become "BZZZ999" the lengths of field value change. I would appreciate any help with this. In the end I want to put this into Java (using JDBC), but for ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
24. Decreasing numbers in SQL coderanch.comHello! What I'm trying to do at the moment is have a query which finds the appropriate record of a particulat product in the STOCK table and then decrement that quantity by 1. However, I'm not sure ntirely how to do this, as I know how to change the number by specifying one but I'm not sure how to minus the ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
25. Microsoft SQL coderanch.comOkay now I am asking this because no one in the building has a clue. I have a stored proceedure that connects to another database using OPENDATASOURCE. Now it had been working for 5 months and all of the sudden stopped. Now it is showing the following: Server: Msg 18, Level 16, State 1, Line 1 Now I checked with the ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
26. sql Limit coderanch.comhi I have some problem with mysql sql statement. When i use the limit sql function it just gets the whole records starting instead of traversing it. Example "select * from MOD1 limit 140,160"; output 160 records instead of from record number 140 till 160. Am i making any mistake here? Does limit really function this way? | |||||||||||||||||||||||||||||||||||||||||||||||||||||
27. SQL question? coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
28. sql querry coderanch.comHi, I have a staff table like this: name year salary ============================ a 1998 50 a 1999 100 a 2000 200 a 2001 300 b 2000 200 b 2001 300 I want to get a new table from the above table: name salary(1998) salary(1999) salary(2000) salary(2001) =================================================================== a 50 100 200 300 b 0 0 200 300 NB-no of years may ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
29. Java&sql mystical problem with Switch coderanch.comFirst, I am sorry about my broken English. I have created simple project manager software with MySql and Java and I got this little problem. my problem: : java.sql.SQLException: No row count was produced -so there is something wrong in my code, but I cant find out what is wrong If I do those sql-clauses without switch they work. This is ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
30. Big SQL coderanch.comHi Pranit, I would recommend laying the query out in the Java source as you would if it was specified in a SQL script or stored procedure. I've adapted it to my style below (well, not quite). I find it helpful to distinguish between keywords and tables/columns using upper/lower case. The neat little column of plusses is rather sad, but a ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
31. sql problems coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
32. sql doubt coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
33. Run dynamic sql in stored procedure problem coderanch.comHi Can somebody tell me whats wrong to my stored procedure (Oracle 8i)? I want to run a dynamic SQL select stmt in my SP which returns set of records as output parameter to my java program. I am getting the following error: 8/1 PL/SQL: Statement ignored 13/6 PLS-00597: expression 'RS' in the INTO list is of wrong type ------------------------------------------------------------------------ CREATE ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
34. simple SQL question coderanch.comI have 2 tables TABLE A ID PRICE DATE_OF_DATE countrycode TABLE B countrycode exchange_rate I used the code: CREATE TABLE ALL as select a.ID, a.price, b.exchange_rate, a.a.DATE_OF_DATA from TABLEA a, TABLEB b where a.DATE_OF_DATA = b.DATE_OF_DATA and a.countrycode = b.countrycode order by a.barra_id, a.DATE_OF_DATA; RUN; It's giving me 2 entried for every ID and DATE i.e. ID price exchange Date A ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
35. Generating XML from SQL coderanch.comI'd like to generate an XML document from a SQL query. Is there a tool that do it easily? How should I do it? 1st step: retrieve the data .... conn = getConnection(); query = "SELECT * FROM CLIENT"; stmt = conn.createStatement(); rs = stmt.executeQuery(query); .... 2nd step: generate the XML file I have no idea how to do it. I ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
36. sql question coderanch.comHi all, I have this sql statement SELECT count(*) as svcvol,sum(Amt) as svcsum,Status,Service,Compcode from SCTABLE where Status='00' GROUP BY Service The problem is if there is no result matches it returns nothing. I want to display something even though there is nothing like displaying the compcode and the count is 0 and the sum is 0 and so on. Is this ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
37. SQL coderanch.comyou can just get the year and divide it by 4. if you get the reminder it means the year is not a leap year, else it is. i think we have a function called mod(). you can use it to in order to get the reminder. any particular function. sorry i dont know. may be someone else. | |||||||||||||||||||||||||||||||||||||||||||||||||||||
38. SQL Help Please coderanch.comI am having a hard time figuring out a query for the following: I have 2 tables. employee_table --------- id firstname lastname email user_table -------- id password role Ok, A user is an Employee. So I didn't want to duplicate all the info in both tables. So what I need is a query that will get the row in the employee_table ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
39. OK here's a daft SQL problem for you... coderanch.comOK second post of the day. Got my DB working and got my JSPs displaying and removing and inserting data, etc... But! Noticed a problem in the code. If in and of the text firlds I put a ' or " symbol, it kills the SQL. I know this is because the query string is just that, a string (in Java/JSP); ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
40. subquery in transact sql coderanch.comhi all, ok..ere's what the problem is. i am using a subquery to select multiple rows in a table, take a specific field out of them, and sum them up, and return the result (which should be one float value) to a field in a temp table in a stored proc. when i run the subquery by itself, using the same ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
41. (IN) PREDICATE in sql coderanch.comI have a SQL statement which uses in predicate . In the in clause I am having around 500 products list like: select * fro Product where prodict_id in (1,2,3,4....) The list of product_id are selected from list on jsp page. Can you suggest the performance issues related to using a query with in predicate and 500 items in the in ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
42. SQL question (not JDBC) coderanch.comHey! Hope someone can help me with a little SQL problem that I have.. Two tables... A and B. A has one field called nick which also exists in B. B also has another field (status) which is an enum. Not every nick that exists in A exists in B. I want to extract all nicks from A WITHOUT the ones ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
43. Yes/No SQL Quieries coderanch.comHi Peter, Some of my projects using Oracle database. And we usually use Number(1) as boolean field. We store that value with '1' for true and '0' for false. We also retrieve that value, and change into boolean value in the value object. Correct me if i am wrong Hope this help daniel | |||||||||||||||||||||||||||||||||||||||||||||||||||||
44. quick SQL help please coderanch.comI am getting the error "Identifier or Quoted Identifier Expected" when i try to execute this in my code, however it works fine in SQL Plus or SQL Worksheet. What am I missing? SELECT DISTINCT matrix_list.sample_type FROM (SELECT DISTINCT s1.sample_type FROM lims_sys.sample_user su1, lims_sys.sample s1, lims_sys.sdg_user sdgu1 WHERE sdgu1.SDG_ID = s1.SDG_ID AND s1.sample_id = su1.sample_id and s1.status <> 'X' AND s1.sample_type ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
45. SQL Jobs coderanch.comArjun, That information is usually stored in the data dictionary, so you simply need to query the data dictionary. Of-course, data dictionaries are not standard across different DBMS's, nor across different versions of the same DBMS, so if you want a more detailed answer, you need to provide a more detailed question -- like what is your definition of a "SQL ... | |||||||||||||||||||||||||||||||||||||||||||||||||||||
46. diff between java.sql.* and javax.sql.*? coderanch.com | |||||||||||||||||||||||||||||||||||||||||||||||||||||
47. sql problem coderanch.compublic String showOrder( String ono ) throws Exception { StringBuffer b = new StringBuffer(); b.append( " " + "
|