I am using 10g database on Microsoft Windows family OS
when i run following query
sql> SELECT CURRENT_TIMESTAMP FROM DUAL;
then i get result
20-01-2011 11:52:17.921000 AM +05:00
but i want to get result as
20-01-2011 11:52:17.921256 AM +05:00
is there any way to get up mentioned result.
the time in my online application is 1 hour later than is actually is. So 21.30is actually 20.30. This is my code:
select 1
from vex_rondes rde
where rde.datum_einde < current_timestamp
and rde.id = b_rde_idrde.datum_einde
is a date column filled with dates of course ins this format: 10-01-2013 20:45 (10th January 2013 20h45).How can I get this current_timestamp correct?
select 1
from vex_rondes rde
where rde.datum_einde+(1/24) < current_timestamp
and rde.id = b_rde_idbut that's not very elegant nor generic...
I am trying to change the default behavior of Hide/Show Region to show, after some attempts i got it partially working but now clicking the icon to toggle hide/show doesn't work also changed the icons and added type="" but its not working.
View 16 Replies View RelatedI have a requirement where I have a value stored in varchar2(12), and no matter what the size of data, I should mask all the characters except the last 4
eg: - 23115645
after masking should be - xxxx5645,
create table test1(x varchar2(12))
insert into test1 values('34567745')
/
insert into test1 values('438765311')
/
insert into test1 values('1112345')
/
commit
[Code].....
y should store value of x in such a way that no matter what the size of x is (7 characters or 9), we should see only the last 4, but the remaining should be masked with xxxx
I tried the above update, but using substr, i am able to get this if x has 9 characters
not sure how to achieve this if x has 7, or 4 characters
I have a table which have three column.
1) empid
2) date
3) status
I want to show the value empid intime outime.
select in1.empid,in1.atttime,out.atttime from (select empid ,atttime from attend
where status=1 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) in1,
(select empid,atttime from attend where status =0 and to_char(atttime,'mmyyyy')='022012'
order by empid,atttime desc) out
where in1.empid=out.empid and in1.empid='02256'
order by in1.atttime,out.atttime
But this query do .one value relation with all column.means first february in time with all out time.
i am using oracle 10 forms ! in report i have used form Query "select sum(sale_price) from (table) expense. i attached it to oracle forms but at that time it does nt show any value!
View 3 Replies View Relatedi want to show data quarterly.My quarter structure is 3 mOnths.
q1 - apr-2013 to jun-2013
q2 - jul-2013 to sep-2013
q3 - oct-2013 to dec-2013
q4 - jan-2014 to apr-2014
I input parameter is from date and to date..
Suppose user enter from date - '01-apr-2012' and to date - '01-aug-2012',
only q1 shows..
and user enter from date - '01-apr-2012' and to date - '30-sep-2012',
only q1,q2 shows..
how i do that..My data is like this
create table t1( t1_month VARCHAR2(10),
t1_val1 number(10),
t1_val2 NUmber(10)
);
INSERT INTO t1('APR-13',100,200);
INSERT INTO t1('MAY-13',100,200);
INSERT INTO t1('JUN-13',100,200);
INSERT INTO t1('JUL-13',100,200);
INSERT INTO t1('AUG-13',100,200);
[code]...
I run follwing query.
select * from crp_00_08
it shows result like
BAS_CODBAS_DES
1MAIN GATE (FRONT)
2NEAR MILL # 3, 3-1
3NEAR MILL # 3, 3-2
4NEAR MILL # 3, 3-3
[Code]...
i want that when i run above query every time bas_cod column should start with difference value. now it is starting from 1, i need when i run next time it should start with another number except 1,
i mean whenever i run above query,order of bas_cod must be change from last.
how can i do it.
How to write a single query for the below scenario?I got only start date column, but I need show the end date based on next (start date-1). For the last row, need to show the same date in the end date column.
For Example:I have the data as shown below,
Start date End date
1-Jan-10
3-Mar-10
3-May-10
5-Jul-10
1-Aug-10
I wanted to output as shown below
Start date End date
1-Jan-10 2-Mar-10
3-Mar-10 2-May-10
3-May-10 4-Jul-10
5-Jul-10 31-Jul-10
1-Aug-10 1-Aug-10
In MySQL, I can get a list of all tables w/ this query: "SHOW TABLES"
Whats the syntax w/ Oracle 9i ?
Looked through the docs online, but w/o knowing what you're trying to look up, you can't really look anything up (kind of a catch22).
I have below mentioned records in mount so whenever i select any mount it should now show the the last row of that mount i.e. test no-3 any records of mount which will not show last row of test number
mount day org1 test_No org2 org3 org4
299.00 1 0 1 1 0 7186
299.00 1 0 2 1 0 7186
299.00 0 3 3 0 7186
[code]...
but i want test_no should be in asceding order
Though I have it all configures:
On node1:
SQL> show parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.28.155.79)(PORT=1521))))
[code]....
Is there any way to get audit_trail.VALUE? I know the statement: show parameter audit_trail; can show that value. But, i want to build a function which will return a string that contain audit_trail.VALUE
View 1 Replies View RelatedMyTable has missing records:
id_group id_column
-------------------------
1 1
1 2
1 3
1 5
1 9
2 1
2 2
2 4
2 6
As you see, it could be missed record 4,6,7 and 8 of group 1 and record 3 and 5 of group 2. That's is an example.
I need to show all sequential records, incluing missing ones, like this:
Id_group Id_column
-------------------------------------------------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 2
2 3
2 4
2 5
2 6
I try this query:
/*---*/
Select G.id_group, C.rownum id_column
from (select distinct id_group from MyTable) G
cross join (SELECT rownum FROM dual CONNECT BY LEVEL <= (select
max(id_column) from MyTable)) C
/*---*/
but it shows record 7, 8 and 9 as members of group 2.
SQL> select * from t1;
NAME
----------
JACK
JOHN
JENN
SQL> select * from t2;
NAME
----------
JACK
PAUL
SQL> select a.name from t1 a, t2 b where a.name <> b.name
2 order by name;
NAME
----------
JACK
JENN
JENN
JOHN
JOHN
I would have expected to see the following:
name
-----
JOHN
JENN
PAUL
how to fix this query? In additon, is there a way to print the table name or some arrows (>>, <<) to show which table the values came from
I.e
name
=====
JOHN t1 or <<
JENN t1 or <<
PAUL t2 or >>
I use oracle 11g r2
i have created a table for objects
create table table_t
(Record_no number,
t_no number,
position SDO_GEOMETRY
, occupation_time number);
and inserted many values (examples below )
INSERT INTO tra VALUES
(2,
20503,
SDO_GEOMETRY
(2001,
NULL,
SDO_POINT_TYPE (1387, 0, NULL),
NULL,
NULL),
23037
)
/
and( position) indexed as Rtree spatial index
now when i run spatial query such as
SELECT * FROM tra t WHERE
SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;
i do not know how many IO accrued ?
i tried set autotrace on
but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree
I am getting a following odd result where the same a Job could be linked to multiple enquiries. What I want it to show is just one job if its linked to multiple enquiries. I show you some sample result:I wrote the following SQL for it:
SELECT
'Enquiry' as Origin,
job.job_number,
enquiry.enquiry_number as Defect_or_Enquiry,
job.site_code,
central_site.site_name,
job.priority_code,
priority.priority_name,
job.target_comp_date,
'Target Completion Date required' as Due_Date
[code]....
Is there anything you would add so that it shows just a unique job number where the job has multiple enquiries linked to it? I tried putting SELECT DISTINCT but that did not work.
I have a sql to pull all payments from vendors for a specific time period; however, now tasked to only show the Address of each Tax Reporting Site. I have tried several commands but have not been successful.
View 7 Replies View Relatedi want to show the date exactly in following format:
00:16:15 Wed 1st Dec 2010
I have tried :
select to_char(to_date('01/12/2010 00:16:15' , 'dd/mm/yyyy hh24:mi:ss'), 'hh24:mi:ss Day dd Mon yyyy')
from dual
Result it is giving
00:16:15 Wednesday 01 Dec 2010
How to show date in form 1st 2nd 3rd and so on?
How to show Abbreviated name of the day?
Is there a way to show the sql statements generated by the Forms?
View 4 Replies View Relatedhow to show total number of department with their department name assign to employee table.
View 2 Replies View RelatedI have an external table as follows:
CREATE TABLE EXT1
(
COL1 NVARCHAR2(2000),
COL2 CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY FILE_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
( "COL1" CHAR,
[code]....
From here I can count all records and the total number of records matching the file record count, but could not read the clob columns. it's not picking up the values. but if I take this out
( "COL1" CHAR,
"COL2" RAW
)
then I am missing the record counts.Also if you put all varchar and char, that also missing the count against the file.
I need to create a report that will show all activity for the day that the report is run. The report will be run or auto-refreshed throughout the day. So, at 10am, 11am, 2pm, 4pm etc. I just need to display quantities received, shipped etc. for that same day. Since the report will not be run at a fixed time, I can't use sysdate - .5 for example.
View 6 Replies View Relatedi create one form and i display employee data so,it showing empno not by order it is random so i can make it showing by order
View 3 Replies View Relatedi want to create function to show number of days between to days such as number of Friday days between to dates
View 13 Replies View Relateddeclare
v_a varchar2(2000) := 'abcd';
v_e varchar2(2000) := '6666';
v_d varchar2(2000) := 'example';
v_final varchar2(4000);
begin
v_final := '"v_a"'||'''|'''||'"v_e"'||'''|'''||'"v_d"';
-- v_final := '"v_a"';
dbms_output.put_line('v_final: '||v_final);
end;
/
above gives me :
v_final: "v_a"'|'"v_e"'|'"v_d"
so it is printing the variable names, But I want to see values, like this: "abcd"|"6666"|"example"
I totally realize that but here is what im trying to accomplish. I have a table with one column that repeats.the table looks like this.
create table testCheck (check_num varchar2(20), amount number(17,2), invh_Code varchar2(20))
and it has data like so
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('001', '50', '1123')
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('001', '50', '1123')
INSERT INTO "TESTCHECK" (CHECK_NUM, AMOUNT, INVH_CODE) VALUES ('002', '100', '1234'
I would like to write a query that would give me the distinct check_num's but put an asterisk on the items that are actually duplicated.so in other words expected result in this case would look like this.
"CHECK_NUM" "AMOUNT" "INVH_CODE"
"001*" 50 "1123"
"002" 100 "1234"
I've been able to get as far as getting the unique values but what i cant do is figure out for the life of me how to put the asterisk in there.here is my query.
with Checkquery as (
SELECT count(*) over (partition by CHECK_NUM order by CHECK_NUM ROWS UNBOUNDED PRECEDING ) thiscount, CHECK_NUM, AMOUNT, INVH_CODE
FROM TESTCHECK
group by
check_num,
AMOUNT,
INVH_CODE)
select check_num, amount,invh_Code from Checkquery where thiscount ='1';
I simply want to identify the dups put an asterisk on the record and then only select one of the records.
I have a scenario for which I need to show same record twice.
i.e
SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul
Is this possible with a single query.
Using Oracle 10g Form Builder, I can connect to the database, compile the form successfully but when I try to run the form by clicking Program/Run form, it opens a webpage(explorer) and displays ?ORACLE FORMS? label, but does not show the form.
After that IE encounters an error and asks me to notify Microsoft Corp. I have noticed that in Start OC4J Instance window 3 new rows:
09/11/04 16:24:42 FormsServlet init():
configFileName: C:DevSuiteHome_2/forms/server/formsweb.cfg
testMode: false
We're trying to use the SQL statement below to show a list of all users and responsibilities however it is only returning employee accounts. For example, the GUEST account is not listed in the results. It also isn't showing us the users who've been end dated, only the users with current accounts. I think one of the AND statements is the problem. What should I change below to achieve the desired results: A list of all users and responsibilities including system and disabled user accounts.
SQL Statement
SELECT a.application_id, a.responsibility_id, d.user_id, e.application_name,
b.responsibility_name, logons.last_access, a.responsibility_key, b.description,
a.end_date, c.end_date user_resp_end_date, d.user_name,
d.end_date user_end_date, d.description NAME, per.department,
per.job_title, per.eeo_worksite worksite, per.LOCATION
FROM apps.fnd_responsibility a,
[code]....