|
|
thanks, i didn't find the right information
|
|
|
|
|
nevermind, found it: sql = "select FIRST 1 SKIP 10 * from RT_TIME ORDER BY RTTI_COUNTER desc "
|
|
|
|
|
Hi!
We are re-writing existing access DB process into Oracle.
With that said, we need to calculate averages upon request. The data is loaded everyday through datawarehouse. We would need to store amount value everyday, maybe into another table, and be able to calculate the average upon request? Just have a table listing the key fields in addition to date and amount? with date and amount changing everyday?
Is there any other option to do this? If it were SQL server, I could have explored the SSIS packages. Not sure in Oracle.
thank you!
|
|
|
|
|
You ask "your" users what sort of queries they "typically" deal with; and (re)design your data warehouse based on that.
This isn't something you base on opinions gathered in the wild.
The Master said, 'Am I indeed possessed of knowledge? I am not knowing. But if a mean person, who appears quite empty-like, ask anything of me, I set it forth from one end to the other, and exhaust it.'
― Confucian Analects
|
|
|
|
|
how to make number 56,500,51 in MySQL
I tried to make a function in MySQL like this
CREATE FUNCTION `fTITIK`(number double(8,2)) RETURNS VARCHAR(255) CHARSET latin1
DETERMINISTIC
BEGIN DECLARE hasil VARCHAR(255);
SET hasil = REPLACE(REPLACE(REPLACE(FORMAT(number, 2), '.', '|'), ',', '.'), '|', ','); RETURN (hasil);
END
hen when it starts it only appears 56,500 while the numbers that are behind the comma do not work
any suggestion?
what should I fix?
modified 17-Sep-19 23:58pm.
|
|
|
|
|
It looks like the value represents a floating point number (or currency.
If that's the case, don't store it as a string. The app that uses the data is responsible for correctly formatting the value for display purposes.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
|
Do your data formatting in the user interface (that includes reports). NEVER do it in the database and as John said never store your numbers as strings ALWAYS use the correct data format. This also applies to dates, store them as DATE or DATETIME, never as strings.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
|
|
There are umpteen reasons why a database may start having performance issues - it is unlikely to be your unchanged code!
This post lists some of the troubleshooting steps you can try to either find out what is wrong and/or just do some housekeeping that should help [SOLVED] SQL Server database slowness troubleshooting - Spiceworks[^]
|
|
|
|
|
Found it...
Between 2 recreations and data repopulation.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index
That seemed like a good idea at the times, they said.... 
|
|
|
|
|
Check the execution plan. Check that you have the correct indexes on the tables. Try running Brent Ozar's First Responder Kit[^] on the server to see if there are any obvious errors.
You mention that the size of the data has increased. Does it now exceed the server's available memory? If it keeps having to go back to disk to load the data, then that can dramatically slow things down. Especially if the data isn't on an SSD.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Found it...
Between 2 recreations and data repopulation.. someone one changed the primary key of many tables that were used in joins from CLUSTERED to NONClUSTERED index
That seemed like a good idea at the times, they said....
|
|
|
|
|
|
Check your index if the fragmentation is high then re organize it or re index. And If you have index like non cluster or cluster and you changed it or add new index please review.thanks
|
|
|
|
|
|
Not legally, no.
Which means you will get no help here.
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
|
Hello All,
I am looking for some help with the following query. The results I get back are fine as long as there is data in the ShiftData table that meets the criteria. If there is no data in the ShiftDaa table then my query returns nothing. What I am after is to return back all LoopCodes and Choppers from MapBushingData table and zeros if there are no items in ShiftData table matching the criterion. Any help is most appreciated.
SELECT MapBushingData.LoopCode,
MapBushingData.Chopper,
Sum(ShiftData.BRKS) AS Total_BRKS,
Sum(ShiftData.BBOH) AS Total_BBOH,
Avg(ShiftData.DTAB) AS Avg_DTAB
FROM ShiftData
INNER JOIN MapBushingData ON ShiftData.[Position] = MapBushingData.LoopCode WHERE ShiftData.ShiftDate >= cast(? as date) AND ShiftData.ShiftDate <= cast(? as date)
GROUP BY MapBushingData.Chopper,MapBushingData.LoopCode
Thanks,
Frank
|
|
|
|
|
A couple of tweaks is all you are going to need:
Using an INNER JOIN requires matching record to be in both tables.
To get all records from one table regardless if it matches or not you would use either a LEFTRIGHT JOIN. As you want all the records from the second table in the ON connector it would be a RIGHT JOIN
The second thing is dealing with returning 0 if no records match. Relatively easy, just slap an ISNULL (or COALESCE for ANSI SQL) with 0 as the null replacement.
My first attempt at this came up with
SELECT m.LoopCode
, m.Chopper
, Total_BRKS = IsNull(Sum(s.BRKS), 0)
, Total_BBOH = IsNull(Sum(s.BBOH), 0)
, Avg_DTAB = IsNull(Avg(s.DTAB), 0)
FROM @ShiftData s
RIGHT JOIN @MapBushingData m ON s.[Position] = m.LoopCode
WHERE ( s.ShiftDate >= cast('02/03/2011' as date)
AND s.ShiftDate <= cast('03/04/2013' as date)
)
OR s.ShiftDate IS NULL
GROUP BY m.Chopper,m.LoopCodeAnd in testing I found out that IF there is a matched record BUT the date was out of range it would not return the record.
If this is desired; great.
If not, we are going to tweak how the data is JOINed together by relocating the WHERE clauses to also be part of the JOIN connections. And this is what I came up with
DECLARE @ShiftData TABLE (ndx int identity(1,1) not null, Position int, ShiftDate Date, BRKS int, BBOH int, DTAB int)
INSERT @ShiftData
VALUES (1, '10/11/2012', 1, 2, 3)
, (2, '12/11/2013', 3, 4, 5)
, (3, '10/11/2011', 1, 2, 3)
DECLARE @MapBushingData TABLE (ndx int identity(1,1) not null, LoopCode int, Chopper varchar(10))
INSERT @MapBushingData
VALUES (1, 'Chopper 1')
, (2, 'Chopper 2')
, (3, 'Chopper 3')
Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional
|
|
|
|
|
Just noticed a block of code is missing, will get this updated within the next couple of hours
Director of Transmogrification Services
Shinobi of Query Language
Master of Yoda Conditional
|
|
|
|
|
Reworked my query as shown below and all is good now. Thank you very much for your response.
SELECT m.LoopCode
, m.Chopper
,
SUM(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
THEN s.BRKS ELSE 0
END) AS Total_BRKS
,
SUM(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
THEN s.BBOH ELSE 0
END) AS Total_BBOH
,
AVG(CASE WHEN s.ShiftDate >= cast(? as date) and s.ShiftDate <= cast(? as date)
THEN s.DTAB ELSE 0
END) AS Avg_DTAB
FROM ShiftData s
RIGHT JOIN MapBushingData m ON s.[Position] = m.LoopCode
GROUP BY m.Chopper,m.LoopCode
ORDER BY m.LoopCode asc
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
