|
SELECT TOP 1 [date], qte
FROM YourTable
ORDER BY [date];
|
|
|
|
|
How to access database using RDBMS from a object oriented language C++?
What are the ways to do it?
|
|
|
|
|
|
Hello,
How do you approach typical scenario of custom values (key/value pairs) for an entity? The example would be something like this:
class Person
{
string Name{get;set;}
IEnumerable<CustomProperty> CustomProperties {get;set;}
}
class CustomProperty
{
string Name{get;set;}
string Value{get;set;}
}
The problem is that Value needs to be of different types so e.g custom property called Color would have a string value of e.g. "Red" while Age would have to be treated as a number (even though it's stored as nvarchar). How would you approach this with a requirement to be able to filter and order over custom properties (multiple at once also with OR/AND) when querying Persons so I'd like to e.g. query over Persons of Age greater than 10 while Value is stored as nvarchar so casting is needed for comparing as numbers instead of strings.
The implementation is based on EF Core 5 and C#.
Possible ways:
1) Building query with casting/converting and adding PropertyType to the CustomProperty entity to decide which type is it. This would store everything in a single table CustomProperties. That's tricky but doable, I guess. The problem would be filtering and ordering with casting everything based on PropertyType. Might not be possible to create every queryable as translatable to TSQL.
2) Creating a separate table per a custom property type so creating tables: StringProperties, IntegerProperties, DecimalProperties which would simply be {string Name, int Number}, {string Name, string Value}, {string Name, DateTime Timestamp} etc. And all these would have a relationship to Person entity, lazy loaded. This would be kinda easy to implement, query and adding new types in the future.
3) The same as 2 but with latest EF Core 5 feature of Table Per Type, meaning there is a base class e.g. CustomProperty with Id and Name and specific classes deriving from base class e.g. IntegerProperty : CustomProperty. EF Core 5 creates the hierarchy in SQL correctly along with FKs. So pretty similar but using the latest feature.
|
|
|
|
|
|
I can't understand why would you like to be forced to create another table for relations if you can just use smth like references in most languages. What is the point of such complexity?
I would rather type:
companies.First(cmpn=>cmpn.Name="Netflix").Employees.Avg(empl=>empl.Sal)
then use relations:
select * from companies join company_employess on ... join ... group by.. avg
I found some NoSQL DBs like Couchbase and other alternatives but all the companies demand SQL-devs.
Why is it the way it is?
(I'm using ASP.NET 6 + Blazor)
|
|
|
|
|
Because you are not using the database you are using c# to do all your work. So only your application will work with the creation you call a database. Most of us (and I'm an old fart) know what a relational database is used for - storing data in a RELATED form and therefore any application can use the same relationships, reporting for instance.
You need to do some research into the difference between a document database (NoSQl) and a traditional RMBDS.
While there is a need and use for NoSql your example is not one of them EVERY company needs a RMDBS for this type of data.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Hopeless Idealist wrote: I would rather type:...but all the companies demand SQL-devs.
First of course because companies do not operate to please you.
They operate to make money. And that comes from pleasing customers. Both in sales and in follow up support.
Additionally they must keep costs low.
At the end of the day technologies do not drive company success. Rather it is matter of successfully creating a product(s)/service(s) which the company can maintain and enhance. And of course then selling it in the first place.
Large companies almost always have multiple persisted datastores. And I only say 'almost' because I can't be sure that there is not one company out there that only uses one. I do know that Microsoft, Oracle, Netflix, Google and Amazon all use different ones.
|
|
|
|
|
hi ,
i have a list of id values ( listOfProducts ) returned from a dropdown list , i need to use them to filter in a sql request
something like this :
SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (listOfProducts) (not sure about that)
i need to get all the records that have a product id that is contained inside the list from the dropdown
thanks
|
|
|
|
|
|
Send the list of products as comma seperated to parameter List of Products and change this query into Dynamic query.
For ex: @listOfProducts is parameter. Send as tilde or comma seperated to SQL query
Inside Query, split the comma seperated and change to list. Use the below script.
DECLARE @listOfProducts VARCHAR(2000)
SET @listOfProducts='174~175~78~77~191~399~451'
IF OBJECT_ID('tempdb..#listOfProducts') IS NOT NULL DROP TABLE #listOfProducts
SELECT
DISTINCT Split.a.value('.', 'VARCHAR(100)') AS Prod INTO #listOfProducts
FROM (SELECT
CAST ('<M>' + REPLACE(@listOfProducts, '~', '</M><M>') + '</M>' AS XML) AS String )
AS A CROSS APPLY String.nodes ('/M') AS Split(a)
WHERE Split.a.value('.', 'VARCHAR(100)')<>''
SELECT stock_id , loc_id
FROM S_ware D
WHERE D.product_id IN (select Prod from #listOfProducts)
|
|
|
|
|
Just for the record, that is not a Dynamic Query
|
|
|
|
|
There's no table named "S_ware" ... so the FROM clause will issue (most likely) "Invalid object name". I've also noticed that, in the second post to this thread, you've given a bit more information ... but you're declaring a variable as VARCHAR; the SET is ok. But suddenly 'listOfProducts' has become a temporary TABLE. So there are naming issues also.
This all sounds like pedantry from me so I'll quit. Suffice to say I'd look up TABLE, SET, DECLARE, and the advanced XML in the BOL for SQL Server version you think you have.
|
|
|
|
|
I need to store lots and lots and lots of timeseries data. Each series is keyed by a unique key, and once written it will almost never be updated. I'm literally after a key/value storage system that's persisted.
I also don't want to use Azure. No offense, Microsoft, but...
So my thoughts were Redis (not the safest), postgres (bit overkill), Cassandra (seems to like writes better than reads and my use case is the opposite), or mongoDB.
Did I mention I want this to cost less than a coffee a week. A good coffee, but I'm not paying $50 a month for this. Total data will be < 1TB.
Most likely this will be running on a Windows or Linux box against a .NET 5 app.
cheers
Chris Maunder
|
|
|
|
|
Chris Maunder wrote: and once written it will almost never be updated
I love specs like this - NOT.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I can promise you that X will never, ever, ever happen. Except sometimes randomly when I need it to happen.
cheers
Chris Maunder
|
|
|
|
|
Have you considered good old Berkeley DB? Oracle Berkeley DB It's not a DBMS, so might not fit your use case.
Keep Calm and Carry On
|
|
|
|
|
I heard the name Cassandra was a direct commentary on Oracle (Cassandra being a cursed Oracle, and all that)
cheers
Chris Maunder
|
|
|
|
|
Data and no "record counts" makes it hard to visualize a solution; or what needs to be done with the "time series data" subsequently; or how much of it there is for a given key.
I used a database table to index a file system of postal carrier address labels (images). Mostly used as an audit trail. The label key was a GUID.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Imagine you load a file that has 3 sets of timeseries data: attributeA, attributeB and attributeC. Each timseries is of the form Array<time, float>. There will be between 1000 and 15,000 time/value pairs in each series, so maybe 8Kb to 120Kb in each series.
I will never query the data in the array. I will only ever return it as a chunk of data (meaning I could compress it into a BLOB for higher storage efficiency at a tradeoff in load speed if I needed to)
A classic database such as MySQL or SQL Server seems massive overkill for this.
cheers
Chris Maunder
|
|
|
|
|
Hi,
Trying to start a New Database in Open Office. Cannot do so, the system baulks at a missing JRE. Installed several of these, to no avail. Updated the Environment Path settings (now that brings me back 40 years)(That was on the advice on UTube from some indian Guru in these matters), Still to no avail. How can one get this DB to go! To be clear, I want (for now) to create a Database on and for one computer only.
Regards,
Bram.
Bram van Kampen
|
|
|
|
|
|
Well Richard,
No, Not so far, Thanks for the link. Going there next.
Bram van Kampen
|
|
|
|
|
Hi, everyone.
I'm trying to recover data from SQL Server using the pivot operator. My command is:
select isnull([1], 0) as Janeiro,
isnull([2], 0) as Fevereiro,
isnull([3], 0) as Março,
isnull([4], 0) as Abril,
isnull([5], 0) as Maio,
isnull([6], 0) as Junho,
isnull([7], 0) as Julho,
isnull([8], 0) as Agosto,
isnull([9], 0) as Setembro,
isnull([10], 0) as Outubro,
isnull([11], 0) as Novembro,
isnull([12], 0) as Dezembro
from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP, PAGAMENTO P
where PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO AND P.SUPA_NA_CODIGO = 5) Tab1
pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1
The way it is above it works fine. Now I need to multiply the sum by a factor, say 0.95. I tried the following:
pivot (0.95 * sum(PAPA_RE_VALORPAGAMENTO) ...
pivot (sum(0.95 * PAPA_RE_VALORPAGAMENTO) ...
0.95 * pivot (sum(PAPA_RE_VALORPAGAMENTO) ...
None of these worked. Can anyone help me? Which is the right way?
Thanks.
|
|
|
|
|
"None of these worked" because the syntax for PIVOT[^] clearly states Quote: PIVOT
(
<aggregation function="">(<column being="" aggregated="">)
So just put the results of the PIVOT into a sub-query or a Common Table Expression or temporary table or a table variable, then manipulate that data. E.g.
;with cte as
(
select isnull([1], 0) as Janeiro,
isnull([2], 0) as Fevereiro,
isnull([3], 0) as Março,
isnull([4], 0) as Abril,
isnull([5], 0) as Maio,
isnull([6], 0) as Junho,
isnull([7], 0) as Julho,
isnull([8], 0) as Agosto,
isnull([9], 0) as Setembro,
isnull([10], 0) as Outubro,
isnull([11], 0) as Novembro,
isnull([12], 0) as Dezembro
from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP
inner join PAGAMENTO P on PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO
where P.SUPA_NA_CODIGO = 5) Tab1
pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1
)
select 0.95 * Janeiro,0.95 * Fevereiro,0.95 * Março,0.95 * Abril,
0.95 * Maio,0.95 * Junho,0.95 * Julho,0.95 * Agosto,
0.95 * Setembro,0.95 * Outubro,0.95 * Novembro,0.95 * Dezembro
from cte; Couple of other points to note
- I've changed the JOIN to use an ON clause rather than defining the join using the WHERE clause. Your style is quite old-fashioned and prevents you from using OUTER joins
- It's a lot easier to answer questions like this if you supply the table schemas and some sample data along with your expected results. And always be specific - "None of these worked" is not helpful. "I get an error reported 'Incorrect syntax near '0.95'.' might have got you an answer quicker
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
