PL/SQL :: To_char Not Working / (||) Is Working With Join Query

Mar 22, 2013

I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.

I am using following two queries:

select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))

by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.

whats the diff between to_char and || symbol?

View 1 Replies


ADVERTISEMENT

Self Join No Longer Working?

May 20, 2010

I have a piece of code that joined the same table onto itself twice in order to get the previous, current and future year's into columns in the same output.

Up until recently this has been working fine but the most recent data has just been uploaded into the table and now it comes up with an error.

On the second (left outer) join it now says that the column is ambiguously defined (ORA-00918). It doesn't matter which order the joins are in it is always the second join that the error pops up on.

View 4 Replies View Related

SQL & PL/SQL :: LEFT JOIN And NVL Isn't Working

Nov 29, 2011

Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Here's a simplified version of the query I am having problems with:

SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND
NVL(assoc_prog.program_id, -1) = NVL(assoc.program_id, -1)

Note that there is no program with program id -1. So the assoc_prog left join will come up with nothing. I was thinking that since assoc_prog.program_id will be null, the second assoc left join would pick the row where assoc.program_id is null. However, the second left join doesn't join to any row.

In this query, it does join to an assoc row (I changed assoc_prog.program_id to NULL)

SELECT
assoc.association_id
FROM mentor_initiative mi
LEFT JOIN program assoc_prog ON assoc_prog.program_id = -1
LEFT JOIN mentor_association assoc ON assoc.mentor_initiative_id = mi.mentor_initiative_id AND NVL(NULL, -1) = NVL(assoc.program_id, -1)

I was thinking it would join to an assoc row in the first query though. How can I change the first query to have the desired effect of left joining to a row where assoc.program_id is null if assoc_prog.program_id is null?

View 7 Replies View Related

Basic Join Not Working?

Apr 20, 2008

am not very experienced with SQL. I have two tables:

Class
-----
ID (int)
Teacher (nvarchar)
Subject (nvarchar)

Student
--------
ID (int)
ClassID (int)
Name (nvarchar)

I am trying to write a SQL query that will give me the total number of students in each class. I have tried the following:

SELECT
c.ID,
c.Teacher,
c.Subject
COUNT(s.ID)
FROM
Class c LEFT OUTER JOINT ON Student s c.ID=s.ClassID

Unfortunately, that gives me an error of:Column 'Class.Teacher' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm afraid if I place everything in a GROUP BY clause, I will get some invalid results.

View 4 Replies View Related

SQL & PL/SQL :: Full Outer Join Not Working?

May 24, 2011

I have problems with the query below. Left join and right join is working. Well actually it's not working correct I think but that's another story wich I will leave for another posting. But with full join I am getting error ORA-00932.The columns starting with id_ are of number datatype and the ones starting with dat_ are of type date.Oracle points out the last line to be faulty. If I make that line a comment it points out the preceeding line and so on.

SQL> /
and trunc(t.dat_trans) <= to_date(:sql_dat_avst_tom,'yyyymmdd')
*
ERROR at line 24:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

select k.id_pers k_id_pers, k.dat_avst
,t.id_pers t_id_pers, t.id_trans, t.dat_trans
from (
select ka.id_pers, ka.dat_avst, ka.dat_nasta_avst

[code]...

View 29 Replies View Related

SQL & PL/SQL :: Join Two Tables - Condition Not Working?

Aug 16, 2010

I have two tables. i need to join the tables. The query is -

select v.c_venditore,v.s_venditore,v.t_diretto_indiretto,v.d_disattivazione,d.s_direzione from VENDITORE v,DIREZIONE d
where v.p_direzione=d.p_direzione order by v.s_venditore

In the table VENDITORE there are 2919 rows. I need to display all the rows. But the joining column p_direzione has some null values.I need to display the null also. But to join the two tables this is the only condition. How can i display all the rows.

View 3 Replies View Related

ORA-00932 - Sample Code Working Fine In 10g And Not Working Now In 11g

Apr 1, 2013

Below is the sample code working fine in 10g and not working now in 11g.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....

we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class

Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

View 3 Replies View Related

SQL & PL/SQL :: Using Outer Join Outside To_char?

Apr 1, 2008

I need to use an outer join outside a to_char

to_char(the_date_field,'YYYYWW')(+) = '200815'

This throws up the error ORA-00936 missing expression.

I know it will work if I put the (+) right after the date before the mask but it need to check the week rather than the date.

I have tried using a function based index without success.

View 7 Replies View Related

SQL & PL/SQL :: Single Quote(') In Query Not Working?

Jan 5, 2013

I have table say Messages. In which there is a column msg_text varchar2(900).My requirement is to fetch the very last character of the msg_text for a single row identified by its msg_code(primary key).

The problem is, whenever msg_text contain second last character as single quote( ' ), it doesn't give me the last character i.e. after the single quote.For example if msg_text is "Congratulations, you opted for 'A'." and if its message_code is 10 then query

SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 10;

returns nothing.

Whereas if msg_text is "Are you sure to continue?" and if its message_code is 20 then query

SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 20;

returns character '?'.

View 4 Replies View Related

SQL & PL/SQL :: Query Based On Date Filter Is Not Working

Aug 14, 2011

is the definition of my table :

CREATE TABLE DATEFETC
(
ID VARCHAR2(10 BYTE),
DT DATE
)

And these are the data that are available,(select * from DATEFETC)

IDDT

00108-09-2011
00208-10-2011
00308-11-2011

That's fine.
Now i am executing this query ,but this is returning no rows.Why ?

select * from datefetc where dt between to_date('08-08-2011','mm-dd-yyyy') and to_date('08-12-2011','mm-dd-yyyy')

View 1 Replies View Related

SQL & PL/SQL :: Query To Get Consecutive Working Days From A Table

Jul 15, 2011

I have a problem with a query. I have a table employee with data as

emp_id date day working_ind
1 01-Jan-2011 Mon Y
1 02-Jan-2011 Tue Y
1 03-Jan-2011 Wed Y
1 04-Jan-2011 Thu Y
1 05-Jan-2011 Fri Y
1 06-Jan-2011 Sat N
1 07-Jan-2011 Sun N
1 09-Jan-2011 Tue Y

Sundays/ Monday/ any public holiday the working_ind will be N. If the emp is absent on one day then there will be no record entered in the table (e.g. 8th jan there is no record). Each table has only one year data.

I need to retrieve for all employees when they worked for 30 consecutive days without being absent which does not include sat/ Sunday / holidays.

Its like:
-- i need to order by emp_id and date
-- get oly the data with working_ind as Y
-- make sure that i get 30 consecutive days (from what ever i get above) where no days data is missing

I tried using lag and inner join but it does not seem to be working.

View 5 Replies View Related

Forms :: Query Mode Not Working At Once The Form Is Opened

Jul 10, 2012

At once when I open the form when I press F11 it is not entering query mode. But if I type something in the text box placed in the form then I press F11 means it is asking "Do you want save the changes you have made?" if I press yes or no, no matter it is going for query mode. How to go to query mode without typing something in it?

View 13 Replies View Related

Sqlplus UI - Not Working?

Apr 22, 2008

I'm having some minor trouble with my sqlplus in Windows. Don't know which feature I accidentally edited by cosole seems to be in debug mode. Everything is ok, except that it print the old and new values of my variables. How can't I restore it to it's original behavior.

View 3 Replies View Related

SQL & PL/SQL :: How To Calculate Working Day

Jan 1, 2013

I need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.

Emp1 : To record the employe's information and weekly rest day.
Attendance :- To record daily attendance.
Leave_appovd : To record the approved leaves.
Holiday : To record the holidays.

W = Attendance
R = Weekly Rest
L = Leave
H = Holiday
A = Absent

Required output is

Emp No.In Date Out Date Type
736912/1/201212/1/2012W
736912/2/201212/2/2012R
736912/3/201212/3/2012W
736912/4/201212/4/2012W
736912/5/201212/5/2012W
736912/6/201212/6/2012L
736912/7/201212/7/2012H
736912/8/201212/8/2012H
736912/9/201212/9/2012R
736912/10/201212/10/2012A
736912/11/201212/11/2012W
736912/12/201212/12/2012W
736912/13/201212/13/2012W
736912/14/201212/14/2012W
736912/15/201212/15/2012L
736912/16/201212/16/2012L
736912/17/201212/17/2012L
736912/18/201212/18/2012W
736912/19/201212/19/2012W
736912/20/201212/20/2012W
736912/21/201212/21/2012W
736912/22/201212/22/2012W
736912/23/201212/23/2012R
736912/24/201212/24/2012A
736912/25/201212/25/2012A
736912/26/201212/26/2012A
736912/27/201212/27/2012W
736912/28/201212/28/2012W
736912/29/201212/29/2012W
736912/30/201212/30/2012R
736912/31/201212/31/2012W
778212/1/201212/1/2012W
778212/2/201212/2/2012W
778212/3/201212/3/2012W
778212/4/201212/4/2012W
778212/5/201212/5/2012W
778212/6/201212/6/2012W

Separate Query will be run for the following output.

Wroking Days
EmpnoAttend WeeklyRestsLeavesHolidays TotalAbsentsG. Total
7369 17 4 4 2 27 4 31
7782 6 0 0 0 6 25 31

If any body work on weekly rest or holiday, it will be considered as weekly rest and holiday. There working on these days will be treated separately.

View 10 Replies View Related

SQL & PL/SQL :: Update Is Not Working

Apr 27, 2012

with a as (
SELECT subscriber_agn,max(ixml_id) ixml_id, claim_id
FROM
(

[Code].....

i want to combine above two and need only one sql stmt.

View 2 Replies View Related

SQL & PL/SQL :: DDL Trigger Not Working?

May 14, 2013

I have a trigger to automate grants to a Role whenever a table is created in my schema.I have dba rights.

CREATE OR REPLACE PROCEDURE deepa.myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;

[Code].....

This works perfectly when i create a table as deepa user. But say when another User creates a table in my schema

like create table deepa.test1 ( a number) This trigger is not fired. Whenever a table is created by me or by any other user in my Schema, i need the trigger to be fired.

View 2 Replies View Related

SQL & PL/SQL :: In Clause Is Not Working?

Sep 18, 2013

SELECT sno,mid,mname
FROM manage_date
WHERE mname IN
('KIRAN-KUMAR',
'RAHUL-RAJ',
'KAUSHAL-SONI');

IF I use this query directly in DB it's working fine. But when this query is calling in .net using parameter as below it's not giving any records for more than one value. IN (:p_mname)

If I pass one name 'KIRAN-KUMAR' from .net It's working.

If I pass multiple names from .net query not returning any records.

View 4 Replies View Related

Oradim Is Not Working

Dec 13, 2010

I trying to create the new service for my physical standby database. It's on the same vista machine. But somehow it's not working.

C:\Oracle\product\10.2.0\mayuprd\database>oradim -new -sid -intpwd ananya -start
mode manual
DIM-00003: An argument is missing for the parameter.

View 5 Replies View Related

SQL & PL/SQL :: Recycle Bin Not Working

Jun 14, 2010

I went through the link given in orafaq and tried to work on my system but the things did not work.

SQL> select *
2 from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> ALTER SYSTEM SET recyclebin = ON;

System altered.

SQL> SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

VALUE
--------------------------------------------------------------------------------
ON

SQL> CREATE TABLE TEST_RBIN(VAL NUMBER);

Table created.

SQL> INSERT INTO TEST_RBIN(VAL) VALUES(10);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> DROP TABLE TEST_RBIN;

Table dropped.

SQL> SHOW RECYCLEBIN;
SQL> SELECT * FROM USER_RECYCLEBIN;

no rows selected

View 6 Replies View Related

SQL & PL/SQL :: Select INTO Is Not Working

Mar 8, 2013

why my "select into" not working.i also create the table testjobs but also not working

SQL> select *
2 into testjobs
3 from jobs;
into testjobs
*
ERROR at line 2:
ORA-00905: missing keyword

View 8 Replies View Related

Oracle 11g EM Not Working (Linux OS)

Jan 20, 2011

I am unable to configure EM(Oracle 11.2g) on Redhat Linux 5 Enterprise edition(32 bit). Environment variables are set to correct SID. If I try to run dbca the wizard does not display the Enterprise manager configuration screen. I have tried to create repository by using emca -config dbcontrol -db config.

But it throws exception that database instance is unavailable?

View 5 Replies View Related

Working With Date In Oracle?

Oct 31, 2011

I tried to insert date in date column from another table date column. Below is SP ans execute procedure.

CREATE OR REPLACE PROCEDURE test(
start_time Varchar
)
AS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(start_time);
Insert into test_ora (report_date) (select start_time from

[code]....

execute test ('27-10-2011 12:01:01');

It gives error as Invalid number.

View 2 Replies View Related

SQL & PL/SQL :: EXISTS Not Working In Oracle 10g

Mar 30, 2012

following is a query which i find difficult to understand why EXISTS is failing. There are two scenarios where if i block LINE 30 and unblock line 31 of the code then one record is returned.

SELECT A.ENTITY_CODE,
A.VRNO,
A.VRDATE,

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: INSERT INTO Table Not Working?

Oct 26, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I am doing a simple INSERT INTO from the sql developer window. it shows that 1 row inserted, but when i query, it doesnt give th values.

insert into events_main(event_id,event_number,last_update_Date, last_updated_by)
values(162,999999, sysdate, 'rkhatiwala');
COMMIT ;

this does not give any error, but when i do

select * from events_main
where event_id = 162

it does not return anything.

View 20 Replies View Related

Cursor In Trigger Not Working?

Dec 25, 2011

i have a problem with Cursor in trigger,

CODEcreate or replace trigger admin
                  after insert on admin
                  for each row
                  declare
                   bang varchar2(250):='ADMIN';  
                  
[code]...

i will try with select :new.i.column_name from dual, but not run (because i can't determination exactly column name thus i must use column_name variable)

View 6 Replies View Related

Enterprise Manager :: OEM Is Not Working

Sep 24, 2010

I tried to start my oem managing but got the following error.

[oracle@Eagle2 ~]$ emctl start dbconsole
OC4J Configuration issue. /home/oracle/app/product/11.1.0/db_1/oc4j/j2ee/OC4J_DBConsole_Eagle_boomiedb not found.

View 19 Replies View Related

Forms :: GET_FILE_NAME Is Not Working

Aug 6, 2013

I am developing a form using Forms [32 Bit] Version 6.0.8.11.3 (Production). In my custom form I have given a Push_Button and my idea is to invoke a File open diaglog box when I hit the button.

So, i wrote the below code under 'when-button pressed ' triger. But, the button is not invoking the File Open diaglog box in TEST environment.

=====
DECLARE
XXFILENAME VARCHAR2(50);
BEGIN
XXFILENAME := get_file_name('C:', NULL,
file_filter=> 'JPEG Image (*.JPG,*.JPEG,*.JPE,*.JFIF)|*.JPG|Bitmap Image (*.bmp)|*.bmp|GIF Image (*.GIF)|*.GIF|TIFF Files (*.tif)|*.tif|All Files (*.*)|*.*|');
END;

View 7 Replies View Related

Forms :: Progress Bar Not Working

Dec 11, 2011

i have this procedure in my form to make some checks on employee data:

Procedure Pu_Check_Emp_Positions_Prc Is
Cursor vc_get_users_data_case1 Is
Select A.User_No, A.User_Responsility, A.User_Position, B.User_Status
From Import_Emp_Positions_Dtl A,
Shows_Users B
Where A.User_No = B.User_No
And (Substr(A.User_Position, 1, 1) = 'R' Or Substr(A.User_Position, 1, 1) = 'r')
And A.User_Responsility Is Not Null
And B.User_Status In (2, 3, 4, 7, 8);
--And A.User_No = 'XSER0001';
[code]....

i need to run this progress bar and finished when the procedure finish.

View 7 Replies View Related

SQL & PL/SQL :: Replace Command Is Not Working?

Jun 18, 2013

database 10GR2, Character_set WE8ISO8859P1

The following command not working.

update consumerappliedform
set name =replace (name,'¿','‡');

What is the problem.

View 3 Replies View Related

SQL & PL/SQL :: Save Point Is Not Working?

Dec 20, 2011

In oracle 9i, I have a table and i inserted the more then 3 records and while inserting each records i have created the save point. But, now i rollback to that particular save point, the whole transaction get roll backed.

Here the similar example what i have tried using SQL Developer Eg:

insert into dept (deptid,dept_name) values (3,'Purchase');
savepoint aa;
insert into dept (deptid,dept_name) values (4,'IT');
savepoint bb;
insert into dept (deptid,dept_name) values (5,'System');
savepoint cc;
rollback to bb;

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved