Is there a way to combine multiple selects into one select statement?

[Former Member]

Hi,

I've been creating reports using Command. Rather than a subreport I like to grab values using a select within a select in my sql query. However, I have the scenario whereby there are multiple fields I would like to grab and I use a select on each one. I see it is a performance hit to the point I'm wondering if a single subreport would be better?

I'm wondering if I can combine all of the subselects into a single select in order to optimize the query. I'm using CR 2011.  My sql query is as follows:

Select ft.field_tickets_id, ft.sequence AS "Ticket Sequence", ft.ticket_date, ft.comments, jobs.job_internal_id, customers.customer_name, customers.street_address, customers.extended_address, customers.locality, customers.postal_code, regions.region_code, v_field_tickets.employee_billing_total, v_field_tickets.resource_billing_total,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1048) AS temperature,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1054) AS wind,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1055) AS precipitation,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1056) AS conditions,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1057) AS fluid_lost,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1058) AS hauled_fluids,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1059) AS tanks_fluid_hauled,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1060) AS out_total_fluid,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1061) AS pumped_bbls,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1062) AS rods_pulled,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1063) AS rods_ran,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1064) AS ponies_ran,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1065) AS polish_rod_size,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1066) AS ponies_pulled,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1067) AS rod_size,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1068) AS casing,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1069) AS casing_size,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1070) AS tubing_size,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1071) AS tubing_ran,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1072) AS td_ft,

(SELECT(cftv.value) from custom_field_tickets_values AS cftv WHERE ft.field_tickets_id=cftv.field_tickets_id AND cftv.custom_fields_id=1073) AS tubing_pulled,

(SELECT(cjv.checkbox_value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1045) AS wyoming,

(SELECT(cjv.checkbox_value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1046) AS colorado,

(SELECT(cjv.checkbox_value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1047) AS pennsylvania,

(SELECT(cjv.value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1050) AS county,

(SELECT(cjv.value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1051) AS afenumber,

(SELECT(cjv.value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1052) AS lou,

(SELECT(cjv.value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1053) AS oa,

(SELECT(cjv.value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1075) AS costcenter,

(SELECT(cjv.value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1076) AS glcode,

(SELECT(cjv.value) from custom_jobs_values AS cjv WHERE ft.jobs_id=cjv.jobs_id AND cjv.custom_fields_id=1085) AS area

from field_tickets AS ft

INNER JOIN jobs ON ft.jobs_id=jobs.jobs_id

INNER JOIN customers ON jobs.customers_id=customers.customers_id

INNER JOIN regions ON customers.regions_id=regions.regions_id

INNER JOIN v_field_tickets ON ft.field_tickets_id=v_field_tickets.field_tickets_id where

IsNull(ft.deleted_at)

Thanks in advance!

Mark

[DellSC · Accepted solution]

Yes, you should be able to do this using joins with a table alias instead of selects.  It might look something like this (I just did the first three so you can see the pattern):

Select
  ft.field_tickets_id, ft.sequence AS "Ticket Sequence", ft.ticket_date,
  ft.comments, jobs.job_internal_id, customers.customer_name,
  customers.street_address, customers.extended_address, customers.locality,
  customers.postal_code, regions.region_code, v_field_tickets.employee_billing_total,
  v_field_tickets.resource_billing_total,

  temp.value as temperature,
  wnd.value as wind,
  precip.value as precipitation,
  ...

from field_tickets AS ft

INNER JOIN jobs
  ON ft.jobs_id=jobs.jobs_id
INNER JOIN customers
  ON jobs.customers_id=customers.customers_id
INNER JOIN regions
  ON customers.regions_id=regions.regions_id
INNER JOIN v_field_tickets
  ON ft.field_tickets_id=v_field_tickets.field_tickets_id
LEFT JOIN custom_field_tickets_values as temp
  ON ft.field_tickets_id=temp.field_tickets_id
  AND temp.custom_fields_id=1048
LEFT JOIN custom_field_tickets_values as wnd
  ON ft.field_tickets_id=wnd.field_tickets_id
  AND wnd.custom_fields_id=1054
LEFT JOIN custom_field_tickets_values as precip
  ON ft.field_tickets_id=precip.field_tickets_id
  AND precip.custom_fields_id=1055
...

WHERE IsNull(ft.deleted_at)

-Dell

[Former Member · Accepted solution]

Hi Dell,

Thanks for this tip. I'm not sure that performance has improved though. Left joins are killers on performance I find. A single subreport will likely be the answer to faster performance?

Mark

[DellSC · Accepted solution]

No, it won't.  However, I have some other thoughts.

Depending on how you need to display your data, you could join once to custom_field_tickets_values.  In the Select Expert, put something like {custom_field_tickets_values.custom_fields_id} in (1048, 1054, 1055,...<all of the values you're looking for>)

This will give you multiple records per Ticket Sequence, so you would then need to group your report on the Ticket Sequence and either use the custom_field_tickets_values values in the details, or use formulas to display them in the group header or footer based on the custom_fields_id.

This way you would only join to the table once and Crystal would handle the various values.

-Dell

[Former Member · Accepted solution]

Hi Dell,

Thanks for your help. It looks like creating one subreport (parameter in subreport sql that links to tickets_id in main report) is the best performance. Then I can use one select and inner joins in the subreport. I can group in subreport as well.

The nature of these custom fields was such when I first tried an INNER or LEFT join in the main report Crystal gave multiple detail rows for each value. That is why I needed select statements within a select or, alternatively, a subreport.

I'll consider this closed - thanks again!

Mark