2

I use an overlycomplex and poorly designed software at my work, and now I need to produce a report of invoices issued between a period of time.

Invoices are held at table ENTR2012, which uses the following structure:

CREATE TABLE ENTR2012 (
ABREV VARCHAR(8) NOT NULL,
NOTA VARCHAR(9) NOT NULL,
NUMEROEMIT VARCHAR(18) NOT NULL,
DATAENTRADA TIMESTAMP,
DATAEMISSAO TIMESTAMP,
VALCONTABIL DOUBLE PRECISION,
PRIMARY KEY (ABREV,NOTA,NUMEROEMIT)

From this table I want only ABREV, NOTA, NUMEROEMIT, DATAENTRADA, DATAEMISSAO, VALCONTABIL.

This should be my table:

| ABREV  | NOTA | NUMEROEMIT | DATAENTRADA | DATAEMISSAO | VALCONTABIL |
| dep1   | 001  | 1234567    | 01.01.2012  | 31.01.2012  | 1,000.00    |
| dep2   | 001  | 898909201  | 02.01.2012  | 16.01.2012  | 998.00      |

As you can see, ABREV holds the departament ID that issued the invoice, NOTA is the number of the invoice (and each depart. have their own invoice count), NUMEROEMIT is the client ID, DATAENTRADA is the date the invoice was issued, DATAEMISSA is the day the invoice was payed, and VALCONTABIL the value of the invoice.

Now I need to find the Invoice description, that is, what exactly we are charging for.

A second table, ITST2012 holds the descriptions:

CREATE TABLE ITST2012 (
ABREV VARCHAR(8) NOT NULL,
NOTA VARCHAR(9) NOT NULL,
NUMEROEMIT VARCHAR(18) NOT NULL,
DATAENTRADA TIMESTAMP,
CODPRODUTO VARCHAR(13),
ALIQISS DOUBLE PRECISION,
VALTOTAL DOUBLE PRECISION,
PRIMARY KEY (ABREV,NOTA,NUMEROEMIT)

ITST has ABREV (dep. ID), NOTA (invoice number), DATAEMISSAO (issue date), and the info I want CODPRODUTO (the product code) and ALIQISS (an useful number my boss requires for this report).

As each department (ABREV) has its own numbering order (NOTA), we have to compare the dep. ID (ABREV), the number (NOTA) and the value (VALTOTAL) at the same time to confirm this is the right invoice. The product should be like this:

| ABREV  | NOTA | NUMEROEMIT | DATAENTRADA | DATAEMISSAO | VALCONTABIL | ITST2012.CODPRODUTO | ITST2012.ALIQISS |
| dep1   | 001  | 1234567    | 01.01.2012  | 31.01.2012  | 1,000.00    | 2                   | 5.00             |
| dep2   | 001  | 898909201  | 02.01.2012  | 16.01.2012  | 998.00      | 3                   | 15.00            |

Next, I need to JOIN the ITST2012.CODPRODUTO with the real product description. Once again, each department uses its own numeration system, so we need to match ABREV (dep. ID) and CODPRODUTO (the product ID) with the table PRODUTOS.

CREATE TABLE PRODUTOS (
ABREV VARCHAR(8) NOT NULL,
CODIGO VARCHAR(13) NOT NULL,
DESCRICAO VARCHAR(50),
PRIMARY KEY (ABREV,CODIGO)

We just need to be sure ABREV = PRODUTOS.ABREV and ITST2012.CODPRODUTO = PRODUTOS.CODIGO. The table should be like this:

| ABREV  | NOTA | NUMEROEMIT | DATAENTRADA | DATAEMISSAO | VALCONTABIL | ITST2012.CODPRODUTO | PRODUTOS.DESCRICAO | ITST2012.ALIQISS |
| dep1   | 001  | 1234567    | 01.01.2012  | 31.01.2012  | 1,000.00    | 2                   | my_product         | 5.00             |
| dep2   | 001  | 898909201  | 02.01.2012  | 16.01.2012  | 998.00      | 3                   | my_product2        | 15.00            |

Finally, we just have to join the client ID (NUMEROEMIT at the first table) with the table FORNEC, which holds all our clients (this time, there there is no need to check department, we use the same table for everything)

CREATE TABLE FORNEC (
RAZSOC VARCHAR(55),
CNPJCPF VARCHAR(18),

We just have to match NUMEROEMIT = FORNEC.CNPJCPF, and get the RAZSOC info.

The final table for the report is like this:

| ABREV  | NOTA | NUMEROEMIT | FORNEC.RAZSOC | DATAENTRADA | DATAEMISSAO | VALCONTABIL | ITST2012.CODPRODUTO | PRODUTOS.DESCRICAO | ITST2012.ALIQISS |
| dep1   | 001  | 1234567    | my_client     | 01.01.2012  | 31.01.2012  | 1,000.00    | 2                   | my_product         | 5.00             |
| dep2   | 001  | 898909201  | my_client2    | 02.01.2012  | 16.01.2012  | 998.00      | 3                   | my_product2        | 15.00            |

My problem is: HOW? I have no idea how to join all those tables at the same time, using SQL. I can INNER JOIN the client easily, but I can't write an query which compares many fields at the same time, and still joins the right way. I'm quite desperate as I've only worked with databases of my own design, not this macaroni database with too much info inside to few tables.

Thanks a lot for any SQL Wizard that can string this together with me.

PS. The database is Firebird, with no row ids.


The question have been answered, so I thank you very much!

The final code is the following:

SELECT notas.ABREV, notas.NOTA, notas.NUMEROEMIT, prestador.RAZSOC, notas.DATAENTRADA, notas.DATAEMISSAO, notas.VALCONTABIL, itens.CODPRODUTO, prod.DESCRICAO, itens.ALIQISS FROM ENTR2012 notas
INNER JOIN ITST2012 itens
ON notas.ABREV=itens.ABREV AND notas.NOTA=itens.NOTA AND notas.VALCONTABIL=itens.VALTOTAL
INNER JOIN PRODUTOS prod
ON itens.ABREV=prod.ABREV AND itens.CODPRODUTO=prod.CODIGO
INNER JOIN FORNEC prestador
ON itens.NUMEROEMIT=prestador.NUMERO;
1

I'm really not sure if this is going to work in this Database of yours, but I made tests with much simpler values and tables in my Oracle XE database and it worked just fine, I would give it a go.

SELECT * FROM ENTR2012
INNER JOIN ITST2012
ON ENTR2012.ABREV=ITST2012.ABREV AND ENTR2012.NOTA=ITST2012.NOTA AND ENTR2012.VALTOTAL=ITST2012.VALTOTAL
INNER JOIN PRODUTOS
ON ITST2012.ABREV=PRODUTOS.ABREV AND ITST2012.CODPRODUTO=PRODUTOS.CODIGO
INNER JOIN FORNEC
ON ITST2012.NUMEROEMIT=FORNEC.CNPJCPF;

Please, review values, columns and such because maybe I got lost during the writing.

  • perfect, worked like a charm after some small modifications. As the tables have lots of fields (I edited the table structure before posting here, not to flood the question with useless sql dump) using SELECT * got me too many fields to work with, but I've selected the correct fields and it simply worked! – Andre Scutieri May 4 '15 at 18:02
  • On a side note, are those tables related by foreign keys and triggers or everything must be updated and inserted manually? This spreaded schema seems highly prone to errors especially if it relies on human intervention. – Enthusiast May 4 '15 at 18:07
  • The software handles the database, so we use its screens and windows and forms to insert data, and then it inserts in the database. BUT, it never does it right: there is no cascading delete or update, and it sometimes copies the name corresponding to an ID into another table, so when you update the name (i.e. corrects an address or typing error) this correction doesn't reflects on previous registries. But it was originally a Delphi software, that was ported to C#, but is backwards compatible with old standarts and tables... – Andre Scutieri May 4 '15 at 19:41
  • As it has no dynamic report tools (i.e. there are some reports built-in, if you want something different, no game) I have to manually fetch data from the database itself (an .gdb file in a shared folder), and transport it to Excel spreadsheets, so my boss can do whatever bosses do with eons old accountant records. As the only one with some db knowledge, every single report request comes to my table... I would be jobless if not for you guys from Stack :( – Andre Scutieri May 4 '15 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.