Home » SQL & PL/SQL » SQL & PL/SQL » creating a query showing file not existing
creating a query showing file not existing [message #294190] Wed, 16 January 2008 15:31 Go to next message
smokin5s
Messages: 18
Registered: September 2005
Location: Columbus, Ohio
Junior Member
This is a SQL question while using Microsoft SMS.

I am trying to write a SQL query looking for a specific file in a specific directory. If the File does not exist, then I want it to display.

here is the Code that I have for it that shows that the file exists... how do I make it show that the file doesn't exist?... what am I doing wrong?

**********************************
SELECT TOP 100 PERCENT 
SYS.Netbios_Name0, SYS.User_Name0, SF.FileName, SF.FileVersion, 
SF.FileSize, SF.FileModifiedDate, SF.FilePath, 
SYS.Operating_System_Name_and0
FROM dbo.v_GS_SoftwareFile SF INNER JOIN
dbo.v_R_System SYS ON SYS.ResourceID = SF.ResourceID
WHERE (SF.FileName = 'UdaterUI.exe') 
AND (SF.FilePath = 'C:\Program Files\McAfee\Common Framework\') 
OR
(SF.FileName = 'UdaterUI.exe') 
AND (SF.FilePath = 'c:\Program Files\Network Associates\Common Framework\')
ORDER BY SF.FileVersion, SYS.Netbios_Name0


[mod-edit] tried to make it look a littler better with CODE tags.

[Updated on: Thu, 17 January 2008 00:29] by Moderator

Report message to a moderator

Re: creating a query showing file not existing [message #294192 is a reply to message #294190] Wed, 16 January 2008 15:37 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>This is a SQL question while using Microsoft SMS.
So why in the world are you posting on an Oracle forum?
Re: creating a query showing file not existing [message #294193 is a reply to message #294192] Wed, 16 January 2008 15:39 Go to previous messageGo to next message
smokin5s
Messages: 18
Registered: September 2005
Location: Columbus, Ohio
Junior Member
anacedent wrote on Wed, 16 January 2008 16:37
>This is a SQL question while using Microsoft SMS.
So why in the world are you posting on an Oracle forum?

please forgive me if I'm wrong, but this part of the forum is titled SQL experts.... I figured that this forum was trying to help SQL people as well.
Re: creating a query showing file not existing [message #294196 is a reply to message #294190] Wed, 16 January 2008 15:41 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
SMS SQL <> ORACLE SQL <> DB2 SQL, etc.
Re: creating a query showing file not existing [message #294197 is a reply to message #294190] Wed, 16 January 2008 15:42 Go to previous messageGo to next message
smokin5s
Messages: 18
Registered: September 2005
Location: Columbus, Ohio
Junior Member
my mistake.

now that we have it out of the way that I shouldn't have posted a SQL question inside of an Oracle forum... does anyone have any idea on what I'm doing wrong since the code should be similar?
Re: creating a query showing file not existing [message #294198 is a reply to message #294190] Wed, 16 January 2008 15:46 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>does anyone have any idea on what I'm doing wrong since the code should be similar?
You don't know, what you don't know.
No, the "code" is not at all similar.
AFAIK, Oracle SQL can't do what you've already posted.
Re: creating a query showing file not existing [message #294199 is a reply to message #294190] Wed, 16 January 2008 15:49 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
try posting here:
http://www.dbforums.com/forumdisplay.php?f=7
Re: creating a query showing file not existing [message #294200 is a reply to message #294198] Wed, 16 January 2008 15:49 Go to previous messageGo to next message
smokin5s
Messages: 18
Registered: September 2005
Location: Columbus, Ohio
Junior Member
can you please stop crapping my my thread?

maybe I'll get lucky and a developer that knows Oracle SQL and SMS SQL will see this and be able to help me out.

your continued harping by posting in this thread, e-mailing me, and PM'ing me this info is enough.

I'm trying to get help on a query and you appear to be getting your jollies off by letting me know that I'm in the wrong section or that I don't know anything about Oracle SQL. You are correct on both accounts. I am hoping that maybe something that knows more than me will be willing to help me out instead of flooding my inbox with non-sense.
Re: creating a query showing file not existing [message #294201 is a reply to message #294199] Wed, 16 January 2008 15:50 Go to previous messageGo to next message
smokin5s
Messages: 18
Registered: September 2005
Location: Columbus, Ohio
Junior Member
anacedent wrote on Wed, 16 January 2008 16:49
try posting here:
http://www.dbforums.com/forumdisplay.php?f=7

ok, I'll give that a shot.

thanks!
Re: creating a query showing file not existing [message #294235 is a reply to message #294190] Wed, 16 January 2008 23:08 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
smokin5s wrote on Wed, 16 January 2008 13:31


I am trying to write a SQL query looking for a specific file in a specific directory. If the File does not exist, then I want it to display.

... how do I make it show that the file doesn't exist?



In Oracle SQL it would be:

SCOTT@orcl_11g> SELECT t1.directory_path, t1.file_name,
  2  	    DECODE
  3  	      (DBMS_LOB.FILEEXISTS (BFILENAME (t2.directory_name, t1.file_name)),
  4  	       0, 'DOES NOT EXIST', 1, 'EXISTS') AS exists_or_not
  5  FROM   (SELECT 'C:\Program Files\McAfee\Common Framework\' AS directory_path,
  6  		    'UdaterUI.exe' AS file_name
  7  	     FROM   DUAL
  8  	     UNION ALL
  9  	     SELECT 'c:\Program Files\Network Associates\Common Framework\',
 10  		    'UdaterUI.exe'
 11  	     FROM   DUAL
 12  	     UNION ALL
 13  	     SELECT 'c:\Oracle11g\', 'test.dat'
 14  	     FROM   DUAL) t1,
 15  	    all_directories t2
 16  WHERE  t1.directory_path = t2.directory_path
 17  /

DIRECTORY_PATH                                        FILE_NAME    EXISTS_OR_NOT
----------------------------------------------------- ------------ --------------
c:\Program Files\Network Associates\Common Framework\ UdaterUI.exe DOES NOT EXIST
C:\Program Files\McAfee\Common Framework\             UdaterUI.exe DOES NOT EXIST
c:\Oracle11g\                                         test.dat     EXISTS

SCOTT@orcl_11g> 

Previous Topic: Help with Datetime
Next Topic: Oracle date comparison issue
Goto Forum:
  


Current Time: Sun Dec 04 00:12:15 CST 2016

Total time taken to generate the page: 7.32124 seconds