PL/SQL :: Dealing With Apostrophes In Query
Dec 12, 2012
I have this very simple SQL statement:
SELECT e_id AS id,
'javascript:$(''P4_ID'','|| e_id ||'); openModal(showEvent);' AS url
FROM event_info evtwhich returns
86 javascript:$('P4_ID',86); openModal(showEvent);
87 javascript:$('P4_ID',87); openModal(showEvent);
88 javascript:$('P4_ID',88); openModal(showEvent);
89 javascript:$('P4_ID',89); openModal(showEvent);
But I'd like it to return the following (apostrophes surrounding the e_id)
86 javascript:$('P4_ID','86'); openModal(showEvent);
87 javascript:$('P4_ID','87'); openModal(showEvent);
88 javascript:$('P4_ID','88'); openModal(showEvent);
89 javascript:$('P4_ID','89'); openModal(showEvent);
And I am struggling with figuring out how to get this working.
View 2 Replies
ADVERTISEMENT
Aug 14, 2012
I have developed a Form for the most part everything is working as expected. However, there is a search functionality that is giving me problems. The issue is that when the user enters search criteria for last_name that has an apostrophe (O'brian) the search lov doesn't get populated because the dynamic record group is not getting created when the string has an apostrophe (ie O'brian). I have a dynamic record group that takes the user's search criteria and populates an LOV on the screen with the records that matched their criteria.
Here is the code that is behind my search button where the dynamic RG gets created. It works fine for all searches that don't contain an apostrophe.
DECLARE
p_where_debtor varchar2(2000);
p_where_liab varchar2(2000);
rg_id RecordGroup;
[Code]....
errcode := Populate_Group(rg_id);
View 5 Replies
View Related
Oct 8, 2011
I am dealing with a bunch of tables containing sales information for an New Zealand organisation. The sale datetime has been recorded as UTC.
New Zealand operates Daylight Savings, so twice a year it changes its clocks.
When New Zealand is on standard time it is UTC+12.
When New Zealand is on daylight savings time it is UTC+13.
Thus an event which actually occurred when New Zealand was on standard time at 2011-08-31 15:20:52 local time, is recorded in the database as having occurred at 2011-08-31 03:20:52. However, an event that actually occurred when New Zealand was on daylight savings time at 2011-10-06 15:20:52 local time, is recorded in the database as having occurred at 2011-10-06 02:20:52.
I want to be able to read the sales dates from my table and convert them to the actual time in New Zealand when the event occurred. The table will contain data for sales that occurred in both standard and daylight savings times.
I do not think that the data has been stored with time zone information, simply that the application writing the data to the Oracle database, calculated the event time as UTC when it occurred and wrote that time to the table.
Does Oracle only know about what UTC-offset is in force right now or is it capable of determining what offset from UTC is required for any given historical date ?
View 6 Replies
View Related
Mar 12, 2011
When working against the Oracle 10G/11G RAC database it's important to have all the relevant entries in the etc/hosts file.
Thus, when running the following code against the database sometimes it finishes with "Ok!" and sometimes getting the error below:
CODEimport java.sql.*;
public class TestDBOracle {
public static void main(String[] args)
[Code].....
The reason is missing RAC-related entries in the etc/hosts.
When dealing with Oracle RAC it should be both entries for the public and for the virtual IPs of the nodes in the etc/hosts file.
CODEA.A.A.A NODEA
A.A.A.B NODEB
...
B.B.B.X NODEA-VIP
B.B.B.Y NODEB-VIP
...
View 1 Replies
View Related
Oct 21, 2010
I am working with oracle 10g / 11. I need to operate with CLOB values of some table, unknown for me: I read them from .Net (3.5), and have to save them to files (Windows) to be loaded after this with DBMS_LOB. Lets say that table contains 3 columns (1 - CLOB) and several rows.I want to store each CLOB value to separate file, and to store it after this into another DB table (another Instance, also).I cannot use dblinks and other techniques like this.
The point is - how to avoid dealing with encoding?
I just want to save the CLOB values to the files, with no meter about Database Instance character set, language, and so on. If I use default for .Net StreamReader UTF-8 format, the loading with DBMS_LOB failed.
what will be the best way to determine the encoding, and to convert Oracle used encoding to .Net one?
View 6 Replies
View Related
Feb 22, 2013
Previously we had 32 bit C++. Now, we have migrated it to 64 bit. And our C++ programs interact with Oracle 10g DB.Our C++ program was working fine with 32 bit. But once after we migrate to 64 bit we are facing problem with one program which does FETCH(EXEC SQL FETCH SUBP1 INTO :new TabRec;) from Oracle DB. ie, We exit from a for loop in the C++ program when we get NOT FOUND(sqlca.sqlcode=1403) on executing the FETCH statement.
The sqlcode generated for NOT FOUND scenario is 1403. But, once after moving to 64 bit C++, we do not see the sqlcode 1403 instead we are seeing a different code 7124089117159473.
As the sqlcode is not 1403, our program does not exit from the for loop and goes on an infinite loop.Am I missing anything that makes me to not get the exact sqlcode?
View 9 Replies
View Related
Dec 8, 2005
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
View 6 Replies
View Related
Dec 5, 2012
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
View 1 Replies
View Related
Apr 26, 2013
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800
XML File
<e_id>
143
876
So, I want the output to be:
e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100
View 2 Replies
View Related
Aug 7, 2009
I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
View 8 Replies
View Related
Jun 19, 2012
I have the following four tables with the following structures Table A
ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE
Table B
ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE
when these two tables were joined like the following:
Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)
Now i have to join the next table C with table B
Table C structure is
ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer
I have the common column such as ColD2 and colB4 to get the Col D3
how do i join the existing query + join between table b and table c?
View 4 Replies
View Related
Jul 17, 2011
how to achieve F11(Query mode) and Execute Query in Oracle Forms?
View 1 Replies
View Related
Apr 6, 2010
I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.
For example:
select
x,y,z
from
mytable@dblink
returns 788,324 rows
while
select
x,y,z
from
mytable
returns 712,102 rows
It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.
View 10 Replies
View Related
Mar 10, 2012
Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?
Background:
We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.
The legacy HR system allows multiple phone numbers to be stored in a simple table structure:
SELECT emp_id, phone_type, phone_number
FROM employee_phones
emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333
The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)
From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.
That can be represented as:
SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333
Or similarly with Top N concept:
SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1
emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
Or really what I want in my export:
SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1
phone_number
-------------------
1111111111
However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.
(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)
1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?
2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?
Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.
View 3 Replies
View Related
Sep 19, 2010
I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.
Query 1 :-
select a.col1,a.col2,b.col3,sum(b.col4)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3
Query 2 :-
select a.col1,a.col2,b.col3,sum(b.col6)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3,b.col5
View 4 Replies
View Related
Sep 18, 2012
This query is written in inner join, can any one try to write using sub query.
SELECT B.CNO
FROM CUSTEN A
INNER JOIN ORDS B
ON A.CNO = B.CNO
AND A.PRNO = B.PRNO
[Code]...
View 4 Replies
View Related
May 24, 2010
I have the folloiwng two queries:
Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;
I need to compute the following:
var:=(yy/zz)*100
How can I achieve this in a single query?
View 3 Replies
View Related
Feb 23, 2012
I have a a table like with columns ( date_field, client_id(c_id), transaction_id(trx_id), mobile, amount )
table example data like
date_fieldc_idtrx_idmobileamount
24-JAN-1215100100120111111100100
24-JAN-1217100100220111111112150
24-JAN-1215100100320111111113100
24-JAN-1216100100420111111114200
24-JAN-1215100100520111111115100
24-JAN-1216100100620111111116100
24-JAN-1218100100720111111117100
24-JAN-1216100100820111111118100
24-JAN-1215100100920111111119200
24-JAN-1216100101020111111110100
24-JAN-1215100101120111111111100
24-JAN-1216100101220111111112100
24-JAN-1215100101320111111113100
Now using the unique index (Trx_id) I need to get max 3 records for each client (c_id).
Expecting result should be
date_fieldc_idtrx_idmobileamount
24-JAN-1217100100220111111112150
24-JAN-1218100100720111111117100
24-JAN-1216100100820111111118100
24-JAN-1216100101020111111110100
24-JAN-1216100101220111111112100
24-JAN-1215100100920111111119200
24-JAN-1215100101120111111111100
24-JAN-1215100101320111111113100
View 4 Replies
View Related
Mar 4, 2009
I have this query and I want to get the COUNT:
SELECT first_np AS n_p FROM dvc
UNION
SELECT second_np AS n_p FROM dvc
UNION
SELECT n_p FROM dc;
This returns one column which is the n_p; how do I get the count of n_p?
View 2 Replies
View Related
Apr 18, 2008
I am facing problem with a select query in oracle 10g database from vb.net.It was working for oracle 9. The select statement I have written is as follows
Str=" select UCC.table_name, UCC.constraint_name, UCC.column_name, UCC.position, UC.constraint_type " & "from USER_CONS_COLUMNS UCC,USER_CONSTRAINTS UC " & "where (UCC.constraint_name = UC.constraint_name) " & "and UC.constraint_type = 'P' " & "and UCC.table_name = " & " '" & TableName & "'"
View 2 Replies
View Related
Jun 25, 2013
0 down vote favorite
I have one table in database that contains 3 foreign keys to another tables(this three tables name are: manager,worker and employee). in each row only one foreign key is filled.I need to write one query that with attention which column of fk is filled in where clause specified condition is performed. I write simple query in jpa but doesn't work properly
select b from allEmployees b where b.manager.name= :name OR b.worker.name = :name OR b.employee.name= :name
View 1 Replies
View Related
Jul 24, 2010
How do I get a query of each sequence and who has the permissions to it?
View 1 Replies
View Related
May 12, 2013
How to use see the query plan.
View 1 Replies
View Related
Dec 18, 2012
I am looking to get the maximum value for every 24 hour period for a month. So for example my date range can be defined by...
select to_date('&date','mm yyyy')-1 + level as DateRange
from dual
connect by level <= '&days'
...where I can provide the first date of the month and number of days in the month or a lesser value if less time is required. So, the results of the above query plus 24 for the range. I thought a some Googling would provide me what I needed, but my search came up empty.
I was hoping to do something like this...
select utctime, max(value) from table where utctime between.
View 3 Replies
View Related
Oct 13, 2011
I am facing the following issue while generating xml using a sql query. I get the below given table using a query.
CODE ID MARK
==================================
1 4 2331 809
2 4 1772 802
3 4 2331 845
4 5 2331 804
5 5 2331 800
6 5 2210 801
I need to generate the below given xml using a query
<data>
<CODE>4</CODE>
<IDS>
<ID>2331</ID>
<ID>1772</ID>
</IDS>
<MARKS>
[code].....
NOTe: IDS which are distinct needs to be displayed. ALL MARKS should be displayed though there are duplicates.
View 1 Replies
View Related
Mar 11, 2009
SELECT so.* FROM shipping_order so WHERE (so.submitter = 20)
OR (so.requestor_id IN (SELECT poc.objid FROM point_of_contact poc WHERE poc.ain = 20))
OR so.objid IN (SELECT ats.shipping_order_id FROM ac_to_so ats WHERE (ats.access_control_id IN (selectac. objid FROM access_control ac WHERE ac.ain = 20
OR ac.group IN ('buyers', 'managers'))))
rewrite this query to use joins. That would greatly simplify my sql query building code. The ids, objids, submitter, ain are numeric and group is a varchar.
View 1 Replies
View Related
Feb 24, 2009
"Determine what departments are in each of the 4 regions. That is, what are the names of the departments that reside in each of the regions." So basically it wants me to list the departments in each region.
This is what the tables look like that it could possibly be drawn from...
Regions
region_id
region_name
Departments
department_id
department_name
manager_id
location_id
Locations
location_id
street_address
postal_code
city
state_province
country_id
Countries
country_id
country_name
region_id
View 3 Replies
View Related
Mar 4, 2013
query for below requiremnet :-
I have table A having column name Varchar2(10), Seq Number ,Address varchar2(20)
Data in table as
name SeqAddress
A1bangalore
A2karnataka
A3India
B1Mumbai
B2Maharastra
B3India
I need to write query to get below Output
Abangalore,karnataka,India
BMumbai,Maharastra,India
I can not use any inbulit function of oracle like "SYS_CONNECT_BY_PATH", "LIST_AGGR" or any other function , even can not use any user defined function.Need to write only SQL to get this result.
how can we get above result
View 6 Replies
View Related
Sep 18, 2011
I have a query in this code?
declare
old_name varchar2(20) not null:='stephen';
new_name old_name%type;
begin
dbms_output.put_line(new_name);
end;
will new_name variable will inherit the datatype only or the default value as well?The output of this code will be stephen or not?
View 7 Replies
View Related
Jun 12, 2013
I have EMPLOYEE table that have 3 records with EMP_ID 1, 2, 3. Now I want to run below query
select emp_id from employee where emp_id in (1, 2, 3, 4, 5);
It will return only 3 records but i want those records also which is not available in employee table. Is this possible without using another table or creating another table. Actually I don't have enough privileges to create table.
& want output like below
EMP_ID
1
2
3
4 Not Found
5 Not Found
Here emp_id 4, 5 is not available in employee table, but query should return those value also with comments like "Not Found"
View 6 Replies
View Related
