rachmatmoradosugiarto

How To Deploy Sqlite With .net

noreply@blogger.com (Sandy Veum) — Thu, 22 Aug 2024 00:42:00 +0000

I got a report from a user that when they installed my .net 2.0 app they got a message that a reference to system.data.sqlite.dll could not be found. I use Inno to install it in th

Solution 1:

Yes, if it is in the folder with the executable, it works. But you will have to deal with 32 bit an 64 bit architectures, there are seperate assemblies for that. 32 bit is in /System.Data.Sqlite.dll, 64 bit in /x64/System.Data.Sqlite.dll.

Solution 2:

As has been pointed out this is probably an architecture problem. We are in fact having the same issue with Oracle.

You have ? solutions:

Create two installers one x86 and one x64 that use the appropriate sql lite dll.
Create an intelligent installer that copies the appropriate dll into the bin folder.
Install the relevant sql lite into the gac. You may need to create the installer for this and you would probably want to set it up as a prerequisite for your application.

If you are not tied to SqlLite you might want to take a look at SqlCe. The installers are already there and available as prerequisite in visual studio and chances are they it is probably already installed on some client machines.

Calculate Totals Of Field Based On Current Fiscal Year Only - Sql

noreply@blogger.com (Sandy Veum) — Thu, 22 Aug 2024 00:22:00 +0000

I have seen many examples regarding calculating the sum of fields using the fiscal year, but I can not find one that fits my needs. What I am trying to do is get just the current

Solution 1:

Add a where clause:

FROM mytable
WHERE startdate >= '2011-07-01'GROUPBY userid

Or with the start of the year dynamically:

where startdate >= dateadd(yy, datepart(yy, getdate())-2001, '2000-07-01')

Solution 2:

Maybe something like this:

SELECT
    userid,
    SUM(total_hours) - SUM(missed_hours) AS hours
FROM 
    mytable
WHERE 
    MONTH(startdate) BETWEEN 6 AND 7
    AND YEAR(startdate) IN (2011,2012)
GROUP BY userid

Solution 3:

For the solution, two additional information is needed

the name of the date column
the vendor type of RDBMS you are using

I supposed your date column is date_col and you are using MySQL

SELECT    
userid,    
SUM(total_hours) -SUM(missed_hours) AS hours    
FROM mytable    
WHERE date_col between STR_TO_DATE('01,7,2011','%d,%m,%Y') and STR_TO_DATE('01,7,2010','%d,%m,%Y')
GROUPBY userid

How Do I Rename A Column In A Database Table Using Sql?

noreply@blogger.com (Sandy Veum) — Thu, 22 Aug 2024 00:04:00 +0000

If I wish to simply rename a column (not change its type or constraints, just its name) in an SQL database using SQL, how do I do that? Or is it not possible? This is for any data

Solution 1:

Specifically for SQL Server, use sp_rename

USEAdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO

Solution 2:

On PostgreSQL (and many other RDBMS), you can do it with regular ALTER TABLE statement:

=>SELECT*FROM Test1;
 id | foo | bar 
----+-----+-----2|1|2=>ALTERTABLE Test1 RENAME COLUMN foo TO baz;
ALTERTABLE=>SELECT*FROM Test1;
 id | baz | bar 
----+-----+-----2|1|2

Solution 3:

In MySQL, the syntax is ALTER TABLE ... CHANGE:

ALTER TABLE <table_name> CHANGE <column_name><new_column_name><data_type> ...

Note that you can't just rename and leave the type and constraints as is; you must retype the data type and constraints after the new name of the column.

Solution 4:

Unfortunately, for a database independent solution, you will need to know everything about the column. If it is used in other tables as a foreign key, they will need to be modified as well.

ALTERTABLE MyTable ADD MyNewColumn OLD_COLUMN_TYPE;
UPDATE MyTable SET MyNewColumn = MyOldColumn;
-- add all necessary triggers and constraints to the new column...-- update all foreign key usages to point to the new column...ALTERTABLE MyTable DROPCOLUMN MyOldColumn;

For the very simplest of cases (no constraints, triggers, indexes or keys), it will take the above 3 lines. For anything more complicated it can get very messy as you fill in the missing parts.

However, as mentioned above, there are simpler database specific methods if you know which database you need to modify ahead of time.

Solution 5:

I think this is the easiest way to change column name.

SP_RENAME 'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'

"inheriting 'base', Which Is Not A Class" In Vs Code Using Sqlalchemy Declarative_base()

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 23:47:00 +0000

VS Code shows 'Inheriting 'Base', which is not a class' as an error message given the below: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Integer

Solution 1:

`Inheriting 'Base', which is not a class` is not actually an error.

Rather, it's a static analysis result coming from Microsoft's Python language server (which in turn leans heavily on pylint) for this kind of analysis. It's not always accurate: If a class is dynamically generated and returned by a function (as is the case here), the static-checking tools may not properly understand its type.

As described in microsoft/python-language-server#1390, this feature can be disabled with the following settings change:

"python.analysis.disabled":["inherit-non-class"],

Solution 2:

As of VS Code 1.47, when using Marshmallow to serialize/deserialize SQLAlchemy objects and inheriting from marshmallow_sqlalchemy.SQLAlchemyAutoSchema, using the solution from the other answer:

"python.analysis.disabled":["inherit-non-class"],

does not seem to work anymore (i.e. you will still get a "ma.SQLAlchemyAutoSchema', which is not a class." warning). You can instead use the more generic #noqa comment on specific lines:

ma = Marshmallow(app)

classUserSchema(ma.SQLAlchemyAutoSchema):  # noqaclass Meta:
        model = Person
        sqla_session = db.session

Note though, that VS Code treats #noqa as a disable-all setting for that line.

Distinct On Only One Column - Only Display First Duplicate Row

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 22:54:00 +0000

I'm using an SQL query in PHP to display 5 columns from my 'users' table. Here is my code for that: SELECT DISTINCT account_id, full_name, email, login, phone, updated_at, last_re

Solution 1:

You should probably add another column. Now it is possible to get distinct record for every account_id using GROUP BY clause but results of all nonagreggated columns can be ambigius. You have to have some order you approve or indicator inside group to determine which record for every accout_id is "first". With column marking which record in each group is first query is simple. Without it you have to accept some order telling query which record is "first". On example alphabetical order of full_name:

SELECT account_id, 
       full_name, 
       email, 
       login, 
       phone, 
       updated_at, 
       last_request_at, 
       unconfirmed_email 
  FROM table1 WHERE full_name IN (
    SELECTMIN(full_name) 
      FROM table1 
      GROUPBY account_id 
      WHERE id <'300'
  )

Solution 2:

Here is the solution to what you asked for,

SELECT account_id,full_name,Superhero
FROM(
SELECT p.*,( 
            CASE account_id 
            WHEN@curTypeTHEN@curRow :=@curRow+1ELSE@curRow :=1AND@curType := account_id END
          ) +1AS rank
FROM      test p, (SELECT@curRow :=0, @curType :='', @curRank :=0) r) A
WHERE rank=2;

Sql_Demo

Output:

ACCOUNT_ID  FULL_NAME   SUPERHERO
1             Batman    1
2            Spiderman  1
3           Professor X 1
4            Cyclops    1

Solution 3:

try this

select * from table1
groupby account_id

demo here

this will give you the first of every account_id

What Are The Use Cases For Temporary Tables In Sql Database Systems?

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 22:35:00 +0000

what are the main purposes for which temporary tables are used? I want to know the practical and commercial uses of temporary tables in actual softwares working in small and large

Solution 1:

In my experience, temporary tables are often used to store intermediate calculations in a complex series of CREATE or UPDATE queries that produce some sort of analysis result. An example might be the creation of summary tables for an OLAP database.

Temporary tables are also sometimes used to increase performance, in certain situations.

Solution 2:

I tend to prefer using CTEs (common table expressions) to temporary tables. There is a good discussion in answer to a similar question.

Sqlparameters Array In Vb.net

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 22:18:00 +0000

I'm trying to create a typed-sized parameters array in VB.Net: Dim parameters() As SqlParameter = New SqlParameter() _ { New SqlParameter('@first_name', SqlDbType

Solution 1:

You need to use the VB syntax for object initializers:

Dim parameters() As SqlParameter = New SqlParameter() _
        {
          New SqlParameter("@first_name", SqlDbType.VarChar, 50) With { .Value = "john"},
          New SqlParameter("@last_name", SqlDbType.VarChar, 50) With { .Value = "doe"},
          New SqlParameter("@age", SqlDbType.Int) With { .Value = 18},
          New SqlParameter("@id", SqlDbType.Int) With { .Value = 123}
        }

How To Write Dynamic Query Columns Into Rows In Sql Server?

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 21:29:00 +0000

I have a question about in SQL Server: how to combine 4 columns into 1 column in a query. Table: emp empid | addr | sal | doj ------+------+-----+------------ 1 | hyd | 10 |

Solution 1:

You could try using UNPIVOT, like this...

SELECT empid, empvalues
FROM (
    select empid, addr, convert(varchar(100), sal) as sal,  convert(varchar(100), doj, 103) as doj
    from emp
) pv
UNPIVOT
(
    empvalues
    FOR ev in (addr, sal, doj)
) AS ev

produces this output...

Resources for your reference...

https://codingsight.com/understanding-pivot-unpivot-and-reverse-pivot-statements/https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

Solution 2:

Use Cross Apply:

createtable emp (empid int, addr varchar(10), sal int, doj datetime) 

insertinto emp (empid, addr, sal, doj)
values (1, 'hyd', '10', '10-01-1990'),
  (2, 'del', '20', '12-03-1999'),
  (1, 'pune', '50', '12-03-2017'); 


select empid,empvalues from emp
cross apply (values (cast (addr asvarchar(10)))
,(cast (sal asvarchar(10)))  
,(Format(doj,'MM-dd-yyyy')))   d(empvalues)

Results:

empid   empvalues
1   hyd
110110-01-19902del220212-03-19991   pune
150112-03-2017

Create Tree Query From Numeric Mapping Table In Sql (specific Format)

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 20:53:00 +0000

I have an exported table from accounting software like below. AccountID AccountName --------- ----------- 11 Acc11 12 Acc12 13

Solution 1:

I modified my answer given in the first question...

It would be best, if your table would keep the relation data directly in indexed columns. Before you change your table's structure you might try this:

A table with test data

DECLARE@tblTABLE ( AccountID  VARCHAR(100), AccountName VARCHAR(100));
INSERTINTO@tblVALUES 
 ('11','Acc11')
,('12','Acc12')
,('13','Acc13')
,('11/11','Acc11/11')
,('11/12','Acc11/12')
,('11/111','Acc11/111')
,('11/11/001','Acc11/11/001')
,('11/11/002','Acc11/11/002')
,('12/111','Acc12/111')
,('12/112','Acc12/112');

This will get the needed data into a newly created temp table called #tempHierarchy

SELECT AccountID
      ,AccountName
      ,ROW_NUMBER() OVER(ORDERBY LEN(AccountID)-LEN(REPLACE(AccountID,'/','')),AccountID) AS ID
      ,Extended.HierarchyLevel
      ,STUFF(
       (
         SELECT'/'+ A.B.value('.','varchar(10)')
         FROM Extended.IDsXML.nodes('/x[position() <= sql:column("HierarchyLevel")]') AS A(B)
         FOR XML PATH('')
       ),1,2,'') AS ParentPath
      ,Extended.IDsXML.value('/x[sql:column("HierarchyLevel")+1][1]','varchar(10)') AS ownID
      ,Extended.IDsXML.value('/x[sql:column("HierarchyLevel")][1]','varchar(10)') AS ancestorID
INTO #tempHierarchy
FROM@tblCROSS APPLY(SELECT LEN(AccountID)-LEN(REPLACE(AccountID,'/','')) +1AS HierarchyLevel
                  ,CAST('<x></x><x>'+ REPLACE(AccountID,'/','</x><x>') +'</x>'AS XML) AS IDsXML) AS Extended
;

The intermediate result

+-----------+--------------+----+----------------+------------+-------+------------+
| AccountID | AccountName  | ID | HierarchyLevel | ParentPath | ownID | ancestorID |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11        | Acc11        | 1  | 1              |            | 11    |            |
+-----------+--------------+----+----------------+------------+-------+------------+
| 12        | Acc12        | 2  | 1              |            | 12    |            |
+-----------+--------------+----+----------------+------------+-------+------------+
| 13        | Acc13        | 3  | 1              |            | 13    |            |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/11     | Acc11/11     | 4  | 2              | 11         | 11    | 11         |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/111    | Acc11/111    | 5  | 2              | 11         | 111   | 11         |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/12     | Acc11/12     | 6  | 2              | 11         | 12    | 11         |
+-----------+--------------+----+----------------+------------+-------+------------+
| 12/111    | Acc12/111    | 7  | 2              | 12         | 111   | 12         |
+-----------+--------------+----+----------------+------------+-------+------------+
| 12/112    | Acc12/112    | 8  | 2              | 12         | 112   | 12         |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/11/001 | Acc11/11/001 | 9  | 3              | 11/11      | 001   | 11         |
+-----------+--------------+----+----------------+------------+-------+------------+
| 11/11/002 | Acc11/11/002 | 10 | 3              | 11/11      | 002   | 11         |
+-----------+--------------+----+----------------+------------+-------+------------+

And now a similar recursive approach takes place as in my first answer. But - as it is using a real table now and all the string splitting has taken place already - it should be faster...

WITH RecursiveCTE AS
(
    SELECT th.*
           ,CAST(NULL AS BIGINT) AS ParentID 
           ,CASEWHEN EXISTS(SELECT1FROM #tempHierarchy AS x WHERE x.ParentPath=th.AccountID) THEN1ELSE0ENDAS HasChild
    FROM #tempHierarchy AS th WHERE th.HierarchyLevel=1
    UNION ALL
    SELECT sa.AccountID
          ,sa.AccountName
          ,sa.ID
          ,sa.HierarchyLevel
          ,sa.ParentPath
          ,sa.ownID
          ,sa.ancestorID
          ,(SELECT x.ID FROM #tempHierarchy AS x WHERE x.AccountID=sa.ParentPath)
          ,CASEWHEN EXISTS(SELECT1FROM #tempHierarchy AS x WHERE x.ParentPath=sa.AccountID) THEN1ELSE0ENDAS HasChild
    FROM RecursiveCTE AS r
    INNER JOIN #tempHierarchy AS sa ON sa.HierarchyLevel=r.HierarchyLevel+1AND r.AccountID=sa.ParentPath
)
SELECT r.AccountID
      ,r.AccountName
      ,r.ID
      ,r.ParentID
      ,r.HierarchyLevel
      ,r.HasChild
FROM RecursiveCTE AS r
ORDERBY HierarchyLevel,ParentID;

And finally I clean up

DROPTABLE#tempHierarchy;

And here's the final result

+-----------+--------------+----+----------+----------------+----------+| AccountID | AccountName  | ID | ParentID | HierarchyLevel | HasChild |+-----------+--------------+----+----------+----------------+----------+|11| Acc11        |1|NULL|1|1|+-----------+--------------+----+----------+----------------+----------+|12| Acc12        |2|NULL|1|1|+-----------+--------------+----+----------+----------------+----------+|13| Acc13        |3|NULL|1|0|+-----------+--------------+----+----------+----------------+----------+|11/11| Acc11/11|4|1|2|1|+-----------+--------------+----+----------+----------------+----------+|11/111| Acc11/111|5|1|2|0|+-----------+--------------+----+----------+----------------+----------+|11/12| Acc11/12|6|1|2|0|+-----------+--------------+----+----------+----------------+----------+|12/111| Acc12/111|7|2|2|0|+-----------+--------------+----+----------+----------------+----------+|12/112| Acc12/112|8|2|2|0|+-----------+--------------+----+----------+----------------+----------+|11/11/001| Acc11/11/001|9|4|3|0|+-----------+--------------+----+----------+----------------+----------+|11/11/002| Acc11/11/002|10|4|3|0|+-----------+--------------+----+----------+----------------+----------+

Joining Two Subqueries Or A Query With A Subquery Sql Server 2008

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 20:35:00 +0000

monster edit: The query will now run but returns incorrect answers. Added a rough schema. PatientID is not the primary key in the tblPatientVisits table because the same patientI

Solution 1:

Hard to tell without sample data and desired results, but perhaps this is what you are after?

;WITH x AS 
(
  SELECT c.CountyName, v.patientCounty, v.patientState, p.patientID
    FROM dbo.tblPatient AS p
    INNER JOIN dbo.tblPatientVisits AS v
    ON p.patientID = v.patientID 
    INNER JOIN dbo.tblStateCounties AS c 
    ON v.patientState = c.stateCode 
    AND v.patientCounty = c.countyCode
  WHERE c.stateCode = '21'
),
y AS (SELECT CountyName, c = COUNT(*) FROM x GROUPBY CountyName),
z AS (SELECT CountyName, c = COUNT(PatientID) FROM x 
  GROUPBY CountyName, patientState, PatientID HAVING COUNT(*)>1)
SELECT y.countyName, reAdmits = MAX(COALESCE(z.c, 0)), totalVisits = MAX(y.c)
FROM y LEFT OUTER JOIN z
ON y.CountyName = z.CountyName
GROUPBY y.CountyName;

Solution 2:

There appear to be several issues with this query. I assume that the subquery for "s" is intended to be substituted for "s" in the query before. Are you familiar with the "with" syntax? This would be close to how you are expressing it.

In any case, your first subquery is missing a ") " between the HAVING clause and the GROUP BY. In addition, the "s" subquery has a GROUP BY, but "SELECT *".

The following may be what you are trying to express:

select t.countyName, count(t.countyName), s.countyName, count(s.countyName)
from (select countyName, count(countyName) as readmitCounts
      from (SELECT tblPatient.patientID, tblStateCounties.countyName
            FROM tblPatient INNERJOIN
                 tblPatientVisits
                 ON tblPatient.patientID = tblPatientVisits.patientID INNERJOIN
                 tblStateCounties
                 ON tblPatientVisits.patientState = tblStateCounties.stateCode AND
                    tblPatientVisits.patientCounty = tblStateCounties.countyCode
            GROUPBY tblPatient.patientID, tblStateCounties.stateCode, tblStateCounties.countyName
            HAVING (COUNT(tblPatient.patientID) >1) AND (tblStateCounties.stateCode ='21')
           ) t
      groupby countyname
     ) t innerjoin
     (select tblStateCounties.countyName
      from (SELECT tblStateCounties.countyName, COUNT(tblStateCounties.countyName) AS counts
            FROM tblPatient INNERJOIN
                 tblPatientVisits
                 ON tblPatient.patientID = tblPatientVisits.patientID INNERJOIN
                 tblStateCounties
                 ON tblPatientVisits.patientState = tblStateCounties.stateCode AND 
                    tblPatientVisits.patientCounty = tblStateCounties.countyCode
            WHERE (tblStateCounties.stateCode ='21')
       GROUPBY tblStateCounties.countyName
     )
     on s.countyName = t.countyName

Solution 3:

I think you are overcomplicating your issue, and that this can be done without mashing 2 queries together:

SELECT  t.countyName, 
        SUM(Admissions) AS TotalAdmissions,
        COUNT(*) AS TotalPatients,
        COUNT(CASEWHEN Admissions > 1THEN PatientID END) AS TotalPatientsReadmitted
        SUM(Admissions - 1) AS TotalReadmissions
FROM    (   SELECT  tblPatient.PatientID,
                    tblStateCounties.countyName,
                    COUNT(*) AS Admissions
            FROM    tblPatient 
                    INNER JOIN tblPatientVisits 
                        ON tblPatient.patientID = tblPatientVisits.patientID 
                    INNER JOIN tblStateCounties 
                        ON tblPatientVisits.patientState = tblStateCounties.stateCode 
                        AND tblPatientVisits.patientCounty = tblStateCounties.countyCode
            WHERE   tblStateCounties.stateCode = '21'GROUPBY tblPatient.PatientID, tblStateCounties.countyName
        ) AS t
GROUPBY CountyName

I have just taken your FROM and JOINS to get what I think is all the data you need (and a little more). I have hopefully given the columns sensible enough names for it to be apparent what each one should be showing.

Sql Performance Max()

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 20:15:00 +0000

Just got a small question. When trying to get a single max-Value of a table. Which one is better? SELECT MAX(id) FROM myTable WHERE (whatever) or SELECT TOP 1 id FROM myTable WHER

Solution 1:

There will be no difference as you can test yourself by inspecting the execution plans. If id is the clustered index, you should see an ordered clustered index scan; if it is not indexed, you'll still see either a table scan or a clustered index scan, but it won't be ordered in either case.

The TOP 1 approach can be useful if you want to pull along other values from the row, which is easier than pulling the max in a subquery and then joining. If you want other values from the row, you need to dictate how to deal with ties in both cases.

Having said that, there are some scenarios where the plan can be different, so it is important to test depending on whether the column is indexed and whether or not it is monotonically increasing. I created a simple table and inserted 50000 rows:

CREATETABLE dbo.x
(
  a INT, b INT, c INT, d INT, 
  e DATETIME, f DATETIME, g DATETIME, h DATETIME
);
CREATEUNIQUE CLUSTERED INDEX a ON dbo.x(a);
CREATE INDEX b ON dbo.x(b)
CREATE INDEX e ON dbo.x(e);
CREATE INDEX f ON dbo.x(f);

INSERT dbo.x(a, b, c, d, e, f, g, h)
SELECT 
  n.rn, -- ints monotonically increasing
  n.a,  -- ints in random order
  n.rn, 
  n.a, 
  DATEADD(DAY, n.rn/100, '20100101'), -- dates monotonically increasing
  DATEADD(DAY, -n.a %1000, '20120101'),     -- dates in random order
  DATEADD(DAY, n.rn/100, '20100101'),
  DATEADD(DAY, -n.a %1000, '20120101')
FROM
(
  SELECT TOP (50000) 
     (ABS(s1.[object_id]) %10000) +1, 
     rn =ROW_NUMBER() OVER (ORDERBY s2.[object_id])
  FROM sys.all_objects AS s1 
  CROSSJOIN sys.all_objects AS s2
) AS n(a,rn);
GO

On my system this created values in a/c from 1 to 50000, b/d between 3 and 9994, e/g from 2010-01-01 through 2011-05-16, and f/h from 2009-04-28 through 2012-01-01.

First, let's compare the indexed monotonically increasing integer columns, a and c. a has a clustered index, c does not:

SELECTMAX(a) FROM dbo.x;
SELECT TOP (1) a FROM dbo.x ORDERBY a DESC;

SELECTMAX(c) FROM dbo.x;
SELECT TOP (1) c FROM dbo.x ORDERBY c DESC;

Results:

The big problem with the 4th query is that, unlike MAX, it requires a sort. Here is 3 compared to 4:

This will be a common problem across all of these query variations: a MAX against an unindexed column will be able to piggy-back on the clustered index scan and perform a stream aggregate, while TOP 1 needs to perform a sort which is going to be more expensive.

I did test and saw the exact same results across testing b+d, e+g, and f+h.

So it seems to me that, in addition to producing more standards-compliance code, there is a potential performance benefit to using MAX in favor of TOP 1 depending on the underlying table and indexes (which can change after you've put your code in production). So I would say that, without further information, MAX is preferable.

(And as I said before, TOP 1 might really be the behavior you're after, if you're pulling additional columns. You'll want to test MAX + JOIN methods as well if that's what you're after.)

Solution 2:

The first one is certainly clearer in the intention.

There shouldn't be a significant performance difference thought for this specific query (they should actually be pretty much identical, even though the result is different if there are no rows in myTable). Unless you have a valid reason to tune the query (e.g. a proven performance issue), always pick the one which shows the intent of the code.

Solution 3:

All query optimizers worth their salt should produce query plans with identical performance for both queries: if there is an index on the column being optimized, both queries should use it; if there is no index, both would produce a full table scan.

Solution 4:

Though I suspect the TOP 1 sort operator is over costed in the plan. I tried with TOP 1, TOP 100, >and TOP 101 and all gave me the same estimated subtree cost despite the fact that the last one >would need to sort all the rows. – Martin Smith Jul 2 at 6:53

Whether you need 1 row or 100 rows the optimizer has to do same amount of work in this example i.e. read all the rows from the table(clustered index scan).Then sort all those rows(sort opertaion) as there is no index on the column C..Finally just display which one are needed.

SELECT TOP (1) bFROM dbo.xORDER BY b DESC
option(recompile); 
SELECT TOP (100) bFROM dbo.xORDER BY b DESC
option(recompile);

Try above code and here top 1 and top 100 shows diff cost because there is an index on column b. Thus in this case you do not need to read all rows and sort them but the work is to go to last page pointer.For one row read the last row on last leaf page of index. TFor 100 row find the last row on last page and then start the backward scan till you get the 100 rows.

How To Store Multiple Values In Same Sql Column

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 19:56:00 +0000

I want to create a Questions and answers application like stack overflow. I do not know how to store multiple answers in MySQL table column. I want to know how to store questions a

Solution 1:

No...don't do it. Very bad idea IMHO. It'll be hard to do any searching and stuff. There's a reason to why we still have R'ship database models.

But if you must go down that path, you can always store it as JSON or XML, and maybe somewhere in future, Oracle will provide functionality to search JSON or XML within columns :)

Solution 2:

Creating two tables and connecting them using foreign key relationship is one of the best solutions. Because keeping questions and answers in a same table may not allow you to have multiple answers for the same questions. If you want a single question to have multiple answers then the redundancy of your DB will surely increase. So, try to use DB-Relationship concepts properly.

Solution 3:

You can create an object (suppose you want to save firstname and lastname in the same column, just for example), something like this (VB example):

publicclassmyClasspublicproperty firstname
publicproperty lastname
endclass

then create an instance and assign properties

dim cls asnewmyClass
cls.firstname= ...
cls.lastname = ...

Then use JavaScriptSerializer to convert this object into json string and store that string in the column. On the way back, desialize the value from the column using JavaScriptSerializer to get back an instance of myClass. Search on this site if you need information about how to use JavaScriptSerializer (it has been answered here several times, hence, not repeating).

If using SQL Server, you may also have a column of XML data type and store XML (instead of json) in the column. You can search in XML in SQL Server. See How can I query a value in SQL Server XML column. MySQL newer versions also support searching in data stored in json format.

Solution 4:

If you need StackOverflow Structure you should to create two tables, Questions and Answers and relate them together with a foreign key from Answers' to 'Questions

Solution 5:

By reading your Question, I realized, from my point of view, that your skills may not be good enough to do and to understand what you want to do.

So I´d like to suggest you to watch this Tutorial by Bucky Roberts on YouTube and to read this Wikipedia Article about Data Redundancy.

Anyways to answer your Question.

I would not store answers and questions all in a single table column. Think about creating a second table called 'answers', give an ID to the answers and connect them with the table'questions'.

This is called the Foreign Key Concept. You can read about it here.

Sql Case With 2 Conditions

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 19:37:00 +0000

I have a stored procedure that will accept 2 different parameters. The first parameter will determine which column I want to sort on, the second parameter will determine whether it

Solution 1:

The following will work:

Select * fromempTableOrderbyCASEWHEN@sortcolumn = 'First_Name'AND@SortOrder = 'ascending' THEN fname END ASC,
CASE WHEN@sortcolumn = 'First_Name'AND@SortOrder = 'descending' THEN fname END DESC

etc...

In order to avoid typing each of these case statements by hand, you could write a "generator" script that you use to create this (especially good if the table definition would change):

SELECT'CASE WHEN @SortColumn = '''+ C.name +''' AND @SortOrder = ''ascending'' THEN '+ C.name +' END ASC,'+CHAR(13) +CHAR(10) +'CASE WHEN @SortColumn = '''+ C.name +''' AND @SortOrder = ''descending'' THEN '+ C.name +' END DESC,'FROM sys.columns C 
WHERE C.object_id = object_id('[Schema].[Table]')

Solution 2:

If you want to avoid dynamic SQL and using 2x your conditions, you can use row_number

eg:

declare@ttable (string varchar(50), number int)

insert@tvalues ('a',9),('f',2),('c',1)

declare@scvarchar(10) ='number', -- or 'string', etc@sovarchar(10) ='desc'-- or 'asc'select*from 
(
    select*,
        case@scwhen'string'thenROW_NUMBER() over (orderby string)
                 when'number'thenROW_NUMBER() over (orderby number)
        end rn
    from@t
) v
orderbycase@sowhen'desc'then-rn else rn end

Solution 3:

You can just copy and paste and run this. I hate dynamic SQL, don't do it.

Unfortunately you'll have to duplicate the query....but it solves your specific problem.

DECLARE@sortcolumnvarchar(10),
    @sortordervarchar(10)

SET@sortcolumn='fname'SET@sortorder='DESC'DECLARE@DataTABLE
        (
        fname nvarchar(10),
        lname nvarchar(10),
        empID int
        )
INSERTINTO@DataVALUES ('BBB', 'BBB', 2)
INSERTINTO@DataVALUES ('AAA', 'AAA', 1)


IF @sortorder='DESC'BEGINSELECT*FROM@DataORDERBYCASEWHEN@sortcolumn='fname'THEN fname
            WHEN@sortcolumn='lname'THEN lname
        ENDDESCENDELSEBEGINSELECT*FROM@DataORDERBYCASEWHEN@sortcolumn='fname'THEN fname
            WHEN@sortcolumn='lname'THEN lname
        ENDEND

Solution 4:

Modifying Jon's answer to cap the ORDER BY list at just 2 instead of 2 * #columns

SELECT*FROM MyTable
CROSS APPLY (VALUES
  ('First_Name',fname),
  ('Last_Name' ,lname),
  ('Id'        ,ID   )
) sort(SortColumn, SortValue)
WHERE SortColumn =@SortColumnORDERBYCASE@SortOrderWHEN'ascending'THEN SortValue ENDASC,
  CASE@SortOrderWHEN'descending'THEN SortValue ENDDESC

Cannot Create A Task From Xml For Task Azure Blob Download Task. Error 0x80070057 The Parameter Is In Correct

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 19:17:00 +0000

We have implemented an SSIS package which downloads the file from Azure blob storage. The package works when we execute from local development box. But it's failing on TEST environ

Solution 1:

For the first error, please refer to Maintenance Plan Error https://blog.sqlauthority.com/2018/02/16/sql-server-maintenance-plan-error-cannot-create-task-xml-task-eue-error-0x80070057-parameter-incorrect-onpreexecute/ Error: The task with the name "Data Flow Task" https://social.msdn.microsoft.com/Forums/ie/en-US/0b57adfa-0872-4cd4-85d8-40bfaa520285/error-when-executing-ssis-package?forum=sqlintegrationservices

Just for clarification : Have you could try following options: 1. Try using DTexce tool to execute the package to check if this issue persist. 2. Register the Azure Blob Task into GAC manually. 3. Check if the account has permission to upload file to Azure.

Azure Blob Download Task https://docs.microsoft.com/en-us/sql/integration-services/control-flow/azure-blob-download-task?view=sql-server-2017

Kindly let us know if the above helps or you need further assistance on this issue.

Sql Select Rows With Unique Values

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 18:57:00 +0000

I'm having a problem getting the rows I need from my database. The question is simple: I have a table with customer data in it. Now I want to do a mailing (via post) to all my cust

Solution 1:

SELECT title
     , last_name
     , first_name
     , address_line_1
     , address_line_2
     , etc
FROM   (
        SELECT title
             , last_name
             , first_name
             , address_line_1
             , address_line_2
             , etc
             , Row_Number() OVER (PARTITIONBY address_line_1, address_line_2, etc ORDERBY date_of_birth ASC) As row_number
        FROM   your_table
       ) As all_duplicatified
WHERE  row_num =1

This gives every row a row number. The row number is "reset" on every partition (which in this case is our address fields) and the ordering of the numbers is determined by age (dob).

Therefore if we only show the ones where row_num = 1 we get just that eldest persons entry first.

Solution 2:

You can you the following SQL construct:

selectdistinct ...

Select distinct will avoid duplicates in your result set. You can use select distinct to gather all unique addresses and then cross reference with the email address

Solution 3:

Use SELECT DISTINCT to only select unique adresses

Solution 4:

Here's how I would do something like this in Oracle:

--Create testing tableCREATETABLE UniqueValTest (
  fname NVARCHAR2(100),
  lname NVARCHAR2(100),
  address NVARCHAR2(100),
  city NVARCHAR2(50),
  state NVARCHAR2(2),
  zip NVARCHAR2(5),
  age NUMBER,
  recid NUMBER
  );

--Create sample dataINSERTINTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('JOHN', 'SMITH', '123 MAIN ST', 'JAMESTOWN', 'LA', '12345', 28, 1);
INSERTINTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('JENNIFER', 'SMITH', '123 MAIN ST', 'JAMESTOWN', 'LA', '12345', 30, 2);
INSERTINTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('RACHEL', 'ALLEN', '225 MAIN ST', 'JAMESTOWN', 'LA', '12345', 25, 3);
INSERTINTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('JOSEPH', 'ALLEN', '225 MAIN ST', 'JAMESTOWN', 'LA', '12345', 25, 4);
INSERTINTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('MARK', 'MCBRIDE', '228 MAIN ST', 'JAMESTOWN', 'LA', '12345', 55, 5);

--Here's the real part, pulling the data with the dedupe and priorityCREATETABLE TestDataPull ASSELECT T.*, ROW_NUMBER() OVER (PARTITIONBY lname, address, zip ORDERBY lname, address, zip, age DESC NULLS LAST) AS dupeid
  FROM UNIQUEVALTEST T;

--Now you can easily select your dataSELECT fname, lname, address, city, state, zip FROM TestDataPull WHERE dupeid =1;

Comparing List Of Values Against Table

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 18:37:00 +0000

I tried to find solution for this problem for some time but without success so any help would be much appreciated. List of IDs needs to be compared against a table and find out whi

Solution 1:

there are many built in public collection types. you can leverage one of them like this:

with ids as (select/*+ cardinality(a, 1) */ column_value id
               fromtable(UTL_NLA_ARRAY_INT(100, 200, 300)) a
            )
select ids.id, casewhen m.id isnullthen'**NO MATCH**'else m.value endvaluefrom ids
         leftouterjoin my_table m
                     on m.id = ids.id;

to see a list of public types on your DB, run :

select owner, type_name, coll_type, elem_type_name, upper_bound, precision, scale from all_coll_types
 where elem_type_name in ('FLOAT', 'INTEGER', 'NUMBER', 'DOUBLE PRECISION')

the hint

/*+ cardinality(a, 1) */

is just used to tell oracle how many elements are in our array (if not specified, the default will be an assumption of 8k elements). just set to a reasonably accurate number.

Solution 2:

You can transform a variable into a query using CONNECT BY (tested on 11g, should work on 10g+):

SQL>WITH DATA AS (SELECT'100,200,300' txt FROM dual)
  2SELECT regexp_substr(txt, '[^,]+', 1, LEVEL) item FROM DATA
  3CONNECTBY LEVEL <= length(txt) - length(REPLACE(txt, ',', '')) +1;

ITEM
--------------------------------------------100200300

You can then join this result to the table as if it were a standard view:

SQL>WITH DATA AS (SELECT'100,200,300' txt FROM dual)
  2SELECT v.id, dbt.value01
  3FROM dbt
  4RIGHTJOIN5      (SELECT to_number(regexp_substr(txt, '[^,]+', 1, LEVEL)) ID
  6FROM DATA
  7CONNECTBY LEVEL <= length(txt) - length(REPLACE(txt, ',', '')) +1) v
  8ON dbt.id = v.id;

        ID VALUE01
---------- ----------100 Ann
       300 John
       200

Solution 3:

One way of tackling this is to dynamically create a common table expression that can then be included in the query. The final synatx you'd be aiming for is:

with list_of_values as (
  select100 val from dual unionallselect200 val from dual unionallselect300 val from dual unionall
  ...)
select
  lov.val,
  ...
from
  list_of_values lov leftouterjoin 
  other_data     t   on (lov.val = t.val)

It's not very elegant, particularly for large sets of values, but compatibility with a database on which you might have few privileges is very good.

Flask-sqlalchemy, Primary Key For Secondary Table In Many-to-many Relationship

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 18:17:00 +0000

I am trying to build simple many-to-many relationship using Flask-Sqlalchemy for Postgresql Database: from app import db from sqlalchemy.dialects.postgresql import UUID authors_bo

Solution 1:

https://groups.google.com/forum/#!topic/sqlalchemy/WAHaPIuzqZQ

authors_books = db.Table(
    'authors_books',
    db.Column('id', UUID(as_uuid=True), primary_key=True, default=lambda: uuid.uuid4().hex),
    db.Column('author_id', UUID(as_uuid=True), db.ForeignKey('authors.id')),
    db.Column('book_id', UUID(as_uuid=True), db.ForeignKey('books.id')),
)

How To Obtain Failed Jobs From Sql Server Agent Through Script?

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 17:05:00 +0000

I want to get the failed jobs which sql server agent failed to start or run through sql script...

Solution 1:

I created a view to obtain the failed jobs in sql server

CREATEVIEW dbo.View_Failed_Jobs
    ASSELECT   Job.instance_id
        ,SysJobs.job_id
        ,SysJobs.name as'JOB_NAME'
        ,SysJobSteps.step_name as'STEP_NAME'
        ,Job.run_status
        ,Job.sql_message_id
        ,Job.sql_severity
        ,Job.message
        ,Job.exec_date
        ,Job.run_duration
        ,Job.server
        ,SysJobSteps.output_file_name
    FROM    (SELECT Instance.instance_id
        ,DBSysJobHistory.job_id
        ,DBSysJobHistory.step_id
        ,DBSysJobHistory.sql_message_id
        ,DBSysJobHistory.sql_severity
        ,DBSysJobHistory.message
        ,(CASE DBSysJobHistory.run_status
            WHEN0THEN'Failed'WHEN1THEN'Succeeded'WHEN2THEN'Retry'WHEN3THEN'Canceled'WHEN4THEN'In progress'END) as run_status
        ,((SUBSTRING(CAST(DBSysJobHistory.run_date ASVARCHAR(8)), 5, 2) +'/'+SUBSTRING(CAST(DBSysJobHistory.run_date ASVARCHAR(8)), 7, 2) +'/'+SUBSTRING(CAST(DBSysJobHistory.run_date ASVARCHAR(8)), 1, 4) +' '+SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time ASvarchar)))
        +CAST(DBSysJobHistory.run_time ASVARCHAR)), 1, 2) +':'+SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time ASVARCHAR)))
        +CAST(DBSysJobHistory.run_time ASVARCHAR)), 3, 2) +':'+SUBSTRING((REPLICATE('0',6-LEN(CAST(DBSysJobHistory.run_time asvarchar)))
        +CAST(DBSysJobHistory.run_time ASVARCHAR)), 5, 2))) AS'exec_date'
        ,DBSysJobHistory.run_duration
        ,DBSysJobHistory.retries_attempted
        ,DBSysJobHistory.server
        FROM msdb.dbo.sysjobhistory DBSysJobHistory
        JOIN (SELECT DBSysJobHistory.job_id
            ,DBSysJobHistory.step_id
            ,MAX(DBSysJobHistory.instance_id) as instance_id
            FROM msdb.dbo.sysjobhistory DBSysJobHistory
            GROUPBY DBSysJobHistory.job_id
            ,DBSysJobHistory.step_id
            ) AS Instance ON DBSysJobHistory.instance_id = Instance.instance_id
        WHERE DBSysJobHistory.run_status <>1
        ) AS Job
    JOIN msdb.dbo.sysjobs SysJobs
       ON (Job.job_id = SysJobs.job_id)
    JOIN msdb.dbo.sysjobsteps SysJobSteps
       ON (Job.job_id = SysJobSteps.job_id AND Job.step_id = SysJobSteps.step_id)
    GO

-- Now execute the select statement to query the failed jobs

Select*from View_Failed_Jobs

Solution 2:

Here is an example for SQL Server 2008: SQL Server Agent Job Query Samples

You'll find a lot more if you google for "sql server failed jobs", but unfortunately most of the examples don't say if they work for SQL Server 2008, for example this one.

EDIT:

Hint: when you copy the code from the first link, use the "view source" button:

There's a "copy to clipboard" button as well which I tried first, but it generated some HTML tags in the SQL code which SQL Server complained about (and didn't let me execute the query).

Solution 3:

you can use shortcut using sql agent-->job activity monitor.

Why Does Wordpress Have Separate 'usersmeta' And 'users' Sql Tables. Why Not Combine Them?

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 16:49:00 +0000

Alongside the users table, Wordpress has a usersmeta table with the following columns meta_id user_id meta_key (e.g. first_name) meta_value (e.g. Tom) Each user has 20 rows in th

Solution 1:

Entity Attribute Value

It's known as the Entity Attribute Value (EAV) data model, and allows an arbitrary number of attributes to be assigned to a given entity. That means any number of meta-data entries per user.

Why use it

By default there are a few keys that wordpress sets (20 stated in the question) but there can be any number. If all users have one thousand meta data entries - there are simply one thousand entries in the usermeta table for each user - it doesn't have (in terms of the database structure) a limit to the number of meta data entries a user can have. It also permits one user to have one thousand meta data entires, whilst all others have 20 and still store the data efficiently - or any permutation thereof.

In addition to flexibility, using this kind of structure permits the main users table to remain small - which means more efficient queries.

Alternatives

The alternatives to using EAV include:

Modify the schema whenever the number of attributes changes
Store all attributes in a serialized string (on the user object)
Use a schemaless db

Permissions is the biggest problem with the first point, it is not a good idea to grant blanket access to alter the schema of your database tables, and is a (sane) roadblock for many if not most wordpress installs (hosted on wordpress.com or on a shared host where the db user has no alter permissions). Mysql also has a hard-limit of 4096 columns and 65,535 bytes per row. Attempting to store a large number of columns in a single table will eventually fail, along the way creating a table that is inefficient to query.

Storing all attribute in a serialized string would make it difficult and slow to query by a meta-data value.

Wordpress is quite tied to mysql, and therefore changing datastore isn't a realistic option.

Further WP info

If you aren't using any/many plugins it's possible you will have a constant number of rows in the usermeta table for each user, but typically each plugin you add may need to add meta-data for users; the number added may not be trivial and this data is stored in the usermeta table.

The docs for add_meta_user may add some clarity as to why the database is structured that way. If you put code like this somewhere:

add_user_meta($user_id, "favorite_color", "blue");

It will create a row in the usermeta table for the given user_id, without the need to add a column (favorite_color) to the main users table. That makes it easy-ish to find users by favorite color without the need to modify the schema of the users table.

Solution 2:

This is really a question about database normalization. You can look for information on that topic in many places.

Basic answer Since there is a huge literature about this, and there are a lot of differences, I will just give some examples of why this might happen - it boild down to trade-offs; Speed versus storage requirements, or ease of use versus data duplication. Efficiency is multidimensional, and since wordpress does a lot of different things, it may have various reasons to keep them separate - space could be an issue, speed of queries may depend on this, it may be easier to look at just the meta table instead of the full table for some purposes, or vice versa.

Further reading This is a deep topic, you may want to learn more - there are hundreds of books and thousands of scholarly papers on these issues. For instance, look at this previous SO question about designing a database: Database design: one huge table or separate tables?, or this one: First-time database design: am I overengineering? or Database Normalization Basics on About.com.

Ordering Parent Rows By Date Descending With Child Rows Ordered Independently Beneath Each

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 16:31:00 +0000

This is a contrived version of my table schema to illustrate my problem: QuoteID, Details, DateCreated, ModelQuoteID Where QuoteID is the primary key and ModelQuoteID is a nullable

Solution 1:

Watching the Olympics -- just skimmed your post -- looks like you want to control the sort at each level (root and one level in), and make sure the data is returned with the children directly beneath its parent (so you can page the data...). We do this all the time. You can add an order by to each inner query and create a sort column. I contrived a slightly different example that should be easy for you to apply to your circumstance. I sorted the root ascending and level one descending just to illustrate how you can control each part.

declare@tbltable (id int, parent int, name varchar(10))

insertinto@tbl (id, parent, name)
values (1, null, 'def'), (2, 1, 'this'), (3, 1, 'is'), (4, 1, 'a'), (5, 1, 'test'),
       (6, null, 'abc'), (7, 6, 'this'), (8, 6, 'is'), (9, 6, 'another'), (10, 6, 'test')

;with cte (id, parent, name, sort) as (
  select id, parent, name, cast(right('0000'+cast(row_number() over (orderby name) asvarchar(4)), 4) asvarchar(1024))
  from@tblwhere  parent isnullunionallselect t.id, t.parent, t.name, cast(cte.sort +right('0000'+cast(row_number() over (orderby t.name desc) asvarchar(4)), 4) asvarchar(1024))
  from@tbl t innerjoin cte on t.parent = cte.id
)
select*from cte
orderby sort

This produces these results:

id    parent    name     sort
----  --------  -------  ----------
6     NULL      abc      000176this00010001106         test     0001000286is0001000396         another  000100041     NULL      def      000221this0002000151         test     0002000231is0002000341         a        00020004

You can see that the root nodes are sorted ascending and the inner nodes are sorted descending.

Insert And Update In A Cursor In Sql Server

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 16:15:00 +0000

I have a Table shown below DateRange 10/1 11/1 12/1 I am looping through this table using cursor and trying to populate a temp table Create table #BidStatusCalculation1 ( C

Solution 1:

Use PIVOT as

SELECT Code,
       [22] Data1,
       [33] Data2,
       [43] Data3
FROM
    (
      SELECT *
      FROM T
    ) TBL
    PIVOT
    (
      MAX(Data) FOR Data IN([22],[33],[43])
    ) PVT

Demo

For dynamic pivot

CREATETABLE T
(
  Code VARCHAR(45),
  Data INT
);

INSERTINTO T VALUES
('SL Payroll',    22),
('SL Payroll',    33),
('SL Payroll',    43);

DECLARE@INVARCHAR(MAX) ='',
        @ColsVARCHAR(MAX) ='';

SELECT@IN=@IN+','+ QUOTENAME(Data),
       @Cols=@Cols+','+ QUOTENAME(Data) +' AS Data'+CAST(ROW_NUMBER()OVER(ORDERBY (SELECTNULL)) ASVARCHAR)
FROM T;

SELECT@IN= STUFF(@IN, 1, 1, ''), @Cols= STUFF(@Cols, 1, 1, '');

DECLARE@SQL NVARCHAR(MAX) = N'SELECT Code, '+@Cols+' FROM (SELECT * FROM T) TBL PIVOT (MAX(Data) FOR Data IN('+@IN+')) PVT';
     EXECUTE sp_executesql @SQL;

Demo

If Loop In 'then' Statement Of Case Statement In Sql Server 2008

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 14:45:00 +0000

If I wanted to use if condition in Then statement of Case statement in SQL Server 2008. How to use it?

Solution 1:

Nest them

CASEWHEN foo ='bar'THENCASEWHEN black ='white'THEN .. 
                 WHEN W ='TF'THEN ... 
                 ELSEENDWHEN fish ='bicycle'THEN ... 
   ELSE ... 
END

Solution 2:

You can use Then keyword Case statement like follows in Sql server

SELECT   ProductNumber, Category =CASE ProductLine
         WHEN TestRowValue ='R'THENCaseWhen TestColumn ='P'THEN'Road'ELSE'Board'WHEN'M'THEN'Mountain'WHEN'T'THEN'Touring'WHEN'S'THEN'Other sale items'ELSE'Not for sale'END,
   Name
FROM Production.Product

How Can I Speed Up This Select Concat/group By Query?

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 14:09:00 +0000

I'm working on selecting locations (city, state) out of a database. The problem is that the query is running a tad slow and I'm not sure how to speed it up. For example: SELECT C

Solution 1:

Create a composite index on (state, city) and rewrite your query as this:

SELECT  CONCAT_WS(', ', city, state) AS location, AVG(latitude), AVG(longitude) 
FROM    places
WHERE   state='NY'AND city='New York'GROUPBY
        state, city

Note that for this very query you may omit GROUP BY clause:

SELECT'New York, NY'AS location, AVG(latitude), AVG(longitude) 
FROM    places
WHERE   state='NY'AND city='New York'

However, this query will still need it:

SELECT  CONCAT_WS(', ', city, state) AS location, AVG(latitude), AVG(longitude) 
FROM    places
WHERE   state='NY'GROUPBY
        state, city

Solution 2:

It's funny, but almost every problem people have with databases is speed, not storage requirements. That should tell you something :-)

We've had problems like this before and I've said this many times: per-row functions generally do not scale well. The best method we've found to fix them is with insert/update triggers (I'm assuming MySQL has these).

Create another column call pretty_city_state (or whatever) and have the triggers populate it from city and state whenever you insert or update a row. Then create an index on it.

This takes advantage of the fact that database rows are generally read far more often than they're written (especially in this case). By evaluating that column on write, you bear the cost across the writes (thousands) rather than the reads (millions probably). And it's the writing when it should be borne simply because pretty_city_state will only change when either city or state changes. If you do the concat on every select, you're wasting effort.

Try that and measure the difference - I'm sure you'll find that your selects will scream along at a minimal cost for the triggers (and that cost disappears totally once you have all cities and states within your database.

And yes, I know this breaks 3NF. It's perfectly acceptable to do so for performance reasons if you know what you're doing.

Your query could be done as:

SELECT pretty_city_state as location, AVG(latitude), AVG(longitude) 
FROM places
WHERE city='New York'AND state='NY'GROUPBY pretty_city_state

or, maybe even faster (measure, don't guess) if you can concatenate the city and state before starting the query:

SELECT pretty_city_state as location, AVG(latitude), AVG(longitude) 
FROM places
WHERE pretty_city_state ='New York, NY'GROUPBY pretty_city_state

Solution 3:

One way to best optimize a query like this is to have those columns set as Index columns. That way it can easily sort/group based off of a tree or hash. Also, concatenation of strings might have some implications too.

Solution 4:

Adding an index on fields "city" and "state" will help.

Also, depending on the cardinality of each field (number of distinct values), on the version of MySQL, the table engine and other parameters, inverting the WHERE clauses may have an impact on the execution time of your query. I'd try:

WHERE state='NY' AND city='New York'

How Do I Write Ef.functions Extension Method?

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 13:32:00 +0000

I see that EF Core 2 has EF.Functions property EF Core 2.0 Announcement which can be used by EF Core or providers to define methods that map to database functions or operators so t

Solution 1:

Adding new scalar method to EF.Functions is easy - you simply define extension method on DbFunctions class. However providing SQL translation is hard and requires digging into EFC internals.

However EFC 2.0 also introduces a much simpler approach, explained in Database scalar function mapping section of the New features in EF Core 2.0 documentation topic.

According to that, the easiest would be to add a static method to your DbContext derived class and mark it with DbFunction attribute. E.g.

publicclassMyDbContext : DbContext
{
    // ...

    [DbFunction("SOUNDEX")]
    publicstaticstringSoundex(string s) => thrownew Exception();
}

and use something like this:

stringparam= ...;
MyDbContextdb= ...;
varquery= db.Customers
    .Where(e => MyDbContext.Soundex(e.LastName) == MyDbContext.Soundex(param));

You can declare such static methods in a different class, but then you need to manually register them using HasDbFunction fluent API.

Solution 2:

EFC 3.0 has changed this process a little, as per https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#udf-empty-string

Example of adding CHARINDEX in a partial context class:

publicpartialclassMyDbContext
{
    [DbFunction("CHARINDEX")]
    publicstaticint? CharIndex(string toSearch, string target) => thrownew Exception();

    partialvoidOnModelCreatingPartial(
        ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasDbFunction(typeof(MyDbContext).GetMethod(nameof(CharIndex)))
            .HasTranslation(
                args =>
                    SqlFunctionExpression.Create("CHARINDEX", args, typeof(int?), null));
    }
}

Aggregate In Django With Duration Field

noreply@blogger.com (Sandy Veum) — Wed, 21 Aug 2024 12:35:00 +0000

My model is: class Mo(Model): dur = DurationField(default=timedelta(0)) price_per_minute = FloatField(default=0.0) minutes_int = IntegerField(default=0) # dublicates

Solution 1:

You can use ExpressionWrapper(F('dur'), output_field=BigIntegerField()) to make Django treat dur as an integer value.

rachmatmoradosugiarto

How To Deploy Sqlite With .net

Solution 1:

Solution 2:

Calculate Totals Of Field Based On Current Fiscal Year Only - Sql

Solution 1:

Solution 2:

Solution 3:

How Do I Rename A Column In A Database Table Using Sql?

Solution 1:

Solution 2:

Solution 3:

Solution 4:

Solution 5:

"inheriting 'base', Which Is Not A Class" In Vs Code Using Sqlalchemy Declarative_base()

Solution 1:

Inheriting 'Base', which is not a class is not actually an error.

Solution 2:

Distinct On Only One Column - Only Display First Duplicate Row

Solution 1:

Solution 2:

Output:

Solution 3:

What Are The Use Cases For Temporary Tables In Sql Database Systems?

Solution 1:

Solution 2:

Sqlparameters Array In Vb.net

Solution 1:

How To Write Dynamic Query Columns Into Rows In Sql Server?

Solution 1:

Solution 2:

Create Tree Query From Numeric Mapping Table In Sql (specific Format)

Solution 1:

Joining Two Subqueries Or A Query With A Subquery Sql Server 2008

Solution 1:

Solution 2:

Solution 3:

Sql Performance Max()

Solution 1:

Solution 2:

Solution 3:

Solution 4:

How To Store Multiple Values In Same Sql Column

Solution 1:

Solution 2:

Solution 3:

Solution 4:

Solution 5:

Sql Case With 2 Conditions

Solution 1:

Solution 2:

Solution 3:

Solution 4:

Cannot Create A Task From Xml For Task Azure Blob Download Task. Error 0x80070057 The Parameter Is In Correct

Solution 1:

Sql Select Rows With Unique Values

Solution 1:

Solution 2:

Solution 3:

Solution 4:

Comparing List Of Values Against Table

Solution 1:

Solution 2:

Solution 3:

Flask-sqlalchemy, Primary Key For Secondary Table In Many-to-many Relationship

Solution 1:

How To Obtain Failed Jobs From Sql Server Agent Through Script?

Solution 1:

Solution 2:

Solution 3:

Why Does Wordpress Have Separate 'usersmeta' And 'users' Sql Tables. Why Not Combine Them?

Solution 1:

Entity Attribute Value

Why use it

Alternatives

Further WP info

Solution 2:

Ordering Parent Rows By Date Descending With Child Rows Ordered Independently Beneath Each

Solution 1:

Insert And Update In A Cursor In Sql Server

`Inheriting 'Base', which is not a class` is not actually an error.