SQL & PL/SQL :: How To Incorporate SUBSTR Into The Query
Aug 19, 2010
This piece of code returns this below:
RPAD(LPAD(' ',12, ' ') || SUBSTR(adj_second_line,(INSTR(adj_second_line,'~',1,1) + 1),
(INSTR(adj_second_line,'~',1,2) - INSTR(adj_second_line,'~',1,1)
- 1)),80,' ') ADJ_SECOND_LINE
Current Output ( ADJ#1-2M1YE2 TYPE 20100524 20100624 1MO/0DY )
===============================================================================================
1) I would like to alter that code above and substr the back 68 characters only with the 12 spaces padded in front, and the trailing nulls to total 80 characters staying as is. How would I put the below substr into that piece of code above?
Desired Output ( 20100524 20100624 1MO/0DY )
SUBSTR(adj_second_line,LENGTH(adj_second_line)-69)
===============================================================================================
2) I also would like the take whatever else there is preceding the 69th character counting it from the back and calling the column ADJ_FIRST_LINE.
Desired Output2 (ADJ#1-2M1YE2 TYPE)
No leading or trailing nulls and This is not a fixed amount of characters.
===============================================================================================
Result would be:
ADJ_FIRST_LINE
-----------------
ADJ#1-2M1YE2 TYPE
ADJ_SECOND_LINE
---------------
20100524 20100624 1MO/0DY
===============================================================================================
View 10 Replies
ADVERTISEMENT
Sep 26, 2012
User table
id | name
----------
2 | Harry
3 | Mary
Course_User table
summer_course_completed | winter_course_completed | user_id | attendance
------------------------------------------------------------------------
y | n | 2 | 20
y | n | 2 | 40
y | y | 2 | 30
n | n | 3 | 20
n | y | 3 | 60
I wish to list each student's name with the number of summer courses he has completed and the number of winter courses he has completed. I am trying this :
select u.name, count(*)
from user u, course_user cu
where u.id=cu.user_id and cu.summer_course_completed = 'y'
group by u.id;
but I can get only the number of summer courses OR the number of winter courses that each student has completed, but never both simultaneously, through a single query. Is there a way to do that ?
View 7 Replies
View Related
May 12, 2008
I am using 10g Database and 10g forms/reports on Windows.
NLS_LANG = AMERICAN_AMERICA.AL32UTF8 for both database and forms/reports. I set this from REGEDIT.
I want to use, Arial Unicode Ms or Mangal font to add or view the data in MARATHI (Regional).In the database browser, I can see the ported data properly with these two fonts, but through form if I try to add then it not showing proper characters, it showing squraes instead of typed characters.
I also typed a label in the form, at design time it showing proper word, but at runtime it not showing proper letters. This I observed only for joined characters.If I generates the report in PDF, format then it not showing data properly. but in runtime it showing properly.
View 15 Replies
View Related
Aug 3, 2011
At the moment, we were loading the file in our system serially. This is a very old and established system.We would like to incorporate parallel loading for our loaders to load data into the database.
Most of the issues would be due to multiple inserts happening due to the files being loaded in parallel. For some reasons, we cannot give regular commits untill the entire batch of items is processed in case the process needs to rollback. A file can contain different set of batch of items clubbed together for loading.
The issue here is untill the first file finishes loading and commits, the second file would just hang. In fact, mulitiple files might hang for the first file to finish. what can I do to overcome this?I tried to used "lock table t1 in SHARE ROW EXCLUSIVE mode nowait". When the leading process is doing inserts, the failing process will fail with a resource busy and acquire with NOWAIT specified. We would catch this exception and redirect that batch to an error file to be reloaded at a later date.
View 15 Replies
View Related
Sep 29, 2013
how can i subtr first name and last name select first_name + lastname from employee for example if i have james michael i what to have JMichael
View 4 Replies
View Related
Jan 10, 2013
i would like to know how can i used the OR "|" with REGEXP_SUBSTR
SELECT REGEXP_SUBSTR('500.90 Oracle Parkway, 12-12-2000 78 Redwood Shores, CA','([0-9]{1,2})-([0-9]{1,2})-([0-9]{4})') "REGEXPR_SUBSTR" FROM DUAL;this two condition
([0-9]{1,2}) ([0-9]{1,2}) ([0-9]{4})
or this i did it like this
[code]...
View 8 Replies
View Related
Jan 7, 2013
i have following table
CREATE TABLE THREAD_SHADES
(
ITM_COD NUMBER NOT NULL,
ITM_DES VARCHAR2(250 BYTE) NOT NULL,
)
[Code]...
Result is
ITM_COD ITM_DES
80064186THREAD TEX-105 SHADE# 7921 (1500 MTRS)
80064187THREAD TEX-40 SHADE#7921 (3000 MTRS)
80114482THREAD TEX 40 SHADE C-8762 1500MTR LOCAL
80130541THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS
I want a query which should return only Sahde # from above data result must be as
7921
C-8762
C-8762
C8676
View 9 Replies
View Related
Jan 21, 2011
I have a following table,
CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......
I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z
How do i use Substr and Regular expression here ?
View 11 Replies
View Related
Aug 28, 2012
i need to isolate the MVNAME schema from the following job;
dbms_refresh.refresh('"SCHEMA"."MVNAME"');
how to use substr/instr to isolate it?so far I have
select substr(what,instr(what,'"',-1,2),15) from dba_jobs;
View 3 Replies
View Related
Aug 27, 2013
Can we have substr(date) as column name. I am trying to create a query where in I need to achieve something like this select s.xyz Today,s.abcd "6 Months ago" || to_char(add_months(sysdate,-6),'Mon rrrr') ||')'from sales_tab s.
View 9 Replies
View Related
Nov 28, 2010
I have a table1 with columns serial no and name as below
serial no name
1 john paul
2 john victor
and i have another table1 with columns serialno,firstname and second name as below
serial no firstname secondname
1
2
I want to update my table2 with the name from table1 and divide and insert in first name and second name as below
serialno firstname secondname
1 john paul
2 john victor
i use the query as below
update table2 set
firstname = (select substr(name, 1, instr(name, ' ', 1) - 1)
from table1
)
but the above query not worked.
View 11 Replies
View Related
Aug 28, 2012
i need to isolate the MVNAME schema from the following job;
dbms_refresh.refresh('"SCHEMA"."MVNAME"');
how to use substr/instr to isolate it?so far I have
select substr(what,instr(what,'"',-1,2),15) from dba_jobs;
But its proving a bit tricky to nail it.
View 6 Replies
View Related
Mar 15, 2011
I am trying to execute the PL/SQL block below:
DECLARE
var VARCHAR2(4000);
BEGIN
SELECT DBMS_LOB.SUBSTR(v_clob,4000,1) INTO var FROM test_clob;
END;
** v_clob is a CLOB column in test_clob table.
I get the below error:
wrong number or types of arguments in call to 'SUBSTR'"SYS"."DBMS_LOB"."SUBSTR": invalid identifier...I have execute privileges to DBMS_LOB.SUBSTR function.
View 2 Replies
View Related
Nov 5, 2012
when I try to run this code
SQL> select
2 regexp_substr('But , soft! What light through yonder window breaks?',
3 'l[[:alpha:]] {4}') AS result
4 from dual;
I get just this ( R
-)
where the result should be like the full sub string which is ( light) not just R.
View 2 Replies
View Related
Jan 23, 2013
I would like to have a column heading as follows in double inverted comma. but sqlplus environment returns column heading length equal to output value.
SQL> select substr('The independence day', 5,12) "Example of substr function" 2 from dual;
Example of s
------------
independence
I know that default column heading length is 30 character long.
but my column heading is less than 30 character long (which is 26)
How can i have column heading as Example of substr function?
View 7 Replies
View Related
Dec 27, 2012
I am using the following substr and it works fine on Toad but when i am trying to use within an ETL tool, there getting the error:
substr(PBBDT,length(PBBDT)-1)
Calling <substr> with <2> parameters, but <3> are expected.
View 2 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
