SQL Query - Oracle GL Transaction Detail

Question

I am a novice SQL coder, and need some assistance with the SQL query I'm building for the GL Transaction Detail from Oracle.

I have been able to write my query, and I am successfully able to put the data how I like it. There's something I'd like to add to the query, which I believe requires a sub query, but I'm not sure how to write it.

SELECT CLIENT,
RESPONSIBILITY,
SERVICE_LINE,
STOB,
PROJECT,
JE_LINES_NET_ACT_AMOUNT AS "ACTUAL_AMOUNT",
EFFECTIVE_DATE,
PERIOD_NAME,
VENDOR_NAME,
DISTRIBUTION_SUPPLIER_NAME,
CASE WHEN JE_LINE_DESCRIPTION='Journal Import Created' THEN
     AP_DISTRIBUTION_DESCRIPTION ELSE 
     JE_LINE_DESCRIPTION END AS DESCRIPTION,
CASE WHEN JE_HEADER_NAME LIKE '%Purchase%' THEN 
     INVOICE_NUMBER ELSE
     JE_HEADER_NAME END AS DOCUMENT_NUMBER
FROM GL_Detail
WHERE RESPONSIBILITY BETWEEN '17500' AND '17511'
     AND EFFECTIVE_DATE BETWEEN '2016/04/01' AND '2017/03/31'
     AND JE_LINES_NET_ACT_AMOUNT <> 0

Above is an example of the code I'm using for my query. Currently I have it pulling data for a range of responsibility centers (or cost centers). For reporting purposes, we have these responsibility centers roll up to different levels for location and department. Most users, want to pull the data by these rolled up responsibility numbers; however our GL transaction detail table does not have these columns.

What I want to be able to do: I want to be able to define a range of responsibility centers and assign them with their rolled up responsibility number that I can define. When I set the parameter after the WHERE, I want to be able to set the rolled up responsibility number as a parameter.

Would a nested case function solve this after select do this? Or is there another function I need to use?

Any help would be greatly appreciated.

Answer 1

You can use a PL/SQL script to give you that result. You use the ACCEPT command before your statement. Also note that because I have you shifting to PL/SQL, I added a semicolon and forward slash to the end of your query.

ACCEPT resp_start NUMBER PROMPT 'Enter responsibility start range: '
ACCEPT resp_end NUMBER PROMPT 'Enter responsibility end range:   '

SELECT
    . . .
FROM GL_Detail
WHERE RESPONSIBILITY BETWEEN '&resp_start' AND '&resp_end'
 AND EFFECTIVE_DATE BETWEEN '2016/04/01' AND '2017/03/31'
 AND JE_LINES_NET_ACT_AMOUNT <> 0;
/

See How do I use variables in Oracle SQL Developer? for more information on substituting variables. I hope that helps!

Answer 2

you did not state the data types of your columns, but I see a potential problem with implicit type conversion. To avoid that, use bind variables that match the type of the column being subset.