Forcing data in the SQL FROM clause
The problem is with figuring out how to report what is NOT in the database.
What I'm asking here is if there is a way to force data into the FROM clause of a SQL statement from with Oracle ReportBuilder. For example,
-- this creates the total set of possible tickets
SELECT columns
FROM (force data between Beginning# and Ending# here)
MINUS
-- this removes the tickets in the database,
-- leaving only those from above not in the database
SELECT columns
FROM table
WHERE
tick_no BETWEEN Beginning# AND Ending#
The report is to 'reconcile' tickets written from a ticket book with tickets entered into the system. For example, the books are 25 tickets each and the tickets are sequentially numbered. For example,
Book 1: 1-25
Book 2: 26-50
Book 3: 51-75
Book 4: 76-100
The report will ask for the ticket beginning and ending numbers, for example,
Beginning#: ____
Ending#: ____
These numbers will be compared to ticket numbers in the database and should report numbers NOT found.
For example, tickets 28, 31, and 70-100 are not in the database. If the user enters:
Beginning#: 26
Ending#: 75
the report should display something like the following:
Tickets not found between _26_ and _75_ are:
28
31
70
71
72
73
74
75
Now, to add a little more complexity, the ticket numbers might actually be more like:
AA00001
B23751
CW464226
It will be easy enough to design the input parameters like this:
Beginning#:__-______
Ending#: __-______
But the real problem is in populating something from which the found tickets can be MINUSed.