So this was my Error:
and (upper(u.sStatus) like 'Vacant Rented%' or u.dtAvailable is Not Null )
As soon as I put 'Vacant Rented' in all caps I got the record back even though the u.dtAvailable was NULL.
West Coast, off to dinner.
patrick
Rest assured, I was wrong.
Thank you for pushing me.
You are correct that "Is Not Null" evaluates just fine.
I made a stupid mistake with case sensitivity and I should know better in oracle. For what its worth I had Upper(Field2) like.... but did not have the right side of the equation in upper...
Mufasa,
I would love to believe you. However, I have had a lot of prior experience with Oracle (at lease 8 or 8i) where a field that truely is NULL will not evauate ture against anything except "is Null". In my test sample I have isolated the single row and If I ask for just that row by its...
Let me try to be more clear.
I want to return true if either: Field1='aName' or Field2 is not Null.
My problem is when I find a record where Field1='aName' usually Field2 is actually NULL, which is fine by me but my understanding of Oracle and NULL is that a null field can not be evaluated...
I have a query (works just great in SQL Server 200) in which I need to test on one of two conditions which due to my schema are usually mutually exclusive of each other.
Snipit:
...where (Field1 = 'aName' or Field2 is Not Null)
The problem is for the most part when Field1='aName', Field2 is...
I have An ASP file in which I create two msxml2.domdocument objects. Populate one with an xml string, and the other with an xsl string. Then translate the xml using the xsl. Finally load the final translated xml into the xls object to write it to the hard drive. This seems to work in 3 out of...
In my experience with Javascript and ASP to use the Javascript it goes inside an html like tag such as
<script language=javascript>
funciton(){
//some logic here
}
</script>
I would keep the language as it is b/c the ASP will need to run on the server as vbscrip but the JavaScript will be...
OK, thanks that table user_objects table will allow me to determine if the SP exists but now I have found that I am unable to drop a procedure from within an IF block.
Here is more or less what I would like to do.
*****************
Declare v_exists numeric;
Begin
Select 1 into v_exists
From...
IN SQL Server I am aware of two statements that drop the caching of a query plan. I am looking for the equivalent in Oracle to use when optimizing a query. The SQL Server statements are.
dbcc dropcleanbuffers
dbcc freeproccache
Need to be able to check if a Stored procedure already exists.
I am familiar with SQL Server code as in
Begin
If Exists (select name from sysObjects where
name = 'procName' and type = 'P)
Drop procedure 'procName'
End
Can I replicate that in concept Oracle.
I don't plan to replace...
Thanks,
That worked.
Unfortunately toggling the flag variable between yes and no does not affect the execution plan. I am wondering if the actual speed in a real query would be affected.
Thanks for the help.
Patrick
Did you consider the mapping table
Update your_table
Set p_code = mt.A_code
From mappingTable mt
Where your_table.LoginDate between dt1 and dt1
and your_table.p_code is null
and mt.A_code = your_table.A_Code
This would require you to have all possibe P_Codes in the mappingTable for all...
For starters
select * from sysobjects where type = 'P'
will give you all the SP's in the DB
Then I belive you can add to the where clause
..and uid in (0,1)
These are the UID's from the SysUsers table
0=public
1=DBO
Yes, w/o examples it would be difficult to speculate.
However, you may consider using a mapping table. A static table with all the 800 combinations built in as 800 records then you can do your update by joining to this table.
Patrick
The error is
"invalid object name '#temp1' "
I have tried both # and ##. The query runs fine but the error is returned when I try to get the execution plan
************************************************
Begin
Declare @cat_flag varchar(10)
Declare @dog_flag varchar(10)
set @cat_flag = 'yes'...
Karl,
re: performance hit with the 'NO' parameters.
Two reasons why I think I am getting them.
1) the execution plan shows a significant cost for those parts of the unioned select where parameter= NO
2) In real time reporting/execution it seems that the SP is taking the same amount of time...
In an attempt to compare two diff. SP methods I have created a T-sql batch to replicate the SP for the purposes of looking at the execution plan. One of my tests uses a simple temporary table but Quary Analyzer will not give me a plan instead I keep getting an error however I can run the query...
Thanks, I think you did answer the question.
If the Union does implicictly create a temp table then by me explicitly creating one I should be able to only execute the "insert into" statements that I want based on the parameters that I have passed in, which should be faster thant the original...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.