set single value to be used by 4-table union query

[zenenigma]

I have an awkward problem.

We have a web application that allows outside users to paste a SQL (select only) query into a textbox, click a button, and have the database results displayed on the page.

I have a union query that includes 4 SQL select statements. The query I've created pulls back the exact results I'm looking for, however it requires the user to replace a value in 4 different places. The User_ID value needs to replace '####' with '1234' (or whatever User_ID they are looking up).

What I'd like is a select query where the user only has to replace one value. Because of the security on the web page I can't use a &variable or "set" command because of the security of the web page.

The 4 queries pull back document information from a few different pages depending on if the document still exists, has been transferred, has been purged, or both transferred & purged. There are three main source tables and two lookups:

Existing (same record will not exist in Purged)
Purged (same record will not exist in Existing)
Transferred (will also be in existing until purged)

transfer_lookup is used for looking up transfer_ids related to that user_id
user_lookup is used for looking up doc_ids related to that user_id (non-transfer)

Queries (Trimmed down alot to get the point accross)

SELECT b.user_id, a.doc_id, a.t_date as transferdate, p.pdate as Purged
FROM transfer_lookup b, transferred a, purged p
where a.transfer_id = b.transfer_id
where b.user_id = '####'
and a.doc_id = p.doc_id

Union

SELECT b.user_id, a.doc_id, a.t_date as transferdate,
'Not Purged' as Purged
FROM transfer_lookup b, transferred a, existing e
where a.td_transfer_id = b.transfer_id
and b.user_id = '####'
and a.doc_id = e.doc_id

Union

SELECT u.user_id, e.doc_id, 'N/A' as transferdate, 'Not Purged' as Purged
FROM existing e, user_lookup u
where e.transfer_status = 'N'
and e.user_id = '####'
and e.user_id = u.user_id

Union

SELECT u.user_id, e.doc_id, 'N/A' as transferdate, p.date as Purged
FROM folder_lookup a, purged p, user_lookup u
where p.transfer_status = 'N'
and a.folder = p.folder
and u.user_id = a.user_id
and u.user_id = '####'


So at this point I'm looking at either

* Restructuring my Union query with iff statements (or some other method) to allow the user to change just one value

* Bagging the whole idea because it's too much work.



Any suggestions would be necessary - at the moment I'm leaning towards option B.

-ZE

 

[gmmastros]

I can think of 2 days this might work.

1. Create a view. You would remove the filter criteria fro the individual UNIONS. You would only need to apply it to the view.

Create View ANY_NAME_YOU_WANT
AS
  SELECT b.user_id, a.doc_id, a.t_date as transferdate, p.pdate as Purged
  FROM transfer_lookup b, transferred a, purged p
  where a.transfer_id = b.transfer_id  
  and a.doc_id = p.doc_id

  Union

  SELECT b.user_id, a.doc_id, a.t_date as transferdate,
  'Not Purged' as Purged
  FROM transfer_lookup b, transferred a, existing e
  where a.td_transfer_id = b.transfer_id
  and a.doc_id = e.doc_id
  
  Union
  
  SELECT u.user_id, e.doc_id, 'N/A' as transferdate, 'Not Purged' as Purged
  FROM existing e, user_lookup u
  where e.transfer_status = 'N'
  and e.user_id = u.user_id
  
  Union
  
  SELECT u.user_id, e.doc_id, 'N/A' as transferdate, p.date as Purged
  FROM folder_lookup a, purged p, user_lookup u
  where p.transfer_status = 'N'
  and a.folder = p.folder
  and u.user_id = a.user_id

Now, you should be able to use it from your front end like this...

Select user_id, doc_id, transferdate, purged
From   ANY_NAME_YOU_WANT
Where  user_id = '####'

2. You could use a derived table technique, which is very similar to the view method.

[!]Select user_id, doc_id, transferdate, purged
From   ([/!]
  SELECT b.user_id, a.doc_id, a.t_date as transferdate, p.pdate as Purged
  FROM transfer_lookup b, transferred a, purged p
  where a.transfer_id = b.transfer_id  
  and a.doc_id = p.doc_id

  Union

  SELECT b.user_id, a.doc_id, a.t_date as transferdate,
  'Not Purged' as Purged
  FROM transfer_lookup b, transferred a, existing e
  where a.td_transfer_id = b.transfer_id
  and a.doc_id = e.doc_id
  
  Union
  
  SELECT u.user_id, e.doc_id, 'N/A' as transferdate, 'Not Purged' as Purged
  FROM existing e, user_lookup u
  where e.transfer_status = 'N'
  and e.user_id = u.user_id
  
  Union
  
  SELECT u.user_id, e.doc_id, 'N/A' as transferdate, p.date as Purged
  FROM folder_lookup a, purged p, user_lookup u
  where p.transfer_status = 'N'
  and a.folder = p.folder
  and u.user_id = a.user_id
[!]  ) As Alias_Name
Where  Alias_Name.user_id = 'XXXX'[/!]

Note that in both methods, I removed the where clause criteria on user_id from each of the 4 union'd queries. The outer where clause will take care of the individual results. Personally, I would prefer the VIEW method, because it looks a little cleaner from the user's perspective. It essentially hides the union and all those joins. All the user sees is a single table... and you can name the view something that would make more sense to the user, too.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom