Posts by tesuji

Hehe
what school you attend selecting from age range 30 to 35? After all you are still programming for hidden dating site, isn't it?

Why not writing a simple query as for example:

select all_my_dating from mytables where YEAR(CURDATE()-DOB) between 30 and 35;

Hint: Never store age of a person in database, except you are planing kind of provision of work ;)

-- tesu

Hi muppet,

sorry for the problems arose on your side through my simple solution. Indeed, I didn't consider that the same reason could appear on both sides. So, if you apply my old union select

select 'right' as Side, reason_right as "Reason", sum( scrap_right) as "Quantity" from bakma
  where "Reason" IS NOT NULL group by "Reason" 
union
select 'left' as Side, reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma
  where "Reason" IS NOT NULL group by "Reason"
order by "Quantity" desc

on the new sample data which seems to be the old one extended by two further rows so as to reveal that weak select, the result is:

/*
Side   Reason    Quantity
-------------------------
right  torn      6
left   wide ties 6
left   dirt      2
right  wide ties 1
right  line up   1
*/

To show which side the rows are from, I added text 'left'/'right'. So it's clear reason 'wide ties' must appear twice. This will also occasionally happen for the other reasons.

Nevertheless, further aggregation is simple if above select is put in SQL-WITH statement, also supported by mysql, to further sum up identical rows:

WITH totaltotal (Side, Reason, Quantity) as
(
  select 'right' as Side, reason_right as "Reason", sum( scrap_right) as "Quantity" from bakma
    where "Reason" IS NOT NULL group by "Reason" 
  union
  select 'left' as Side, reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma
    where "Reason" IS NOT NULL group by "Reason"
  order by "Quantity" desc
)
select Reason, sum(Quantity) as "Total Quantity" …

I am back again. You are completely right, I have mistaken your problem. However, the most important thing is that:

Given your table:

DB_Salgspris db_interval_start db_interval_end db_customer
59          |         2       |   3           | 59
99          |         7       |   8           | 149
149         |         11      |   12          | 199
699         |         65      |   68          | 1099
1099        |         102     |  110          | 1699
 
If I do this:

SELECT DB_SALGSPRIS from pris_interval WHERE DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END >= '11.78'

I get:

DB_SALGSPRIS
------------
149

If I do this:

SELECT DB_CUSTOMER  from pris_interval WHERE DB_INTERVAL_START < '7.19'AND DB_INTERVAL_END >= '7.19'

I get:

DB_CUSTOMER
-----------
149

These results are correct. There aren't any further rows but the listed two. So something must be wrong with your database. I did both selects on Sybase database (MS SQL Server's mother).

-- tesu

Tesu you I have table that already has the totals. I need to output several of them like a tree relation

Can you give examples?

Well, any idea how to get this?

Hint: You may start from my grouping-by select and combine some selects by UNION.

The first part could look like

select cast(idproject as varchar(10)) || '. Project total: ' as "Projects" , 
sum(stval) as "Totals" from secondtable group by idproject order by "Projects"

what is rather identical with my earlier select. I put the project number 1,2,3 in front of each line to allow easier sorting after union with grand total)

-- tesu

Instead of:

... DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END >= '11.78'

should it not be:

... DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END <= '12.78'
? Sorry, wrong too

or better:

...  '10.78' < DB_INTERVAL_START AND DB_INTERVAL_END <= '12.78'  -- to get 5th line
this works

-- tesu

These are meaningful examples, thank you.

Now you can sum-up the values of second table grouped by id proiect, for example:

select idproiect, sum(stvalues) from secondtable group by idproiect order by idproiect ;

/*
Should give:

id_proiect   sum(stvalues)
--------------------------
1              15
2              10
3               5
*/

So what else?

-- tesu

Good, now I got it. (If you had the keys arranged as in first example, it wouldn't have been that easy). Sorry, I had mistaken you at first.

Now, pls give clear examples for your both tables, master table and detail table where a one-to-many relationship exists.

You can use CODE-tags for better reading, example using code tags:

d_f     sum of valu id_s id_f value
-----------------------------------
1       15           1    1    10
2       10           2    1    5
3        2           5    ?    ?
4        2           5    ?    ?

Click on (CODE) and put your table between both tags.

Please give meaningful examples for both tables where one can understand the relationship, primary keys and FOREIGN KEYS. Thanks!

-- tesu

Sorry, to ask frankly: All your keys are in ONE row, is that so?

If your given example means that table 1 has 3 rows where 1st row ID is 1, 2nd row id is 2 etc and table 2 has 8 rows (!) then you can apply simple inner join to get the result:

select some_rows_from_table1, some_from_table_2 from table1 a join table2 b on a.idcolumn = b.idcolumn where a.idcolumn = 3;

(I have got that funny feeling that the keys of your given example are collected on one row only, isn't it?)

Btw, id of id_column suggests that id_column is kind of key, primary key? If so, id is somewhat misleading for the duplicates 1 1 1, 2 2, 3 3 3. Ok, this is possible if id_column is foreign key.

-- tesu

Glad to meet you again.

Are you still suffering from the old database problem we were talken about in this thread? What about the ERM fragment I sent you in the very last post of that thread, wasn't there any chance to improve data model?

As for your first select, yes, this cannot function for the GROUP BY is plain wrong, (it might work like game of dice or even French roulette ;)). You may read here and some more details are there on wrong usage of GROUP BY clause.

-- tesu

Hello and good morning

I was playing about with your sample data and I found a simple solution based on self-join. I'll explain it in two steps:

/* 1. Step: All different mess_id and their corresponding maximum date_sent-timestamps are selected:*/

select mess_id, max(date_sent) as maxdate from yourtable group by mess_id;

/*
The result should be:

---------------------------------------
|   mess_id    |       maxdate        |
---------------------------------------
|  12          |  2010-08-04 21:00:22 |
|  448         |  2010-07-13 17:48:24 |
---------------------------------------

To get the missing columns id and message this intermediate result must be self-joined with original table. So the 1st-step select is put in from clause of a second select and joined with original table. Thanks God, such kind of subselect in from-clause has finally been supported by mysql since near version 4.

2. Step: Self-join
*/
select y.id as "ID", y.mess_id as "Message id", y.message as "Message", y.date_sent as "Date sent"
from (select mess_id, max(date_sent) as maxdate from yourtable group by mess_id) as x 
  join yourtable y on x.mess_id = y.mess_id and x.maxdate = y.date_sent order by y.id;

/*
Now the result should be:

---------------------------------------------------------
 ID |  Message id  |   Message    |      Date sent      |
---------------------------------------------------------
 0  |  12          |  mess 12_0   | 2010-08-04 21:00:22 |
 4  |  448         |  mess 448_2  | 2010-07-13 17:48:24 |
---------------------------------------------------------
*/

Simple solution, isn't it :-O

You may test the select statement of 2nd step. There might be typos yet it should fit mysql syntactically largely. I didn't test it so far.

-- tesu

you may try this:

select mess_id, max(date_sent) as max_date_sent from yourtable group by mess_id order by mess_id, max_date_sent;

-- tesu

From your example

---------------------------------------------------------
 id |   mess_id    |   message    |      date_sent      |
---------------------------------------------------------
 0  |  12          |  mess 12_0   | 2010-08-04 21:00:22 |
 1  |  12          |  mess 12_1   | 2010-08-03 17:30:55 |
 2  |  448         |  mess 448_0  | 2010-07-11 21:12:20 |
 3  |  448         |  mess 448_1  | 2010-07-12 17:30:48 |
 4  |  448         |  mess 448_2  | 2010-07-13 17:48:24 |
---------------------------------------------------------

you would like to select both green-colored rows you said so. Can you tell me what are things in common of these rows?

-- tesu

>>> ..retrieves All the rows correctly ..

that means that mess_id or id are unique keys. In such case mysql cannot do any mischief for all rows will be selected. (no grouping).

-- tesu

Such wrong result is quite usual, it's a painful mysql gotcha!

In your query

SELECT DISTINCT mess_id, id, message, MAX(date_sent) as date_sent FROM mail GROUP BY message_id

ALL columns not being parameters of aggregate functions (max(), sum(), count() etc.) must always be enumerated in GROUP BY clause. There is no exception from this stringent rule. All but one database systems refuse execution of queries contravening this rule.

So your correct query is:

SELECT DISTINCT mess_id, id, message, MAX(date_sent) as date_sent FROM mail GROUP BY message_id, id, message

Note: If message contains widely unique values, there would be nothing to group by. Possibly you need to drop message from query (I guess, you don't like that). GROUP BY clause acts this way: It sorts out all row where (message_id, id, message) have identically same values. From this subset max() will be taken. The result will be listed. If any of these three columns changes its value, sort out starts etc. In extreme case if for example mess_id is primary key of your table, no grouping will happen and all rows be listed.

You may read what I wrote there, especially about chapter 11.16.3 of MySQL reference manual.

-- tesu

Hi

who is OWNER of table user in: "select * from User" ?

If dbo, then "select * from dbo.User"

Aha, and not forgetting: "User" is a reserved keyword in SQL server which is not allowed to get redefined by you.

Btw, there isn't any system table "User" which contains the names and PASSWORDS of SQL Server users ;) (that would be really too simplistic!)

-- tesu

Hi

don't worry, rows in database tables never have a particular order. This is a general rule for relational databases are based on set theory (at least as regard that). So if you want to get a certain order, you must make explicitly use of order function by ORDER BY clause, for example:

SELECT * FROM dbo.names ORDER BY id_record ;
/* result (assumed that there exist a column id_record) 
1. dog
2. cat
4. llama
5. snake **now this is wrong, right?**
*/

.
If you don't have column id_record to order your rows, you can also use the lastname und name columns to get alphabetical order, for example:

SELECT * FROM dbo.names ORDER BY lastname asc, name desc;

Where ASC stands for ascending order and DESC for descending order.

Or you add a further column id_record to your table and you nominate id_record to be primary key of this table:

create table names(id_record integer not null, prename varchar(50), surname varchar(50),
primary key (id_record));

-- tesu

Hello

I am not sure whether even and odd series of row numbers could help:

select -1+2*row_number() over (order by ID1 ASC) as rownumber, 'First Set' as whichSet, colA as "Data" from rowTest
union
select 2*row_number() over (order by ID2 DESC) as rownumber, 'Second Set' as whichSet, colB as "Data" from rowTest
order by rownumber;
/* result:
rownumber  whichSet    Data
---------------------------
1          First Set   51
2          Second Set  27
3          First Set   10
4          Second Set  25
5          First Set   20
6          Second Set  26
*/

In first select -1+2*row_number() calculates the odd series: 1, 3, 5 etc. 2*row_number() in 2nd select makes series 2, 4, 6 etc. Where the order by rownumber put them into series 1, 2, 3 etc.

Is that what you mean by consecutive record number?

(Note: If I change -1+2*row_number() to 2*row_number()-1 Sybase DB says there is illegal char '-' after (), I don't why. But -1+2*row_number() works fine.)

Addition: Instead of calculating even and odd series also this is possible by adding 1+count(*) of first set to row_number() of second set:

/* result:
rownumber  whichSet    Data
---------------------------
1          First Set   51
2          First Set   10
3          First Set   20
4          Second Set  27
5          Second Set  25
6          Second Set  26*/

-- tesu

That is a more human data structure now. "And i would like to get:" implies you haven't got so far? I am afraid that such a constellation should also be possible:

| 0 | 22 | 2010-08-03 23:39:55 |
| 1 | 00 | 2010-08-04 00:41:00 |

-- tesu

Hi firstPerson,

I am not sure of that you just stated. Below is a small program where I tested a C function which searchs for longest common substring, a question arised by peter budo about a week ago.
Actually I also had some problems with clock() taken from time.h. I did a somewhat practical calibration for the execution time shown after the end of the program was always around 2 seconds in total. This is very close to the computed value of 1.75 seconds what resulted from clock()-values (ends minus starts) divided by CLOCKS_PER_SEC, see below.

#include <iostream>
#include <string>
#include <sstream>
#include <time.h>
using namespace std;
#include "lcstring_V4.h"
int main(){ 
  // testing LCString() --> Peter Budo
  clock_t starts, ends; double dura; int i,n;
  char s1[] = "/system/images/777/medium/Debrecen_-_University.jpg?1279547675";
  char s2[] = "Debrecen_-_Protestant_Great_Church.jpg";
  char s3[strlen(s1)];
  cout << "s1: " << s1 << endl;
  cout << "s2: " << s2 << endl;
  starts = clock();
  n = 100000;
  for (int i = 0; i<n; i++) LCString (s1, s2, s3);
  ends = clock();
  dura = double((ends-starts))/ CLOCKS_PER_SEC;
  cout << "LCString (v4) callings: " << n << endl;
  cout << "Effective loopings:     " << lcstring_counter << endl;
  cout << "Worst case loopings:    " << n*strlen(s1)*strlen(s2) << endl;
  cout << "Begin (clocks):         " << starts << endl;
  cout << "End (clocks):           " << ends << endl;
  cout << "Duration (clocks):      " << ends-starts << endl;  
  cout << "Duration (sec):         " << dura << endl; 
  cout << "LCS:                    " << s3 << endl; …

I am trying to understand :)

What you can also do is explicit type casting, for example

select '12345.678' as stringnumber, cast(stringnumber as decimal(15,2)) as "now numeric value"

results in 12345.68

cast() function also works for insert and update.

btw, text datatype has some limitations as compared with varchar. There were some postings regarding varchar vs. text recently on daniweb.

Why you need datatpye CLOB for bulbDesc? CLOB is huge string type (4GBYTE ore so) but very restrictive usage. So most standard functions cannot handle clob data.

-- tesu

Hi

simply divide clock-result by clocks per second, and don't forget the cast:

double_difference=double((end-start)) / CLOCKS_PER_SEC;

to get the correct seconds.

-- tesu

Hi

I am sure, you know that the precision and scale should be explicitely specified. If not, precision and scale of numeric and decimal DT are 10 and 0, that is default is decimal(10,0) or numeric(10,0). So numbers having decimal places will be automatically rounded. If you have money values, decimal(15,2), scale 2, for example is good idea.

Just out of curiousity, which mysql version are you running, below 5.0.3 ?

-- tesu

It doesn´t matter. Variable start- and end-months are covered by the select I posted. You only need to consider that in the where clause by an OR condition, for example:

... ((from_month=@fmonth) OR (to_month=@tmonth)) order by to_month, leaveno ;

Where @fmonth and @tmonth are the month-values extracted from the dates the user typed in and syntactically depending on the environment this select will be carried out. You can also compose such a select by simple string functions.

Btw, If you add further AND-terms to above where clause, parenthesises as shown are mandatory because of operator precedence.

-- tesu

Nice ERM :)

Invoice against purchase of your customer is related to order. Your relationship –generates< is one-to-many which means that a certain order may have various invoices what is a good idea if your company is dealing with partial shipment. Here invoices usually have their own primary keys which are often the debtor number. OrderID is non-identifying foreign key in invoices. If there is only one invoice per order –generates< is a one-to-one relationship. Then primary key of invoice could be orderID (or is it sales_id ?). Btw, there might also Invoices against purchases of your company itself (from your creditors).

As for Purchase Manager <Purchase Order-< supplier, shall a certain supplier receive only one order by your company? If so, putting purID into supplier is ok. But if your company wants to buy repeatedly from that supplier and former purchases shouldn’t be deleted, your cardinality is wrong. Then you must put supID into Purchase Order. On the other hand a certain purchase order may consist of various products, then you could treat it analogous to customer <order <order line> product, you see?

As for the keys in relational model: You should always define all primary keys and all foreign keys correctly. On mysql you have to choose innodb which fairly supports referential integrity. If there is a chance for using better relational database, e.g. MS SQL server (express), Oracle (express), SQL Anywhere, PostgreSQL, Derby, JavaDB etc., take your chance.

-- tesu

Hi

if you are only interested in start and end dates of leaves of a given month, you may check that month whether it is contained in those dates, as for example:

select To_Char(fromdt,'MM') as from_month, To_Char(todt,'MM') as to_month, leaveno 
from onleave ... etc ...

If you like to get shorts for months, as for 'Jan', 'Feb' etc, you may replace MM by MON.

There are comprehensive manuals for Oracle DB, so you might examine date and time functions there.

-- tesu

Well, MySQL does not have any olap functionality standardized by ANSI since SQL1999. Even the primitive aggregate function row_number() is missing. This is just another gotcha of that eminently imperfect so-called database. Since the early 2000th there have been thousands postings on forums on how to simulate that aggregate function.

Ok, this is what I would run on almost all other SQL databases:

select row_number() over (order by swin desc, sloss asc) as rank, team, sum(win) as swin, sum(loss) as sloss, cast(cast(swin as double)/(swin+sloss) as decimal(5,1)) as PCT from overall GROUP BY team;
/*
What should produce suchlike table (I hope so) 
rank team swin sloss pct
-------------------------
1    A     4     1   0.8
2    D     1     1   0.5
3    B     1     2   0.3
4    C     0     2   0.0*/

Double casting is necessary because of integer division. If you don´t need the rank column, you may try this on msql:

select team, sum(win) as swin, sum(loss) as sloss, cast(cast(swin as double)/(swin+sloss) as decimal(5,1)) as PCT  from overall GROUP BY team order by swin desc, sloss asc;

You may also search the forums on how to simulate row_number(), here is such a try I have found and modified to fit the above listing:

SET @rank=0; SELECT @rank:=@rank+1 AS rank, team, sum(win) as swin, sum(loss) as sloss, cast(cast(swin as double)/(swin+sloss) as decimal(5,1)) as PCT  from overall GROUP BY team order by swin desc, sloss asc; -- I don't know whether it will work

Attention: above sql statements not tested

Hi

I think it's a good idea to state a convenient example in the form of a data table and you may define "ranking" more precisely. (There are nice ranking functions in OLAP which has been offered by almost all databases since the ANSI SQL1999 standard.)

-- tesu

>>> and for the newInstance(); , i tried adding but there is an error..

How did you figured out that an error occurred? Are there really error messages? If so, what do you think of posting them?

I went through your code again, what I am missing are:

1. There is no init() method, servlets executes init() first. If you add init(), don't forget to call there super.init(..) first !

2. Your doSend(..) is nowhere called. Maybe you put it in the doPost() method which I also missing in your code. It depends on where your servlet executes (server or web site) whether you need doGet() or doPost() method.

Assuming you need doPost() for your servlet is running on website, I would simply rename doSend(its parameter list is ok) into doPost(...) (instead of creating a new doPost() where doSend() must be called from.)

When you write out your html form, there must also be a call for your servlet what is done by form action method "POST".

Well, you stated that you were already able to retrieve data from your table ("I am only able to retreive data from the table") so I think that you must have already known above stuff I am talking of.

Any way, try something out of that stuff, especially introduce both doPost() in your java code and form action method "post" on your html form.

-- tesu

Well, that depends on where doSend(..) is ececuted from.

I also hope that this

"jdbc:mysql://localhost:3306/student_info?"
+ "user=root&password=password";

is syntactically correct and right user and password be set. Eventually there must be a
space here: + " user=...

(I stopped doing such stuff on MySQL a couple of years ago)

I have checked connection string. Yours is ok, no space requiered. Also your imports (import java.sql.*) are ok.

Question: did you also register your driver, e.g. that way:
Class.forName("com.mysql.jdbc.Driver").newInstance();
(on line 78 of your code you might add .newInstance())

-- tesu

Hi

Glad to meet a netbeans fan too.

These problems leap to my eye:

PreparedStatement ps = conn.prepareStatement("INSERT INTO info VALUES (?,?,?,?,?)");
ps.setString(2, userInputname);
ps.setString(3, userInputNIRC);
ps.setString(4, userInputcontact);
ps.setString(5, userInputemail);
ps.setString(6, userInputl1r4);
ps.execute();

-- The first ? is numbered 1, so the setters should be called with:

ps.setString([B]1[/B], userInputname);
ps.setString([B]2[/B], userInputNIRC);
ps.setString([B]3[/B], userInputcontact);
ps.setString([B]4[/B], userInputemail);
ps.setString([B]5[/B], userInputl1r4);

Also I am missing commit statement. You can attach it to your insert statement :

conn.prepareStatement("INSERT INTO info VALUES (?,?,?,?,?)[B] ; commit ;[/B] ");

Attention: where to place the commit depends on your overall transaction. So it might be not a good idea to make a commit after every insert-statement. However, one should never forget the commit (or rollback) after insert, update, delete.

It is always saver to list the columns (example: a, b...) you want to insert values:

conn.prepareStatement("INSERT INTO info [B](a,b,c,d,e)[/B] VALUES (?,?,?,?,?) ; commit ; ");

>>> Here are my current codes, I am only able to retreive data from the table but unable to send over.

Btw, is there any error message / exception ?
Because of wrong "ps.setString(6, userInputl1r4);" there should arise sql exception.

-- tesu

Hi muppet

Glad to meeting you again. How is Hayley Westenra doing? I didn't heard of any new song from her for some time past. I myself prefer her older po kare kare ana, also her interpretation of Kate Bush's Wuthering Heights is masterly performance.

Of course, there is a solution for accumulating your down and up times. However, one cannot do this within single sql query statement. You need to write some procedural code using PSM, C, Java etc. I am just sitting in front of some SQL server and Sybase databases, there I have some special own-written user defined function (UDF, written in ANSI standard programming language PSM, yes that's truly ANSI standard for SQL programming). So it was easy to accumulating anything what looks like a date/time or so.

Replacing "22" by any arbitrary string can simply be done in select statement by using ANSI standard CASE function. This is the result:

select downtime() as "Down/Up Time (sec.)", 
   case 
      when werror = 22 then 'Spout Jam' || ' (22)'
      when werror = 99 then 'This Error ' || ' (99)'
      else cast(werror as varchar)
   end as "Error", wtimestamp as "Timestamp", timeStampToModJD(wtimestamp) as "mod. Julian Day"
from Waiapu where "Error" is not null;

/* Result
Down/Up Time (sec.)  Error           Timestamp                mod. Julian Day
------------------------------------------------------------------------------
138.000:183.000      Spout Jam (22)  2010-07-27 08:29:15.000  245540430555.000
*/

I put down and up time together showing you both. Btw, nice sequence of digits 138 and 183 calculated by your above …

Well, you should carefully read and understand my reply.

Again, your business rule #1 is in direct contradiction to rule #2. Furthermore, rule#2 contradicts your ER-Diagram:

rule #1. A book can contain many pages, and each page can belong to different books.

rule #2. Each Page contains different information about patients and a patient only can be listed in one page on a certain book.

Your ER-Diagram tells that between entity book and entity page be a many-to-many relationship what is made by relationship BOOKPAGE commented 'Junction Table' by yourself.

We can't proceed until these contradictions are solved, especially your ERD should be correct concerning the many-to-many relationship represented by BOOKPAGE.

I didn't mention any "ternary" relationship for there isn't any. There is only one binary relationship so far.

Maybe you think of traditional books: A book consists of many pages. A page can only belong to a particular book. If so, there is a one-to-many relationship between book and page, and you need to change rule#1.

>>> What do you mean on 1. (pk of page become non identifying fk of patient)?
Primary key (pk) of entity page goes into entity patient. So it becomes foreign key (fk) there. This foreign key is not member of patient's pk, therefore "non identifying fk". This one-to-many relationship, designed by yourself, is entirely correct.

Btw, what about that error message: "The current row is not available." ?

…

Hi

A look-up table for ranks could look like:

/*
rankNo salesfrom salesto credit
-----------------------------------
0          0        99   no credit 
1        100       499   Consultant
2        500       999   Supervisor
3       1000      4999   Manager
4       5000    999999   Executive  
   
Primary key: rankNo. (Also salesfrom is possible yet not recommendable.)

Usage, for example in subtype-table manager:

create table manager(distributorNo integer not null,rankNo integer not null,
... further_columns ...,
primary key (distributorNo),
foreign key (distributorNo) references distributor(distributorNo),
foreign key (rankNo) references rank(rankNo)); */

Btw, you should allow to copy from your pdf-file!

-- tesu

He he,

You are ERM master! Your ERM is perfect. (I would only complete the other entities and the relationship by their primary keys as you already did for salesorder, also showing foreign keys would look nice (but this depends on your teacher: some don't like to see keys on erm level))

Also supertyp and subtypes are complete, and well set optional/mandatory cardinalities too. On ERM design level it is a good idea to distinguish between those subtypes. Later when you map this design into relational model (RM) you will have to decide what should happen with all those subtypes.

For there is always a one-to-one relationship between super-/subtypes one can mix them together in RM, in principle. If subtype have various attributes and if they are managed relatively independently from each other, they should have their own tables in RM.

As for 3rd point: Rank is an attribute of subtypes. However, if rank is the only attribute of subtypes, you can simply add rank to supertype/table distributor in relational model (I wouldn't do this in ERM). Later, the values of rank can also be in an extra table in relational model, then it would be kind of look-up table.

-- tesu

Hi

This problem cannot be solved with one single sql select statement. You need some procedural code, e.g. programmed in PSM, C, Java or PHP. I would design a user defined function or stored procedure in PSM to carry out this task. The procedure may function that way:

Given a guest's request for booking consecutive dates starting with startdate up to enddate, the procedure tentatively allocates rooms. If booking period cannot be covered by only one room, UDF tentatively books alternative rooms. The result will be shown to guest/user. If he decide to book the suggested rooms changes to booking table will be committed, if not, then rollbacked.

Inside the procedure this query may be used to get an overview on the booking situation:

select caldate, roomID, bookingid from booking 
 where rstatus = 1 and caldate between '2010-08-01' and '2010-08-05'
  group by caldate, roomID, bookingid order by caldate, roomID;

-- where '2010-08-01' and '2010-08-05' are examples for startdate and enddate which
-- should be replaced by variables depending on your programming environment.

Attention: This code not tested

-- tesu

Hi

Error message is correct for book of [BOOK].[BookNo] isn't listed in from clause. Also join with bookpage which makes the many-to-many relationship is missing.

select b.bookno, a.pageno, PatientNo, FirstName, LastName from patient p 
  join page a on p.pageno = a.pageno
   join bookpage bp on bp.pageno = a.pageno
   join book b on b.bookno = bp.bookno
     where ...

Your erm seems to be nearly correct for

1. A patient can only be on one page (pk of page become non identifying fk of patient) and a page has many patients
2. A book has many pages and a page is in many books (curious on the latter)

3. But "a patient only can be listed in one page on a certain book" is not complied with.

-- tesu

hi

>>> A distribute places an order
is this a customer?

>>> distributor will be ranking based on their sales
are order and sales identical?

>>> An order can consist of many product...
ERM looks like:

distribute? ----< order ----< order_details/item >---- product
(---< is crowfoot)
where order_details is many-to-many relationship which besides the two foreign keys of the related entities may also have additional attributes as for example quantity and item price.

>>> attached docx?
I don't like to open that. You may post pictures/.jpeg or pdf.

-- tesu

Selamat siang,

You are showing two tables jawab and kunci. Your select contains further tables: siswa in from clause and tabel_mk in where clause where the latter is missing in from clause.

There is a further table tabel_jwb mentioned in your last question but one. So which tables are correct? You also need to know all primary and foreign key of your tables in detail. As for example, I myself would say that column kd_jwb, value 321, seems to be kind of primary/foreign key on where joins might function. However, you are using column "nis" in query.

Well, not knowing your tables and keys exactly nobody can help you. Although your select statement is wrong in principle, for that immense lack of information it is impossible to advice you a better one.

Ok, replacing a columns value by another one, e.g. value of Kjwb1 by 2.5 or 0 is usually done by sql CASE function.

-- tesu

Hi, glad to see you again

I thought her old but similar problem would have already been solved by master mike (Indeed, I had mistaken decomposition with elimination, I say sorry for that.)

-- tesu

Hello

Usually such project is based on OOA/OOD, therefore realising such a design with c++ well structured classes incorporated in convenient design pattern (mvc, facade etc) are a must.

How to realize persistently stored data is another question.

Stock management requires complex data model and consists of many entities which are related to each other so inserting, updating, or deleting entity data aren't simple tasks. Using binary files for that require enormous individual programming efforts and the result would be a rather imperfect propritary data management system for stock management.

So to keep your additional c++ costs low and to get instant access to all these great data management functions you would have else implemented in a sweat-inducing way you should make use of a relational database. Using a RDBMS within C++ is standardized and rather simple. There are lots of tutorials on the web, also on daniweb this topic has been covered more often. I myself prefer ODBC, plain C or class-oriented interfaces as well as. As for the database, I am usually fond in MS SQL Server and Sybase SQL Anywhere (if I have the option, I wouldn't harm myself with mys..).

If you decide for relational database, I immediately suggest you to design a complete entity relationship model (ERM) of your stock management first before starting to implement data models on your chosen database. I myself believe the ERM is the most important task to map real-world requirements successfully and effectively into …

Hi

there arise two problems with your both single selects when unionizing them together:

1. only the last select is allowed to have an ORDER BY, therefore:

SELECT a, b, count (c) as "MasterHeadline" ...  group by ....
union
select a, b, count (d) as "not that important name" ... group by .....
ORDER BY  "MasterHeadline" ;  -- no further order bys allowed

2. All selects must be union-compatible, that is a) corresponding columns must have same or similar datatype which can be automatically casted into common DT, and important for your selects: b) the number of columns must be same.

You don't have same numbers of columns, so you must add a placeholder. Simplest placeholder is NULL:

SELECT a, b, count(c), sum(d) from ...
union
select a, b, count(d), NULL from ...

You can replace placeholder by any string, also empty string '', but then sum(d) must be type casted into string too:

SELECT a, b, count(c), CAST(sum(d) AS VARCHAR(20)) as "total" from ...
union
select a, b, count(d), 'Placeholder' as "total" from ...

This shows also a way to distinguish between both resultsets, as for example (using your [New Customer Added]):

SELECT a, b, count(c), CAST(sum(d) AS VARCHAR(20)) as "total", 'from 1st query' as "Where from" from ...
union
select a, b, count(d), 'Placeholder' as "total", 'New Customer Added!' as "Where from" from ...

You know the difference between double marks " " and single marks ' '.

-- tesu

Hi

what did you already do, are there any sql statements approaching the problem by yourself, tentatively in nature?

-- tesu

These union selects

select reason_right as "Reason", sum(scrap_right) as "Quantity" from bakma_table
  where "Reason" IS NOT NULL group by "Reason"
union
select reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma_table
  where "Reason" IS NOT NULL group by "Reason"
order by "Quantity" desc ;

should give such a list

| Reason      | Quantity |
| Torn        |    6     |
| wide ties   |    5     |
| dirt        |    2     |
| line up     |    1     |

Quotation marks (" ") could be omitted, sometimes.

This code not tested, thought.

-- tesu

C99 standard defines some erf() in math.h (not cmath). Unfortunately, MS C++ math.h doesn't have erf().

Maybe my little erf() once I posted here can also help you. Sure, boost is the best.

-- tesu

Your problem description is really hardly understood. Can you state examples with meaningful data for all three levels and also one on the result you are looking for?

I am vaguely supposing, your problem deals with hierarchies and trees in sql databases. If so, you might search for Joe Celko trees, who wrotes a complete book about SQL and trees.

-- tesu

Hi Peter

I couldn´t stop thinking about the longest-common-substring problem for SQL language (user defined function). So I took and old version of LCS which I once implemented in plain C. Its time complexity is O(m*n), storage O(m+n). I translated this C version line by line into a user defined function (PSM of SQL 1999) for Microsoft SQL Server and Sybase database. Finally I also compiled a Java version from my old C LCS function.

All three functions implement the very same LCS algorithm and I myself can also affirm that every function is based on the same programming methods and approaches what guaranties that they have same O(m*n).

Then I did some benchmarks using your sample strings containing Hungarian Debrecen. I executed all three functions/ methods 100000 times on these strings on an "medium upper class" win XP computer.

[I]Here are the results for 100000 function calls:

Programming Language          Execution time
-------------------------------------------- 
plain C (no classes)             2.2 seconds       
Java, static member              2.8 seconds
SQL user defined function     2658.0 seconds [/I]

I am very surprised at the poor SQL-UDF result. I had never believed that SQL-UDF would consume a thousand times execution time of C/Java function. So it is rather out of the question to implement LCS algorithm in SQL user defined function for practical usage.

This benchmark pleasantly surprised one by the computing power of Java. It put up a great fight by contrast with fast C for this non-numerical algorithm.

-- tesu

... could refer to more than one table listed in the FROM clause ...

Aha, I see!

You might be more precise, state a practical example, post in the concrete, possibly wrong sql select statement you are talking about, or even post your create-table DDLs you would like to get some pieces of advice.

btw, if it refers to various tables, you may have to use table joins.

-- tesu

Well, not sure whether you want to fetch all rows where owner_id contains empty string (where an instance physically exists in table) or where owner_id is NULL (that special value what indicates that instance does not physically exist in table) I state example for both, empty string and NULL value:

select dissent_type from thattable where owner_id = '' or owner_id IS NULL;

On the other hand, suffix _id of owner_id connotes someone that owner_id would be kind of primary key. If so, you should improve your table design for those rows you are looking for are orphans, in other words, referential integrity is destroyed.

Remark: "" (two double quotes) should not be used as string delimeters, use '' (two single quotation marks) instead. There are databases which report errors when mistaken these both different delimeters.

-- tesu