Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>... Wow, that's some query. This isn't related to your problem, but the query should be cleaned up itself. Among other things, please always explicitly specify your joins, don't use the comma-separated <code>FROM</code> clause.</p> <p>This <em>may</em> work slightly better:</p> <pre><code>SELECT AL1.EMPLID, AL1.NAME, AL4.COVERAGE_ELECT, AL4.FLAT_AMOUNT, AL1.JOBTITLE, AL1.JOB_FAMILY, AL3.ANNUAL_RT, AL3.ANNL_BENEF_BASE_RT, AL4.BENEFIT_PLAN FROM TABLEONE AL1, JOIN (SELECT DISTINCT EMPLID, EMPL_RCD FROM TABLETWO WHERE PLAN_TYPE = '20' AND COVERAGE_ELECT = 'E' AND EFFDT &lt;= CURRENT_DATE) AL2 ON AL2.EMPLID = AL1.EMPLID AND AL2.EMPL_RCD = AL1.EMPL_RCD JOIN (SELECT T3.EMPLID, T3.EMPL_RCD, T3.ANNUAL_RT, T3.ANNL_BENEF_BASE_RT FROM TABLETHREE T3 JOIN (SELECT a.EMPLID, a.EMPL_RCD, a.EFFDT, MAX(EFFSEQ) EFFSEQ FROM (SELECT EMPLID, EMPL_RCD, MAX(EFFDT) EFFDT FROM TABLETHREE GROUP BY EMPLID, EMPL_RCD) a JOIN TABLETHREE z ON z.EMPLID = a.EMPID AND z.EMPL_RCD = a.EMPL_RCD AND z.EFFDT = a.EFFDT GROUP BY a.EMPLID, a.EMPL_RCD, a.EFFDT) INC ON INC.EMPLID = T3.EMPLID AND INC.EMPL_RCD = T3.EMPL_RCD AND INC.EFFDT = T3.EFFDT AND INC.EFFSEQ = T3.EFFSEQ) AL3 ON AL3.EMPLID = AL.EMPLID AND AL3.EMPL_RCD = AL1.EMPL_RCD JOIN (SELECT T4.EMPLID, T4.EMPL_RCD, T4.COVERAGE_ELECT, T4.FLAT_AMOUNT, T4.BENEFIT_PLAN FROM TABLEFOUR T4 JOIN (SELECT EMPLID, EMPL_RCD, BENEFIT_NBR, PLAN_TYPE, MAX(EFFDT) EFFDT FROM TABLETWO WHERE PLAN_TYPE IN (?) AND EFFDT &lt;= CURRENT_DATE GROUP BY EMPLID, EMPL_RCD, BENEFIT_NBR, PLAN_TYPE) INC ON T4.EMPLID = INC.EMPLID AND T4.EMPL_RCD = INC.EMPLRCD AND T4.BENEFIT_NBR = INC.BENEFIT_NBR AND T4.PLAN_TYPE = INC.PLAN_TYPE AND T4.EFFDT = INC.EFFDT WHERE T4.COVERAGE_ELECT = ?) AL4 ON AL4.EMPLID = AL1.EMPLID AND AL4.EMPL_RCD = AL1.EMPL_RCD </code></pre> <p>(Untested - without knowing your data layout it's somewhat difficult. If there was more information available, it may be possible to simplify things still farther; notably if <code>TABLETHREE.EFFSEQ</code> is always increasing, or at least per <code>EFFDT</code>, that section can be collapsed to one reference)</p>
    singulars
    1. This table or related slice is empty.
    1. POmulti parameters in SQL for DB2, using SSRS
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USClockwork-Muse
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1... good code, but i can't see where did you add the part to select according to parameter? It looks better with the joins, good job
      singulars
      1. PO
      1. USFarkiba
    2. CO@Farkiba - I wasn't able to change that, and unfortunately I don't know anything about SSRS. The only option may be to do dynamic SQL for the `IN` clause (the `COVERAGE_ELECT` can be a regular parameter marker, of course).
      singulars
      1. PO
      1. USClockwork-Muse
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload