OraFAQ Forum: SQL & PL/SQL » NOT IN STATEMENT

Home » SQL & PL/SQL » SQL & PL/SQL » NOT IN STATEMENT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NOT IN STATEMENT [message #429733]

Thu, 05 November 2009 09:16

needurgent
Messages: 18
Registered: October 2009
Location: penang

Junior Member

I wish to perform query to display the TAB_ID where the column for FROM_PATH is not same or not in with FROM_MAP_DRIVE in the table call
DATA_FLOW_TABLE_FLOW_PATH.

example:

TAB_ID   FROM PATH    FROM_MAP_DRIVE
1189      E:\         F:\Data\AOPTR\TRANS\*.TDF
1190      F:\         F:\Data\APTR\*.TDF
1192      P:\         P:\Data\SLC\TRANS\*.TDF

[Mod-Edit: Frank added code tags]

[Updated on: Fri, 06 November 2009 00:04] by Moderator

Report message to a moderator

Re: NOT IN STATEMENT [message #429739 is a reply to message #429733]

Thu, 05 November 2009 09:40

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I think

INSTR(FROM_MAP_DRIVE,FROM_PATH) > 0

is what you're looking for.

Report message to a moderator

Re: NOT IN STATEMENT [message #429784 is a reply to message #429733]

Thu, 05 November 2009 15:21

needurgent
Messages: 18
Registered: October 2009
Location: penang

Junior Member

Hi,
Thanks

Is this query statement for below:

select INSTR(FROM_MAP_DRIVE,FROM_PATH) > 0 from
DATA_FLOW_TABLE_FLOW_PATH.

Report message to a moderator

Re: NOT IN STATEMENT [message #429785 is a reply to message #429784]

Thu, 05 November 2009 15:33

Its_me_ved
Messages: 979
Registered: October 2009
Location: India

Senior Member

select the fields from the table
where INSTR(FROM_MAP_DRIVE,FROM_PATH)= 0

Report message to a moderator

Re: NOT IN STATEMENT [message #429804 is a reply to message #429733]

Thu, 05 November 2009 18:00

needurgent
Messages: 18
Registered: October 2009
Location: penang

Junior Member

Thanks for reply

select the fields from the table
where INSTR(FROM_MAP_DRIVE,FROM_PATH)= 0

all same FROM_PATH and FROM_MAP_DRIVE will display. not i want ..

what is mean of equal to = o
select TAB_ID,FROM_MAP_DRIVE,FROM_PATH from DATA_FLOW_TABLE_FLOW_PATH
where INSTR(FROM_MAP_DRIVE,FROM_PATH)= 0

actually my question is
TAB_ID FROM_PATH FROM_MAP_DRIVE
1189 E:\ F:\Data\AOPTR\TRANS\*.TDF
1190 F:\ F:\Data\APTR\*.TDF
1192 P:\ P:\Data\SLC\TRANS\*.TDF

when i select from this table, the query statement will display out the tabid and FROM_PATH and FROM_MAP_DRIVE is not same.
TAB_ID FROM_PATH FROM_MAP_DRIVE
example: 1192 E:\ F:\Data\AOPTR\TRANS\*.TDF

thanks
Regards
TGT

Report message to a moderator

Re: NOT IN STATEMENT [message #429823 is a reply to message #429804]

Thu, 05 November 2009 22:01

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Z:\>sqlplus sriram/sriram

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 6 09:21:27 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table needurgent (TAB_ID NUMBER,FROM_PATH VARCHAR2(50),FROM_MAP_DRIVE VARCHAR2(50));

Table created.
SQL> ED
Wrote file afiedt.buf

  1* INSERT INTO NEEDURGENT VALUES (1189, 'E:\', 'F:\Data\AOPTR\TRANS\*.TDF')
SQL> /

1 row created.
SQL> ED
Wrote file afiedt.buf

  1* INSERT INTO NEEDURGENT VALUES (1190, 'F:\', 'F:\Data\APTR\*.TDF')
  2  /

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO NEEDURGENT VALUES (1192, 'P:\', 'P:\Data\SLC\TRANS\*.TDF'
  2* )
SQL> /

1 row created.

SQL> COMMIT;

Commit complete.
SQL> SET LINE 10000
SQL> SELECT * FROM NEEDURGENT
  2  WHERE INSTR(FROM_MAP_DRIVE,FROM_PATH) > 0;

    TAB_ID FROM_PATH                                          FROM_MAP_DRIVE
---------- -------------------------------------------------- --------------------------------------------------
      1190 F:\                                                F:\Data\APTR\*.TDF
      1192 P:\                                                P:\Data\SLC\TRANS\*.TDF

SQL> SELECT * FROM NEEDURGENT
  2  WHERE INSTR(FROM_MAP_DRIVE,FROM_PATH) =0;

    TAB_ID FROM_PATH                                          FROM_MAP_DRIVE
---------- -------------------------------------------------- --------------------------------------------------
      1189 E:\                                                F:\Data\AOPTR\TRANS\*.TDF

SQL>

Quote:

all same FROM_PATH and FROM_MAP_DRIVE will display. not i want ..

Is the above one you required ?

Sriram

Report message to a moderator

Re: NOT IN STATEMENT [message #429837 is a reply to message #429733]

Thu, 05 November 2009 23:33

needurgent
Messages: 18
Registered: October 2009
Location: penang

Junior Member

Hi,
Thanks,
the above statement unable to query out. still same problem

Below is table for DATA_FLOW_TABLE_FLOW_PATH

TAB_ID FROM_PATH FROM_MAP_DRIVE
1190 F:\ F:\Data\APTR\*.TDF
1190 E:\ E:\Data\AOPTR
1191 E:\ P:\Data\SLC

when perform the query, all the output came out with this.
I only the ouput like this

1191 E:\ P:\Data\SLC

from TGT

Report message to a moderator

Re: NOT IN STATEMENT [message #429843 is a reply to message #429837]

Thu, 05 November 2009 23:54

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

needurgent wrote on Thu, 05 November 2009 23:33

Below is table for DATA_FLOW_TABLE_FLOW_PATH
TAB_ID FROM_PATH FROM_MAP_DRIVE
1190 F:\ F:\Data\APTR\*.TDF
1190 E:\ E:\Data\AOPTR
1191 E:\ P:\Data\SLC

when perform the query, all the output came out with this.
I only the ouput like this
1191 E:\ P:\Data\SLC ]---Assuming he wants the out put like this ....

from TGT

SQL> desc needurgent
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TAB_ID                                             NUMBER
 FROM_PATH                                          VARCHAR2(50)
 FROM_MAP_DRIVE                                     VARCHAR2(50)

SQL> set line 10000
SQL> SELECT * FROM NEEDURGENT;

    TAB_ID FROM_PATH                                          FROM_MAP_DRIVE
---------- -------------------------------------------------- --------------------------------------------------
      1190 F:\                                                F:\Data\APTR\*.TDF
      1190 E:\                                                E:\Data\AOPTR
      1191 E:\                                                P:\Data\SLC
SQL> SELECT * FROM NEEDURGENT
  2  WHERE INSTR(FROM_MAP_DRIVE,FROM_PATH) > 0;

    TAB_ID FROM_PATH                                          FROM_MAP_DRIVE
---------- -------------------------------------------------- --------------------------------------------------
      1190 F:\                                                F:\Data\APTR\*.TDF
      1190 E:\                                                E:\Data\AOPTR

SQL> SELECT * FROM NEEDURGENT
  2  WHERE INSTR(FROM_MAP_DRIVE,FROM_PATH) =0;

    TAB_ID FROM_PATH                                          FROM_MAP_DRIVE
---------- -------------------------------------------------- --------------------------------------------------
      1191 E:\                                                P:\Data\SLC

SQL>

Sriram

Report message to a moderator

Re: NOT IN STATEMENT [message #429844 is a reply to message #429837]

Thu, 05 November 2009 23:56

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

needurgent wrote on Thu, 05 November 2009 23:33

Hi,
Thanks,
the above statement unable to query out. still same problem

Copy And paste what you did and get.

sriram

Report message to a moderator

Re: NOT IN STATEMENT [message #429845 is a reply to message #429837]

Thu, 05 November 2009 23:58

ayush_anand
Messages: 417
Registered: November 2008

Senior Member

Quote:

all same FROM_PATH and FROM_MAP_DRIVE will display. not i want ..

what is mean of equal to = o
select TAB_ID,FROM_MAP_DRIVE,FROM_PATH from DATA_FLOW_TABLE_FLOW_PATH
where INSTR(FROM_MAP_DRIVE,FROM_PATH)= 0

all those records for which from_path is not an instring of from_map_drive will be displayed ,which is same as your problem.

please run it in sql*plus and copy paste what you are getting in sql*plus using code tags

Report message to a moderator

Previous Topic:	date problem
Next Topic:	Upgrade Publish and Subscribe Model

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 18 05:15:59 CDT 2025