Board index » delphi » Impossible SQL problem, help required

Impossible SQL problem, help required

Subject:
             Impossible SQL Problem
        Date:
             Thu, 11 Nov 1999 19:41:12 +0000
       From:
             Andy Murphy <andy_mur...@compu-learn.co.uk>
 Organization:
             Compu-Learn UK Group
 Newsgroups:
             borland.public.delphi.database.desktop

After trying nearly every method put to me after a week i'm no closer at

getting around my problem of merging to tables together

the two tables are structured thus:

Rate99 (this holds all the records for the current year)
==========
Ref char(25)
Rval INT

Rate95A (this holds 1995 values which i would like inserted into the
above
table in a new field if the reference numbers of both match)
==========
Ref char(25)
Rv95a INT

They currently look like:

Rate99                                     Rate95A

Ref            Rval ('s)                Ref              RV95A('s)
===============              ===============
0000142   3000                       0057323     2300
0001425   2500                       0000142     2300
0014753   1800                       0014753     5000

as an example, in the real tables there are 1.7 million records.

What i want is one table based on the rate99 table but with values added

in a new field RV95A if the references are matched. Any values not used
in the RV95A table can be discarded when i drop the RV95A table, and any

RATE99 records that do not find a match should have 0 or null in the
RV95A field which indicates that the reference is a new one and couldn't

be found on the 1995 list.

Based on the above example, i want the rate99 (or a new table) to end up

looking like this:

Ref            Rval ('s)   RV95A ('s)
=========================
0000142   3000          2300
0001425   2500          null or 0
0014753   1800          5000

This is surely fairly straightforward SQL isnt it?

Btw i tried the outer join, it worked for 20mins and then locked solid.

This isnt the only problem i have, the other problem is no matter how i
try
the database (Paradigm.Gdb - INTERBASE 4) will not let me create a
primary
key on any table?

I'm very stuck as you can imagine lol. Cheers.

ANDY

 

Re:Impossible SQL problem, help required


heres what to do - - -
ALTER TABLE  RATE99 ADD RATE95A INTEGER DEFAULT 0;
CREATE VIEW TEMP_RATES(NEW95A, OLD95A)   AS
        SELECT N.RATE95A, O.RATE95A
            FROM RATE99 N INNER JOIN RATE95A O
            ON N.REF=O.REF;
UPDATE TEMP_RATES SET NEW95A=OLD95A;
DROP VIEW TEMP_RATES;

should be quick!!!!
But. . .  This isn't a normal form schema!!!!
ANY ONE GOT A STORED PROCEDURE TO IMPLEMENT PIVOT TABLES IN INTERBASE????

bas

Re:Impossible SQL problem, help required


it alters the table ok, but then errors on

CREATE VIEW TEMP_RATES(NEW95A, OLD95A)   AS
        SELECT N.RATE95A, O.RATE95A
            FROM RATE99 N INNER JOIN RATE95A O
            ON N.REF=O.REF;

it cant find N.RATE95A ?????????

Quote
Blair Allen Stark wrote:
> heres what to do - - -
> ALTER TABLE  RATE99 ADD RATE95A INTEGER DEFAULT 0;
> CREATE VIEW TEMP_RATES(NEW95A, OLD95A)   AS
>         SELECT N.RATE95A, O.RATE95A
>             FROM RATE99 N INNER JOIN RATE95A O
>             ON N.REF=O.REF;
> UPDATE TEMP_RATES SET NEW95A=OLD95A;
> DROP VIEW TEMP_RATES;

> should be quick!!!!
> But. . .  This isn't a normal form schema!!!!
> ANY ONE GOT A STORED PROCEDURE TO IMPLEMENT PIVOT TABLES IN INTERBASE????

> bas

Re:Impossible SQL problem, help required


Im sorry. . . My bad. . . The view is not updateable for one. . and it had a
typo!!!!

Execute this SQL

UPDATE RATE99 R set
 R.RATE95A = (select O.Rv95A from RATE95A O where ref=R.Ref)

It Works!!!
If its too slow. . .

CREATE TABLE RATE(ref CHAR(25) NOT NULL, rval99 INT, rval95a INT, PRIMARY
KEY(ref));

CREATE VIEW TEMP_RATES (REF, RVAL99, RVAL95A) AS
       SELECT N.REF, N.RVAL, O.RV95A
           FROM RATE99 N INNER JOIN RATE95A O
            ON N.REF=O.REF

INSERT INTO RATE
    SELECT * FORM TEMP_RATES

Bbas

Re:Impossible SQL problem, help required


Im sorry. . . My bad. . . The view is not updateable for one. . and it had a
typo!!!!

Execute this SQL

UPDATE RATE99 R set
 R.RATE95A = (select O.Rv95A from RATE95A O where ref=R.Ref)

It Works!!!
If its too slow. . .

CREATE TABLE RATE(ref CHAR(25) NOT NULL, rval99 INT, rval95a INT, PRIMARY
KEY(ref));

CREATE VIEW TEMP_RATES (REF, RVAL99, RVAL95A) AS
       SELECT N.REF, N.RVAL, O.RV95A
           FROM RATE99 N INNER JOIN RATE95A O
            ON N.REF=O.REF

INSERT INTO RATE
    SELECT * FORM TEMP_RATES

Bbas

Re:Impossible SQL problem, help required


If you do the New Table Solution. . .you have to do this, too:
Insert into RATE select REF, RVal from RATE99
let the primary key weed out the duplicate REF IDs
probably just as slow

bas

Quote

> CREATE TABLE RATE(ref CHAR(25) NOT NULL, rval99 INT, rval95a INT, PRIMARY
> KEY(ref));

> CREATE VIEW TEMP_RATES (REF, RVAL99, RVAL95A) AS
>        SELECT N.REF, N.RVAL, O.RV95A
>            FROM RATE99 N INNER JOIN RATE95A O
>             ON N.REF=O.REF

> INSERT INTO RATE
>     SELECT * FORM TEMP_RATES

> Bbas

Re:Impossible SQL problem, help required


I'm gonna run with two independent tables because after being sent about 20
ways to do this it  still dont work.

Thanks anyways people

Quote
Blair Allen Stark wrote:
> If you do the New Table Solution. . .you have to do this, too:
> Insert into RATE select REF, RVal from RATE99
> let the primary key weed out the duplicate REF IDs
> probably just as slow

> bas

> > CREATE TABLE RATE(ref CHAR(25) NOT NULL, rval99 INT, rval95a INT, PRIMARY
> > KEY(ref));

> > CREATE VIEW TEMP_RATES (REF, RVAL99, RVAL95A) AS
> >        SELECT N.REF, N.RVAL, O.RV95A
> >            FROM RATE99 N INNER JOIN RATE95A O
> >             ON N.REF=O.REF

> > INSERT INTO RATE
> >     SELECT * FORM TEMP_RATES

> > Bbas

Other Threads