Solved

Oracle: Passing a variable in a query

Posted on 2013-06-07
22
614 Views
Last Modified: 2013-07-05
Hello experts,

I have a query that is composed of several queries by a UNION ALL. For example a single query would give me how many tickets were created, the next query would be how many were escalated, etc.  Here is the kicker, I am manually entering the names of the employees for each query.  This is not an efficient way but the only way I know how.

I am looking to create a variable like Employee_Name := ('Jon Doe', 'Richard Simmons', Michael Jordan')

Then I would be able to run the query that would look something like this:

Select *
From  Table
Where Submitter = Employee_Name

Attached you will find my query.

Thank you!
Employee-Query-Example.txt
0
Comment
Question by:Maliki Hassani
  • 11
  • 6
  • 5
22 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39230230
See if this gets what you want.

I might have missed some parans
with mydata as (
select 'Jon Doe,Richard Simmons,Michael Jordan' mystring from dual
)
SELECT REQUESTOR_FULL_NAME AS OPERATOR_NAME,

  CASE WHEN SEVERITY = '0-Informational' THEN 1
        when SEVERITY = '1-Low' THEN 2
        when SEVERITY = '2-Medium' THEN 3
        when SEVERITY = '3-High' THEN 4
        when SEVERITY = '4-Urgent' THEN 5
        when SEVERITY = '5-Critical' THEN 6
   end ACTIVITY_SCORE
  FROM ARADMIN.JOIN_CONSOLIDATED_STATUS_ASSIG
         WHERE UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
          AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY) 
          AND NOT(FORM_NAME = 'Maintenance')
          AND REQUESTOR_FULL_NAME in (
		select rtrim(regexp_substr(mystring,'([[:alnum:]]*)(,)?',1,column_value),',') mystring
		from mydata,
	 	table(
	  	cast(
	  	multiset(select level from dual connect by level <= ((length(mystring)-length(replace(mystring,','))))+1)
	  	as sys.odcivarchar2list
	  	)
	  	)
)
UNION ALL
--tickets accessed
SELECT MX.SUBMITTER_FULL_NAME AS OPERATOR_NAME,

  CASE WHEN TKT_SEVERITY_ALIAS = '0-Informational' THEN 1
        when TKT_SEVERITY_ALIAS = '1-Low' THEN 2
        when TKT_SEVERITY_ALIAS = '2-Medium' THEN 3
        when TKT_SEVERITY_ALIAS = '3-High' THEN 4
        when TKT_SEVERITY_ALIAS = '4-Urgent' THEN 5
        when TKT_SEVERITY_ALIAS = '5-Critical' THEN 6
   end ACTIVITY_SCORE
  FROM ARADMIN.SD_TICKET_ACCESS_LOG MX INNER JOIN VIEW_TKT_REPORTING ON TICKET_ID = TKT_ID
         WHERE UDF_CONVERT_UNIX_DATETIME(MX.DATE_TIME_ACCESSED, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
          AND UDF_CONVERT_UNIX_DATETIME(MX.DATE_TIME_ACCESSED, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY) 
          AND MX.SUBMITTER_FULL_NAME in (
		select rtrim(regexp_substr(mystring,'([[:alnum:]]*)(,)?',1,column_value),',') mystring
		from mydata,
	 	table(
	  	cast(
	  	multiset(select level from dual connect by level <= ((length(mystring)-length(replace(mystring,','))))+1)
	  	as sys.odcivarchar2list
	  	)
	  	)

)

/

Open in new window


It's based off this test case I had that converts a CSV to a list of values and checks them in a table.

with mydata as (
select 'a,b,X,d' mystring from dual
)
select * from dual where dummy in (
	select rtrim(regexp_substr(mystring,'([[:alnum:]]*)(,)?',1,column_value),',') mystring
	from mydata,
 	table(
  	cast(
  	multiset(select level from dual connect by level <= ((length(mystring)-length(replace(mystring,','))))+1)
  	as sys.odcivarchar2list
  	)
  	)
)
/

Open in new window

0
 

Author Comment

by:Maliki Hassani
ID: 39230249
Okay, will try..  

I must add that I am adding this query to a view if that matters any.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39230266
>>I must add that I am adding this query to a view if that matters any.

How do you plan on adding dynamic parameters to a view?  Oracle doesn't really do this.

Just create your view without the two IN pieces in the where clauses then add the parameters when you query the view.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39230272
Something like this:

create or replace myView as
SELECT distinct(OPERATOR_NAME) AS OPERATOR_NAME,SUM(ACTIVITY_SCORE) AS ACTIVITY_SCORE
FROM
(
--tickets created
SELECT REQUESTOR_FULL_NAME AS OPERATOR_NAME,

  CASE WHEN SEVERITY = '0-Informational' THEN 1
        when SEVERITY = '1-Low' THEN 2
        when SEVERITY = '2-Medium' THEN 3
        when SEVERITY = '3-High' THEN 4
        when SEVERITY = '4-Urgent' THEN 5
        when SEVERITY = '5-Critical' THEN 6
   end ACTIVITY_SCORE
  FROM ARADMIN.JOIN_CONSOLIDATED_STATUS_ASSIG
         WHERE UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
          AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY) 
          AND NOT(FORM_NAME = 'Maintenance')
UNION ALL
--tickets accessed
SELECT MX.SUBMITTER_FULL_NAME AS OPERATOR_NAME,

  CASE WHEN TKT_SEVERITY_ALIAS = '0-Informational' THEN 1
        when TKT_SEVERITY_ALIAS = '1-Low' THEN 2
        when TKT_SEVERITY_ALIAS = '2-Medium' THEN 3
        when TKT_SEVERITY_ALIAS = '3-High' THEN 4
        when TKT_SEVERITY_ALIAS = '4-Urgent' THEN 5
        when TKT_SEVERITY_ALIAS = '5-Critical' THEN 6
   end ACTIVITY_SCORE
  FROM ARADMIN.SD_TICKET_ACCESS_LOG MX INNER JOIN VIEW_TKT_REPORTING ON TICKET_ID = TKT_ID
         WHERE UDF_CONVERT_UNIX_DATETIME(MX.DATE_TIME_ACCESSED, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
          AND UDF_CONVERT_UNIX_DATETIME(MX.DATE_TIME_ACCESSED, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY) 
)
GROUP BY OPERATOR_NAME
/


select * from myview where OPERATOR_NAME in ('Jon Doe','Micahel Jordan') 

Open in new window

0
 

Author Comment

by:Maliki Hassani
ID: 39230276
Okay,

so I am a confused...  I updated the attached view that runs.  What are your thoughts on passing a variable for operator names?
Employee-Query-Example.txt
0
 

Author Comment

by:Maliki Hassani
ID: 39230278
Let me see if what you wrote works
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39230285
Here is Tom Kyte's solution for a parameterized view:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1448404423206
0
 

Author Comment

by:Maliki Hassani
ID: 39230401
So no luck..  I am trying to alter the view that i created but when I enter the new query it compiles it but won't save the new query.  

Also will the query be longer to as a runtime?  Is it querying the employee names after it scans the tables, and then uses the variable?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39230423
>> it compiles it but won't save the new query.  

I don't know what this means.  I assume you are using Toad or some GUI for this?  Sorry but I'm not a GUI user.

>>Also will the query be longer to as a runtime?

Probably but as I mentioned, Oracle doesn't really do parameterized queries.
0
 

Author Comment

by:Maliki Hassani
ID: 39230428
I found this on the internet  I notice that I am not setting the (parm1 INTEGER, parm2 DATE)

code found:  http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:906341500346611919


CREATE OR REPLACE VIEW VIEW_ABC (parm1 INTEGER, parm2 DATE) AS
  SELECT *
  FROM   XYZ
  WHERE  EXISTS (SELECT 'X'
                 FROM   QPR
                 WHERE  QPR.JOIN_COLUMN_1 = XYZ.JOIN_COLUMN_1
                 AND    QPR.ATTR_COLUMN_1 = (parm1)
                 AND    QPR.ATTR_COLUMN_2 = (parm2))
/

Then, all we need do is,

SELECT *
FROM   VIEW_ABC (101, SYSDATE)
/
0
 

Author Comment

by:Maliki Hassani
ID: 39230455
I see what you were doing now, you stripped off the filter of the names and when I go to query the view I include the operator names there.  

See what I was afriad of is that there will be a longer runtime.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Maliki Hassani
ID: 39230492
Not really any longer runtime.  So let me ask you this...

In my query
....OPERATOR_NAME in ('Jon Doe','Micahel Jordan'),
 what if in my union ALL queries i had SUBMITTER_NAME in ('jdoe','mjordan')

what could I do so when I group the Operator Names it does think these are 2 different people?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39230615
>>I found this on the internet  

You missed the wording here:  Oracle should introduce proper.

That was a post saying that Oracle really needs to do it.  It doesn't mean they currently do it.

>>what could I do so when I group the Operator Names it does think these are 2 different people?

Sorry but I'm not following.  Can you explain it a little more?  Maybe with sample data and expected results.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39231010
views do NOT accept parameters
You simply use a where clause

also: you do NOT need "DISTINCT" in that view.
(group by produces unique rows, there is no point in then asking for distinct)

You are also using a function on the source data UDF_CONVERT_UNIX_DATETIME which could slow down these queries. Could this be reversed? (apply a function to the SYSDATE so it is comparable to the source data.)

Then: I do not follow what you mean here:
....OPERATOR_NAME in ('Jon Doe','Micahel Jordan'),
 what if in my union ALL queries i had SUBMITTER_NAME in ('jdoe','mjordan')

what could I do so when I group the Operator Names it does think these are 2 different people?
If you have 2 operators with the same name - yes - your query would merge the data for 2 people together. To guarantee you get 2 people reflected in the results don't use name as the only group by field. (e.g. use a login id or something unique for each operator)

e.g.
CREATE OR REPLACE myTicketsCreatedView AS

SELECT /* "DISTINCT" is NOT required - at all */

      (OPERATOR_LOGIN) as OPERATOR_LOGIN /* this is a guess */
    , (OPERATOR_NAME) AS OPERATOR_NAME
    , SUM(ACTIVITY_SCORE) AS ACTIVITY_SCORE
FROM (
    --tickets created
    SELECT REQUESTOR_FULL_NAME AS OPERATOR_NAME
        , CASE 
            WHEN SEVERITY = '0-Informational'
                THEN 1
            WHEN SEVERITY = '1-Low'
                THEN 2
            WHEN SEVERITY = '2-Medium'
                THEN 3
            WHEN SEVERITY = '3-High'
                THEN 4
            WHEN SEVERITY = '4-Urgent'
                THEN 5
            WHEN SEVERITY = '5-Critical'
                THEN 6
            END ACTIVITY_SCORE
    FROM ARADMIN.JOIN_CONSOLIDATED_STATUS_ASSIG
    
    /* is it possible to reverse the date conversion here? 
       i.e. apply logic to the SYSDATE so that it can be compared directlt to the data
            to avoid applying functions on the data
       e.g. CREATE_DATE >= UDF_CONVERT_TO_UNIX_DATETIME(TRUNC(SYSDATE) - INTERVAL '1' DAY)
    */
    
    WHERE UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
        AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
        AND NOT (FORM_NAME = 'Maintenance')
GROUP BY
      (OPERATOR_LOGIN) /* this is a guess */
    , (OPERATOR_NAME)

Open in new window

It might be worthwhile creating 2 views instead of one, one for Tickets Created, and another for Tickets Assessed - but this would only be worthwhile I guess if you needed one part of this information without the other.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39231013
by the way, as an alternative to using views, you could use a "table function" and these do accept parameters.
0
 

Author Comment

by:Maliki Hassani
ID: 39231536
Thank you for your posting...

I apologize for the confussion but I made a grammar issue it should be "doesn't think these are two people".
Error found below:
>>what could I do so when I group the Operator Names it does think these are 2 different people?

This is the only issue that I can see with creating my query in a view.  The reason I need to consider the submitter full name and the user ID is because in some of my union all statements the tables may not have the full name field do I end up using the login ID. However, when I run the full query it will group these user Id
0
 

Author Comment

by:Maliki Hassani
ID: 39231550
Continued:
Group these user ID's and not link them to the full names.  If there was a case statement that I could add case when Operator_Name in ('John Doe', 'jdoe') then 'John Doe' else .....
Maybe that could work.  Perhaps there is a better way to do this.


I was also going to ask about putting it into a function, like you mentioned.
If I did that, I would have to create a table do that the function would be storing the data , right? Meaning executing the function once a day so I can report on yesterday's stats.

Would you be able to show me how I would at the parameters to my query? Let's call the function "my function".

Thanks!!
0
 

Author Comment

by:Maliki Hassani
ID: 39231564
Here is another thought to go with the function request.

Not sure if this is possible here but let me ask...  Could we create a function that knows the names of the employees and still have the view.  However, when I query the view I add the function name?  So thinking this:
Select *
From myview
Where Operator_name in (myfunction)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39232417
>>Group these user ID's and not link them to the full names.
IF you are able to, yes absolutely, in the view group by something more reliable than name.

You could leave names out of the equation until the last possible moment overall (e.g. after using the view join to the user table and get the names that way in the final output.)

>>If I did that, I would have to create a table do that the function would be storing the data , right?
No. When I mentioned "table function" I did not mean you need a table. The function returns data "as if it were a table" - they are better known as "pipelined table functions".
e.g. http://www.akadia.com/services/ora_pipe_functions.html
http://www.oracle-developer.net/display.php?id=429

and as mentioned they do support the use of parameters.

I'm not convinced however you need to go that far, one or more views should work for you.

Assuming you do start using UserID inside the view (not name) then a function to return a userID from a name MIGHT be useful :: note this is the reverse of what you indicate above

select
userID, Activity_Score
from MyView
where UserID = MyFunction('Persons Name') -- the function returns a UserID

NB: this type of function is a "scalar function" it returns just one value

I think it is more likely you would use something like this:

select
 u.UserName, u.userdID, v.Acttivity_Score
from user_table u
inner join MyView  v ON u.userID = v.UserID
where u.UserName in ('John Doe','Michael Jordan')
0
 

Author Comment

by:Maliki Hassani
ID: 39232440
Wow, that makes so much sense...  It is crazy how much you learn on EE.  Just learned a lot.. Thank you!  I will check and see if all my queries have the User_ID , rather than going with full names.  If not, I will follow more of your suggestions.

I am interested in practicing making functions like you mention I will make another post for that one.  

Let me keep this question open till Mobday when I return to work.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39232449
ok, no problem.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39232642
by the way, if you want to grapple with functions you could look at this:
http://www.orafaq.com/usenet/comp.databases.oracle.misc/2004/09/02/0035.htm
note you will have to deal with the timezone issue in doing this.

I mentioned above that your view would be more efficient if you reversed the way you handle the date filtering. Currently you are executing a function twice for every row of data. If however you can find a way to convert sysdate to an appropriate unix timestamp then you only execute the function on sysdate and also maximize use of indexes in the data.

It is almost always preferable to modify the filter criteria to suit that data instead of modifying data to suit the criteria.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now