SQL Nestled doubt

Asked

Viewed 47 times

0

I’m using that SELECT, but now I need to search the fields in the client table (ex: cli_clifone and cli_clicontato) , how can I include to bring this information into the SELECT below, I’m trying but only returns me error:

SELECT cli_cdcli, cli_razsoc
 From
 (
 SELECT cli_cdcli, cli_razsoc FROM vw_os_finalizada_cliente
 Union
 SELECT cli_cdcli, cli_razsoc FROM vw_titrec_aberto_cliente
 ) AS ConCli
 GROUP BY ConCli.cli_cdcli
 ORDER BY ConCli.cli_razsoc, ConCli.cli_cdcli
sql crystal-reports

  • How did you try? What was the error returned?

  • Wouldn’t just change the first line to SELECT cli_cdcli, cli_razsoc, cli_clifone, cli_clicontato?

  • Query : SELECT cli_cdcli, cli_razsoc, cli_clifone From ( SELECT cli_cdcli, cli_razsoc FROM vw_os_finalizada_cliente Union SELECT ...
Error Code : 1054
Unknown column 'cli_clifone' in 'field list'

  • I can’t because she’s not in the views and I can’t include her in the views !

  • I need to include a SELECT only for the phone and for the customer contact with the SELECT below but I’m not able to fit the syntax.

  • use the views for this is very bad ein... the right would be to run the select straight in the clients table, the way it is will need to make a Join with this to fetch the other fields

  • However I need to have the views because they are specific fields.

  • or you think you need... not even this sub-select and group by are needed...

  • I took this program with this structure, to provide maintenance and requested that I add these fields what Oce suggests to do ?

  • Each case is a case, initially I would eliminate the view... but I can only know what to do, analyzing the specific case... if you want to do only one gambiarra... just put the Join with the client table and search the fields

  • my idea at first was to put a Join to the client table but I’m not able to fit it ?

  • What is the name of this client table and what are the fields there?

  • the table is called : client and its fields are: cli_nmcontato, cli_nrdddfn1, cli_fone1, cli_nrdddfn2, cli_fone2, cli_nrdddfn3, cli_fone3, cli_nrdddfax, cli_fax

  • Adds a Join Inner on the outside of everything linking with the client id that comes from the view, and adds the new columns of this new table.

Show 9 more comments

1 answer

0

For the little you informed I would do as follows:

SELECT cli_cdcli, cli_razsoc, A.cli_clifone, A.cli_clicontato
From
(
SELECT cli_cdcli, cli_razsoc FROM vw_os_finalizada_cliente
Union
SELECT cli_cdcli, cli_razsoc FROM vw_titrec_aberto_cliente
) AS ConCli
INNER JOIN (tabela de cliente original) as A ON (A.cli_cdcli=ConCli.cli_cdcli)
GROUP BY ConCli.cli_cdcli
ORDER BY ConCli.cli_razsoc, ConCli.cli_cdcli, A.cli_clifone, A.cli_clicontato

Since you already have the right customers through the view and cannot add the other information, I believe it would be the easiest way.

Browser other questions tagged sql crystal-reports

You are not signed in. Login or sign up in order to post.