Hi
I am trying to extract the last file name from a file path so in this instance ABFT.
/Standards Development /DATABASE SERVICES/Trade associations/ABFT/
I have tried using reverse and substring without success, any help would be really appreciated as I am really struggling with this one.
Thank you
Lyn3009
The following is deliberately a little long-winded to make it easier to understand, so you can extend it or adapt it as you need.
DECLARE @x VARCHAR(256) = '/Standards Development /DATABASE SERVICES/Trade associations/ABFT/'
SELECT
REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1))
FROM
( VALUES (REVERSE(@x)) ) A(a)
CROSS APPLY( VALUES (STUFF(a,1,CHARINDEX('/',a+'/'),'')) )B(b);Hi James
Thank you. How do I include it in this query. Sorry I don't know much about using declare. the field is ADS_EVENT.Object_FolderPath
SELECT
ADS_EVENT.Top_Folder_Name,
ADS_EVENT.Object_Name,
ADS_EVENT.Object_Folder_Path,
ADS_EVENT.User_Name,
CAST( ADS_EVENT.Start_Time AS CHAR ),
ADS_OBJECT_TYPE_STR.Object_Type_Name,
Convert(float,ADS_EVENT.Duration_ms) / 1000,
CASE ADS_STATUS_STR.Status_ID WHEN 0 THEN 'successful' ELSE 'failed' END
FROM
ADS_EVENT_TYPE_STR INNER JOIN ADS_EVENT_TYPE ON (ADS_EVENT_TYPE.Event_Type_ID=ADS_EVENT_TYPE_STR.Event_Type_ID AND ADS_EVENT_TYPE_STR.Language = 'en')
INNER JOIN ADS_EVENT ON (ADS_EVENT.Event_Type_ID=ADS_EVENT_TYPE.Event_Type_ID)
INNER JOIN ADS_OBJECT_TYPE_STR ON (ADS_EVENT.Object_Type_ID=ADS_OBJECT_TYPE_STR.Object_Type_ID AND ADS_OBJECT_TYPE_STR.Language = 'en')
INNER JOIN ADS_STATUS_STR ON (ADS_EVENT.Event_Type_ID=ADS_STATUS_STR.Event_Type_ID and ADS_EVENT.Status_ID=ADS_STATUS_STR.Status_ID AND ADS_STATUS_STR.Language = 'en')
WHERE
(
ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run'
AND
datepart(yyyy, ADS_EVENT.Start_Time) >= 2016
AND
ADS_EVENT.Top_Folder_Name NOT IN ( '','Auditing','Feature Samples','Functions & Templates','IT SERVICES','IT SERVICES - Copy (1)','IT SERVICES - OLD','IT SERVICES [1]','itelliTest','LCM','Report Conversion Tool','Report Samples','Report Samples - Copy (1)','Web Intelligence Samples' )
)
Thank You
SELECT ADS_EVENT.Top_Folder_Name ,
ADS_EVENT.Object_Name ,
ADS_EVENT.Object_Folder_Path ,
ADS_EVENT.User_Name ,
CAST(ADS_EVENT.Start_Time AS CHAR) ,
ADS_OBJECT_TYPE_STR.Object_Type_Name ,
CONVERT(FLOAT, ADS_EVENT.Duration_ms) / 1000 ,
CASE ADS_STATUS_STR.Status_ID
WHEN 0 THEN 'successful'
ELSE 'failed'
END,
--- ADDED THIS COLUMN
REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1)) AS FileName
FROM ADS_EVENT_TYPE_STR
INNER JOIN ADS_EVENT_TYPE ON ( ADS_EVENT_TYPE.Event_Type_ID = ADS_EVENT_TYPE_STR.Event_Type_ID
AND ADS_EVENT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_EVENT ON ( ADS_EVENT.Event_Type_ID = ADS_EVENT_TYPE.Event_Type_ID )
INNER JOIN ADS_OBJECT_TYPE_STR ON ( ADS_EVENT.Object_Type_ID = ADS_OBJECT_TYPE_STR.Object_Type_ID
AND ADS_OBJECT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_STATUS_STR ON ( ADS_EVENT.Event_Type_ID = ADS_STATUS_STR.Event_Type_ID
AND ADS_EVENT.Status_ID = ADS_STATUS_STR.Status_ID
AND ADS_STATUS_STR.Language = 'en'
)
-- ADDED THIS CROSS APPLY
CROSS APPLY( VALUES (STUFF(ADS_EVENT.Object_FolderPath,1,CHARINDEX('/',ADS_EVENT.Object_FolderPath+'/'),'')) ) AS B(b)
WHERE ( ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run'
AND DATEPART(yyyy, ADS_EVENT.Start_Time) >= 2016
AND ADS_EVENT.Top_Folder_Name NOT IN ( '', 'Auditing',
'Feature Samples',
'Functions & Templates',
'IT SERVICES',
'IT SERVICES - Copy (1)',
'IT SERVICES - OLD',
'IT SERVICES [1]',
'itelliTest', 'LCM',
'Report Conversion Tool',
'Report Samples',
'Report Samples - Copy (1)',
'Web Intelligence Samples' )
);Hi James
Thanks ever so much for your help. Unfortunately when I enter this query into the BI Launch pad web intelligence I get the following error message
The query SQL has 9 columns instead of 8 columns.(WIS 10810)
Cause The SQL generated by the query has an invalid number of columns.
Lyn
Another method would be
DECLARE @x VARCHAR(256) = '/Standards Development /DATABASE SERVICES/Trade associations/ABFT/'
select right(x,charindex('/',reverse(x))-1)
from
(
select substring(@x,1,len(@x)-1) as x
) as t
In the query I posted I ADDED a column - see the line below the comment "--- ADDED THIS COLUMN". Perhaps that is not what you want? Did you want to replace one of the existing columns with that?
Hi James
if we can replace either the Object_type_name or the object_folder_path that would be great.
Thank you again
Lyn
You can remove the columns or move columns around, as in the example below where I replaced the Object_Type_Name with the new value
SELECT ADS_EVENT.Top_Folder_Name ,
ADS_EVENT.Object_Name ,
ADS_EVENT.Object_Folder_Path ,
ADS_EVENT.User_Name ,
CAST(ADS_EVENT.Start_Time AS CHAR) ,
--ADS_OBJECT_TYPE_STR.Object_Type_Name ,
REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1)) AS Object_Type_Name
CONVERT(FLOAT, ADS_EVENT.Duration_ms) / 1000 ,
CASE ADS_STATUS_STR.Status_ID
WHEN 0 THEN 'successful'
ELSE 'failed'
END
FROM ADS_EVENT_TYPE_STR
...rest of your code here.Hi James
Thank you for all your help.
I have now got the code
SELECT ADS_EVENT.Top_Folder_Name ,
ADS_EVENT.Object_Name ,
ADS_EVENT.Object_Folder_Path ,
ADS_EVENT.User_Name ,
CAST(ADS_EVENT.Start_Time AS CHAR) ,
--ADS_OBJECT_TYPE_STR.Object_Type_Name ,
REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1)) AS Object_Type_Name
CONVERT(FLOAT, ADS_EVENT.Duration_ms) / 1000 ,
CASE ADS_STATUS_STR.Status_ID
WHEN 0 THEN 'successful'
ELSE 'failed'
END,
--- ADDED THIS COLUMN
---REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1)) AS FileName
FROM ADS_EVENT_TYPE_STR
INNER JOIN ADS_EVENT_TYPE ON ( ADS_EVENT_TYPE.Event_Type_ID = ADS_EVENT_TYPE_STR.Event_Type_ID
AND ADS_EVENT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_EVENT ON ( ADS_EVENT.Event_Type_ID = ADS_EVENT_TYPE.Event_Type_ID )
INNER JOIN ADS_OBJECT_TYPE_STR ON ( ADS_EVENT.Object_Type_ID = ADS_OBJECT_TYPE_STR.Object_Type_ID
AND ADS_OBJECT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_STATUS_STR ON ( ADS_EVENT.Event_Type_ID = ADS_STATUS_STR.Event_Type_ID
AND ADS_EVENT.Status_ID = ADS_STATUS_STR.Status_ID
AND ADS_STATUS_STR.Language = 'en'
)
-- ADDED THIS CROSS APPLY
CROSS APPLY( VALUES (STUFF(ADS_EVENT.Object_FolderPath,1,CHARINDEX('/',ADS_EVENT.Object_FolderPath+'/'),'')) ) AS B(b)
WHERE ( ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run'
AND DATEPART(yyyy, ADS_EVENT.Start_Time) >= 2016
AND ADS_EVENT.Top_Folder_Name NOT IN ( '', 'Auditing',
'Feature Samples',
'Functions & Templates',
'IT SERVICES',
'IT SERVICES - Copy (1)',
'IT SERVICES - OLD',
'IT SERVICES [1]',
'itelliTest', 'LCM',
'Report Conversion Tool',
'Report Samples',
'Report Samples - Copy (1)',
'Web Intelligence Samples' )
However it is still showing the folder path in exactly the same order with the / at both the beginning and the end, I am really really confused now.
Lyn
Hi Madhivanan
Thanks for the response, as I said to James I am really a bit thick when it comes to using declare.
How would I use it in the original query, Web intelligence does not allow you to add in an extra column in the SQL query.
Hi James
Thank you. How do I include it in this query. Sorry I don't know much about using declare. the field is ADS_EVENT.Object_FolderPath
SELECT
ADS_EVENT.Top_Folder_Name,
ADS_EVENT.Object_Name,
ADS_EVENT.Object_Folder_Path,
ADS_EVENT.User_Name,
CAST( ADS_EVENT.Start_Time AS CHAR ),
ADS_OBJECT_TYPE_STR.Object_Type_Name,
Convert(float,ADS_EVENT.Duration_ms) / 1000,
CASE ADS_STATUS_STR.Status_ID WHEN 0 THEN 'successful' ELSE 'failed' END
FROM
ADS_EVENT_TYPE_STR INNER JOIN ADS_EVENT_TYPE ON (ADS_EVENT_TYPE.Event_Type_ID=ADS_EVENT_TYPE_STR.Event_Type_ID AND ADS_EVENT_TYPE_STR.Language = 'en')
INNER JOIN ADS_EVENT ON (ADS_EVENT.Event_Type_ID=ADS_EVENT_TYPE.Event_Type_ID)
INNER JOIN ADS_OBJECT_TYPE_STR ON (ADS_EVENT.Object_Type_ID=ADS_OBJECT_TYPE_STR.Object_Type_ID AND ADS_OBJECT_TYPE_STR.Language = 'en')
INNER JOIN ADS_STATUS_STR ON (ADS_EVENT.Event_Type_ID=ADS_STATUS_STR.Event_Type_ID and ADS_EVENT.Status_ID=ADS_STATUS_STR.Status_ID AND ADS_STATUS_STR.Language = 'en')
WHERE
(
ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run'
AND
datepart(yyyy, ADS_EVENT.Start_Time) >= 2016
AND
ADS_EVENT.Top_Folder_Name NOT IN ( '','Auditing','Feature Samples','Functions & Templates','IT SERVICES','IT SERVICES - Copy (1)','IT SERVICES - OLD','IT SERVICES [1]','itelliTest','LCM','Report Conversion Tool','Report Samples','Report Samples - Copy (1)','Web Intelligence Samples' )
)
Since I don't have access to your system, I can only be of limited help here. You have to look at the query and correlate that with what your front-end client wants to get.
A query has several sections/logical pieces. In the code below, I have intended the text so you can distinguish different sections .
Look at the first section which is the one that starts with SELECT. It returns 8 columns which I have numbered. Anything after a dash-dash ( -- ) is a comment, so you can ignore all of that. Are those 8 columns the ones your client front end expects? For example, there is a column called Object_Folder_Path. That is probably what you are seeing with the full path name. The column that I added is #6 named Object_Type_Name.
You don't have to try to become an expert, but read up a little bit on SELECT statements, for example here.
-----------------------------------------------------------------------------
SELECT ADS_EVENT.Top_Folder_Name , -- 1
ADS_EVENT.[Object_Name] , -- 2
ADS_EVENT.Object_Folder_Path , -- 3
ADS_EVENT.User_Name , -- 4
CAST(ADS_EVENT.Start_Time AS CHAR) , -- 5
REVERSE(LEFT(b, CHARINDEX('/', b + '/') - 1)) AS Object_Type_Name , -- 6
CONVERT(FLOAT, ADS_EVENT.Duration_ms) / 1000 , -- 7
CASE ADS_STATUS_STR.Status_ID
WHEN 0 THEN 'successful'
ELSE 'failed'
END -- 8
-----------------------------------------------------------------------------
FROM ADS_EVENT_TYPE_STR
INNER JOIN ADS_EVENT_TYPE ON ( ADS_EVENT_TYPE.Event_Type_ID = ADS_EVENT_TYPE_STR.Event_Type_ID
AND ADS_EVENT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_EVENT ON ( ADS_EVENT.Event_Type_ID = ADS_EVENT_TYPE.Event_Type_ID )
INNER JOIN ADS_OBJECT_TYPE_STR ON ( ADS_EVENT.Object_Type_ID = ADS_OBJECT_TYPE_STR.Object_Type_ID
AND ADS_OBJECT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_STATUS_STR ON ( ADS_EVENT.Event_Type_ID = ADS_STATUS_STR.Event_Type_ID
AND ADS_EVENT.Status_ID = ADS_STATUS_STR.Status_ID
AND ADS_STATUS_STR.Language = 'en'
)
-- ADDED THIS CROSS APPLY
CROSS APPLY ( VALUES
( STUFF(ADS_EVENT.Object_FolderPath, 1,
CHARINDEX('/', ADS_EVENT.Object_FolderPath + '/'), '')) ) AS B ( b )
-----------------------------------------------------------------------------
WHERE ( ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run' )
AND DATEPART(yyyy, ADS_EVENT.Start_Time) >= 2016
AND ADS_EVENT.Top_Folder_Name NOT IN ( '', 'Auditing',
'Feature Samples',
'Functions & Templates',
'IT SERVICES',
'IT SERVICES - Copy (1)',
'IT SERVICES - OLD',
'IT SERVICES [1]', 'itelliTest',
'LCM', 'Report Conversion Tool',
'Report Samples',
'Report Samples - Copy (1)',
'Web Intelligence Samples' );
-----------------------------------------------------------------------------