I want to select data inserted in the table for that day only.
Table name -->ADJCOLUMNS
i want to select areAccount_no-->number datatype TRANSACT_DATE-- NOT NULL DATE I have written the query below .Is the below query correct.
select account_no,to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') T_date from adj
where to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') between
to_char(TRUNC(sysdate),'DD-MON-YY hh24:mi:ss') AND
to_char(TRUNC(sysdate+1) - 1/86400,'DD-MON-YY hh24:mi:ss');
I'm facing ORA-28500 error while configuring HSODBC to SQL Server.
Below is the complete information related to listener.ora, sqlnet.ora and tnsnames.ora files. Listener status is showing dg4msql instance successfully started.
I guess I'm facing some issue while creating DB link.
In Oracle 11g Release 11.2.0.1.0 we need to create DB link as below
CREATE DATABASE LINK "DBLNK_ETM_PRODUCTION" CONNECT TO "USER_NAME" IDENTIFIED BY VALUES 'Password' USING 'ServerIPAddress:PortNumber/SID';
How to create DB link to SQL Server.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production "CORE11.2.0.1.0Production" TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
CREATE PUBLIC DATABASE LINK DBLNK_1 CONNECT TO "manoj" IDENTIFIED BY "mypassword" USING 'SQLDB' ;
SELECT * FROM MyTable@DBLNK_1 ;
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver][libssclient24]General network error. Check your network documentation. {08001,NativeErr = 11}[Oracle][ODBC SQL Server Driver][libssclient24]ConnectionOpen (Name or service not known()). {01000}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
ORA-02063: preceding 2 lines from DBLNK_AVL_CLUSTER2 28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:" *Cause: The cause is explained in the forwarded message. *Action: See the non-Oracle system's documentation of the forwarded message. Error at Line: 5 Column: 20
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1246/1/2006410 2247/1/2007360
I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.
BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for HPUX: Version 11.2.0.3.0 - Production
[code]...
SELECT job_request_id, CAST (COLLECT (USER_ID) AS SYS.OdcinumberList) user_ids FROM mytable GROUP BY job_request_id;
ORA-22814: attribute or element value is larger than specified in type
I have tables SUBJECT(subject_id, name, number) and PS(ps_id, subject_id, student_id). I need to select all from SUBJECT,subject_id and student_id from SP, joined by subject_id, where student_id needs to be read from session. I'm using asp.net with oracle database. How to get the value from the session.
how can I select whole table in parts of 100 rows?
If I have primary key I can:
CODEstart=0; end=100; select * from table where ID>=start_point and ID<end; start=end; end=end+100; and repeat: CODEselect * from table where ID>=start_point and ID<end;
How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]...
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]...
One person (zmo_kodas) can have a lot of reports (ats_nr).
Every report can have one or more services (pas_kodas).
I'm putting together a path to select a revision of a particular novel:
SELECT e.documentname, e.Revision, e.VersionNumber FROM Catalog, BookInCatalog INNER JOIN NovelMaster INNER JOIN HasNovelRevision INNER JOIN NovelRevision e LEFT JOIN NovelRevision s
[code]...
My goal here is to select the earliest revision from the set of Novel revision. The revision field is a string.
When I run the query for Novels that have multiple revisions I get multiple records. If there is just one record I only get one row. If there are two I get four (two for each revision). As the number of revision increases it looks like it just mushrooms from there.
One other challenge is the format of the revision- a revision sequence could look like this:
A B C1 C2 C D E1 E
So there are "intermediate" revision referred to by a number. In this case I would select revision A, but if I had:
A1 A B1 B
I would want to select B. I am pretty sure that all the revision are stored in the db in order.Notice that the comparison operator ">" is used in e.Revision > s.Revision. I initially though it should have been "<" because we want to select the initial but the other way gives me the right order (though the wrong results).
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]
What I get is in green (services are calculated more than 2 times BUT in the same report). What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]
One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).
I have tried using the max function (select part_no, quantity, max(applied) from pcuk_BG_alloc_TAB group by part_no, quantity) but seems as the records have different quantities it treats them separately.
I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way
SQL> set heading off; 1 select '#ENCODING WINDOWS-1252' from dual 2 union 3 select ' ' from dual 4 union 5 select 'Language Section EN-US' from dual 6 union 7 select ' ' from dual 8 union 9* select 'Catalog Section Title Date Source' from dual SQL> /
#ENCODING WINDOWS-1252 Catalog Section Title Date Source Language Section EN-US
- - - - - - - - - - - - - - - - -
Desired Output: #ENCODING WINDOWS-1252 Language Section EN-US Catalog Section Title Date Source
I'm selecting a set of records from one table, for example: ID, description and date. From this I'm only wanting the latest inserted row. I've used the max function on the date which is fine, however, there are some records that have had their description changed. This then returns two values for one ID, the max for the original description and the max for the changed description.
I am trying to select the owner of a certain object, only knowing the name of the object and the user calling it.
Problem is this object might exist in more than one schema with the same name, and I only need the one that is called by the active user, which himself/herself might have access to other schemas containing their own copy of the object.
Example:
SELECT OWNER INTO v_schema_name FROM ALL_OBJECTS WHERE OBJECT_NAME = p_object_name;
This works fine, until the object exists in more than one schema to which the current user has access.
CREATE TABLE ID_comments ( ID CHAR(10 BYTE) NOT NULL, S_COMMENTS VARCHAR2(255 BYTE), P_COMMENTS VARCHAR2(255 BYTE), C_COMMENTS VARCHAR2(255 BYTE) );
For each Id, I can have multiple records.
Below is the insert script of one of the ID:
Insert into ID_comments values ('0813654254','','JR/0813653606 single',''); Insert into ID_comments values ('0813654254','','JR/0813653606 single',''); Insert into ID_comments values ('0813654254','','JR/0813653606 SINGLE',''); Insert into ID_comments values ('0813654254','','JR',''); [code].......
Now I want to select only one record from this table for an ID, which will have "not null" values for s_comments,p_comments,c_comments columns. If for some ID , there is no "not null" row for any column, then pick up the "null" row/value for that column.
I have a table with a count of customers for about a year, but I only want to select Wednesday and Thursday of each week, starting at the beginning of dates. Table is simple and has two columns. Each row is Distinct to a Date, there are no Date duplicates, it's counted for each day of the year.
column 1: count of customers, count(customer_id) column 2: Date
Not even sure if this is possible to select a date using the day name ?
Basically I want to select each Wednesday and Thursday from each week and compare the counts week over week, over week for all week to see if the counts are going up or down, to get trends .
Program_Name Effective_Date Valid_Flag ABCD 2/10/2012 N ABCD 2/14/2012 N ABCD 2/20/2012 Y ABCD 3/01/2012 N ABCD 3/10/2012 N
[Code]...
I have to write a select statement to to keep the first record and then pull only the records when the Valid_Flag changed. The result set should be like below.
Program_Name Effective_Date Valid_Flag ABCD 2/10/2012 N -- I have preserved the first record ABCD 2/20/2012 Y -- Valid_Flag chages to a Y for teh first time and so on. ABCD 3/01/2012 N ABCD 3/14/2012 Y ABCD 3/25/2012 N ABCD 4/25/2012 Y
If there is no change in the flag, I do not have to pull that record.
I have a table test with 10,000 records in it and 50 columns.I have to select those rows which contain values as "Sales Dum" in their field..For table with small number of colums i did this
SELECT * FROM tbl_website_dtl WHERE created_by like '%Sales%' or website_name like '%Sales%' or website_code like '%sales%';But should i do for table containing 50 columns.
