Help inserting additional queries

1

Hi I have the following query and I am endeavouring to add further columns but it keeps failing could someone be kind enough to show me how? I keep getting outer failures so I am guessing I am inserting the requests in the wrong place.

I want to add cus_email, cus_hometel, cus_worktel, cus_mobile, (in table "Customer") 7 con_brokref (in Table "contract")

select cus_id, con_polid, cus_surname, cus_forename1, con_renewal, con_type,
(r_premium1.12), r_charge, r_legalexp, (case when r_insr_charge is null then 0 else r_insr_charge end),
(case when sum((ba1.bad_premium1.12)) is null then 0 else sum((ba1.bad_premium1.12)) end) RnwlAddon,
(case when ar_premium is null then 0 else (ar_premium1.12) end),
(case when ar_charge is null then 0 else ar_charge end),
(case when ar_legalexp is null then 0 else ar_legalexp end),
(case when ar_insr_charge is null then 0 else ar_insr_charge end),
(case when sum((ba2.bad_premium1.12)) is null then 0 else sum((ba2.bad_premium1.12)) end) AltAddon,
ad.add_address1, ad.add_address2, ad.add_address3, ad.add_address4, ad.add_postcode,
(case when con_lob = "HHI" then ha.add_address1 else (case when ca.add_id is null then ad.add_address1 else ca.add_address1 end) end),
(case when con_lob = "HHI" then ha.add_address2 else (case when ca.add_id is null then ad.add_address2 else ca.add_address2 end) end),
(case when con_lob = "HHI" then ha.add_address3 else (case when ca.add_id is null then ad.add_address3 else ca.add_address3 end) end),
(case when con_lob = "HHI" then ha.add_address4 else (case when ca.add_id is null then ad.add_address4 else ca.add_address4 end) end),
(case when con_lob = "HHI" then ha.add_postcode else (case when ca.add_id is null then ad.add_postcode else ca.add_postcode end) end),
veh_reg, veh_man_name, veh_model_name
from customer, address ad, contract, renewals, outer (broker_addon ba1),
outer (alt_renewals, outer(broker_addon ba2)),
risk_history, outer (cover, outer (address ca)),
outer(hh_risk_history, hh_premise, outer(address ha)),
outer(master_vehicle, vehicle_details)
where con_cus_id = cus_id
and ad.add_id = cus_add_id
and rsh_con_id = con_id
and rsh_cov_id = cov_id
and hpr_id = hrk_hpr_id
and hrk_rsh_id = rsh_id
and hpr_add_id = ha.add_id
and cov_add_id = ca.add_id
and con_polid> 0
and con_status = 4
and ba1.bad_con_id = con_id
and ba1.bad_type = "R"
and r_con_id = con_id
and ar_con_id = r_con_id
and ba2.bad_con_id = ar_alt_con_id
and ba2.bad_type = "C"
and rsh_mvh_id = mvh_id
and veh_mvh_id = mvh_id
group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 14, 15, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29
order by 1, 2

2

Please post the failure message(s)

3

This doesn't appear to be SQL Server (which doesn't allow "GROUP BY" using a column number).

This is a SQL Server forum; if you are using a different dbms, you will probably have much better luck on a different forum.

4

Hi I have added the extra queries in the first line and it says "could not add the table 'outer'"

select cus_id, con_polid, cus_surname, cus_forename1, con_renewal, con_type,cus_email, cus_hometel, cus_worktel, cus_mobile, con_Brokref,

5

Hi I access the database via Excel is says Microsoft query and has an SQL button

6

This means select from a bunch of tables, including outer. Since outer is also a keyword, try writing [outer] instead, Also, I don't know what you mean by that expression in parentheses but it doesn't look like any SQL Syntax I know.

First, get your query working in SSMS. Then copy the working query to Excel.

1 Like
7

Hi thank you for taking the time to reply, I don't really understand what I have to do as old employee wrote this query, which works as is but what I wanted to do was add the extra columns - select cus_id, con_polid, cus_surname, cus_forename1, con_renewal, con_type,cus_email, cus_hometel, cus_worktel, cus_mobile, con_Brokref, - so that they appeared in the same query. I have tried adding them at the beginning but then get that error re outer. If for example I take out veh_model_name and substitute it for con_brokref it pulls through the details of con_brokref. I just cant seem to see where to insert the extra columns ?

8
  1. Copy you query to SSMS. If you don't have SSMS, it's a free download from here:

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

  1. Launch SSMS and connect to the server to issue the query. Use the connection information in the connection properties in Excel (same place your query is) e.g.

image

Get the details from your connection and use them to connect in ssms like this:

image
image.png1407×656 33 KB

image

Then click connect. When connected, paste in the original query and click the Execute button or hit f5. Observe the results. Now, add the additional columns and hit f5 again. Keep at it until you get the right results. Then copy the finished query back to Excel,

Voila!