For Liquibase createView, is there a way to store a value of a subsearch?

Question

I am trying to create a view in SQL Server using a liquiBase yaml changelog file where each row looks like:

employee_id | provider_id | days_remaining | employed_dates

and there are three tables

EMPLOYEE - employee_id, first_name, last_name
JOB - job_id, employee_id, start_date, end_date, provider_id
ASSIGNMENT - assignment_id, job_id, employee_id, start_date, end_date, provider_id
employee_id given for the row
provider_id is the provider_id of the job_id that has the latest end_date for this employee_id
days_remaining is the number of days left until the last assignment end day for this employee_id for the above provider_id
employed_dates is a concatenation of all of the assignment_id for this employee_id for the above provider_id

My code so far:

databaseChangeLog:
  - changeSet:
  id: 100
  author: 100
  dbms: "mssql"
  changes:
    - createView:
        schemaName: ${schemaName}
        viewName: employee_history_view
        replaceIfExists: true
        selectQuery: >
          SELECT EMPLOYEE.employee_id,
          (
            SELECT TOP 1 provider_id from JOB
            where JOB.employee_id = employee_id
            order by end_date DESC
          ) as provider_id,
          employee.first_name,
          employee.last_name,
          DATEDIFF(DAY,
          (
            SELECT max(ASSIGNMENT.end_Date)
            from ASSIGNMENT
            where ASSIGNMENT.employee_id = EMPLOYEE.employee_id
            and ASSIGNMENT.provider_id = (
                                            SELECT TOP 1 provider_id from JOB
                                            where JOB.employee_id = employee_id
                                            order by end_date DESC
                                          )
            and end_date < getDate()
          ),
          getdate() ) as days_remaining,
          (
            '[' + SUBSTRING (
              (
                SELECT TOP 100 ', {"start":"'+CONVERT(CHAR(10),ASSIGNMENT.start_date,120)+'","end":"'+CONVERT(CHAR(10),ASSIGNMENT.end_date,120)+'"}'
                from ASSIGNMENT
                where ASSIGNMENT.employee_id = EMPLOYEE.employee_id
                and ASSIGNMENT.provider_id = (
                                                SELECT TOP 1 provider_id from JOB
                                                where JOB.employee_id = EMPLOYEE.employee_id
                                                order by end_date DESC
                                              )
                order by program_type DESC
                for xml path ('')
              ), 2, 8000) + ']'
          ) as employed_dates
          FROM ${employeeSchema}.EMPLOYEE

I have two questions:

Is it possible to avoid redundantly calculating provider_id (the below code) three times?
```
SELECT TOP 1 provider_id 
FROM JOB  
WHERE JOB.employee_id = employee_id  
ORDER BY end_date DESC 
```
Since there are no support for variables, variable tables, or CTEs in liquibase, how else could I do this?
Is it possible to modify the above so that each employee_id is listed in multiple rows (for each provider_id associated to this employee_id)? UNIONs won't work because liquibase does not support loops or some kind of for-each construct, and CROSS JOINS won't work as it would pair employee_ids to every provider_id whether or not they are associated.

score 1 · Answer 1 · answered Oct 31 '19 at 17:07

Sorry, wasn't thinking clearly before, this could be solved with just the following INNER JOIN at the end:

          INNER JOIN (
            SELECT DISTINCT employee_id, provider_id from JOB
          ) as DT
          ON EMPLOYEE.employee_id = DT.employee_id

For Liquibase createView, is there a way to store a value of a subsearch?

1 Answers1