|
Hi, thanks for responding. You said "calling it userstable is redundant", that was a typo.
|
|
|
|
|
What r the works u r doing in the IT company..I want to become plsql developer..please share ur works in brief..
|
|
|
|
|
The first thing you should do is learn to use a keyboard!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I foresee a return to single letter variables. Bring back the ZX80!
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
|
|
|
|
|
Hi all,
Suppose I have a database table named JobsTable and among the fields in the table are JobDescriptions, and JobTitle. How would I write my Where clause if users can get all the JobTitle where the JobDescriptions field contains any word in the comma separated list they entered?
I tried the Like keyword but it is not doing what I want. Thanks in advance for your help.
|
|
|
|
|
I have a function (every dev does) which splits a string and returns a table with ID and Item, you could then join the split with your data table
Select *
From Table T
inner join fn_Split(@List,',') l on L.item = T.FieldName
ALTER FUNCTION [dbo].[fn_Split]
(@List varchar(8000), @Delimiter char(1))
RETURNS @Results table
(Item varchar(8000),ID int Identity(1,1))
AS
begin
declare @IndexStart int
declare @IndexEnd int
declare @Length int
declare @Word varchar(8000)
set @IndexStart = 1
set @IndexEnd = 0
set @Length = len(@List)
If @Delimiter = '' Set @Delimiter = ','
Set @List = Replace(@List,char(9),'')
Set @List = Replace(@List,char(10),'')
Set @List = Replace(@List,char(13),'')
while @IndexStart <= @Length
begin
set @IndexEnd = charindex(@Delimiter, @List, @IndexStart)
If @Delimiter = char(32)
set @IndexEnd = charindex(Space(1), @List, @IndexStart)
if @IndexEnd = 0
set @IndexEnd = @Length + 1
set @Word = substring(@List, @IndexStart, @IndexEnd - @IndexStart)
set @IndexStart = @IndexEnd + 1
INSERT INTO @Results(Item)
SELECT @Word
end
return
end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Yeah I probably snaffled that code from someone in the 90s and have not looked at it since  , I'm certain there is a better way of splitting the items.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi guys
I need some advice , i have a Query that runs very long because of the following lines of Code , i know you dont understand my data or table structure , you can just advice on a High Level
( CASE WHEN ( SELECT TOP 1
ATTRIB_CODE
FROM SDE.[NOTIFICATION] WITH ( NOLOCK )
WHERE ( LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = LP.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
AND FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
)
AND ARCHIVE_DATE IS NULL
) LIKE '00090009%'
THEN ( SELECT TOP 1
ATTRIB_CODE COLLATE SQL_Latin1_General_CP1_CI_AS
FROM SDE.[NOTIFICATION] N WITH ( NOLOCK )
WHERE N.ARCHIVE_DATE IS NULL
AND N.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
AND N.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
AND N.ATTRIB_CODE LIKE '00090009%'
AND N.ARCHIVE_DATE IS NULL
AND V.ARCHIVE_DATE IS NULL
)
ELSE ISNULL(( SELECT TOP 1
ATTRIBUTE_CODE
FROM SDE.VALUATION WITH ( NOLOCK )
WHERE ( LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.LIS_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
AND FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS = V.FUNCTION_KEY COLLATE SQL_Latin1_General_CP1_CI_AS
)
AND VAL_STATUS_ID = 2
AND ARCHIVE_DATE IS NULL
), '')
END ) AS ATTRIBUTE_CODE ,
thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
SQL Server? Did you check the execution plan?
|
|
|
|
|
Vuyiswa Maseko wrote: you can just advice on a High Level
There is a high level checklist for performance here[^].
I'd recommend against optimizer hints unless you can explain how SQL server handles locking. I'd also recommend on removing the collation-checks; it would be set once for the server, and not be repeated in each query as that takes extra time.
It also looks a lot like logic that could be run when inserting/updating the record. If that is not helping enough, then look into partitioning the table.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
As a last resort look into Parameter Sniffing[^], I know it sounds like a perversion but it can be an issue.
Basically the work around is to create a set of local variables for each parameter passed into the proc and use the local variables in the proc and not the parameters.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you very much for the Advice, no my Query runs for 5 Min on 800 000 Records
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
On this query, I get 2 sets of results for each part number, which is probably what I asked for.
I'm trying to get 1 set of results for each part number, I just need to figure out the proper method.
I'll try some critical thinking here, it can't be the group by clause, so perhaps I'm suppose to wrap the union in a select statement like my count in the post below? But then I have the distinct, and I don't want to mess that up.
Once I have this done, I think I'm done writing for these old account mate dos programs.
Dim queryString As String = _
" SELECT " & _
" DISTINCT h.FITEMNO " & _
", SUM(h.FSHIPQTY) " & _
", AVG(h.FCOST) " & _
", AVG(h.FPRICE) " & _
", SUM(h.FSHIPQTY * h.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(h.FSHIPQTY * h.FPRICE - h.FSHIPQTY * h.FCOST)" & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" GROUP BY h.FITEMNO "
queryString +=
" UNION ALL "
queryString +=
" SELECT " & _
" DISTINCT v.FITEMNO " & _
", SUM(v.FSHIPQTY) " & _
", AVG(v.FCOST) " & _
", AVG(v.FPRICE) " & _
", SUM(v.FSHIPQTY * v.FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = v.FITEMNO) AS FREALDESC " & _
", SUM(v.FSHIPQTY * v.FPRICE - v.FSHIPQTY * v.FCOST)" & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" GROUP BY v.FITEMNO "
|
|
|
|
|
I think got it. The numbers look correct on a couple of test items.
I'm shocked that it works, doesn't really make sense to me how the inner select lines up with the main select.
queryString +=
" SELECT " & _
" DISTINCT FITEMNO " & _
", SUM(FSHIPQTY) " & _
", AVG(FCOST) " & _
", AVG(FPRICE) " & _
", SUM(FSHIPQTY * FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST)" & _
" FROM " & _
"(" & _
" SELECT " & _
" h.FITEMNO " & _
" , h.FSHIPQTY " & _
" , h.FCOST " & _
" , h.FPRICE " & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" UNION ALL " & _
" SELECT " & _
" v.FITEMNO " & _
" , v.FSHIPQTY " & _
" , v.FCOST " & _
" , v.FPRICE " & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
")" & _
" GROUP BY FITEMNO "
|
|
|
|
|
Sort of a dumb question here, I have 2 DBF files, fox-pro DBF files that are identical, in which I want to union all and get the count of the FITEMNO, which there is 1 in the first file and 1 in the 2nd file, so I get a count of 2.
Is this normal behavior or did I construct the statement wrong below?
SELECT COUNT(FITEMNO) AS hCount
FROM
(
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE
FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
AND
FCUSTNO = @FCUSTNO
AND
FITEMNO = @FITEMNO
GROUP BY FITEMNO
UNION ALL
SELECT
FITEMNO
FROM ARTRS01H.dbf
WHERE
FSHIPDATE >= @startDate AND FSHIPDATE <= @stopDate
AND
FCUSTNO = @FCUSTNO
AND
FITEMNO = @FITEMNO
GROUP BY FITEMNO
)
|
|
|
|
|
That's what you asked for.
Suggestions:
0) Move the GROUP BY to the outer SELECT
or
1) Use SUM in the outer SELECT
|
|
|
|
|
OK, Thanks
That's what I thought.
|
|
|
|
|
Hi everyone,
I have a view with multiple outer joins and a on a lot of the fields I'm invoking a scalard functions.
The result is a very poor performance. I've been browsing the internet quite some time now (also read the article: Using Table-Valued Functions in SQL Server) but I can't seem to manage it on my own.
I know the difference between SVF and TVF and that's why I want to use TVF.
This is the Scalard function I want to recreate into a Table Valued Function:
The Shipping unit of my articles must be divided by 100 except for articles < 99999 or article 4859696
USE [Staging]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Devide_Shipping_Unit]
(
@ValueString nvarchar(8),
@ArticleString nvarchar(8)
)
RETURNS Int
AS
BEGIN
DECLARE @result int
IF ISNUMERIC(@ValueString)<> 1 OR ISNUMERIC(@ArticleString)<> 1
BEGIN
set @result = 0
END
ELSE
BEGIN
if Cast(@ArticleString AS int) <= 99999 or @ArticleString = '4859696'
set @result = 1 --CAST(@ValueString AS int) / 1000
Else
set @result =CAST(@ValueString AS int) / 100
END
return @result
END
GO
Another scalar function is a conversion of dates to a numeric datatype:
ALTER FUNCTION [dbo].[CONVERT_DATE_TO_NUMERIC]
(
@dateValueString nvarchar(10)
)
RETURNS Numeric(8, 0)
AS
BEGIN
DECLARE @result Numeric(8, 0)
--DECLARE @dateValueString nvarchar(10) = '03/12/2013'
DECLARE @startdate nvarchar(10) = ''
-- set @dateValueString = '2013-07-23'
set @dateValueString = REPLACE(@dateValueString,'/','')
set @dateValueString = REPLACE(@dateValueString,'-','')
declare @_year nvarchar(4) = LEFT(@dateValueString,4)
declare @_month nvarchar(2) = SUBSTRING(@dateValueString,5,2)
declare @_day nvarchar(2) = RIGHT(@dateValueString,2)
set @startdate = @_year + @_month + @_day
IF ISNUMERIC(@startdate)<> 1
BEGIN
set @result = 0
END
ELSE
BEGIN
set @result = @startdate END
return @result
END
GO
Can anybody please help me I'm stuck 
Ambertje
|
|
|
|
|
Have you used SQL Profiler to identify the expensive parts of the query. It is much more likely you are missing indexes. Try that before dumping on the scalar functions.
A scalar that does not reference another database object (eg looks up a table) is not going to have a great deal of impact.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm working with Microsoft SQL Server Management Studio, I've searched for the profiler but can't find it.
Do you mean an SQL Server Profiler?
Can you give me an example or a link to where I can find that please?
This is what I get when I Set Statistics IO and Time ON:
Table 'Worktable'. Scan count 252, logical reads 20839, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AD'. Scan count 1, logical reads 109674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'D_Article'. Scan count 1, logical reads 729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'porcKD'. Scan count 1, logical reads 1473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 91947 ms, elapsed time = 119500 ms.
I wanted to create indexes on the View but a View doesn't allow to create indexes.
The view gets the data out of the bottom (STAGING) area in the form of text files.
The View is used to load the data through SSIS in TOP area (DWH)
No table in the Staging area contains indexes.
|
|
|
|
|
Ambertje wrote: a View doesn't allow to create indexes
Yes it does[^]. You just need to make sure the view is created WITH SCHEMABINDING.
However, to properly use an indexed view on any edition lower than SQL Enterprise, you'll need to use the WITH ( NOEXPAND ) query hint.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
My view contains aggregates, outer joins, subqueries so With Schemabinding wont work
|
|
|
|
|
Ambertje wrote: The view gets the data out of the bottom (STAGING) area in the form of text files.
Ah I thought you were doing something sensible, expecting performance from text files is not going to work.
I suggest you bulk copy the text files into staging tables, do attempt to apply transformations, and then use a stored proc to do the transforms from staging to your destination.
I would expect an order of magnitude performance gain with proper tuning of the staging indexes and procedure design. If the data is extreme you may want to look into dropping staging indexes before the insert and reimplementing them after import.
Doing transforms on the way in is a really lousy design, I know SSIS and all the tools do this but it is just wrong. Extract, Load, Transform
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try adding WITH SCHEMABINDING to your functions, between the RETURNS <type> and As lines.
http://www.sqlservercentral.com/blogs/sqlstudies/2014/09/15/i-schemabound-my-scalar-udf-and-you-wont-believe-what-happened-next/[^]
ISNUMERIC is not reliable. All of the following values will be considered numeric, but cannot be cast to an integer:
- ISNUMERIC('-')
- ISNUMERIC('.')
- ISNUMERIC('-$.')
If you're using SQL 2012 or higher, use the new TRY_PARSE function[^] instead. Otherwise, test that the string doesn't contain any non-numeric characters:
If @ValueString Like '%[^0-9]%' Or @ArticleString Like '%[^0-9]%'
Begin
set @result = 0
End
Assuming you're starting with a date or datetime / datetime2 type, your CONVERT_DATE_TO_NUMERIC function can be replaced with:
Convert(int, Convert(char(8), YourDateColumn, 112))
Passing 112 to the Convert function[^] formats the date as yyyyMMdd. Converting that to an integer gives the same result as your function.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
