Home » SQL & PL/SQL » SQL & PL/SQL » NOT IN STATEMENT
NOT IN STATEMENT [message #429733] Thu, 05 November 2009 09:16 Go to next message
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 Go to previous messageGo to next message
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.
Re: NOT IN STATEMENT [message #429784 is a reply to message #429733] Thu, 05 November 2009 15:21 Go to previous messageGo to next message
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.
Re: NOT IN STATEMENT [message #429785 is a reply to message #429784] Thu, 05 November 2009 15:33 Go to previous messageGo to next message
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
Re: NOT IN STATEMENT [message #429804 is a reply to message #429733] Thu, 05 November 2009 18:00 Go to previous messageGo to next message
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







Re: NOT IN STATEMENT [message #429823 is a reply to message #429804] Thu, 05 November 2009 22:01 Go to previous messageGo to next message
ramoradba
Messages: 2454
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

Re: NOT IN STATEMENT [message #429837 is a reply to message #429733] Thu, 05 November 2009 23:33 Go to previous messageGo to next message
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



Re: NOT IN STATEMENT [message #429843 is a reply to message #429837] Thu, 05 November 2009 23:54 Go to previous messageGo to next message
ramoradba
Messages: 2454
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
Re: NOT IN STATEMENT [message #429844 is a reply to message #429837] Thu, 05 November 2009 23:56 Go to previous messageGo to next message
ramoradba
Messages: 2454
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
Re: NOT IN STATEMENT [message #429845 is a reply to message #429837] Thu, 05 November 2009 23:58 Go to previous message
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
Previous Topic: date problem
Next Topic: Upgrade Publish and Subscribe Model
Goto Forum:
  


Current Time: Sun Sep 25 12:57:44 CDT 2016

Total time taken to generate the page: 0.09434 seconds