Posts by tesuji

malloc ?? this is not c++, maybe plain c
--> new

34 GB ?? 2 bytes just allow 2,1 GB to be addressed by

Hi conwayce

Cannot figure out the logic how result is created from the given tables.
How are these two tables related to each other? Are there any (hidden) relationships?
Best you post complete schema of both tables.

krs,
tesu

NY local time = UTC - 5 or -4 (daylight saving time)

Hi all,

I would never suggest beginners to program considerable GUIs by using MSDN because it may last until the cow comes home as Ancient Dragon already pointed out. Maybe a better and clearer way for creating GUIs is QT 4 (has nice bar graph) or wxWidgets (both free), also suggested by Ancient Dragon and niek_e. There is also Fox toolkit from a Netherlands guy which is completely free. Using one of them you can freely roam between Win and Unix. Then you should also use gcc with codeBlocks or wxdevC++ on Win, or KDE on Unix (as I do).
(Best of all would be Delphi or Kylix;) )
krs,
tesu

Hi chsarp_vijay,

Derby is pure Java. if you want to use it, you must program Java. Why not using SQL Anywhere/Ultralight from worldwide leader in mobile computing, Sybase? There mobile computing database run on almost all mobile systems, even on mobile phones (also Derby does so). You can download developer system from Sybase without charge and limitations (ok, one limit exists: you shall not use developer licence in commercial projects).

krs,
tesu

hi tusharvichare

The new net framework has a nice xml class containing almost all methods you need to work with xml.

krs,
tesu

Hi
you may start SQL Server Management Studio (If you are using SQL server Express you can download SQL Server Management Studio separately) an then create staff table, enter some rows, for example:

-- create the table
create table staff (
staffID integer not null,
fname varchar(50),
lname varchar(50),
job varchar(20),
salary decimal(8,2),
primary key (staffID) );

-- Add some rows to your table
insert into staff (staffID, fname,  lname, job, salary)
  values (1789, 'George', 'Washington', 'CEO', 10000);

insert into staff (staffID, fname,  lname, job, salary)
  values (1801, 'Thomas', 'Jefferson', 'Fighter', 10000);

insert into staff (staffID, fname,  lname, job, salary)
  values (1841, 'John', 'Tyler', 'Whig', 10000);

insert into staff (staffID, fname,  lname, job, salary)
  values (2001, 'George W.', 'Bush', 'Rep', 10000);
 
-- Select all staff members
select * from staff;

-- Who was first staff member?
select top 1 * from staff order by staffID

Ok, these are some SQL statements to generate staff table interactively by using Management Studio. You can also integrate all above SQL statements into your VB program.
You need to read a book or a tutorial how to do that. (I myself only know c++ and java, no vb knowledge here)

krs,
tesu

AFAIK there is no ISO table for nationalities.

Sure there are country codes standardized by ISO 3166, look here:

Some codes from ISO 3166 
#
Updated by the RIPE Network Coordination Centre.
#
Source: ISO 3166 Maintenance Agency 
#
Latest change: Thu Aug  7 17:59:51 MET DST 1997
#
#
Country                                         A 2     A 3     Number
----------------------------------------------------------------------
AFGHANISTAN                                     AF      AFG     004
ALBANIA                                         AL      ALB     008
ALGERIA                                         DZ      DZA     012
AMERICAN SAMOA                                  AS      ASM     016
ANDORRA                                         AD      AND     020
ANGOLA                                          AO      AGO     024
ANGUILLA                                        AI      AIA     660
ANTARCTICA                                      AQ      ATA     010
ANTIGUA AND BARBUDA                             AG      ATG     028
ARGENTINA                                       AR      ARG     032
ARMENIA                                         AM      ARM     051  
ARUBA                                           AW      ABW     533
AUSTRALIA                                       AU      AUS     036
AUSTRIA                                         AT      AUT     040
AZERBAIJAN                                      AZ      AZE     031  
BAHAMAS                                         BS      BHS     044
BAHRAIN                                         BH      BHR     048
BANGLADESH                                      BD      BGD     050
BARBADOS                                        BB      BRB     052
BELARUS                                         BY      BLR     112  
BELGIUM                                         BE      BEL     056
BELIZE                                          BZ      BLZ     084
BENIN                                           BJ      BEN     204
BERMUDA                                         BM      BMU     060
BHUTAN                                          BT      BTN     064
BOLIVIA                                         BO      BOL     068
BOSNIA AND HERZEGOWINA                          BA      BIH     070
BOTSWANA                                        BW      BWA     072
BOUVET ISLAND                                   BV      BVT     074
BRAZIL                                          BR      BRA     076
BRITISH INDIAN OCEAN TERRITORY                  IO      IOT     086
BRUNEI DARUSSALAM                               BN      BRN     096
BULGARIA                                        BG      BGR     100
BURKINA FASO                                    BF      BFA     854
BURUNDI                                         BI      BDI     108
CAMBODIA                                        KH      KHM     116
CAMEROON                                        CM      CMR     120
CANADA                                          CA      CAN     124
CAPE VERDE                                      CV      CPV     132
CAYMAN ISLANDS                                  KY      CYM     136
CENTRAL AFRICAN REPUBLIC                        CF      CAF     140
CHAD                                            TD      TCD     148
CHILE                                           CL      CHL     152
CHINA                                           CN      CHN     156
CHRISTMAS …

Your problem is kind of running totals just like that of #7. The examples you have shown to date contain consolidated data only. So I can't figure out the relations between NumAffected, Passed, Failed etc. Maybe it s a good idea to post some original data and its consolidated data to figure this out.

tesu

Hi niek_e

You are right, I have overlooked the semicolon at he far right.

Intuitively I also would put a semicolon at left side if for-init statement is off. But the ISO standard tells the opposite. You may have a look at this standard on http://www-d0.fnal.gov/~dladams/cxx_standard.pdf
In the lower half page 95 you will find the definition of the for-loop. Where
you can see that the first semicolon is integral part of the for-init statement,
just consider the gap between for-init statement and condition statement, also see
the given note. That means, if one omits the for-init statement the semicolon also
disappears.

Obviously, almost all C++ compilers do not implement the for loop in concordance with the ISO Standard, do they?

krs,
tesu

Hi 2eXtreme,

in another thread http://www.daniweb.com/forums/thread127644.html there was a similar
problem like yours. I gave the advice to solve it with the new with clause. You should carefully compare the table structure and given solution in post #7 with your table structure. I think it is possible to adopt the there given solution to your problem successfully. (#5 is similar too but not that useful as #7, I think so.)

That solution works fine for MySQL, yet I have used only standard SQL features, so running such a with-clause select statement on MS SQLServer would be no problem.

If I had time I would have rewritten the select of #7 to meet your requirement, maybe by end of this week I will get more time.

Suggestion: pls post your complete table structure, e.g. create table statement and give a sample of some table data also of the result you expected (maybe also some INSERT statements to create a testing environment on my database).

krs,
tesu

Hi QuantNeeds

. . .
warning C4552: '>' : operator has no effect; expected operator with side-effect
. . .

int numStudents = 10;  // assignment necessary 
	for(numStudents > 0; numStudents -= 1;)

Your for loop is correct. It corresponds completely with the Standard ISO/IEC 14882:1998(E) of C++. So your compiler seems to be something outdated, especially the phrase "expected operator with side-effect", what means that an for-init statement would have been expected, tells that your compiler isn't that conform to the standard.

brs,
tesu

Hi amarjot

I am on my training and want to assess microsoft xcel database using a c++ program for my project..

Did you mean MS Excel spreadsheet, which is not a database?
If so, you may read this: http://support.microsoft.com/kb/308407/en-us/

krs,
tesu

Hi
maybe you would be able to create such table from below list by writing small Python program?

Country 	Code
----------------------
Afghanistan 	93
Albania 	355
Algeria 	213
American Samoa 	+1-684*
Andorra 	376
Angola 	244
Anguilla 	+1-264*
Antigua 	+1-268*
Argentina 	54
Armenia 	374
Aruba 	297
Ascension 	247
Australia 	61
Australian External Territories 	672
Austria 	43
Azerbaijan 	994
Bahamas 	+1-242*
Bahrain 	973
Bangladesh 	880
Barbados 	+1-246*
Barbuda 	+1-268*
Belarus 	375
Belgium 	32
Belize 	501
Benin 	229
Bermuda 	+1-441*
Bhutan 	975
Bolivia 	591
Bosnia & Herzegovina 	387
Botswana 	267
Brazil 	55
British Virgin Islands 	+1-284*
Brunei Darussalam 	673
Bulgaria 	359
Burkina Faso 	226
Burundi 	257
Cambodia 	855
Cameroon 	237
Canada 	1
Cape Verde Islands 	238
Cayman Islands 	+1-345*
Central African Republic 	236
Chad 	235
Chatham Island (New Zealand) 	64
Chile 	56
China (PRC) 	86
Christmas Island 	61
Cocos-Keeling Islands 	61
Colombia 	57
Comoros 	269
Congo 	242
Congo, Dem. Rep. of (Zaire) 	243
Cook Islands 	682
Costa Rica 	506
Côte d'Ivoire (Ivory Coast) 	225
Croatia 	385
Cuba 	53
Cuba (Guantanamo Bay) 	5399
Curaçao 	599
Cyprus 	357
Czech Republic 	420
Denmark 	45
Diego Garcia 	246
Djibouti 	253
Dominica 	+1-767*
Dominican Republic 	+1-809* and +1-829*
East Timor 	670
Easter Island 	56
Ecuador 	593
Egypt 	20
El Salvador 	503
Ellipso (Mobile Satellite service) 	17625
EMSAT (Mobile Satellite service) 	88213
Equatorial Guinea 	240
Eritrea 	291
Estonia 	372
Ethiopia 	251
Falkland Islands (Malvinas) 	500
Faroe Islands 	298
Fiji Islands 	679
Finland 	358
France 	33
French Antilles 	596
French Guiana 	594
French Polynesia 	689
Gabonese Republic 	241
Gambia …

Hi motofoto

There is kind of inheritance - in EERM - where we have Generalization / Specialization, such called isa-relationships. Maybe you should first design an EERM. Your implementation idea of isa-relationships is correct, generic data might be collected in a person entity (table), specialized data in additional entities (tables, if you map EERM into relational model). Also there are one-to-one relationships between generic entity and specialized entities.

There is no other way to map isa-relationships from EERM into relational model (RM) then yours till this day. Though the latest SQL standard from 2003 has got a little OOP ideas added but it is far from classical OOP inheritance.

One cannot say that your approach would put more complexity into your design. Sure, because of one-to-one relationship you can merge generic and specialized entities into one one, where you would get three independent entities (tables) Client, Owner, Staff. But if the sketched problem is your assignment, probably your prof expects an isa-relationship solution of the problem.

krs,
tesu

Hi silverside,

what is a data flow diagram?

krs,
tesu

Design as follows-

<date1> <date2> <date3> <date4>--- <attended>
<rollno1> 1 0 1 1 3
<rollno2> 0 0 1 1 2
<rollno3> 0 1 0 0 1

How to design the appropriate select statement? Also, what if you want to add a further date <date5>? From a theoretical point of view, your design even violates the first normal form (1NF), because of the repeating date-x group. You will run into serious difficulties by that design. Your table might be considered to be badly designed.
Simple rule: if you are forced to expand the number of columns when new data need to be recorded, you must put this sort of data into rows, and not in columns! So a rather better design is:

table: roda
roll#     dates
-------------------
rollno1   date1
rollno1   date3
rollno1   date4
rollno2   date3
rollno2   date4
rollno3   date2

This arrangement is much more flexible (also fulfils 1NF, 2NF, 3NF, BCNF, where 3NF is the appropriate normal form a table always should meed. You need to learn something about Codd's normalization theorie if you want to design good tables).

For example: select attended:
select roll#, count(roll#) as attended from roda group by roll#
will give:

roll#       attended
-----------------
rollno1   3
rollno2   2
rollno3   1

Further advice: Tables always must have (yes, they must have that!) primary key, for example primary key(roll#, dates).

Hope this will help you.

krs,
tesu

correct, it is cartesian product, not cross product. thank you, varmadba :)

True, the cardinality is just the total product of the cardinalities of all involved tables. But this might be ok to him. So he would learn how to figure out the essential rows manually doing true handiwork.

It depends on the data type of the attribute you demand to be excluded from result set, for examples

if numeric: ... where Attribute != number
if char: ...where Attribute != 'excludeme'
if char surrounded with other stuff: ... where Attribute NOT LIKE '%excludeme%'
if NULL ... where Attribute IS NOT NULL

if to exclude a set, for example a set of single char values:
... where Attribute NOT IN ('1', '2', ...)

There is also ANY, SOME, ALL, EXISTS, but this is just another thing.

The reason im using if/else if is because if i use case IDC_Server: etc it does all case statements then quits instead of just doing the case statement for that button.

break it off !!!

put your create table syntax for all 4 tables
then some 1 can send a exact query

Obviously a task too overcharging him. Why not letting him doing plain cross products first a la posting #2 ?

As a beginner, after creating tables, what is the next step?

After having done that hard job shutdown server is badly necessary !

If you're going to make a statement like that you should supply a link to the compiler =/

Also, for my above code it needs and edit. I never incremented current @_@.

Either way, if this is true then the above code wont matter. Supply the compiler soon! I need a new one anyways =P

I dont care this code. Particularly it does not meet the requirements of the questioner. You may try this one:

int main(int argc, char* argv[])
{ int n=255; long double f=1.0;
   for (int i=1;i<=n;i++){
      f*=i; printf("%3i factorial is %25.18Le\n",i,f);
   }
   return 0;
}
I have to apologized deeply for having mistaken "faculty" for "factorial".

And to be more informed about technical standards you may study "What Every Computer Scientist Should Know about Floating-Point Arithmetic" by Goldberg, for example there: http://www.validlab.com/ goldberg/paper.ps. Especially on page 191 you will find the parameters of standardized floating point numbers.

krs,
tesu

p.s.
it s c++builder (or delphi or ;) )
which also have computed: 1000 factorial is 4.023872600770937732e+2567

255! is 3.3 * 10^503, long double wont be enough =/

Wrong!

long double is stored in 80 bits. So their numbers have approximately 19 digits of precision in a range from 3.37 x 10-4932 to 1.18 x 10+4932 !

The poor problem is that neither printf nor cout of M$ visual c++ is able to print out long double values! So you need to changes compilers to do your homework. This is the result of 255 ! computed and printed out from a program compiled with well designed compiler:

1 Faculty is 1.00000000000000E+0000
2 Faculty is 2.00000000000000E+0000
3 Faculty is 6.00000000000000E+0000
4 Faculty is 2.40000000000000E+0001
5 Faculty is 1.20000000000000E+0002
6 Faculty is 7.20000000000000E+0002
7 Faculty is 5.04000000000000E+0003
8 Faculty is 4.03200000000000E+0004
9 Faculty is 3.62880000000000E+0005
10 Faculty is 3.62880000000000E+0006
11 Faculty is 3.99168000000000E+0007
12 Faculty is 4.79001600000000E+0008
13 Faculty is 6.22702080000000E+0009
...
21 Faculty is 5.10909421717094E+0019
22 Faculty is 1.12400072777760E+0021
23 Faculty is 2.58520167388850E+0022
. . .
100 Faculty is 9.33262154439442E+0157
101 Faculty is 9.42594775983836E+0159
102 Faculty is 9.61446671503513E+0161
. . .
200 Faculty is 7.88657867364791E+0374
201 Faculty is 1.58520231340323E+0377
202 Faculty is 3.20210867307452E+0379
. . .
249 Faculty is 1.29314250436364E+0490
250 Faculty is 3.23285626090911E+0492
251 Faculty is 8.11446921488186E+0494
252 Faculty is 2.04484624215023E+0497
253 Faculty is 5.17346099264008E+0499
254 Faculty is 1.31405909213058E+0502
255 Faculty is 3.35085068493298E+0504

Happy computing !

krs,
tesu

hi
I want to fetch data from four tables using a single query.please tell me how to do that.
thanks in advance

try select * from a, b, c, d

then sort out at your leisure :D

Great task!
You should also have a look into the ERP branch too. For example SAP R/3, the world largest and most famous ERP system consists of about 18.000 tables (yes, eighteen thousands tables, compared with just 400 tables of MS Dynamics AX 4), 16.000 programs, 60.000 window forms. To install it about 25 GByte are needed only to storing the system's metadata including IDES. Microsoft, a heavy user of R/3 is managing about 4 TBytes of enterprise data with a single global production R/3 environment. Data management system of the 3-tier R/3 usually will be logically installed on one database, no matter the size is. Sure, database is physically spread over many disks, and multifold data mirroring is quit usual for safeguarding against failure. R/3 has its own distributed transaction processing which fully ensures acid no matter database servers are physically located.

What are the metrics of the ERP you are designing on?

(True pieces of advice: You need to have an exact data model (ERM) where almost every essential table derived from must fulfil 3NF without restrictions of any kind. And never try to develop serious ERP based on MySQL)

krs,
tesu

Hi all,

though Ancient Dragon has already answered almost every question, I would like to put in my twopenn'orth too.

-Why use the stack of the space is so limited?

Stack is not that limited. One can allocated as much stack as much memory from heap.
Today processors have 32bit stack pointers (e.g. ESP, EBP), what allows one to address some Giga bytes on stack.

-What are the advantages to using the stack over the heap?

Memory on stack will be automatically allocated when variable is to create and
automatically released if variable is no further needed, see below answer to lifetime.
So a programmer does not need care about his variables, except of those which must be
created by new/malloc. Stack is one of the most important data structure or principle in
computer science. Recurrence, especially recursive function calls would really be hard
to implement without stack, or even impossible. On the other hand, every time we don't know the size of an data object when program it, the object must be created, that is its memory must be allocated from heap during runtime. So heap allocation is also essential for programming.

-Is it really so bad to use pointers vs "reference variables?"

I personally think it isn't that bad because there is no difference between them,
except c++ reference variables look more beautiful and accomplish Bjarne Stroustrup's
modern conception …

http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.3.2%2Fwin32%2Fpostgresql-8.3.2-1.zip

1. unzip complete file in folder
2. carefully read readme.txt
(there are further links you should consider, especially faq. You should fully understand, how to deal with installing features BEFORE trying to install PostgreSQL)
3. run msi installer file

Postresql is a complete object-rational database system, what is also some kind of Oracle clone due to its own PL/SQL. MySql is not able to hold a candle to power PostgreSQL, entirely. Actually, it isn't that difficult to set up postgresql on win32. However, it is said that postgresql would run rather slower on win32 than an linux. I myself prefer linux.

krs,
tesu

. . . ORDER BY date DESC

oops wujtehacjusz, you may have a look at issue date;)

...
WHERE...
AND SDModelProdPrices.EffectiveDate = MAX(SDModelProdPrices.EffectiveDate)
...

Aggregate functions are not allowed in WHERE clause. So aggregate functions cannot be used for selecting specific rows. However, you can use them in HAVING clause. For MS Sql Server one solution to get the most recent date values is:

select TOP 1 ......
......
ORDER BY SDModelProdPrices.EffectiveDate desc

krs,
tesu

Hi playah,

your data model seems to be able to answer almost all questions you have asked. Also this one: Are there owners who own both, boats and shipyards? select owner.name, shipyard.name, boat.name from owner join shipyard on owner.onwerID = shipyard.onwerID join boat on owner.onwerID = boat.onwerID;

If you want to find whether a boat still belongs to the shipyard because of not having been sold so far, one solution would be to distinguish between both sorts of owners, for example by adding a category attribute to the owner entity, say category {'private individual', 'shipyard company'}. Then the select may look like this one: select owner.name, boat.name from owner join boat on owner.ownerID = boat.ownerID where owner.category = 'shipyard company';

Another data model might be based on ERM generalization/specialization (isa-relationships), where you would have one common owner entity for generic data and two sub categories (specializations) to hold specific data on private boat owners and shipyards on the other hand.

krs,
tesu

Hi tanha,

as far as I know there are only binaries for win32. You can download them there:
http://www.postgresql.org/ftp/binary/v8.3.2/win32/

If you want to compile your own Postrgresql, you need to do this on Linux, for example Debian or openSuse.

btw, Postgresql covers all kind of user definded functions. The sort you are probably loooking for are there "query language functions".

krs,
tesu

Hello kutta_vin,

well, "forgotten to mention" has created some superfluous work. However it is rather simple to get the same result from your modified tables. Supposing a table act2(AccountId, TransactionID, trtype, trvalue) exists, then there are some possibilities to solve your problem. All solutions that come into consideration are based on new CASE clause (MySql supports it). Then, one can solve the problem by means of 1) stored procedures, 2) SQL views, or 3) WITH clause (MySql 5 has it).

I have used the WITH clause as you can see. With WITH clause first a temporary table tact which selects from your new table structure act2 the old structure with columns transaction, credit and debit. On that temporary table my yesterday posted SQL select can be executed without any changes. Nice, isn't it?

with tact (transaction, credit, debit) as
(
   select TransactionID, 
     case trtype
          when 'Deposit' then coalesce(trvalue, 0)
          else 0
     end as Credit,
     case trtype
          when 'Withdrawal' then coalesce(trvalue, 0)
          else 0
     end as Debit
   from act2 where accountID = 'SA001'
) 
select transaction, credit, debit, credit+coalesce((select sum(credit) from tact b
  where b.transaction < a.transaction), 0) as sumCredit, debit+coalesce((select sum(debit) from tact b
    where b.transaction < a.transaction), 0) as sumDebit, sumCredit - sumDebit  as Balance
       from tact a order by transaction;

/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/

I …

hi wzb
Primary key of newRates should consist of Access_Code and operatorID to allow that same access code can be assigned to various operators. Like in:

create table newRates(DestinationName varchar(10) not null, 
Access_Code varchar(15) not null, operatorID varchar(15) not null, Tariff decimal(10.4),
primary key (Access_code, operatorID), foreign key (operatorID) references operators);

Thus far, I have a problem with your data model: If a call is made, the information is recorded in the CALLS table. CALLS table has called number and duration to compute the total amount. To do this you need the tariff of the operator, e.g. from AT@T. what operated that specific call. Is this necessary information stored in CALLS, maybe together with called number?

krs,
tesu

have u seen the complexity of the previously qouted CODE.....?????

Yea! Usually one needs a couple of lines, for examples:

long int reversi(long int z)
{ long int r = 0;
  while (z != 0) { r = r * 10 + z % 10; z /= 10; }
  return r;
}

...
cout  << reversi (12345);  // result: 54321

krs,
tesu

oops sorry, already solved. does it make no odds?

Hi kutta_vin

this is really a hard task. Finally I have got the following solution:

select transaction, credit, debit, credit+coalesce((select sum(credit) 
  from act b where b.transaction < a.transaction),0) as sumCredit, 
    debit+coalesce((select sum(debit) from act b 
      where b.transaction < a.transaction),0) as sumDebit,  
        sumCredit - sumDebit  as Balance  from act a order by transaction;

/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/

It is important that the rows be uniquely sequenced by Transaction column.

This is a modification of a well-known Celko-solution for running totals I have rewritten to meet your requirements. However, I haven't tested it completely, yet it seems that this solution meets at least your small sample. Tell me whether above solution is right to solve your problem.

krs,
tesu

p.s. Oh sorry, I really hope that MySQL would also be able to deal with subselects as specified in SQL standard 1999!

Hi wzb

Hi tesu,
... In the NewRates table, the field (Access_Code) which is a (pkid) is supposed to be unique. However, I need to enter the tariff for the same access code on per operator basis. Meaning I will need to have two (001) - as USA for both operator1 and operator2...

I also assumed this. It has been the reason why I wrote operatorID (pk ??) in newRates. So opeatorID must become part of pk. Hope this will work.

krs,
tesu

... I have tried your code but it does have a problem with the word (join)...
HB25

Sorry, I thought Access would have been able to handle joins. The inner joins you need can be replaced by cross products and join conditions in where clause, try this:

select c.CustomerID, c.Surname, c.Address, c.Town, c.Postcode, 
o.OrderID, o.Date, i.ProductID, i.Qty, p.Description 
 from Customer c, Order o, Item i, Product p  
   where c.CustomerID = o.CustomerID
    and  o.OrderID = i.OrderID
      and i.ProductID = p.ProductID
        and WarehouseID='Manchester' AND Date='02/06/2008'

krs,
tesu

Hi HB25,

Your first select should look like:

select c.CustomerID, c.Surname, c.Address, c.Town, c.Postcode, 
o.OrderID, o.Date, 
 i.ProductID, i.Qty, p.Description from Customer c 
   join Order o on c.CustomerID = o.CustomerID
     join Item i on o.OrderID = i.OrderID
       join Product p on i.ProductID = p.ProductID
         WHERE WarehouseID='Manchester' AND Date='02/06/2008'

I didn't test it. It should work on all databases which supply standard SQL 1999. As far as I know, Access also supports inner joins. If not, let me know. In this case the joins must be replaced.

Btw, there should be done some improvement of your data model:
There must be an 1:m relationship between Stock and Product: Stock ->------- Product
Primary key of Item must be ProductID and OrderID (the thing with composite and foreign keys)
What is CustID of Item?
Table name Order should be replaced by another name because ORDER is SQL word ( ORDER BY ...)

krs,
tesu

Welcome kutta_vin,

I really can't understand anything of your code and of that you are asking for. Perhaps you can give a small sample, for example on how the output of your select statement should look like (you can reduce it to the essential facts only).

krs,
tesu

Hi Valdemar

Do you mean console window of a running c program? On win XP you can right click on the console window title, then choose properties and simply change the layout to maximize the window. Maybe this also works on Vista?

krs,
tesu

Hi tanha,

till this day you have been asking for UDF sample. Now you post stored procedure. I HOPE you have not mistaken UDF for stored procedure. As for UDFs, I have sent you some information how such thinks can be done with MySQL, and as you may have seen it's really a heavy job there as compared to standard SQL 1999 UDF's where it s absolutely simple. You have got such a SQL1999 sample from me. Back to your stored procedure, I believe it's also a good idea to tell us the respective error message you might have received when trying to execute this procedure.

krs,
tesu

Although this post is viewed by more than 800 persons, still not solved. As I mentioned I am using WAMP package, whuch has mysql verion 5.0.4 and also PhpMyAdmin and I dont know what is wrong with the following procedure:

CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
INSERT INTO table1 VALUES (variable1);/* statement */
END /* end of block */

Hi wzb,

. . .
CALLS
• ID (pkid)
• Called Number
• Duration

RATES
• Destination Name
• Access_Code (pkid)
• Tariff
. . .
In conclusion we will have a replica of the RATES table for multiple operators. An operator is only supposed to have two fields I guess (name and ID).
So now we need to re-engineer the schema to adapt to this situation.
Eg. 95004433313445 (Will be identified as BT operator)
93004422376234 (Will be identified as AT&T operator)
. . .

You can create new table Operators

OPERATORS
•	operatorID (pk)
•	name

Then add operatorID to RATES

newRATES
•	Destination Name
•	Access_Code (pkid)
•	operatorID (pk ??)
•	Tariff

I hope you can select the right operator for a given "Called Number".

Do not create separate RATES tables for various operators. All rates with identifying operator information should be in one table. If not, you would run into difficulties when creating SQL select statements.

krs,
tesu

Dear friend

. . .
Task 1: Formulate a query and prepare a report to find order details for a selected warehouse on a specific date, this will help the warehouse gather and pack all goods ready for the courier.

Task2: Formulate a query and prepare a report so that a specific warehouse can check their stock if any quantities of stock fall below 5, stock description should also be listed.
. . .
HB25

One can help you only if you show the corresponding data model your tasks are based on!

krs,
tesu

p.s. your selects seem to be rather imperfect.

hi marcel1,
below select with case clause is standard SQL1999. Maybe it works also on MS SQL Server.

SELECT x,  
(
  CASE
     WHEN x = 'AB' and y like 'CD%' then 'CD'
     WHEN 'ZZ' and y like 'CC%' then 'BA'
     WHEN x in ('bbb','sss','ddg') then 'BB' 
     ELSE 'unknown thing'
  END 
) AS xx
FROM yourtable where ... ;

This will produce a list with two columns, for example
x         xx
------------
AB       CD
sss       BB
wow     unknown thing

You can also omit the first attribute x from the select, if you don't need original x values.
Like x, y must also be attribute of yourTable.

Maybe this will help you.

krs,
tesu

The Employee Table contains
Emp_Num(PK), Proj_Num(PK), Emp_Name, job_class, chg_hour and Hours.

is still 1NF because Emp_Name and job_class only depend on Emp_Num, that is they depend on a part of PK only. Partial dependency must be removed by moving the regarding attributes into an extra table. Also job_class and chg_hour (I assume this is hourly rate) do not depend on Proj_Num(pk). We get then:
1. Employees: Emp_Num(PK), Emp_Name, job_class, chg_hour
2. ProEmp: Emp_Num(PFK), Proj_Num(PFK), Hours
Both tables now fulfil 2NF. (Hint: If a table fulfils 1NF and its PK consists of one attribute only, such table also fulfils 2NF)

3NF?
ProEmp also fulfils 3NF (there cannot be a transitive dependency if a table has only one non-prime attribute), but Employees does not. There exists transitive dependency Emp_Num(PK) --> job_class --> chg_hour. Removing this leads to 3NF:

3. Employees2: Emp_Num(PK), Emp_Name, job_class(FK)
4. Jobs: job_class(PK), chg_hour

Employees2 and Jobs now fulfil 3NF requirements.

(PFK denotes primary and foreign key, PK primary key, FK foreign key)

I hope this will help you.

krs,
tesu

Hi booly_joon,

there is something wrong with your conception of two dimensional arrays. I suggest you should start learning some pointer's basic. This http://pweb.netcom.com/~tjensen/ptr/pointers.pdf
is a very good introduction to pointers and http://www.codeproject.com/KB/cpp/arrayDinamic.aspx gives a good example on how-to-use two dimensional arrays.

krs,
tesu

Hi Matthew,

below is a piece of code showing a pseudo random generator based on the linear congruential method by Lehmer to generating uniformly distributed random numbers over 0 ... s - 1. Random generators implemented in today programming languages are usually based on this method. Sure, they might be better and faster then mine, but lehmer is a complete random generator what also has rather good statistical properties. Its period is 2147483646.

unsigned long int lehmer(long int s)
//  linear congruential pseudo random number generator based on D. Lehmer
{  static unsigned long long a = 2007, b = 4194301, c = 2147483647, z = b;
    if ( s < 0 ) {s = -s; a = s;}
    z = (a + b * z) % c;
    return z % s;
}

If you call lehmer with s > 0, the random number is drawn from interval 0...s-1. Every time you start it with same s it produces the same sequence of random numbers, what is favorable when doing physical experiments. For game programming it s better to start it with s < 0 to initialize lehmer to produce various sequences. So s < 0 is kind of random seed.

Hope this will help you to understand how random generators work.

Theory of random generators can be found in D. Knuth: The Art of Computer Programming. One of the best set of random generators ever written is Mersenne Twister MTrand.

krs,
tesu

…

nb n qn alrdy slvd n dnwb cpl f dys g: http://www.daniweb.com/forums/thread126961.html