How To Execute Part Of WHERE Clause Conditionally
Oct 30, 2010
I need to insert Data into a table based on particular condition, So I'm using an Insert select as follows:
INSERT INTO error_table
(error_code,cust_id,customer_name,error_message )
SELECT '34', cust_id,customer_name, 'Missing xxx'
FROM customers
WHERE cust_id = cur_cust.cust.id
AND ( NOT EXISTS ( Select .....) -- control 1
OR (EXISTS ( Select .....) --control 2
OR (NOT EXISTS ( Select .....) -- control 3
I have around 10 controls. I want these controls to run conditionally..I was wondering if this can be done using a decode based on some variable outside the query.
I want the query to run like this:
if A then execute control 1: AND ( NOT EXISTS ( Select .....)
If B then execute control 2: AND ( OR EXISTS ( Select .....)
View 2 Replies
ADVERTISEMENT
Sep 23, 2010
I'm trying to optimize an application running heavy updates/inserts/deletes, by having it using bind variables instead of "string queries".
The columns to be updated can vary (possibly from one column to all columns of a table), thus I have made som logic to build the query accordingly. My problem is now that I cant get the EXECUTE below to handle the VARRAY passed in the USING clause, it fails with "PLS-00457: expressions have to be of SQL types"
Environment:
create table table_x (a varchar2(10),b varchar2(10),c varchar2(10),d varchar2(10));
insert into table_x values('a','b','c','d');
commit;
Code, simplified with static number of columns:
declare
type v is varray(10) of varchar2(20);
v_values v:=v('A','B','c');
myupdate varchar2(2000);
begin
myupdate:='update table_x set a=:a, b=:b where c=:c';
dbms_output.put_line(myupdate);
execute immediate myupdate using v_values;
end;
I've understood that I cannot send TABLE type variables by USING, but this should be an VARRAY.
View 11 Replies
View Related
Nov 3, 2013
I have 3 pages: - Classes: a Classic Report that shows a list of classes and summarises attendance- Class Attendance Report: An Interactive Report that shows a row for each student that attended a class- Attendance Add / Edit Form On the Classes page, the Date value is a Link which passes a filter in the URL to the Class Attendance Report, showing only students who attended the class held on this date On the Class Attendance Report, I want to have a Region Button that, when clicked, goes to the Attendance Add / Edit Form and the Class Date field on this form is automatically populated with the same date value as was being used for the filter on the Interactive Report on the Class Attendance Report Page. So, I click on the Class Date link for 2013-11-01, this takes me to the Class Attendance Report and filters for students who have been already entered as attending on 2013-11-01. If I click on the "Add Attendance" button, it goes to the Attendance Add / Edit form and the Class Date is automatically set to 2013-11-01. I assume I need to do this via a Dynamic Action that runs when the "Add Attendance" button on the Class Attendance Report page is clicked?
View 6 Replies
View Related
Nov 2, 2012
I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.
The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:
Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;
But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?
View 2 Replies
View Related
Jan 21, 2013
I want to conditionally re-arrange the serial. Sample test case is as below.
DROP TABLE t1;
CREATE TABLE t1
(
[Code].....
SQL> Select * from t1 order by no;
NO T
---------- -
1245 R
1246 N
1247 N
1248 R
1249 R
1250 R
1251 N
1252 N
1253 R
1254 R
1255 N
11 rows selected.
Required output is
OLD_NO T NEW_NO
---------- - --------
1245 R 1245
1246 N 1247
1247 N 1251
1248 R 1248
1249 R 1249
1250 R 1250
1251 N 1252
1252 N 1255
1253 R 1253
1254 R 1254
1255 N 1256
Scnario is to add 1 in no. field but no.s having type 'R' will remain unchanged. e.g. Record no. 1247 is changed to 1251 because 1248,1249,1250 have typ 'R'.
View 12 Replies
View Related
Nov 12, 2012
APEX 4.2 Theme 25 Oracle DB 11gr2 - I need to conditionally display Html5/Flash chart regions depending on browser compatibility, how to achieve this.
View 8 Replies
View Related
Oct 23, 2013
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS
(SELECT ph.* FROM po_header ph WHERE 1 = 2),
pf AS
(SELECT DISTINCT pf.order_id, pf.fund
FROM po_fau pf, ph
WHERE 1 = 1
AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746
po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDL_Cursor, sSQLStr, 2);
FOR x IN 1 .. nCols LOOP
sCols(x) := '';
dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100);
END LOOP;
nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
View 14 Replies
View Related
Jan 8, 2009
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
View 5 Replies
View Related
Apr 30, 2007
why this query works fine:
SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK
FROM zajezd Z
ORDER BY Z.JMENO
But when I try to use the alias to filter the results like this (note the WHERE part):
SELECT Z.ZAJEZD_ID, Z.JMENO,
(
SELECT COUNT(ZAJEZD_ID)
FROM trasa T
WHERE T.ZAJEZD_ID = Z.ZAJEZD_ID
) AS POCET_ZASTAVEK
[code]...
Then it throws an error message ORA-00904: Unknown Identifier for the column/alias POCET_ZASTAVEK?
View 2 Replies
View Related
May 10, 2013
How can i convert the time part into second. for example
in mu table i have below rows
1753-01-01 09:18:00.000
1753-01-01 09:52:00.000
1753-01-01 09:36:00.000
I want to convert the time part of each rows to number of second in different column. so for the 1st rows it is 09:18:00.000 that means 9hour and 18 min = 9*60*60+18*60.
But that is complex column.
View 14 Replies
View Related
Feb 25, 2010
I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:
SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID
However, I only need the DATABASE piece selected in my FOR loop below.
CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;
[Code]...
View 2 Replies
View Related
Jan 9, 2013
To make SQL query to before and after specific character.
Create table test(flist not null VARCHAR2(200));
First field content with below record:
FC028CONNE_IMPORT_WRONG_COMP_LENGAPXXXXPPPP
FC024CALL_FUNCTION_OPEN_ERRORAPXXXXPP
FC025OPEN_DATASET_NO_AUTHORITYAPXXXXPPPPPPPPPPPPPP
FC015RAISE_EXCEPTIONAPAXEPPPPPPPPPPPPPPPPPPPP
to filter the above record from FLIST column thorugh sql script as below:
FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP
means remove first 5 charator and after APXXXXXXXXX.
Output of SQL query should come like below:
CONNE_IMPORT_WRONG_COMP_LENG
CALL_FUNCTION_OPEN_ERROR
OPEN_DATASET_NO_AUTHORITY
RAISE_EXCEPTION
View 24 Replies
View Related
Apr 14, 2010
I started one packaged procedure in plsql developer yesterday..thats being run from yesterday...I wanted to know what part of the query that is being run now from the procedure.
View 23 Replies
View Related
Mar 25, 2010
In a table, I have a column in a table with the following values:
targetcol
30 WD
180 WD
70%
0
80%
180 CD
70%
0
7 WD
2 WD
0
-
-
-
-
-
-
-
-
-
where WD is working days and CD Calendar days.
I just need to extract number from above.
View 11 Replies
View Related
Feb 13, 2012
I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.
View 3 Replies
View Related
Oct 16, 2012
For an entered date, I want to hardcode the time part as 18:00:00, and rest of the date should be whatever is passed as input, how can i do this?
View 4 Replies
View Related
Jun 14, 2012
i am working on sql server developer in on of the organization. i got requirement from client that migration part from oracle 6i forms to oracle 10g and i got existing forms backup from client that is have .fmb, .mmb, .rdf and some of libraries having .pll files, but am new to oracle which form need to open from form builder first?
i tried to open .fmb file given database connection also but it is showing some libraries are missing. what should be the solution for my problem.
View 1 Replies
View Related
Mar 11, 2010
Select * from XXX where xxx.a in �123,456�-----> this values comes dynamic to my query.
Here xxx.a will contain values like 123 or 456 like that .
View 6 Replies
View Related
Apr 30, 2013
I work with Oracle Forms and Report Builder since several weeks, but now i have something that i want to do with Report Builder.
I get string from DB (It's a paragraph) But i would like to set bold weight on a part of the string
for now, i set bold text i want to and i have created a fonction that make text bold like this
l_chaine := (SUBSTR(MyStrFromDB, InSTR(UPPER(MyStrFromDB), '[B]'),InSTR(UPPER(MyStrFromDB), '[/B]') ));
if(length(l_chaine) > 0) then
SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT);
end if;
but it doesnt look to works..
how can i do that? And where i can do that?
View 2 Replies
View Related
Aug 31, 2010
i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"
-------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER)
IS
C DIA_RL_PKG.CURSOR_TYPE ;
[Code]....
View 2 Replies
View Related
Jul 8, 2012
I have a table
select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002
2 rows selected
select * from testing where name like ('GHI,TCF');
now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.
View 9 Replies
View Related
May 11, 2011
I have a column where the values are like
SQL> SELECT distinct a.legacy_product_code FROM im_polprdmap_pukka a where a.new_product_code like '%GIFT%';
LEGACY_PRODUCT_CODE
-------------------
GIFT25
GFTPF21NP
GIFT21/H
GIFT21//
[code]...
I need to calculate a field taking the numeric part of the above filed.How can i write this?
View 2 Replies
View Related
Jun 8, 2011
I want to write a plsql program to display half part of a string .Example there is a string like gillmadden@myharbour.org.uk
I want to display only the later part of the string
myharbour.org.uk as output.
Can we write using string functions in plslql?
View 19 Replies
View Related
May 17, 2011
I have a column where I have time stamp with date and time(8-May-10 10:20:55 Am) from that i will get only time(10:20:55 Am)[query for this mentioned below] Now i need to append the time to current date and insert into a Timestamps column only .
so my result should be 17-May-11 10:20:55 Am(Assuming current Date is 17-May-11) . I tried search it by i found a way in sql server by using DataAdd and DataDiff .
query to get the time is:select to_char(to_date('11-MAY-11 05.00.00 PM', 'DD-Mon-YY HH:MI:SSAM'),'HH24:MI:SS') "Time Now" from dual
View 1 Replies
View Related
Jan 18, 2013
There is a readonly user on our reporting server. Developers want to use global temporary tables with this user. I don't want the user to have permissions other than readonly.I can grant the user CREATE TABLE privilege and did not grant quota on any permanent tablespace, therefore user would not be unable to create permanent tables but still should be able to create global temporary tables.
Question is would a user with such permissions still be able to utilize temp tables as part of a scheduled job?
View 2 Replies
View Related
Sep 10, 2013
I'm using following select to parse XML as table ( Oracle 11.2.0.3.0 EE Linux and Windows)
SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........
However, when DESCRIPTION contains "<" as part of it - I'm receiving an error:
SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........
Quote:ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: Invalid character 61 ("=") found in Name or Nmtoken
I'm receiving a XML from some external source and can not change it.
View 8 Replies
View Related
Aug 1, 2012
I am trying to create a silent install as part of a batch file that does other things after. I'm running into the following issues
1) I need the batch file to wait until the install is done to continue. Trouble is that running setup with a response file opens up a new windows to run the install. I tried the wait command but doesn't work right either
2) Same issue with DBCA and a response file.
Any way to run the silent installs and have the batch file wait until the install is done and then continue? The less user intervention required, the better.
View 11 Replies
View Related
Sep 14, 2012
For our customer we have build a custom login page. The form we currently made has de following items:
- Username
- Password
- Select list for selecting the task type
When the customer enters his username, automatically the select list of the task type will be refreshed to get only the task type where the customer has the rights to do it. This part works...It can occur that a customer has only one task type (which is default) then we want to hide the select list. This part doesn't work
We used APEX version 4.1.1.What we did is:
- One action item to refresh the selection list (works)
- One action item to hide the selection list by starting the application (works)
- One action item to show the selection list only when the selection list has more than one result. For this part we used the conditional part of the action item (Function returning a boolean).
In the query (inside the function) we want to use the entered username, looking into the application in the session state I saw the correct username, but for some reason the function cannot use this entered username, it becomes empty. When I use a hardcoded username the code works.
Why I cannot use a username into the conditional part of the Action Item.
View 3 Replies
View Related
Mar 7, 2013
When I see the apex dashboard in the IE or google chrome, part of screen goes missing.Last part of dashboard doesn't come but it works fine in Mozilla Firefox.What should I do? Is there some settings I need to do in IE.I am using the IE version 9.0 for 64 bit.
View 0 Replies
View Related
Jan 14, 2013
if it is possible to include 'data' files as part of the Application Export process. Up till now I've only been able to include supporting object files which will recreate a table structure but not the data records - I've been creating the latter outside of APEX.
View 2 Replies
View Related