Posts by tesuji

It is not actually the division you mentioned. Division is one of the extraction operations (i.e; Union, difference, join, select, ...etc).

This is what I mean.

Gracious SWEngineer!

DIVISION is synonym for

1. difference --> read your posting !

2. minus (oracle)

3. EXCEPT what is its official name by sql 1999 standard

So you may go back and try to understand my FIRST posting !

-- tesu

Hi munur93,

I am glad that my little erf() saved your time.

Unfortunately, MS visual c++ and other poor compilers do not have any Gaussian erfxcz function implemented to date despite the C99 standard provides some error functions in math.h. (Then I cribbed from famous Irene Stegun's Handbook of Mathematical Functions, what sadly only contains an approximation of medium-range precision, possibly the old one from Goldstine.)

There are erf() and erfc() in gnu c and c++, header file is math.h, not cmath.h.

If you are interested in higher precision erf() (about 1e-14), you can send me a message. I have calculated some erf() with higher precision using rational functions in L1-approximation.

-- tesu

Ah sorry, I misread you.

Obviously you meant plain division operator such as 13/3 makes 4 (if you were to stumble about archaic transact sql).

Simply forget my previous posting.

-- tesu

Hi SWEngineer

Consider these two tables:

table student:
Name
-------
Ada
Benita 		
Cecile
Fatma

table subjects_passed:
Name     subject
---------------------------
Ada      Relational Algebra
Fatma    Relational Algebra
Ada      Databanks
Fatma    Databanks

select name as "No exam passed" from students division select name from subjects_passed;

Result:

No exam passed
--------------
Benita 		
Cecile

So divison (except) operator gives all students which have not passed any exam.

That is, the result set contains all rows which are in left table but not in right table of division operator.

Same result is given by left join:

[B]select students.name as "No exam passed" from students  left join subjects_passed on students.name = subjects_passed.name where subject is null;[/B]

Another name for this relational operation is EXCEPT what is the official standard SQL/1999 name. There are also other names: difference, minus (Oracle).

Further relational operations are union and intercept (can be computed by division, so not necessary).

-- tesu

hi angel

We are just discussing similar stuff on another thread. What about your former problems, alreday solved?

-- tesu

I don't believe that files generated for processing with fast LOAD DATA INFILE to read them back into tables be compatible with mysqldump. However, mysqldump is able to generate complete insert-statements too. But I would never mix it.

There are many options one can set for speeding-up bulk-insert, for example delayed-insert (most important), disable-keys, no-locks.

-- tesu

Hi

seems to be a bigger task, not just suitable for first-year students. First, I suppose you want to input two-dimensional matrices (not multi-dim. ones), because of your both loops i,j. If so, you should decide how your 2D matrix is to store. In principle, you can do that column by column or row by row. Here is some algorithm in principle for the latter approach:

Let m be the number of columns (length of row), n be the number of rows (length of column) and di the offset for storing element A(i,j) of matrix A, then

set di = 0
for i = 0 to n-1 do
   for j = 0 to m-1
      get element A(i,j) from input
      compute address of Element A(i,j) by ij = di + j;
      store element A(i,j) in A(ij)
   next j
   set di = di + m
next i

Btw, address can also be computete by m*(i-1) + j which is somewhat ineffective.

You need to know how to input integer values. That may depend on the assembly tool you are working with, for example winASM has nice procedures StdIn and StdOut for managing I/O. In detail you need to know the allowed adressing modes both your processor and assembler supplies. There are assemblers not offering all possible modes, for example indirect addressing such as AX[BX+offset] sometimes missing.

So if you will have to figure out these things, we could proceed.

-- tesu

There seems to be some error in the code.

Here are the lines that show errors:

inbuffer db maxleng, ?, maxleng dup(?)     ;;; defines special buffer for int 21h/0AH function
mov dx, offset inbuffer 
mov bl, inbuffer[1]
mov inbuffer[bx+2], '$'   ;;; terminate string by $ for output int 21h/09h
mov dx, offset inbuffer+2

AH YES, the worst has come to the worst! Regrettably, such stuff also depends on the assembly tool on you are trying to assemble that code (I am supposing flat assembler of a kind;)). Unfortunately, you are still hiding this information also you didn't hit on the idea to mention the specific error messages you had got when trying to execute this very plain 80x86 code in real mode.

So I am rather unable to give you any further pieces of advice :(.

Btw, above code you complained on cannot cause of trouble for it runs on my machine without any error message :D

-- tesu

Hello,

151262 rows are too much, they can hardly be inserted by means of insert statements and php because of too much time consuming. Instead, bulk insert should be done with MySQL LOAD DATA INFILE.

-- tesu

Hi,

obviously your asm is real mode (because of int 10H (= 0x10) then the code for inputting a string terminated by enter key (0DH) could be:

maxleng equ 100 ; maxleng of inbuffer is 100 chars ;;; not knowing EQU? then replace maxleng of inbuffer by 100.

inbuffer db maxleng, ?, maxleng dup(?)     ;;; defines special buffer for int 21h/0AH function

;;; 0AH require offset of special buffer in dx
mov dx, offset inbuffer 
mov ah, 0ah
int 21h      ;;; read string, if input length exceeds inbuffer[0] string is cut

;;; Now inbuffer[1] contains number of chars read in, inbuffer[2].. inbuffer[1+inbuffer[1]] contains these chars

;;; For output place '$' in inbuffer[2+inbuffer[1]], be careful: must not exceed maxleng

mov bx, 0;
mov bl, inbuffer[1]
mov inbuffer[bx+2], '$'   ;;; terminate string by $ for output int 21h/09h

;;; now output inbuffer to screen, starting from inbuffer[2] 
mov dx, offset inbuffer+2
mov ah,09H                ;;; outputs $ terminated string to screen
int 21h

Well, that's it all. But this code not tested. You should check whether I set the '$' in the right place.

Tell me, if above code is working, also your changes made.

-- tesu

Okay, so I have a project activity to find maximum and min elements of a matrix. I kinda know how to make .asm file, but I don't know how to make .exe and .obj. Or are they generating by themselves? How to connect .exe with .asm?

Hello

OKay, that depends on the segements organisation of the program (including ORG) you define. And it depends strictly on the macro assembler you are using. For example using MASM or archaic TASM the model TINY and ORG 100H tells MASM to assembly a COM file. Model SMALL (1 data, 1 stack 1 code segment) and omitting ORG derictive just makes the minimal possible EXE file. Recently there was a question here on daniweb concerning the ORG directive. There I told something about this stuff.

So which assembler are you using, MASM, (TASM), MASM32, NASM, WinASM, or even an ARM or MIPS assembler ... ? On MSDOS/Windows they all "connect" .asm with .exe/.com files (by means of assembling and linking).

-- tesu

Are you about to design multi-client software (like SAP R/3)? If so, almost every table must have client's number in the first position of its primary key.

Question: clientid and employeeid are they given by the clients or are they generated on your side?

no, I meant the "=" in your statement: myList[placeholder] = number;

** deleted ** (i miss-understood you)

-- tesu

Hi
Shall only whole numbers be counted as: myList[placeholder] = number; (if so, then change 0 into +=). Or shall each digit of an inputed number be counted, e.g. input 33 here 3 counts twice.

>> Andreas5: deleted, i got it, sorry
-- tesu

Hi

well, understanding a data model only by names of listed tables (entities) and keys where some of them express vague relationships is hardly feasible. You should describe verbally what are client, vendor, employee, what are their relationships? One cannot review your keys without knowing that meta-data. Nobody knows whether "employee" belongs to client or vendor or somthing else, why "contract" hasn't any relationship etc.

However that be, let me annotate somewhat:

>>> employee----composite primary key(clientid and employeeid)
Means, that every time the (physically) same employee is to associate to a new client the data of this employees must be duplicated/copied, what's obviously wrong. employee's pk should be employeeid only.

If there is an 1-to-many relationship between employee and client, pk of employee becomes non-identifying foreign (nfk) key of client. That means a certain client would have one permanently assigned employee (salesman?).

If the relationship is many-to-many (what is more likely), a linking table clientemployee is necessary, where the pk of this special table is at least (clientid,employeeid).

>>> Same for invoice(aphistory)----composite primary key(vendorid and invnumber)
pk should be invnumber, vendorid is nfk to invoice. Assuming your pk(vendorid, invnumber) were correct, then a certain invnumber which makes the causal invoice could have been assigned to various vendors.

>>> aplid(invoicelineitem)-----composite primary key(invnumber, vendorid, invlinenum)
pk is (invnumber, invline#) only! Because invoicelineitem is already assigned to an invnumber, the corresponding vendor is uniquely identified.

>>> purchaseorder: analogous to invoice

>>> …

Hi

we had similar question recently. You may look here. Possibly you already know this older post by yourself.

If you don't like classless plain odbc (mysql: Connector/ODBC) you could make use of mysql: Connector/C++. There are also nice tutorials on Connector/C++.

-- tesu

rajarajan07 suggestion on proper NORMALIZATION is the most important and crucial fact when designing databases.

Improper or even ignoring normalization always leads to inflexible "bigger" tables which usually suffer from redundancy, inserting, and updating anomalies. To work around such anomalies noticeably additional programming efforts on plain php, c, cpp, java level is necessary. On such poor designed database creating efficient sql queries/selects is hardly feasible.

-- tesu

(I am involved in very lage database system consisting of quite a few thousands tables. Detractors always saying if these sea of tables had only seen some proper normalization, company would have been able to cut down programmer staff to 50 percent.)

Well, if the scenario I sketched out is correct, then there are two eventualities:

1. Your first posted query is wrong (then one could assume your data model might be correct)

or

2. Your first posted query is correct, then your data model is wrong.

Your data model should have at least one one-to-many (---<) and two many-to-many (---< >---) relationships:

1. customer ----< orders ----< ordered_items >---- product

2. ordered_items ----< ordered_accessories >---- accessory

Entity product and accessory can be put together in one entity only (yet, i would not prefer)

But ordered_items and ordered_accessories cannot by in one and the same linking entity (relationship that forms the many-to-many).

So redesign of damo seems to be necessary.

Finally, your posted problem cannot be solve by OR (I already told) nor by UNION you had been asking in a message to me.

I wish you success in your upcoming exams! (term is ending now?)

-- tesu

Before looping, loop instruction decrements contains of cx and jumps to @b: (assuming this label) if contains of cx != 0.

Unfortunately, your mov cx, 0ffffh fills cx with bit pattern 1111 1111 1111 1111B what is decimal -1 (2's complement).

Therfore -1 -1 makes -2 etc, what is !=0. Your loop ends only if 0 appears in cx, and that is not until after 65535 cycles what is in almost most cases not the action desired.

Btw the greatest positive value one can store in a 16 bit register is 7FFFH.

- tesu

Hello DAmanding

One live and learn!

I have fiddled about with plenty of computed columns and finally I got this monster solely containing computed columns:

select 4 as "need", 5 as drink, 2 as "of", 6 as "nature", 3 as "now", 1 as "I", 1 as "a", 9 as "alcoholic", cast( 8915./62991.+3 as varchar) as "Wilbur", '<'as '', cast( "now"||'.'||"I"||"need" ||"a"||"drink"||"alcoholic"|| "of"||"nature" as varchar) as "Now I need a drink alcoholic of nature", '<' as ' ', cast(9552./67441.+3 as varchar) as Knorr, cast(WilBur+cast(Knorr as double) -2*"Now I need a drink alcoholic of nature" as varchar) as "Chin-chin!";

which seems to be a true cracker for separating quiche-eater databases from real-programmer databases. Does it also execute on your database showing some telling findings?

-- tesu

Hi baby19

You may also have a look at databaseanswers, especially at 108. Medical.

Happy modelling ;)

-- tesu

Now I have got some better understanding of your problem. I am supposing you are using adodb. So why not populating your dropdown explicitly, in principle:

1. create an adodb recordset object
2. create your select statement to get the ProductName from db, order doesn´t matter.
3. connect and open select
************
4. populate dropdown with your existing ProductName, makes first row  on top of dropdown 
************
5. Populate dropdown in while-loop with other ProductName from products (now I do understand 
   why you made the UNION, first predicate with =, 2nd predicate with != )

do while not eof
  here populate dropdown list with ProductName from table products 
  excluding already existing ProductName (simple if) 
  move next
loop

Hopefully, that your wanting: "I need a way to have it return the existing ProductName that is in use currently, and THEN list the remaining products below." could be fulfilled, at least above pseudo code may inspire you a little.

(I don't know ASP, however I did some adodb programming a couple of years ago)

-- tesu

Hi

I have just finished above outlined code also your both tables installed. It works, result is:

/*
select user_subject.Name as "Name", ParseAndReplace(user_subject.Name) from user_subject;

Name    ParseAndReplace(user_subject.Name)
------------------------------------------
John    Science, English
Riya    Maths, English, Chemistry
Mack    Maths
Nik     Maths, Science, English, Chemistry
*/

I did it on a Sybase database, I am just sitting in front of it.

-- tesu

I am afraid this can't be done in a plain select statement. You need some procedural code for parsing the string which contain the subject numbers and translating these numbers into subject names. A userdefined function can easily solve this problem, in principle:

create function parseandreplace (in vName varchar(50)) returns varchar(255) 
begin

... some declarations and initialisations here ...

-- get subject string
select subject into vSubject from user_subject where name = vName;

-- put a ',' on right end of subject string
set vSubject = INSERTSTR(length(vSubject)+1, vSubject, ','); 
 
-- for each subject number in vSubject do:
WHILE i < length(vSubject) LOOP

  -- position of next comma
  set comma = locate (vSubject, ',', i);

  -- get next subject number
  set vID = cast(substr(vSubject, i, comma-i) as integer);  

  -- get name of subject  
  select subjectName into vsubjectName from subject where id = vID;

  -- concat names
  set vsubjectNames = vsubjectNames || ', ' || vsubjectName;

  -- scip to next subject number
  set i = comma+1; 
END LOOP;

-- return concat names
RETURN vsubjectNames;
END;

-- usage
select name, parseandreplace(name) from  user_subject;

Well, above code should work in principle. Similar things could also be done in php. As for a UDF effort for programming and testing should not exceed one hour.

-- tesu

as there are no return statements, in the solution you gave, so does it means that stack unwinding is independent of the return statement.

the functions returns back due to stack unwinding.

one more thing, what all things are stored in the stack while pushing in the stack (function call)

Hi mithunp

To clarify what happen if a C function is called, let's analyse what will happen if this function int fun (int a, double b) is to execute.

1. Just before the call
First, the calling program saves the values of EAX, ECX and EDX on the stack

Then the value of last actual parameter b is pushed on the stack. Next follows parameter a.

Finally contains of EBP register is pushed which is now on top of stack.

2. Now we enter function fun().
All local variables locally defined in fun will also be created on the stack. Then some calculation is done, especially the return value is calculated.

3. Just before leaving function fun
All locally defined variables are poped from stack (partly unwinding)

Important: the return value is passed back in EAX register and not on the stack. This is true as long as the length of the return value is less or equal 4 bytes (if greater, capacity of EAX would be exceeded).

Longer return values will be passed back by an extra actual parameter which is automatically generated if its length exceeds 4 …

Hello,

You can do this converting by means of basic bit operations on "hhmmss.sss" for masking (e.g. '8' = 0x38 --> 0x38&0x0F --> int 8) and then composing the single int digits to short int numbers with Horner method, for example:

void pasti (char *s, struct stm *st){
  st->hh = 10*(s[0]&0x0F) + (s[1]&0x0F);
  st->mm = 10*(s[2]&0x0F) + (s[3]&0x0F);
  st->ss = 10*(s[4]&0x0F) + (s[5]&0x0F);
  st->ms = ((s[7]&0x0F)*10 + (s[8]&0x0F))*10 + (s[9]&0x0F);
}

/* where the struct is: */

struct stm {
short hh;
short mm;
short ss;
short ms;    
};

/* You may call pasti(), for example: */
char s[] = "180555.123";
stm st;
pasti(s,&st);
printf("%i   %i   %i   %i\n", st.hh, st.mm, st.ss, st.ms); 
/* 18   5   55   123 */

Hopefully that it will work.

-- tesu

Hi

tucanoj is right, I agree with him. There is an another solution

void print4(int pme){ if (pme > 0) print4(pme-1); printf("%d,",pme);}

which saves some if-branches.

What's hidden? Do you mean how does recursiveness function?

When print4 is called with parameter pme, say pme is 3, 3 is pushed on stack. Next 3 > 0 applies so print4 is called with parameter 2 which is also pushed on stack before print4(2) is executed. 2 > 0 applies too so print4 is called with param 1 which is pushed on stack too and print4(1) will be executed. Finally 1 > 0 applies and 0 is pushed on stack then print4(0) will be executed.

Now if(pme > 0) doesn't apply anymore and print4(-1) will not be executed. Printf now prints the actual parameter 0. Then the functions returns back to caller (which was print4(1)) and there value 1 of actual parameter will be printed out and so on.

The printed sequence is 0 1 2 3.

There is a further hidden thing: Because the actual parameters of function calls are pushed on stack before a function call is executed, the same sequence appears on stack:
0
1
2
3
If function returns to caller, the upper stack value will be removed. Without this stack mechanism recursive function calls would be impossible.

I hope that I could clarify a little of hidden things.

-- tesu

Well, possibly is this idea too simple:

SELECT product.product_name FROM orders, product WHERE orders.product_id=product.product_id;
UNION
SELECT accessory.accessory_name FROM orders, accessory WHERE orders.accessory_id=accessory.accessory_id;

I have got the feeling that there could be a relationship between products and accessory since the wording "accessory" suggest such an assumption.

So the senario could be: A customer purchase a certain product and some optional accessories which are associated with this product. Other Products have other associated accessories. A buyer of a certain product is free in whether he purchase zero, some or all accessories.

If so, you should urgently revise your data model for table order can't achieve the requirements of the outlined senario.

Hint: table name ORDER is already a SQL system word. Such a collision should be avoided, so rename your table order to orders for example.

-- tesu

hi Virangya

glad to meeting you again. Do you already solve your other problem?

Well, what you want to do with boolean OR does not function for it combines the wrong rows from the three tables, which is kind of cross product, what you might have already noticed.

Such an unfavorable sql construct originates from poor table design. If you have the chance to make a redesign of your tables, you will eliminate this and for sure further problems for the future.

On the other hand, php is nice programming language which allow almost every patch of poor-designed tables/ data models. Possibly the problem may also be solve by means of case function in sql statement what I will have to check.

-- tesu

Just think of alpha-numeric phone numbers such as 867-5309/Jenny. Therefore always char-type without any restriction. I suggest varchar(12). There is no minus when searching for alpha-numeric values.

-- tesu

hi

Because of many-to-many relationship you need a linking table:

create table employee_college(employee_id, college_id, ... here you can add further data
e.g. date of graduation etc);

where pk is (employee_id, college_id), both are also foreign keys.

Hint: I myself name foreign keys of child tables and primary keys of father tables always equally.
Advantage: joins are very simple to construct without any join constraint in where-clause or ON conditions.

As for the 1 to many relationship between college and state: Foreign key state_name in table college already defines this 1-to-many. You should define this foreign key explicitly:

create table college (....state_name char(3)... foreign key (state_name) references states_table on delete RESTRICT); -- restrict is default, therefore on-delete clause can be omitted.

Don't use this "on delete CASCADE" that much (otherwise your children would be killed automatically if one of their fathers dies).

-- tesu

hi

the server mysqld.exe is usually installed as a service on MS XP. You can check it:

Start > run > enter: services.msc

Examine sevice mysqld whether it is started. If not start it.

If already started and error still exists, examine service mysqld again, then click right mouse key on it to see its properties tabs. Here click on LOG ON tab and check mark for Allow Service To Interact With Desktop.

Hopefully this will help.

-- tesu

What is the meaning of review_type in third schema ?

Now that you have also shown schema last_review which has just 5 attributes, it is obviously that the 3rd schema is more suitable, especially if there would be only an 1 to 1 relationship of employee with last_review.

I thought column review_type were to identify various sorts of last-review information. But this is obviously wrong.

-- tesu

I see. To modify already stored data update is the appropriate DML statement.

If you have the chance to drop the table and re-insert all rows where you have added the missing authorid, would really be the best way.

If you want to modify the exisitng rows in table joke, you can do it in such a manner:

update joke set authorid = [B]3 WHERE LEFT(joketext, 20)= 'Why did the chicken'[/B];

You see the crucial problem: It is very important that the correct row be identified exactly (bold-faced part in above update example). If not, wrong authorid would be assigned.

Therefore re-inserting corrected data is best method.

-- tesu

Hi

you have been posting twice. I tried to answer a little on forum Databases.

-- tesu

Hi,

Once I had heard of all of them, well 10 years ago, when I finished Uni. So let me take a try:

A1. worst case requires O(N)reads, where N= 100000/10 = 10,000 pages to read. Best case is 1 read. (Efforts for page scanning in main memory ignored.)

A2. B+ requires O(log n) reads. Because of dense indexes retrieving one tuple requires log(100,000) + 1 reads = 17+1 = 18 reads (worst case). Best case is one read for getting the key + 1 read for tuple, makes 2 reads.

A3. If directory is in main memory, there is only one read to get the bucket where hash B points to. Effort for bucket scan to retrieve the key ignored. Finally tuple is read, therefore 2 reads. Best case also 2 reads.

These should be for point queries (one tuple per select). As for range queries I have no ideas.

I hope above approach to your problem isn't that completely wrong, at least it could inspire and direct you a little.

Please, let me know the correct result.

-- tesu

You have column last review type which implies there are diversified last reviews. So they also may differ in their fields/parameters. Obviously this is kind of ISA-relationship (generalization/specialization), therefore I would put the data of last reviews in separate tables.

Addition: If your last reviews only differ slightly, gathering all fields together in general table seems possible (however, this would violate some normalization requirements).

-- tesu

Ah sorry tyson.crouch!

I ought to have read your posting really more precisely. You have already given a complete correction of vibhadevit's table design. I was to much focused on his unfavorably designed table `user_subject`.

-- tesu

I completely agree with tyson.crouch. To guarantee referential integrity, all foreign keys must be correctly defined. Unfortunately, there is a further very serious flaw in below table user_subject:

CREATE TABLE `user_subject` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `user_subject`
--

INSERT INTO `user_subject` (`id`, `name`, `subject`) VALUES
(1, 'John', [B]'2,3'[/B]),
(2, 'Riya', [B]'1,3,4'[/B]),
(3, 'Mack', [B]'1'[/B]),
(4, 'Nik', [B]'1,2,3,4'[/B]);

The many-to-many relationship between subject and student is constructed by the bold-faced list of keys. This is sort of horizontal repeating group, what means that this table does not fulfill first normal form. But most important: such coded relationship does not allow to create any useful query, for example a query for getting a list of students and their assigned subjects is hardly to write down. How should this query be designed, only consider that the numbers of assined subjects are varying from one to four?

Therefore this table must be redesigned. Hint: Because of many-to-many relationship we actually have these three tables:

1. subject

2. student

3. student_subject

where the latter one defines the many-to-many relationship. This table has at least two columns: studentid and subjectid which make the primary key of this table and they are also foreign keys concurrently.

I will give some further hints later.

-- tesu

Hi,
table joke shouldn't be a joke, therefore column joke.authorid must have proper values, assuming that first joke is from Tim and second from fredl your joke table would then look like:

+----------------------+----------+
| LEFT(joketext, 20) | authorid |
+----------------------+----------+
| Why did the chicken | 3 |
| What is a cows favor | 1 |
+----------------------+----------+

Now your inner join condition "WHERE authorid = author.id" results: 

LEFT(joketext, 20)    name         email  
-----------------------------------------------            
Why did the chicken   tim horton  timmy@home.ca
What is a cows favor  fredl       fredl@home.ca

-- tesu

float numbers;
|
?
|
V
numbers(index)> numbers(0) ??

Do you mean:  

array_numbers[index]  ?

Of course, USE CODE TAGS !

-- tesu

hi

glad to hearing of you. I was also afraid that the requirements (mainly initial value?) for integrating your partial diff. equations by means of a system of ODEs using RK solver weren't achieved. Unfortunately, I have lost so much of my knowledge on numerical how-to solve ODE/DAE/PDE ever since I left the uni for over ten years, so I wasn't any more able to give fair help.

I wish you all the very best for your research.

-- tesu

So why don't you comply with all those valuable piece of advice you have got in your 2-day-old former thread?

If you had only compared above program with that one posted in your former thread, you would have already spotted the principal mistake. Then you were ready to figure out some logic mistake too.

Ah, Dennis already hint at it.

-- tesu

Your sql statement seems to be not correct. Probably there will be errors if it is executed. (possible reason: more than one value in resultset of subselects)

Most likely the "=" or "!=" in front of the subselects are wrong and should be replaced by "in", or even combined with "any", "some" or "all" (hoever, which would be more complex).

Unfortunately, your explantation of the scenario is rather cloudy.

-- tesu

Hello,

yes there is, as you have already noticed: if you want fast access on the file name, the primary key should be this file name, provided that it is unique. If you had decided an autoincrement to be PK as usually, you would have to create an additional index on column filename. Because for PK an unique index is always automatically generated, you need to manage both indexes in parallel. Therefore, autoincrement pk is somewhat contra-productive.

Well, you are talking of about 2 million + rows what is not really a great amount of table rows (I am involved in a database which has a monthly delta of about 8 millions rows). You can speed-up access by using a hash index too, if you don't need an order on the file name.

The crucial point will be how to load a database with an already existing bulk of rows, If you want to start with, say about 1.5 mill, you can't make use of standard inserts.

-- tesu

Hi,
you may try this:

select 'aaa_01' as st, left(st, locate ('_', st)-1) as shorter
-- st       shorter
-------------------
-- aaa_01   aaa

-- however, code not tested. you should look-up left and locate functions from mysql manual whether
-- the parameters are in correct sequence (mysql has its own notion on how to put the parameters in 
-- sequence)

-- Also this should work (parameters are in correct sequence now for instr() had been copied 
-- from Oracle):

select 'aaa_01' as st, left(st, instr(st, '_')-1) as shorter

-- tesu

Hi,

you can try this:

select coalesce(columnSometimesNULL, '0.00') from yourTable;

-- tesu

try like this....(Just remove AS before tablename A) you will get it.

SELECT * FROM
(
SELECT top 10 * FROM tableX ORDER BY ID DESC
) A
ORDER BY A.ID ASC

Both (...) AS A and (...) A is SQL standard 1999/2003 and can't be responsible for his error, unfortunately he hasn't shown the exact select and message by now.

-- tesu

Hi,

you should give some more details, especially on the obviously wrong select statement containing those computed columns. If mmtot, mattot, and odctot are also computed columns, what they are computed from, from real table columns?

Simple post your select statement and some information on the involved tables too.

-- As for example

select (a+ b) * c as x, x + d / e as y, x + y as z from t;

-- where a,b,c,d,e are columns of t and x , y, z are computed columns 

-- is valid on almost every database systems, I have just run this on ms sql server 2008.

-- tesu

Hi,

In this thread I gave a solution on how to split strings into parts although it was for Oracle this statement also works on SQL Server and Sybase databases without changes.

But I am afraid, you will need a more sophisticate solution because the number of tags within a string you want to separate seems to vary.

-- tesu