Posts by tesuji

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

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

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

Obviously, size of paintComponent is a global variable (instance or class). Where the scope of Vizatuesi's size is local within this method even it is a formal parameter. Any changes of the local variable does not affect the globally defined size, that is just as living in two different worlds.

Therefore the second code is correct.

You should read something about local, instance and class/ static variables, maybe at sun site.

-- tesu

Hello

First, variables must be initialize before using them, e.g. smallest has been defined but never been initialized. So put these two lines just before your while loop:

fin >> next;
smallest = largest = next;

Second, do not use fin.get(next) for this reads one char only, and not an int, result would be DT mismatching. The >> operator is much more powerful for it is overloading with almost all data types of c++, e.g. istream& operator>> (int& val ), just that what you need here.

Third, your while loop starts fine. But inside there is some trouble.

Think of redesign it that way:

while (! fin.eof())  // if you make use of eof(), fin should has been read once at least
{
     // 1. process your just  read-in data here:
     if ( next < smallest ) ...; else; if ( largest < next ) ...;

     // 2. read next data from file 
     fin >> next;  // again, do not use fin.get(.) for data type mismatching
} 

// coming here, smallest and largest shoud contain their appropriate values.
// Sorry, I don't have tested this code, so there might be some minor mistakes.

I hope these hints may help you a little.

-- tesu

so the 4ah will become 4 and a? but both will be stored in al, so how will i be able to separate that?

Hi

Here is some code for processing the lower nibble (e.g. hex digit A of 4AH)

;....
	mov	dl, [bx]		; 2. processing bits 3 .. 0
	and	dl, 0fh			; clear bits 7 .. 4
	add	dl, 30h			; add '0' to make an ascii for output (!)
	cmp	dl, 3ah			; compare it with  3ah
	jb	lo			; hex digit   '0' .. '9'
	add	dl, 7h			; hex digit 'A' .. 'F' 
lo:     int     21h		        ; doscall, output <cl> on screen
;...

[bx] points to a char array in data segement, for example

chars	db 'abcdefghijklmnopqrstuvwxyz'	; to be output by hex$out
cends	db ?		; create a symbol to calculate number of chars

; then in code segment load byte ptr to chars array:
mov	bx, byte ptr chars	; start offset

Now you should reason how to process higher nibble (e.g. hex digit 4 of 4AH), for that you need a shr instruction (don't use cl reg)

-- tesu

Standard dev is Sum((datapt - mean)^2)/(counter -1) so you can't add the squared data and then subtract the mean squared unfortunately since there is a cross term of -2*datapt*mean when you square it out. You need to hold all the data then calculate the mean, then do the calculation.

Sorry, even a phrenologist is allowed to make a mistake. Unfortunately, your implication is incorrect.
Proof: ;)

Let standard deviation be defined as:

s^2 = sum (a - xi)^2 /( n - 1)

By multiplying out the sum we get:

(n-1)*s^2 = sum(a^2) - 2*sum (a * xi) + sum(xi^2)
(n-1)*s^2 = sum(a^2) - 2*a*sum(xi) + sum(xi^2)
(n-1)*s^2 = sum(a^2) - 2*a*n*a + sum(xi^2)
(n-1)*s^2 = sum(xi^2) - n*a^2

and finally:

s = sqrt((sum(xi^2) - n*a^2)/(n-1)); 

(where n is number of measured data, xi is a single measured data, a is average sum(xi)/n, s is standard deviation of the measurement series, ^ stands for power of, sqrt() is square root)

You may take a try:

n = 5                              
i      1    2    3    4    5    sum
x      1    2    3    4    5    15
xi^2   1    4    9   16   25    55

a = 15 / 5 = 3
s = sqrt((55 - 5 * 3^2)/(5-1)) = sqrt(( 55 - 45) / 4 ) = sqrt(10/4) = 1.58

Prove it by taking the inconvenient way:

s = sqrt(((3-1)^2 + (3-2)^2 +(3-3)^2 +(3-4)^2 +(3-5)^2)/(5-1))  ?

You can also examine this facts on wikipedia. You may study there the last formula of paragraph "Identities …

Sorry, drop the s, so MUL stands for the unsigend multiply instruction.

Number-two homework? But this one is easy by contrast with your first one.

True, there are reasonable doubts in going that way, Jay-Z usually says. Its really easier because of one hexadecimal digit stands for four binary digits. So start by splitting 8 bits into two nibbles (4bits long, do not mistaken that american word :ooh:)

-- tesu

13 0000000E B8C90B mov ax,3017
14 00000011 BB0300 mov bx,03h
15 00000014 F7F3 div bx

3017 divided by 3 makes 1005 remainder 2
1005 is in ax, remainder in dx
(1005D = 03EDH)

Now it's your turn to carry out these three steps:

16 00000016 0D7777 or ax,7777h
17
18 ; test
19 00000019 A821 test al,21h
20 0000001B B402 mov ah,02h

Hint: for the boolean instruction (test is and AND operation, al AND 21h) you have to write down the binary representation of the operands first. You can shorten the calculations for only 8 bits are significant ;)

-- tesu

Btw, it is not necessary to read the data file twice for average and standard deviation can be calculated together within the first loop. You may do the following extensions to do this:

// After         double total = 0; // variable to hold total of all numbers

// add:
   double qtotal = 0;   // variable to hold the total squares of all numbers


// After 	total += next; // add numbers from file and store in total

// add:
   qtotal += next*next; // add the squares of the numbers


// After        double avg = (total/counter); // calculate the average

// add:
   double std = sqrt((qtotal - counter * avg)/(counter - 1)); // calculate the standard deviation
   cout << "The standard deviation is " << std; 

/*
The identity 

     sqrt(SUM(avg - next)^2/(counter - 1)) == sqrt((qtotal - counter * avg)/(counter - 1))

can easily be shown by multiplying out the summation of (avg - next)^2. 

If a large quantity of data should be calculated that way and where the standard deviation becomes very small the term (qtotal - counter * avg)/(counter - 1) could slightly fall below nought caused by numerical reasons. If this happens abs(qtotal - counter * avg) should be calculated. And sure, if there is only one number (counter=1), std is not defined.  
*/

You may try this new formula, it will save reading the file twice and the second loop.

-- tesu

Hello

Could that be that you have mistaken counter and avg in:

stdDev = sqrt((totalDevSqrd / (avg - 1))); /* standard deviation equals the square root of the total square deviations devided by the average minus 1 */

// shouldn't it be?
	stdDev = sqrt((totalDevSqrd / (counter - 1))); //... divided by counter-1

-- tesu

Yes, by running it manually.

For example what is the result in dx:ax after execution of 00000014 ?

I was wondering if it is possible to subtract 30 from 4ah? i thought so i can convert the ascii character 'C' to hexadecimal '43', i will just subtract 30h from 43h, then add 30 so it will become 43 again and i will be able to perfrom arithmetic on it, but then i realized, what about when the hexadecimal has letters on it? hmm. help please!

Hi cmsc,

I am glad that we have solved your other task. Fortunately, your new homework is much more easier than the one which required int inputs, just about a full dozen of instructions, all dealing with 8 bit registers, currently no MULs :cool:

-- tesu

The character in memory position 109h = F

Hello

Well done !

Do you know how to prove it?

-- tesu

I found it how to show postgresql's execution plan (query plan):

EXPLAIN SELECT your_further_select_statement, and it's like Oracle's EXPLAIN PLAN (what has to be expected). Here is something about joins and cartesian products.

-- tesu

(The operator has decided that I can't update posting older than 29.999 minutes. So I did new posting)

Hello,

I am glad to meeting here a postgresql fan too! So let's proceed unter mysql's hood.

What do you actually mean by

>> relataions.relation_type_id foreign from relation_type.id
>> relations.from_id foreign from items.id
>> relations.to_id foreign from items.id
?

Sure, because of the one-to-many relationships relataions.relation_type_id is a foreign key which points to master table relation_type. Both columns are used in the where clause to define the inner join.

But this usage DOES NOT mean that the table also has a foreign-key constraint what is usually table-defined in:

create table relataions (... id integer, ... FOREIGN KEY (id) REFERENCES relation_type, ...);

Also usage of relation_type.id does not mean that id is primary key of table relation_type, as usually defined in:

create table relation_type (... id integer, ... PRIMARY KEY (id), ...);

Well, if all these primary and foreign key constraints are well defined the way I just sketched out, there would be automatically put an index on all those primary and foreign key columns.

These indexes are very important to carry out all inner joins with high speed. If these indexes DO NOT exist because of omitting proper definition of primary AND foreign keys the inner joins would degenerate into cartesian products (cross products).

For example if all of your three tables have 1000 rows each, the number of comparisons necessary to compute the degenerated inner …

Well, some 8k entries and excecuting that select statement would last about 22 sec ? sounds really kind of unreal.

So which database engine? Are there any primary keys and foreign keys, or indexes defined on tables: items it, relations rel, relation_types relType?

If NOT, access time is proportional to the cardinality of numbers of rows (NOR) of table items times NOR of table relationals times NOR of table relation_types, what could be a very large number. You may use count(*) to compute the cardinality of those inner joins.

Therefore, properly defined primary and foreign keys would really speed up access time.

If there aren't any properly defined primary and foreign keys, you can put indexes on the appropriate colums for speeding up access time afterwards, what does not directly affect the existing database schema, in other words, you don't do a redesign when adding indexes.

-- tesu

@tesu:
The algorithm is correct. Its just that the first tw0 terms of the series must be printed separately. fibo(18) is 2584 iff fibo(0) = 0. So what you are printing as your first term should be the 2nd term (ie fibo(2) if we take the first term as fibo(0)).

generally the series is :

fibo(0)------ 0
fibo(1)------ 1
fibo(2)------ 1
fibo(3)------ 2
fibo(4)------ 3

...............
...............

fibo(18)------ 2584

hope it helps.

P.S: @xaop
once you get the logic correct spend some time on coming up with meaningful variable names. Also use proper indentation. As of now, a small advice, never use void main(). Its plain wrong (and disrespect to the creators of C).

Sorry, I don't agree, NPC.

His algorithm does not produce FIB(18). So it must be considered to be incorrect, even if we don't regard his mistake in if-statement.

Here is a somewhat more compact iterative Fibonacci:

for(int i=0,n=18,x,y=0,z=1;i<=n;i++,x=y,y=z,z=x+z,printf("%3d %5d\n",i-1,x));

-- tesu

xor ah, ah is equivalent to mov ah, 0 ; yet a bit faster (the fasted 0 one can generate)

AND al, 0FH can be replaced by SUB al, 30H ; 3xH - 30H = 0XH (x from 0..9)

Again, logical instruction AND is faster than numeric SUB.

-- tesu

hello! i need to make this program that lets the user input like for example 99 then the program will output 'c'.

i don't know how to start. but i thought about getting the first 9 first, then subtract 30h, the multiply it by 10. afterwards, add the other 9, then subtract 26, because c -> 99 -> is 63h in hexadecimal. but... when i try the others i need to subtract different values to convert them to hexadecimal. am i using the wrong approach?
thank you very much for your replies. :D

Your approach starts well but then the idea of subtracting 26 (what base?) leads to wrong solution. Assuming that you want input two-digit integer values, for examples decimal 07, 99, max. 127, by int21h and ah function code 01h, ascii-result (30h..39h) in al twice done, then:

A. Processing first digit

1. AND al, 0FH ; mask 3XH to 0XH, where X is from {0,1..9}

2. XOR ah, ah ; we need AX for multiplying X by 10D

3. MOV bx, 10D ; to compute <ax> * 10 = X0D

4. MUL bx ; computes ax * bx, result in dx:ax, you only need ax

5. MOV bx, ax ; save X0D for further processing

B. Processing second digit and add both decimal digits

Because of 80x86's weak mul capability we need to restore ah first

Now get second digit and mask it, as is first digit, result …

he, there seems to be a mistake in your algorithm, because fib(18) is 2584.

-- tesu

Still no difference :(

all it prints is " 1 "

that s true, you should also replace if by for loop:

//if(n<=18)

for (int i=1; i <=18; i++)
{
printf("%2d    %5d\n", i ,n);
n=f+s;
f=s;
s=n;
n=f+s;
}
/*
now result is:

 1        1
 2        2
 3        3
 4        5
 5        8
 6       13
 7       21
 8       34
 9       55
10       89
11      144
12      233
13      377
14      610
15      987
16     1597
17     2584
18     4181

>>> Your algorithm isn't completely correct for fibo(18) = 2584. Also f1=f2=1 and f0=0.
Possibly s contains correct series.
*/

-- tesu

hi,

you should remove the ; in this line: if(n<=18);

-- tesu

Well, if you want to do suchlike string separation in query statement, you can make use of ora's string functions, such as:

select 'tag: key1, key2, key3' as st, length(st) as le, instr(st, ':') as lo, substr(st, 1, lo-1) as tag, substr(st, lo+1, le-lo) as whateverfor;
/*
st                     le  lo tag  whateverfor
---------------------------------------------------
tag: key1, key2, key3  21  4  tag  key1, key2, key3 
*/

-- tesu

Hi Madawar,

first i have to correct the following line:

>> MOV AX, 0FFFFFFFEH ;;; correct, AX is filled with FFFFFFFEH

is wrong because length of AX is 16bits. Therefore correct is:

MOV AX, 0FFFEH ;;; correct, AX is filled with FFFEH

1k is short for one's complement (in Bavaria;))
2k is two's complement

msb = h.o. bit, lsb = l.o. bit

As for sign extension: we have negative whole number FEH = 1111 1110H. If we want to extend it for some reason, extension must be done with sign bit, for example 11 1111 1110B. How to translate it back into hex? each hex figure consist of 4 bits, therefore further extension must be made: 11 11 1111 1110B = FFEH. For 80x86 all register are divisible by 4 so sign extension usually happens in portion of 4 bits that makes just a hex figure, FEH, FFFEH, FFFFFFFEH (for al, ax, eax).

Btw, for 80x86 there exists a special move instruction MOVSX for move with sign extension.

Dante's very good explanation didn't cover negative whole numbers too, so you can't hardly say there would be a contradiction.

-- tesu

Now I see you are a assembly programmer. Here the leading 0 has a special meaning.

Consider this two instructions:

MOV AL, FEH
MOV AL, 0FEH

In first instruction the value of the variable FEH (it is an offset) is moved into reg. AL. Therefore such variable must exists in data segment, for example by definition: FEH DB 1

In second instruction the 8-bit constant FEH is moved into 8bit reg. AL.

Also consider these:

;; negative FEH should be loaded:
MOV AX, 0FEH    ;;; wrong, bcause AX is filled with: 000000FEH
MOV AX, 0FFFFFFFEH    ;;; correct, AX is filled with FFFFFFFEH

-- tesu

Hi,

Am a self teaching Newbie Assembly programmer :) Still in the theoretical Stages.

My question is....

According to the book am reading

When you convert 254(decimal) to hexadecimal you get 0FEh.

And when you convert -2(decimal) to hexadecimal using two's complement you get OFEh.

When i do it manually i get FEh.

Is the zero before FEh always necessary or is it just another way of representing your code..?
Am fully aware that 0 in hexaecimal stands for 0000(base 2)

Thanks in advance......

Well, the 0 of 0FEH is wrong if it should represent -2D. The most significant bit (msb) of an integer numbers always indicates whether the number is positive or negative. msb = 0 is for positive numbers, msb=1 stands for negative numbers, where negative whole numbers usually are in two's complement.

Let's code -2D in binary 2k and then in hex 16k:

2D = 10 --> extend it by some 0 to indicate true positive number: 0000 0010
now 1K: 1111 1101, 2k = 1k + 1 = 1111 1101 + 1 = 1111 1110B
-2D = 1111 1110B = FEH

If you add a 0, you get 0FEH, binary: 00001111 1110B what is a positive number, therefore wrong! If you want to extend a negative whole number, you must extend with the sign bit, that is for example FEH = FFFEH = FFFFFFFEH.

-- tesu

Hi,

Yes, I know, MySQL indeed did this insane expansion of the mathematically well defined select-order-by clause. Now everybody will be able to use MySQL for comparing apples to oranges without cease. Now you should, no, you must carefully examine every row result whether it contains idiotic combinations of invalid column values or not.

Let me allow to cite from chapter 11.16.3 of MySQL reference manual:

"MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the select list that do not appear in the GROUP BY clause."

And further on this warning:

"When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."

Do you got it? Indeed, MySQL pulled off a feat to turn the complete relational algebra and set theorie topsy-turvy.

NEVER EVER use this foolish MySQL feature!

-- tesu

Hi iqra123

You may have a look into gnu C library. It contains fully DES Encryption, also MD5.

If you are only interested in putting some cipher on your data, there is a nice method by using pseudo random numbers:

sender side:
for all chars of message to send do
..compute random number with given random number generator
..add random number to char

send message to receiver

receiver side:
for all chars of message received
..compute random number with given random number generator
..subtract random number from char

Important: Sender and receiver must use a random number generator which generates
same sequence of random numbers. This can be controlled by the random number's seed.
Encryption with superposed random numbers is hardly to break.

>> lionaneesh

Imperator Julius Caesar's encryption/decryption cipher is really way too weak, even for Junior Ones.

-- tesu

>> string currentChar = 0;

should be

char currentChar;

Question: how to finish this loop:

>> while (subscript < numChars) ?

so what about subscript++ ?

Greetings to Cotton State! I've often been to Huntsville :)

-- tesu

Why would you do this recursively? It will be better to do it iteratively.
For example :

//assume coefficient is written from highest degree to lowest 
float evaluate(std::vector<float>& coeff, const float x){
 int degree = coeff.size();
 float result = 0.0f;
 for(int i = 0; i != degree; ++i){
   float powResult = std::pow(x,degree - i);
   result += coeff[i] * powResult;
 }
 return result;
}

Well, firstPerson

your code:

//assume coefficient is written from highest degree to lowest 
float evaluate(std::vector<float>& coeff, const float x){
 int degree = coeff.size();
 float result = 0.0f;
 for(int i = 0; i != degree; ++i){
   float powResult = std::pow(x,degree - i);
   result += coeff[i] * powResult;
 }
 return result;
}

evaluates a polynomial in place x by explicitely computing all x to the power of k, that is: result = coeff[0]*x^degree + coeff[1]*x^(degree-1) +..+coeff[degree] where x^k is given by pow(x,k) method.

Unfortunately, this program code is anything but Horner algorithm.

But you can easily meet Rashakil's stringent requirement for Horner's algorithm by replacing the for-loop and the return statement of your above program code by:

for(int i = 0; i <= degree; i++) result = x*result + coeff[i]; return result;

At this point I am far apart from dubbing persons "scrap posters" as Mr Rashakil did. With regard to the somewhat perceived compliment I can't deny myself to cite a more famous German-American guy: "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.", …

Ignore all these scrub posters and use Horner's algorithm.

Hi Rashakil,

as for Horner you are completely right!

I am already using Horner's first algorithm in this code:

void pore (int d, float a[], float x, float *r){
  if (d>0)pore(d-1,a,x,r); *r=x**r+a[d];}

(I have just somewhat minimized this by dropping two curly brackets what is possible if d>=0 is also replaced by d>0.)

Just consider:

*r=x**r+a[d]

this is very straight Horner's first algorithm. Obviously, you might have ignore this facts accidently.

btw, this very useful scheme should be named after Ruffini because he invited it several years earlier than Horner, therefore all Spanish people call it "Regla de Ruffini".

I am not native English (certainly you have already noticed this), so I would really appreciate you if you could explain the meaning of:

"scrub posters"

to me.

Thank you very much!

-- tesu

Hi,

though your polynomial function calls itself recursively (if we believe that "polinomyal" equals to "polynomial"), yet the value of the polynomial isn't computed recursively instead it is done in for loop if n becomes 0.

You may study this code:

void pore(int d, float a[], float x, float *r){
 if (d>=0){pore(d-1, a, x, r); *r = x**r+a[d];}}

If pore() is called from this context:

// recursively calculated polynomial
//  p(x) = 1*x^3 + 2*x^2 + 3*x^1 + 4*x^0  
int d=3; float a[]={1,2,3,4}, x=2.5, r=0; 
pore(d, a, x, &r);
cout << "polynomial: " << r << endl;

The result is polynomial: 39.625

-- tesu

I have a table in my database named products with the fields id and model in it. Now, I need to get 'all' the products but with different model names and each result represent a valid product.
I have tried with:
select id, modelname, ... from products group by modelname

you can't have a list of columns so as to select and only a subset of those columns listed in the group by clause. In such case all columns to select must also be itemized in group by clause, for example:

select a, b from t group by a, b;  -- correct
select a, b, c from t group by a, b;  -- wrong
select a, b from t group by b;  -- also wrong
select a, b, sum(c) from t group by a, b;  -- but this correct

-- tesu

...MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, DatePaid...

As griswolf already stated mostly it isn't a good idea to disassemble a date in year, month, day and storing these parts in a table.

You should only store DatePaid in the view. YEAR(DatePaid) gives then INT (1..12),
MONTH(DatePaid) gives then INT (28,29,30,31).

Atention: MONTHNAME(DatePaid) returns a STRING ! ('January', 'February' ...) which is not useful in ORDER BY for its alphabetical order! ----> MONTH(DatePaid) is almost better.

DatePaid is a date column type in all the related tables in the query.
so when i do AS YearPaid = NOW ( ) it seems to work.
what you think?

Sorry, but YearPaid IS NOT DatePaid !

-- tesu

I then created this query to the view to allow me to create the graph i was having trouble with implimenting the months in the right order:

SELECT MonthPaid AS XLABEL, SUM(Amount) AS YVALUE
FROM monthlycommission
WHERE YearPaid= NOW( )
GROUP BY MonthPaid
ORDER BY DatePaid

this seems to be working but maybe it is not right?

Can hardly be correct. Carefully see my last post just above and compare your code with mine!

Very important to me: please, tell me whether this quoted code, your code, would really run on MySQL without any error messages!

another thing Tesu, what is difference with me using a veiw and querying rather than using a long complex SELECT query instead?

The only difference is that a view can be used everywhere without copying its code, also the selects working on views look somewhat more handier.

A further difference exists if we consider materialized views managing a huge amount of data.

Drawback of views: There are very restrictive rules for updating columns of a view.

I am glad if you progress ...

-- tesu

i created a view like your examples, and where i am struggling is querying it to use with a flash chart:

SELECT MonthPaid AS XLABEL, SUM(Amount) AS YVALUEFROM monthlycommissionWHERE YearPaid= NOW( )GROUP BY MonthPaid

i want to order by month in the proper order but cannot find a solution.

Scanning your posts I found:

YearPaid= NOW( ) --> always false for YearPaid is int (2010, 2009 etc) and NOW() is date (2010-06-20). So above query always gives empty result set. Btw, your database should report an error because of incompatible data types.

order by month:
supposing Month and Year can be gotten from DatePaid simply add order by clause:

SELECT YEAR(DatePaid) as cyear, MONTH(DatePaid) AS XLABEL, SUM(Amount) AS YVALUE  FROM monthlycommission
 WHERE cyear = YEAR(NOW()) GROUP BY cyear, XLABEL ORDER BY XLABEL

You can't omit cyear from GROUP BY clause. If you want to drop cyear from output list, you could do a second query on above query:

Select XLABEL, YVALUE FROM
 (SELECT DatePaid, MONTH(DatePaid) AS XLABEL, SUM(Amount) AS YVALUE  FROM monthlycommission
    WHERE YearPaid= YEAR(NOW()) GROUP BY XLABEL) AS must_have_a_name
      ORDER BY XLABEL;

Don't get distract by this interjection...

-- tesu

Hi andydeans,

there seems to be a lot of problems, kind of going round in circles. I personally would suggest that we should go these problems step by step. In each step we shall handle a small and manageable problem. To each step I'll make a suggestion of sql code. Then it will be your task to get that code running on your database. If errors occur, you must post firstly the ORIGINAL sql statement which caused the error and secondly the complete error message. If and only if a current problem is solved we proceed to next problem.

Possibly we shall go back to the UNION problem, where we should start from with a very simple example. I suggest that we start from the below reduced statements, which are the original sqls posted by you some days ago:

SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.amount) as sub_amt, DatePaid    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN buytolet p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL     

UNION  SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.amount) as sub_amt, DatePaid    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN mortgage p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL

If this problem is already solved or become obsolete, you could suggest another small problem we can start from. In this case you should post the complete sql code.

Ok, …

Refactoring menu is visible for C# and VB only. Besides primitive search-and-replace, which is incomparable to refactoring, unfortunately, MS do not offer true refactoring for C++.

There exist several refactoring tools for C++ from other vendors which can be plug-in in Visual Studio 2008, some of them are freeware (I just found this: refactor! for C++ from devexpress.com).

In my opinion the lack of true refactoring is a big disgrace and disqualifies VS 2008 from being a convenient professional tool for C++ developers.

-- tesu

Sorry, my answer should be for your older post MySQL errorno:150.

Which column 'count' you actually mean? Where is it hidden in?

Possibly you should consider what i already stated for errorno 150:

btw, it is always a good idea to declare columuns having auto_increment option unsigned int, e.g. _id unsigned not null auto_increment. On older Mysql versions using int together with auto_increment also mixing int and unsigned int when defining primary and foreign keys sometimes produced similar odd errors.

-- tesu

Hi,

there can be a discrepancy between the innodb data dictionary entry for table assets and additional information still holding separately in assets.frm file (really a mess, mysql should eventually learn not to store information about a table schema in various places! Such data must be hold in data dictionary (system catalog, system tables) soley).

You may look at the server's error log. If there is an error message saying that table assets already exists, try to DROP TABLE assets. If all else fails, instead of trying to fix this odd problem it is often easier to give the concerning table just another name.

btw, it is always a good idea to declare columuns having auto_increment option unsigned int, e.g. _id unsigned not null auto_increment. On older Mysql versions using int together with auto_increment also mixing int and unsigned int when defining primary and foreign keys sometimes produced similar odd errors.

--tesu

...Note the error at: alterInstructor.

I create the instructor table then the booking table. The instructor table has book_num FK.
After the booking table is created I run the script alterTable to alter the instructor table.

Note: the alterPayee script runs fine but the book_num is a different data type. Is this a problem?
It creates the database but it says there is an error when it tries to alter the instructor table....

Well, you are showing that much sql code except the most important script which do the ALTER TABLE ....

You should consider:

1. All your table should have primary keys, at least the table where a foreign key is referencing to must have one. That is book_num must be primary key of booking table.

2. The order of ALTER statements is important: The object a reference points to must already exist. So the order should be: Alter table booking ADD primary key... ; Alter table instructor ADD foreign key ... ;

3. Whether different datatypes are significant or not depends on whether the database is able to do the converting automatically. If not possible, you need an cast() explicitly. If the datatypes are too different, also cast() won't work. You should read and understand derby manual about the compatibility and (automatical) typecasting of DTs.

4. If you have inserted some data before defining foreign keys, sometimes this is impossible depending on already existing constraints (e.g. NOT NULL etc). Also …

seriously, this *.cpp file includes only the following line...

int main{ return 0; }

N.B. I am in Codeblocks and i have changed the settings of linker as tesuji suggested.

>>> int main{ return 0; } ???

So what about this one:

int main(){return 0;}

tesu

hi

if you want to select rows where duedate is from current year, you should extend where clause: WHERE ... AND YEAR(DatePaid) = YEAR(CURDATE()).

I am about to examine your select statements more exact this evening (just during watching world football championship). I have already got the funny feeling that something is going wrong concerning the logical insight of what aggregate functions are and how to use them practically. I'll answer if here is somewhat more clarity.

-- tesu

Here is an example on how to group and order by year and month.

select year(orderDate) as Year, month(orderDate) as Month, 
 sum(quantity) as Quantity, sum(quantity*ItemPrize) as "Toal per Month" 
   from orderline join customerorder group by Year, Month order by Year, Month;

-- Result
/*
Year  Month  Quantity  Toal per Month
-------------------------------------
2009  10     36         7946.94
2009  11      3         2248.00
2009  12     32        17231.83
2010   1      3          192.00
2010   2      9         1365.95

Please consider that this result has been selected from a Sybase database. So possibly some details of the select need to be adjusted.
*/

If you decide to add a further column, for example say the item price, your above compact monthly settlement would be destroyed because itemprice must appear in group by clause.

I hope this example will be a little help.

Addition: FROM orderline JOIN customerorder is modern form of FROM orderline o, customerorder c where c.orderID = o.orderID. This only works if all primary and foreign keys are set up correctly.

-- tesu

Hi andydeans,

still having problems?

Each select statement is incorrect, for example:

...
UNION SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, [B][U]sum(p.amount)[/U][/B] as sub_amt, DatePaid FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN mortgage p on c.ClientID = p.clients_ClientID WHERE DatePaid IS NOT NULL
...

Because of the aggregate function sum(...) a GROUP BY clause is absolutely necassary, where each column not appearing in aggregate function must be contained. Therefore you have to add

GROUP BY c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MonthPaid, YearPaid

to the above SELECT and similar GROUP BY clauses to the other selects.

I did already express the necessity of GROUP BY several times if a select statement contains aggregate functions. You should carefully read and understand the example I showed in your other thread as somebody asked why that ORDER BY would have been that necessity.

Important: If a GROUP BY is necessary because of aggregate functions you must carefully decide which other columns should be listed in the select statement. If you choose too much, for example all columns of a table there wouldn't be any useful data to group by and therefore to sum up. In other words, the sum then only contains the single value of each row.

For example, you decide to sum up all sales amounts obtained per month: select product, month(date) as Month, sum (salesamount) from ... group by product, month order by month; …

Hi,
I had the same problem when I installed new codeblocks version 10.05 recently. I solved it by entering the complete path to kernel32.lib that way:

1. Identify kernel32.lib
on my computer, also running visual studio2008, I found complete path:
C:\Program files\Microsoft SDKs\Windows\v6.0A\Lib\kernel32.lib

2. on codeblocks v10 choose
Settings->Compiler and debugger-> linker setup
There ADD C:\Program files\Microsoft SDKs\Windows\v6.0A\Lib\kernel32.lib. I also added there odbc.lib to using odbc within c++

Both now work fine. There are no problems with new Visual Studio 2008. The same programs where I had had some trouble on codeblocks with kernel32.lib and odbc run perfectly there.

I hope this will also work on your codeblocks.

-- tesu

Hi

>>> //This continues all the way to a[10], sum == 12

1. why not replacing them all by a single: a[sum]++; ?
2. did you really start with initializing a: for(i=0;i<12;i++)a=0; ?
3. using old compiler where int is 16 bit ?

-- tesu

Sorry, this has been my mistake, I thought we were talking about mysql.
MS sql server has much more comprehensive system tables. You can get a good impression by downloading one of those great posters (such an originally colored poster of sql server 2008 is pinned on a wall in my office)

For example table sys.foreign_key_columns contains all foreign keys of a database..

-- tesu

I see, all information on them can be found in MySQL's Information-schema tables (system tables, cataloge), for example, information on primary keys, foreign keys etc. is in table_constraints table.

select * from table_constraints;

Enough privileges?

-- tesu