SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Bad Titty Puker: Nov 3, 2007; Soiled Meat

TagUrIt posted:

I'm toying around with writing something to visually represent a lot of data, and I figure a database might be a good way to do the actual storage of the data. Having never working with databases before, though, I have a few questions:

1. I'm going to need to use Foreign Keys to represent the links between different sets of data (students with schools, for example). However, there are at least a few relations in my data that could need multiple FKs in the same field (i.e. having 2 co-valedictorians). Is there a way to have one column have an arbitrary number of FKs in it? If not, do you have any suggestions for dealing with stuff like this?

It sounds like you understand the entities and relationships and rules that you are modeling, and are trying to figure out the design of the tables and keys. Is that correct? By "multiple FKs in the same field" or "one [column] having an arbitrary number of FKs in it", do you mean columns that can have foreign key relationships to more than one table? It's certainly possible to do that, but I'm not sure what would be modeled by that. But in your example of "multiple FKs in the same field" of having 2 co-valedictorians -- I don't think that would be multiple foreign keys on one column referencing one column in two other tables.

If one of the rules of your data model, and I'm just making this up, is that schools may have one, or possibly two, valedictorians each year, then you may be better off representing it as

code:

Valedictorians( 
  school_id not null, 
  school_year not null [, check constraint], 
  student1_id not null, 
  student2_id null;  

  primary key(school_id, school_year, student1_id), 
  /*only one set of valedictorians per school and school year. 
    if student2 is not null, there are two co-valedictorians)*/

  foreign key(school_id) references Schools(school_id), 

  foreign key(student1_id) references Students(student_id), 

  foreign key(student2_id) references Students(student_id) 

  check(student1_id <> student2_id)

if that makes sense; instead of storing one valedictorian per row:

code:

Valedictorians(
 school_id not null, 
 school_year not null, 
 student_id not null,

 primary key(school_id, school_year, student_id)
 foreign key(school_id) referencing Schools(school_id)
 foreign key(student_id) referencing Students(student_id)

, and then having to enforce the rule that there can't be more than two valedictorians without being able to use DDL constraints (in other words, having to enforce that rule through application code, or triggers. Messy). Or, modeling valedictorians as an attribute of students, where there is no Valedictorians table, but a status code on the Students table, has a similar issue.

quote:

2. Is there a way to make a "Last Updated" field for every row that updates anytime something in that row is changed? Rows get updated individually and periodically; it'd be nice if I could find which entries are potentially the most outdated.

Well, the simplest way is to add a last_updated_date column to your table and update it with INSERT and UPDATE triggers.

quote:

3. Do you guys have any preferred gui editors/admin things? I was poking around with OpenOffice Database, but I couldn't figure out how to do FKs, which is probably due to my lack of sleep.

Really I would recommend doing your DDL by writing SQL, and not by using some fancy-pants citified gui. Foreign keys are simple to write.

edit: Just to carry this on a bit more, the example that I posted for #1 is probably not quite right, because it does not enforce that valedictorians of a school have to attend that school. For simplicity let's assume that students are enrolled in one school per year only. Then it might look like this:

code:

Enrollments(
 student_id not null,
 school_year not null [, check constraint]
 school_id not null,

 primary key(student_id, school_year),  
/* only one student per enrollment per year */

 foreign key(student_id) references Students(student_id),

 foreign key(school_id) references Schools(school_id)
)

Valedictorians( 
  school_id not null, 
  school_year not null [, check constraint], 
  student1_id not null, 
  student2_id null;  

  primary key(school_id, school_year, student1_id), 
  /*only one set of valedictorians per school and school year. 
    if student2 is not null, there are two co-valedictorians)*/

  foreign key(school_id, school_year, student1_id)
    references Enrollments(school_id, school_year, student_id)

  foreign key(school_id, school_year, student2_id)
    references Enrollments(school_id, school_year, student_id)

  check(student1_id <> student2_id)

if that makes sense.

Bad Titty Puker fucked around with this message at 11:07 on Mar 29, 2009

# ? Mar 29, 2009 10:47

Adbot: ADBOT LOVES YOU

# ? May 16, 2024 05:31

TagUrIt: Jan 24, 2007; Freaking Awesome

I had thought, well, the "valedictorian" field can have as many FKs to the Student table as it needs, but I like the idea of having that data as a table of its own. And since I can set a tight upper bound on the number of valedictorians, I can simply make X fields in the Val's table for FKs to the student table, with all but 1 of them being nullable.

Thanks for the help!

# ? Mar 29, 2009 16:54

Whilst farting I: Apr 25, 2006

I'm a bit new when it comes to foreign keys, and when trying to make queries to extract data from related tables I'm a bit lost. What I have right now is 3 tables: division, employees, and intersection. Division corresponds to academic department, employee is employee, and intersection is a table that contains information from two of the other tables, as well as additional data.

code:

   
DIVNUM     DIVNAME                        DIVLOCATION          DIVPHONE
---------- ------------------------------ -------------------- ------------
         1 Accounting                     Math/Science 342     555-555-5555
         2 Applied Statistics             Business 344         555-555-5555
         3 Biology                        Life Science 324     555-555-5555
		 
EMPNUM     EMPNAME              EMPRANK         EMPYEARSTART
---------- -------------------- --------------- ------------
         1 Boehnstrom, James    Assoc Prof              1992
         2 Charles, Larry       Asst Prof               2005
         3 Ersatz, Manuel       Assoc Prof              1984

IDIVNUM    IEMPNUM    IOFFICE              IPHONE
---------- ---------- -------------------- ------------
         2          1 Business 344         555-555-5555
         1          2 Math/Science 342     555-555-5555
         3          3 Life Science 324     555-555-5555

In the third table, the intersection table, IDIVNUM corresponds to the DIVNUM, the division number, in the first table (number 2 is statistics, in the Business building) and IEMPNUM corresponds to the EMPNUM, the employee number, in the second table (number 1 is Boehnstrom).

If I were to query the division number 2, how would I be able to output all information for all employees in that department? Some divisions have up to 20 employees, it's not unique for each employee. As far as I can gather, it should be something like

select * from DIV, EMP, INT where DIVNUM = X

but that doesn't seem right, and describe does not state whether or not any of these are foreign keys.

Also, how would I go about putting in an employee number and determining in which divisions they are enrolled, also while outputting all relevant information? Some have multiple divisions, so I'd imagine it'd be something like

select * from DIV, EMP, INT where EMPNUM = X

but again, due to lack of knowledge about the foreign keys, I'm uncertain if this is correct (or if it would be in any context).

# ? Mar 30, 2009 18:07

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

You need to learn about Joins.

code:

select *
  from INT inner join
       EMP on IEMPNUM = EMPNUM inner join
       DIV on IDIVNUM = DIVNUM
 where DIVNUM = X

Since this case is an inner join, you could also move the join conditions into the where clause without changing your results, but that syntax is probably considered old depending on what database you are using.

# ? Mar 30, 2009 18:31

Vince McMahon: Dec 18, 2003

FeloniousDrunk posted:

Looking to reduce my app to a single query, but I have one difficulty. Say I have a table "users":
code:
USER | ID
---------
Fred | 1
Joe  | 2
Hank | 3
and another, "status":
code:
ID | STATUS
-----------
1  | 1
1  | 22
2  | 3
3  | 1
What I want to do is nearly "select users.USER, users.ID from users, status where status=1" except I don't want any users who have a status not equal to 1 anywhere in the "status" table. So what I really want as output for this example is just Hank, 3.

It's an Oracle 9 database, if that matters.

Sorry for my potential retardedness, but couldn't you do something like this:

SELECT users.user, users.ID FROM users WHERE users.id IN (SELECT id FROM status GROUP BY id HAVING MAX(status) = 1)

or

SELECT users.user, users.ID FROM users INNER JOIN (SELECT id FROM status GROUP BY id HAVING MAX(status) = 1) as Results ON users.id = results.id

# ? Mar 30, 2009 21:52

Aredna: Mar 17, 2007; Nap Ghost

Sorry for the late response, but how would the MINUS keyword perform for FeloniusDrunk's query?

code:

select users.USER, users.ID 
  from users, status
 where user.id = status.id and status=1

minus

select users.USER, users.ID 
  from users, status
 where user.id = status.id and status<>1

# ? Mar 30, 2009 22:50

Warbling Castrato: Sep 25, 2003

I'm developing drop-down filters for a company's internal video library. The two variables to filter by are $client and $cat. They have tables as follows (simplified):

code:

table - clips:
*-------------*--------*
| ID | Client | Title  |
*-------------*--------*
| 11 |   1    | clip_1 |
| 12 |   2    | clip_2 |
| 13 |   3    | clip_3 |
*-------------*--------*

table - clients:
*-------------*
| ID | Name   |
*-------------*
| 1  |client1 |
| 2  |client2 |
| 3  |client3 |
*-------------*

table - categories:
*-------------*
| ID | Name   |
*-------------*
| 3  |cat1    |
| 7  |cat2    |
| 6  |cat3    |
*-------------*

table - clips2cat:
*------------------*
| clipID | catID   |
*------------------*
|    11  |3        |
|    11  |6        |
|    11  |7        |
|    12  |7        |
|    13  |6        |
|    13  |3        |
*------------------*

So clips2cat has a row for each category assigned to a clip.

Say someone chooses 'client2' from the dropdown, and 'cat3' from the category dropdown (so $client=2 and $cat=6).

What kind of query do I need to run that is going to retrieve rows from the clips table, where clips are in category 6 (matched from clips2cat) and belong to clientid 2?

I've been trying to use Unions but I'm getting lost. Sorry if this doesn't make any sense at all.

# ? Apr 2, 2009 12:21

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Your base concept is clips. Select from clips join clients on client id, join clips2cat on clip id, where clip id is 6 and client id is 2.

The feature of SQL you want to be looking up is "joins", not unions.

# ? Apr 2, 2009 12:43

Warbling Castrato: Sep 25, 2003

Triple Tech posted:

Great, I get it now. Here's what I ended up with:

code:

SELECT clips.*,
       clients.*
FROM   clips,
       clients,
       clips2cat
WHERE  clips.client = clients.id
       AND clips2cat.clipid = clips.id
       AND clips2cat.catid = $cat
       AND clients.id = $client

# ? Apr 2, 2009 14:15

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

FYI, in production code, you never want to select star and interpolate variables like that. Explicitly select the columns you want (for both speed and future proofing) and learn how to use bind variables for injecting values into where clauses.

# ? Apr 2, 2009 14:21

roadhead: Dec 25, 2001

EndOfRadio posted:

Great, I get it now. Here's what I ended up with:

code:

SELECT clips.*,
       clients.*
FROM   clips,
       clients,
       clips2cat
WHERE  clips.client = clients.id
       AND clips2cat.clipid = clips.id
       AND clips2cat.catid = $cat
       AND clients.id = $client

Close, but your FROM clause seems to have leaked into your WHERE.

code:

SELECT clips.*,
       clients.*
FROM   clips
 JOIN   clients ON clips.client = clients.id
 JOIN   clips2cat ON clips2cat.clipid = clips.id
WHERE  clips2cat.catid = $cat
       AND clients.id = $client

This is superior as it separates the table relationships from the restrictions specific to this running of the query.

# ? Apr 2, 2009 15:56

Roundboy: Oct 21, 2008

roadhead posted:

Close, but your FROM clause seems to have leaked into your WHERE.
code:
SELECT clips.*,
       clients.*
FROM   clips
 JOIN   clients ON clips.client = clients.id
 JOIN   clips2cat ON clips2cat.clipid = clips.id
WHERE  clips2cat.catid = $cat
       AND clients.id = $client 
This is superior as it separates the table relationships from the restrictions specific to this running of the query.

aren't they 2 ways of doing the same thing? The 2nd way is preferable, and follows later SQL syntax changes. But the first is still perfectly valid (although hard to follow, a bitch to trouble shoot, and difficult to change?)

# ? Apr 2, 2009 16:08

var1ety: Jul 26, 2004

Roundboy posted:

aren't they 2 ways of doing the same thing? The 2nd way is preferable, and follows later SQL syntax changes. But the first is still perfectly valid (although hard to follow, a bitch to trouble shoot, and difficult to change?)

While valid, the older way precludes you from doing outer joins. It is important to understand, but it is equally important to never use it.

# ? Apr 2, 2009 16:40

Whilst farting I: Apr 25, 2006

Jethro posted:

You need to learn about Joins.
code:
select *
  from INT inner join
       EMP on IEMPNUM = EMPNUM inner join
       DIV on IDIVNUM = DIVNUM
 where DIVNUM = X
Since this case is an inner join, you could also move the join conditions into the where clause without changing your results, but that syntax is probably considered old depending on what database you are using.

Thank you! I'm just getting familiar with the types of joins, so this looks very close to what I need. This is probably because I am using embedded SQL, but my output looks like

DIVNUM EMPNUM DIVLOCATION DIVPHONE EMPNUM EMPNAME EMPRANK EMPYEARSTART

when it should be

DIVNUM DIVNAME DIVLOCATION DIVPHONE EMPNUM EMPNAME EMPRANK EMPYEARSTART

Is this because of the join statement, or the way the rest of my program is structured (C++)?

Also, I'm having a few other problems with this program - I'm not quite sure how to bind a variable so it'll be usable. I'm trying to let the user input a number to query, but any time I put the variable name into the select statement, it refuses it (I've tried numReq, :numReq, numReq.arr, :numReq.arr but none work). Here's all the relevant code. It's really lovely but I'd rather get the basics working before tightening it up further because it's driving me nuts.

code:

EXEC SQL BEGIN DECLARE SECTION;

VARCHAR username[7];
VARCHAR password[20];

VARCHAR tableReq[3];
unsigned int numReq;

VARCHAR sqlStatement[1000];

unsigned int CS_DivNum;
VARCHAR      CS_DivName[50];
VARCHAR      CS_DivLocation[20];
VARCHAR      CS_DivPhone[12];

unsigned int CS_EmpNum;
VARCHAR      CS_EmpName[20];
VARCHAR      CS_EmpRank[15];
unsigned int CS_EmpYearStart;

unsigned int CS_IDivNum;
unsigned int CS_IEmpNum;
VARCHAR      CS_IOffice[20];
VARCHAR      CS_IPhone[12];

EXEC SQL END DECLARE SECTION;

....

cout << "Please enter the number you wish to query: ";
cin >> numReq;

....

strcpy ((char *) sqlStatement.arr, "select * from INT inner join ");
strcat ((char *) sqlStatement.arr, "EMP on INT.IEMPNUM = ");
strcat ((char *) sqlStatement.arr, "EMP.EMPNUM inner join ");
strcat ((char *) sqlStatement.arr, "DIV on INT.IDIVNUM = ");
strcat ((char *) sqlStatement.arr, "DIV.DIVNUM ");
strcat ((char *) sqlStatement.arr, "where DIV.DIVNUM = 04");

sqlStatement.len = strlen ((char *) sqlStatement.arr);

EXEC SQL PREPARE S1 FROM :sqlStatement;

EXEC SQL DECLARE C1 CURSOR FOR S1;

EXEC SQL OPEN C1;

do
{
	EXEC SQL FETCH C1 INTO CS_DivNum,
			   CS_DivName,
			   CS_DivLocation,
			   CS_DivPhone,
			   CS_EmpNum,
			   CS_EmpName,
			   CS_EmpRank,
			   CS_EmpYearStart;

	if (sqlca.sqlcode == 0)
		printData();

} while (sqlca.sqlcode == 0); // loops if not end-of-cursor

...

printData()
{
...
      cout << setw(7) << CS_DivNum;
      cout << setw(12) << (char *) CS_DivName.arr;
      cout << setw(25) << (char *) CS_DivLocation.arr;
      cout << setw(16) << (char *) CS_DivPhone.arr;

      cout << setw(12) << CS_EmpNum;
      cout << setw(25) << (char *) CS_EmpName.arr;
      cout << setw(19) << (char *) CS_EmpRank.arr;
      cout << setw(10) << CS_EmpYearStart;
...
}

The part giving me trouble is this.

code:

strcat ((char *) sqlStatement.arr, "where DIV.DIVNUM = 04");

That's supposed to be the user's input number. If I hardcode a number in, everything works fine except for the divname/empname swap on display. But if I try to put in the variable name, I get

quote:

The query is: select * from INT inner join EMP on INT.IEMPNUM = EMP.EMPNUM inner join DIV on INT.IDIVNUM = DIV.DIVNUM where DIV.DIVNUM = :numReq

*** Oracle Error:
ORA-01008: not all variables bound

I've spent hours Googling binding variables and that specific error message and I've found no clear explanations or examples. :smith:

# ? Apr 2, 2009 17:16

var1ety: Jul 26, 2004

Whilst farting I posted:

I've spent hours Googling binding variables and that specific error message and I've found no clear explanations or examples.

You should always refer to Oracle's documentation first. You can download it for free from Oracle's site - I keep a local copy for 9.2/10.2/11.1. In this case, you would be interested in the Pro*C/C++ Programmer's Guide. A link to the relevant page follows:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_04dat.htm#i27299

# ? Apr 2, 2009 17:27

Whilst farting I: Apr 25, 2006

var1ety posted:

You should always refer to Oracle's documentation first. You can download it for free from Oracle's site - I keep a local copy for 9.2/10.2/11.1. In this case, you would be interested in the Pro*C/C++ Programmer's Guide. A link to the relevant page follows:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_04dat.htm#i27299

I checked that, but it just left me more confused. According to their documentation, what I wrote should work

quote:

EXEC SQL SELECT ename, sal INTO :ename, :sal FROM emp
WHERE empno = :empno;

That's the given example, and although I do reference the select statement via :sqlStatement, what's contained in it is the same format.

# ? Apr 2, 2009 17:33

var1ety: Jul 26, 2004

Whilst farting I posted:

I checked that, but it just left me more confused. According to their documentation, what I wrote should work

That's the given example, and although I do reference the select statement via :sqlStatement, what's contained in it is the same format.

Do you have an "int empno" declared in your routine?

# ? Apr 2, 2009 17:37

Whilst farting I: Apr 25, 2006

That was their example select statement. What I have is

code:

EXEC SQL PREPARE S1 FROM :sqlStatement;

EXEC SQL DECLARE C1 CURSOR FOR S1;

EXEC SQL OPEN C1;

Where :sqlStatement is equal to the following

quote:

select * from INT inner join EMP on INT.IEMPNUM = EMP.EMPNUM inner join DIV on INT.IDIVNUM = DIV.DIVNUM where DIV.DIVNUM = :numReq

and numReq is declared in the beginning in the declare section.

# ? Apr 2, 2009 17:41

var1ety: Jul 26, 2004

Whilst farting I posted:

That was their example select statement. What I have is
code:
EXEC SQL PREPARE S1 FROM :sqlStatement;

EXEC SQL DECLARE C1 CURSOR FOR S1;

EXEC SQL OPEN C1;
Where :sqlStatement is equal to the following

and numReq is declared in the beginning in the declare section.

You probably meant "int" instead of "unsigned int" (or long/short, as appropriate). The documentation describes valid C => Oracle mappings.

# ? Apr 2, 2009 17:44

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Whilst farting I posted:

Thank you! I'm just getting familiar with the types of joins, so this looks very close to what I need. This is probably because I am using embedded SQL, but my output looks like

DIVNUM EMPNUM DIVLOCATION DIVPHONE EMPNUM EMPNAME EMPRANK EMPYEARSTART

when it should be

DIVNUM DIVNAME DIVLOCATION DIVPHONE EMPNUM EMPNAME EMPRANK EMPYEARSTART

Is this because of the join statement, or the way the rest of my program is structured (C++)?

Triple Tech's advice to EndOfRadio applies to you too:

Triple Tech posted:

FYI, in production code, you never want to select star[...]. Explicitly select the columns you want (for both speed and future proofing).

quote:

Also, I'm having a few other problems with this program - I'm not quite sure how to bind a variable so it'll be usable. I'm trying to let the user input a number to query, but any time I put the variable name into the select statement, it refuses it (I've tried numReq, :numReq, numReq.arr, :numReq.arr but none work). Here's all the relevant code. It's really lovely but I'd rather get the basics working before tightening it up further because it's driving me nuts.

I don't really know much PL/SQL, but I think you need a step in there that explicitly binds the :numReq parameter in your query to the numReq variable in your program, but I don't know how to go about doing that.

E:F,b.

# ? Apr 2, 2009 17:46

Whilst farting I: Apr 25, 2006

var1ety posted:

You probably meant "int" instead of "unsigned int" (or long/short, as appropriate). The documentation describes valid C => Oracle mappings.

Just int didn't work, either. I'll take a closer look at the examples given.

Jethro posted:

Triple Tech's advice to EndOfRadio applies to you too:

I don't really know much PL/SQL, but I think you need a step in there that explicitly binds the :numReq parameter in your query to the numReq variable in your program, but I don't know how to go about doing that.

E:F,b.

I know select * is not that great of a practice, but I'm not sure how to condense the select statement without just not using the intersection relation altogether and not using a join. It seems like a join is the most simplistic way to go.

# ? Apr 2, 2009 17:56

var1ety: Jul 26, 2004

Whilst farting I posted:

Just int didn't work, either. I'll take a closer look at the examples given.

It's probably because of building the string dynamically at run-time. It looks like in this case you have to use USING to pass the host variables to Oracle.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14407/pc_13dyn.htm#i2337

code:

    EXEC SQL EXECUTE S USING :empno, :deptno1;

# ? Apr 2, 2009 18:05

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Whilst farting I posted:

I know select * is not that great of a practice, but I'm not sure how to condense the select statement without just not using the intersection relation altogether and not using a join. It seems like a join is the most simplistic way to go.

Not sure what you're worried about. Instead of saying select a.*, b.*, just say select a.wanted, a.wanted1, b.wanted. The select clause of the select statement has nothing to do with the joinagenessosity.

# ? Apr 2, 2009 18:28

Whilst farting I: Apr 25, 2006

So apparently Oracle decides to poo poo its pants for no reason?

Here's the structure of the 3 relations (with a variable I'm using for embedded sql at the top

code:

unsigned int numReq;

unsigned int CS_DivNum;
VARCHAR      CS_DivName[50];
VARCHAR      CS_DivLocation[20];
VARCHAR      CS_DivPhone[12];

unsigned int CS_EmpNum;
VARCHAR      CS_EmpName[20];
VARCHAR      CS_EmpRank[15];
unsigned int CS_EmpYearStart;

unsigned int CS_IDivNum;
unsigned int CS_IEmpNum;
VARCHAR      CS_IOffice[20];
VARCHAR      CS_IPhone[12];

The first relation is called DIV, the second EMP, the third INT.

Here are two separate statements I'm using. Either one runs or the other depending on user input. Whenever I run the second one, it works fine. Oracle, for whatever reason, does not like the first one.

code:

select * from EMPS, INT, DIVS where EMPS.EMPNUM = :numReq and EMPS.EMPNUM = INT.IEMPNUM and INT.IDIVNUM = DIVS.DIVNUM

select * from DIV, EMP, INT where DIV.DIVNUM = :numReq and DIV.DIVNUM = INT.IDIVNUM and INT.IEMPNUM = EMP.EMPNUM

I get the delightful error message ORA-01722: invalid number whenever I try to do it, which appears to be some kind of warning that you're trying to convert from VARCHAR to a number! No matter what I enter (1, 01, 001) Oracle just flat out will not accept. :smithicide:

# ? Apr 3, 2009 00:09

Bad Titty Puker: Nov 3, 2007; Soiled Meat

INT is a reserved word in SQL-92, but that doesn't explain why the second query worked. Might be worth trying to put it in double-quotes and alias it to a different name.

Might also be worth a try to write it with ANSI joins (INNER JOIN).

code:

select * from EMPS, INT, DIVS where EMPS.EMPNUM = :numReq and EMPS.EMPNUM = INT.IEMPNUM and INT.IDIVNUM = DIVS.DIVNUM

select * from DIV, EMP, INT where DIV.DIVNUM = :numReq and DIV.DIVNUM = INT.IDIVNUM and INT.IEMPNUM = EMP.EMPNUM

The code you posted is not consistent. Is the table name "EMPS" or "EMP"? "DIVS" or "DIV"?

Column names such as "EMPNUM" and "IEMPNUM" ought to use the same column name to represent the same data element across the schema. If you have any influence over the schema you might well want to get rid of those loving awful all-uppercase and cryptically-abbreviated table and column names.

# ? Apr 3, 2009 01:03

Whilst farting I: Apr 25, 2006

INT is INTS, and EMPS is EMP.

quote:

select * from EMP, INTS, DIVS where EMP.EMPNUM = :numReq and EMP.EMPNUM = INTS.IEMPNUM and INTS.IDIVNUM = DIVS.DIVNUM

select * from DIV, EMP, INTS where DIV.DIVNUM = :numReq and DIV.DIVNUM = INT.IDIVNUM and INT.IEMPNUM = EMP.EMPNUM

I've been kicking around the idea of trying to do this with a character array instead of an unsigned int, but I have no idea how that'd work, especially with the cursor - the numbers I need to input are 3 digits (003, etc).

# ? Apr 3, 2009 01:33

var1ety: Jul 26, 2004

Whilst farting I posted:

ORA-01722: invalid number

You are either not passing a valid number into your bind, or you're querying a text column which has a value which cannot be cast into a number. For example, emps.empnum is defined as varchar and you're trying to query using a numerical bind.

# ? Apr 3, 2009 01:35

Whilst farting I: Apr 25, 2006

var1ety posted:

You are either not passing a valid number into your bind, or you're querying a text column which has a value which cannot be cast into a number. For example, emps.empnum is defined as varchar and you're trying to query using a numerical bind.

code:

unsigned int CS_EmpNum;

# ? Apr 3, 2009 01:41

var1ety: Jul 26, 2004

Whilst farting I posted:

code:

unsigned int CS_EmpNum;

Are any of these defined as varchar and participating in a join with a numerical column?

code:

EMP.EMPNUM
INTS.IEMPNUM
INTS.IDIVNUM
DIVS.DIVNUM

If your bind variable is fine that means that Oracle is trying to cast a character column into a number so that it can participate in a join.

# ? Apr 3, 2009 02:23

Whilst farting I: Apr 25, 2006

Every single one of those are declared as unsigned ints. That's why I'm so confused and frustrated, and willing to say "gently caress it" and implement a character array to take care of it, even though I have no idea how to do that.

The digits being entered are 3 digit and when googling I saw some posts saying that might make them considered strings or something?? But when I throw in a cout statement to make sure my numbers are still the same as taken in by c-in, they're fine for comparison purposes (014 goes to 14, etc)

Edit: For a test, I drastically shortened the statement.

select * from EMP, INTS, DIVS where EMP.EMPNUM = 014

And STILL I get errors.

Here's info on EMPNUM if this helps

code:

EMPNUM                                    NOT NULL NUMBER(3)

Whilst farting I fucked around with this message at 02:37 on Apr 3, 2009

# ? Apr 3, 2009 02:33

var1ety: Jul 26, 2004

Are the other columns I asked about NUMBER also?

# ? Apr 3, 2009 02:59

Whilst farting I: Apr 25, 2006

var1ety posted:

Are the other columns I asked about NUMBER also?

Yeah. In the embedded C++ they're unsigned ints, but the relations themselves, they're numbers.

# ? Apr 3, 2009 03:21

Whilst farting I: Apr 25, 2006

Welp, nothing wrong with the statement after all. Turns out it was my FETCH that had problems. Whoops. :nyoron:

# ? Apr 3, 2009 17:12

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

I am writing the following statement:

code:

DELETE FROM bc_similar_node WHERE parent_nid = (SELECT parent_nid FROM bc_similar_node WHERE parent_nid = 112 OR child_nid = 112);

And getting the following warning:

code:

ERROR 1093 (HY000): You can't specify target table 'bc_similar_node' for update in FROM clause

What gives?

cannibustacap fucked around with this message at 06:01 on Apr 4, 2009

# ? Apr 4, 2009 05:55

Namlemez: Jul 9, 2003

You can't normally update or delete in MySQL by referencing a copy of the same table. You can work around it by wrapping the subquery in an extra subquery as depicted here:

http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

# ? Apr 4, 2009 14:26

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

When Daylight Saving Time began here recently, I ran into a problem with my PHP/MySQL website. Certain things (posted messages, moves taken in games, etc.) have a DATETIME variable associated with them to say when they happened. When someone, for example, views a posted message, the time it was posted is displayed at the top. The intention was always to have this time displayed as UTC (the application isn't very advanced and currently doesn't serve users times converted appropriately for their time zones). When DST started here, the times started to be shifted ahead by one hour, which isn't what was wanted. I initially assumed the problem was originating with PHP, but following some posts in the PHP general help thread and some experimentation, I realised the problem is with the way I'm recording the times when communicating with MySQL.

At present, whenever I insert or update a record with a datetime value, I just use the NOW() command to record the time. As in,

code:

'UPDATE Thread SET NumberOfPosts = NumberOfPosts + 1,
                   LastPoster = '.$_SESSION[MyUserID].',
                   LastPost = NOW()
               WHERE ThreadID = '.$EscapedThreadID

Reading the documentation for NOW() it becomes clear that the time recorded is appropriate to the time zone, including Daylight Saving Time if it applies. This isn't what I want. Now if I send the following query to MySQL:

code:

SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00')

I get the correct current UTC time. So the obvious (but maybe clunky) solution that occurs is to go through my code replacing NOW() everywhere with CONVERT_TZ(NOW(), @@session.time_zone, '+00:00'). So the above query would become

code:

'UPDATE Thread SET NumberOfPosts = NumberOfPosts + 1,
                   LastPoster = '.$_SESSION[MyUserID].',
                   LastPost = CONVERT_TZ(NOW(),
                                         @@session.time_zone,
                                         '+00:00')
               WHERE ThreadID = '.$EscapedThreadID

Is this needlessly complicated? Is there a better way of doing it?

# ? Apr 7, 2009 13:10

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

UTC_TIMESTAMP()?

# ? Apr 7, 2009 18:49

Boogeyman: Sep 29, 2004; Boo, motherfucker.

Here's a weird one...in SQL 2008, I'm trying to export data from one database to another database. The table structures are exactly the same. In SQL 2005, I would right-click on the database, choose Tasks -> Export, choose the source and destination, and tell SQL to append the data from the source table to the destination table. It would crank for a while (the source table has about 20 million rows, and the destination table has about 600 million rows), and all would be well.

In SQL 2008, it seems that something has changed. I go through the exact same process, but after I tell SQL to append to the destination table, it decides that a datetime column in the source table is actually a datetime2 column. The table structure hasn't changed since 2003, and I've double checked anyways to make sure no one screwed it up...it's definitely a datetime column. Anyways, SQL attempts to convert the "datetime2" values into datetimes, which takes freaking forever.

So, why does SQL keep picking up that source column as a datetime2 and not a datetime? Is there a bug or something that I don't know about in the import/export wizard?

# ? Apr 7, 2009 19:49

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Jethro posted:

UTC_TIMESTAMP()?

Ah, thanks, that's just what I needed.

# ? Apr 7, 2009 23:20

Adbot: ADBOT LOVES YOU

# ? May 16, 2024 05:31

sonic bed head: Dec 18, 2003; this is naturual, baby!

I am trying to import a few thousand row CSV file (from Excel) into mysql and everything works fine except the few lines that have a comma inside them. There are about 150 of these and it's a pain to manually change them. Is there anyway to get mysql's load function to respect "Smith, Jr." as one entity without the quotes? That's how excel exports it and I don't think that I can format it any other way. Thanks!

# ? Apr 10, 2009 02:27

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »