1

Basically I don't know where to start here as far as coding this query and my searching on it has been largely unfruitful. To begin with the database we're querying is Oracle 11. I work in a payroll company and trying to build out a query that will have a variable number of columns returned. So for background our clients can define labor fields, these designate types of work a company may keep track of such as what department, job or project is worked. There are 12 hard coded fields, one of which must always be used but the rest is up to what any individual client needs to track. For any defined field there's a flag for if it is used in a Rate Program which can modify the pay rate of an employee based on the labor field selected for hours.

So the issue I'm running into is when trying to query all the Rate Programs for a client. Not only are the labor fields present different for each client, but which ones they use in the rate program can vary.

Here's a bit of an example of how the database tables look for these two fields

Labor Field
LABORID LABEL      USEINRATEPROGRAM
------- ---------- ----------------
      1            FALSE 
      2 DEPARTMENT TRUE
      3 JOB        FALSE

Rate Program
RATEPROGRAMID RATEPROGRAMDESC   LCF1  LCF2         LCF3
------------- ----------------- ----- ------------ ----
MACHINE SHOP  MACHINE SHOP RATE ~ANY~ MACHINE SHOP ~ANY

To expand slightly the Labor Rate table has spots for 12 fields, any of them not in use has a blank Label field and which ones they use is completely arbitrary, from the above example JOB could simply be defined under LABORID field 8, it just depends on where we enter it. I can do a simple query to pull a list of all validly configured Labor Fields for a given client and I can do one for Labor Fields configured to to be used by the Rate Program. What I can't seem to do is make a SELECT query for the Rate Program that will only include any of the LCF# columns that have USEINRATEPROGRAM set to TRUE. From what I can tell I'll have to use Dynamic SQL in order to do this since the number of columns returned will be different depending on which client database I run the query against but it's not something that I've ever had to use before.

If anyone could provide any pointers on how to proceed it would be amazing. I have written some, for me, dumbly complicated queries, but they were all fixed column results so it was just a matter of iterating on a beginning query until it all flowed together but this is another thing altogether that's just slightly outside of my reach.

EDIT: Adding some more details as it was asked for.

My interface with the database is an html portal that restricts me to SELECT queries only so there are limits to my interactions with the database but as far as I've been told dynamic queries should still function correctly. The output through this interface is an html table that can exported as a csv file.

The portal requires me to enter the database info of any individual client before a query can be passed so my hope is just a general script that I can insert into any client's database to get the relevant fields. Currently what happens when I query the Rate Program information is I will receive the actions of the rate program (I didn't include them here because they're not important towards the question) and the combination of labor fields that allows that action to happen. Every Labor Field is included in the Rate Program table, our overlying system will just display/apply those that are set as TRUE, if a field does not have a specific entry for a labor field it is populated with "~ANY~" which is true of both the fields that are used and not used by the Rate Program.

Using my tables from above what I would want to see in my query results would be:

RATEPROGRAMID RATEPROGRAMDESC   LCF2        
------------- ----------------- ------------
MACHINE SHOP  MACHINE SHOP RATE MACHINE SHOP

I would want to omit LCF1 because it's not configured at all and LCF3 because the USEINRATEPROGRAM is set to FALSE. If a different client had 10 different labor fields configured and 6 were set to TRUE for USEINRATEPROGRAM I would want the query to dynamically export just those 6 fields.

Improve this question
1
  • 4
    It might be helpful if you can add some representative sample data and expected results, including the form the results need to take - which may depend on how this will be called and what by. For example, can the caller handle a ref cursor as the result? Or XML? Commented Feb 24, 2021 at 18:43

2 Answers 2

Reset to default
2

If whatever will run this query can cope with a ref cursor result set, you could have a function that builds up the dynamic SQL from your labor_field table, and then opens and returns a ref cursor from that dynamic query; something like:

create function get_result return sys_refcursor is
  l_query varchar2(4000);
  l_cursor sys_refcursor;
begin
  select 'select rateprogramid, rateprogramdesc'
    || listagg(', lcf' || laborid || ' as ' || label, null) within group (order by laborid)
    || ' from rate_program'
  into l_query
  from labor_field
  where useinrateprogram = 'TRUE';

  dbms_output.put_line('l_query: ' || l_query);

  open l_cursor for l_query;

  return l_cursor;
end;
/

You could do that as a procedure with an OUT parameter; and either way could take input parameters if that's relevant to what you're doing.

Something then has to call the function or procedure, and exactly how it does that will depend on what 'it' is - whether it's a JDBC call, SQL*Plus, a reporting tool, etc.

As a hard-coded demo of handling the dynamic result sets, this db<>fiddle uses the settings in your question so only returns the fixed fields and lcf2 via the generated query:

select rateprogramid, rateprogramdesc, lcf2 as DEPARTMENT from rate_program

And this db<>fiddle changes the flag to include lcf3 as well, generating this query:

select rateprogramid, rateprogramdesc, lcf2 as DEPARTMENT, lcf3 as JOB from rate_program

You can call the function directly with

select get_result from dual

which returns the ref cursor; some tools will show that (SQL Developer for example, when run as a script), and JDBC can retrieve that cursor as a result set, but without knowing exactly how you are queryign the database and handling the results it's impossible to tell if your platform can cope with that.

Without more data and expected results it's hard to tell if this exactly what you are after, but should at least give you some pointers.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Sounds like creating a view, one view per client, might be what you are looking for. This would end up not being truly dynamic, but maybe this might be all you need.

Here is something to get you started. Assume you have 2 clients, "ITCHY" and "SCRATCHY", you would create two views. I am being lazy with column names; just guessing:

create or replace view labor_rate_itchy as
    select labor_id
           , rate_pgm_id
           , rate_pgm_desc
           , lcf2 as MACHINE_SHOP 
       from labor_rate
       where -- put your ITCHY client criteria here
;

create or replace view labor_rate_scratchy as
    select labor_id
           , rate_pgm_id
           , rate_pgm_desc
           , lcf1 as OUTSIDE_LABOR 
           , lcf3 as MATERIALS 
       from labor_rate
       where -- put your SCRATCHY client criteria here
;

And now you can select from labor_rate_itchy or labor_rate_scratchy as needed.

If you want to get real dynamic, you could make the view definition dynamic, and invoke that whenever you get a new client, or more likely, as a trigger on the labor rate table, so when the labor rate table is updated, the view is rebuilt.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.