#StackBounty: #mysql #sql #sql-order-by #innodb Why is my case-insensitive order by failing to sort results properly?

Bounty: 50

I’m using MySql 5.6.15 on Amazon Linux. I’m trying to write a simple query to get results ordered by the lower-cased version of my table’s NAME column, which is of type VARCHAR(100). The table has attributes

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

But notice I’m getting different results and the only thing that is different is that in the incorrect results, below, I’m getting one extra column …

mysql> select NAME, ACTIVE, ADDRESS_ID, COUNTRY_ID, CREATED_ON, ORGANIZATION_ID, IMPORT_ADMIN_DATA_FROM_SIS, IMPORT_DATA_FROM_SIS, MDR_NUMBER from organization order by lower(NAME);
+--------------------------------------+--------+------------+------------+---------------------+-----------------+----------------------------+----------------------+------------+
| NAME                                 | ACTIVE | ADDRESS_ID | COUNTRY_ID | CREATED_ON          | ORGANIZATION_ID | IMPORT_ADMIN_DATA_FROM_SIS | IMPORT_DATA_FROM_SIS | MDR_NUMBER |
+--------------------------------------+--------+------------+------------+---------------------+-----------------+----------------------------+----------------------+------------+
| Billy Madison Elementary             |       | NULL       | US         | 2018-11-29 22:35:57 | 7788            |                            |                    0 | NULL       |
| Bradley County Schools               |       | NULL       | US         | 2018-11-29 22:35:57 | 8888            |                            |                    0 | NULL       |
| Billy Madison Elementary             |       | NULL       | US         | 2018-11-29 22:35:57 | 9998            |                            |                    0 | NULL       |

When I don’t request that extra column, I get the correct results …

mysql> select NAME, ACTIVE, ADDRESS_ID, COUNTRY_ID, CREATED_ON, ORGANIZATION_ID, IMPORT_ADMIN_DATA_FROM_SIS, IMPORT_DATA_FROM_SIS from organization order by lower(NAME);
+--------------------------------------+--------+------------+------------+---------------------+-----------------+----------------------------+----------------------+
| NAME                                 | ACTIVE | ADDRESS_ID | COUNTRY_ID | CREATED_ON          | ORGANIZATION_ID | IMPORT_ADMIN_DATA_FROM_SIS | IMPORT_DATA_FROM_SIS |
+--------------------------------------+--------+------------+------------+---------------------+-----------------+----------------------------+----------------------+
| Billy Madison Elementary             |       | NULL       | US         | 2018-11-29 22:35:57 | 9998            |                            |                    0 |
| Billy Madison Elementary             |       | NULL       | US         | 2018-11-29 22:35:57 | 7788            |                            |                    0 |
| Bradley County Schools               |       | NULL       | US         | 2018-11-29 22:35:57 | 8888            |                            |                    0 |

What the hell is going on here? How do I return results ordered by the lower-cased name?

Edit: Create table statement from running “SHOW CREATE TABLE” …

| organization | CREATE TABLE `organization` (
  `ID` varchar(32) COLLATE utf8_bin NOT NULL,
  `STATE_ID` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `ORGANIZATION_ID` varchar(32) COLLATE utf8_bin NOT NULL,
  `COUNTRY_ID` varchar(10) COLLATE utf8_bin NOT NULL,
  `NAME` varchar(100) COLLATE utf8_bin NOT NULL,
  `ORGANIZATION_TYPE_ID` varchar(2) COLLATE utf8_bin NOT NULL,
  `PARENT_ORGANIZATION_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `USER_ENTERED` tinyint(4) DEFAULT '0',
  `SAMPLE_ORGANIZATION` tinyint(4) DEFAULT '0',
  `IMPORT_DATA_FROM_SIS` tinyint(1) NOT NULL DEFAULT '0',
  `USE_EXTERNAL_AUTHENTICATION` tinyint(1) NOT NULL DEFAULT '0',
  `ADDRESS_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `LTI_REFERER_DOMAIN` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `URL_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `CREATED_ON` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `MDR_NUMBER` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `ACTIVE` bit(1) DEFAULT b'1',
  `IMPORT_ADMIN_DATA_FROM_SIS` bit(1) DEFAULT b'0',
  `USE_EXTERNAL_AUTH_FOR_ADMINS` bit(1) DEFAULT b'0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_ORGANIZATION` (`ORGANIZATION_ID`),
  KEY `FK1_ORGANIZATION` (`COUNTRY_ID`),
  KEY `FK3_ORGANIZATION` (`ORGANIZATION_TYPE_ID`),
  KEY `FK2_ORGANIZATION` (`PARENT_ORGANIZATION_ID`),
  KEY `FK_ORGANIZATION` (`ADDRESS_ID`),
  KEY `FK5_ORGANIZATION` (`URL_ID`),
  CONSTRAINT `FK1_ORGANIZATION` FOREIGN KEY (`COUNTRY_ID`) REFERENCES `cb_country` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `FK2_ORGANIZATION` FOREIGN KEY (`PARENT_ORGANIZATION_ID`) REFERENCES `organization` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `FK3_ORGANIZATION` FOREIGN KEY (`ORGANIZATION_TYPE_ID`) REFERENCES `cb_org_type` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `FK5_ORGANIZATION` FOREIGN KEY (`URL_ID`) REFERENCES `sb_url` (`ID`) ON UPDATE NO ACTION,
  CONSTRAINT `FK_ORGANIZATION` FOREIGN KEY (`ADDRESS_ID`) REFERENCES `cb_address` (`ID`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |


Get this bounty!!!

#StackBounty: #magento2 #php #magento2.2 #sql #customer-attribute How to mass update custom dropdown customer attribute to all customer…

Bounty: 50

I created a new customer attribute (Drop Down) and added it to the registration form.

Attribute details found in eav_attribute table:

enter image description here

Everything work as expected, attribute is added to the customer when new account is registered or an existing account was edited and the attribute was added. I can find that in the table customer_entity_int:

enter image description here

Now, I have about 90,000 customers and I need to set a value of this attribute.

How can I update all the customers custom drop down attribute with the above value? Any method would suit me, PHP or SQL.


Get this bounty!!!

#StackBounty: #sql #database-design #cassandra #cql Cassandra table design for query with ORDER, LIMIT and IN predicate

Bounty: 50

I have data that looks like:

select * from test;

 department | employee | batch_number | hash
------------+----------+--------------+-------
 dep1       | Bart     |            1 | hash1
 dep1       | Bart     |            1 | hash2
 dep1       | Lisa     |            3 | hash3
 dep1       | Lisa     |            4 | hash4
 dep1       | John     |            5 | hash5
 dep1       | Lucy     |            6 | hash6
 dep1       | Bart     |            7 | hash7
 dep1       | Bart     |            7 | hash8

And I would like to query the data with a where clause on batch_number, an ordering on batch_number and an in predicate on employee.

In a relational database this would look like

select * from test 
  where department='dep1' 
  and employee in ('Bart','Lucy','John') 
  and batch_number >= 2 
  order by batch_number desc 
  limit 3;

 department | employee | batch_number | hash
------------+----------+--------------+-------
 dep1       | Bart     |            7 | hash7
 dep1       | Bart     |            7 | hash8
 dep1       | Lucy     |            6 | hash6

I’m having some issues modelling a table for this query in Cassandra. department will be my partition key and hash needs to be part of the primary key. But I’m struggling with cluster keys and/or ((SSTable attached) secondary) indexes.

Because I want ordering on batch_number I tried including it as a cluster key:

CREATE TABLE keyspace.test(
    department      TEXT,
    batch_number    INT,
    hash            TEXT,
    employee        TEXT,
    PRIMARY KEY ((department), batch_number, hash)
) WITH CLUSTERING ORDER BY (batch_number DESC);
CREATE INDEX tst_emp ON keyspace.test (employee);

But this does not allow for queries with an in predicate on my index:

select * from keyspace.test where department='dep1' and employee in ('Bart','Lucy','John');
InvalidRequest: Error from server: code=2200 [Invalid query] message="IN predicates on non-primary-key columns (employee) is not yet supported"

So I tried adding the employee column as cluster key as well:

CREATE TABLE keyspace.test(
    department      TEXT,
    batch_number    INT,
    hash            TEXT,
    employee        TEXT,
    PRIMARY KEY ((department), batch_number, hash, employee)
) WITH CLUSTERING ORDER BY (batch_number DESC);

But this fails because I can not put a non-EQ relation on batch_number:

select * from keyspace.test where department='dep1' and batch_number > 1 and employee in ('Bart','Lucy','John');
InvalidRequest: Error from server: code=2200 [Invalid query] message="Clustering column "employee" cannot be restricted (preceding column "batch_number" is restricted by a non-EQ relation)"

But whenever I put employee before batch_number I lose the ability to order on batch_number:

CREATE TABLE keyspace.test(
    department      TEXT,
    employee        TEXT,
    batch_number    INT,
    hash            TEXT,
    PRIMARY KEY ((department), employee, batch_number, hash)
);

select * from keyspace.test where department='dep1' and employee in ('Bart','Lucy','John') ORDER BY batch_number DESC;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Order by currently only support the ordering of columns following their declared order in the PRIMARY KEY"

So what table design would allow for such a query?
Can this be done in Cassandra?

Edit:

Other queries I would like to be able to run on this table are:

select * from keyspace.test where department='X' and batch_number=Y 

and

delete from keyspace.test where department='X'


Get this bounty!!!

#StackBounty: #sql #scala #apache-spark #apache-spark-sql Dynamic where condition genaration in scala

Bounty: 50

I have to generate where condition based on case class/dataframe.

For example I will have sample data like below, which I can get from case class/dataframe with 4 columns, these dataframe has lot of data, i have to filter based on the id. for a id I have to generate the whereQuery

columns are (id, col1, col2, col3)

|-------------------------------------------------------|
|id      |      col1     |      col2     |      col3    |
|-------------------------------------------------------|
|"1"     |   "col1vr1"   |   "col2vr1"   |   "col3vr1"  |
|"1"     |   "col1vr2"   |   "col2vr2"   |   "col3vr2"  |
|-------------------------------------------------------|

For above data I have to generate a where clause as below,

( col("col1")<=>col1vr1 &&  col("col2")<=>col2vr1 && col("col3") <=> col3vr1 ) ||  ( col("col1")<=>col1vr2 &&  col("col2")<=>col2vr2 && col("col3") <=> col3vr2 )

so that I can apply the above query to WHEN condtion when( finalColumn, "We don't have any records for this rule" ) //Here finalColumn is generated query

I tried as below

case class test(id: String, col1: String, col2: String, col3: String)

Test data :

 val testmap = List(
 test("1", "col1v", "col2va", "col3va"),
 test("1", "col1v", "col2va", "col3vb"),
 test("1", "col1va", "col2va", "col3vc"),
 test("1", "col1va", "col2va", "col3vd"),
  test("1", "col1vb", "col2vb", "col3vd"),
  test("1", "col1vb", "col2vb", "col3ve"),
  test("1", "col1vb", "col2va", "col3vd"),
  test("1", "col1vb", "col2va", "col3vf"),
  test("1", "col1vc", "col2vb", "col3vf"),
  test("1", "col1vc", "col2vc", "col3vf"),
  test("2", "col1v", "col2va", "col3va"),
  test("2", "col1v", "col2va", "col3vb"),
  test("2", "col1vb", "col2vb", "col3ve"),
  test("2", "col1vb", "col2vb", "col3vd"),
  test("2", "col1vc", "col2vc", "col3vf"),
  test("3", "col1va", "col2va", "col3va"),
  test("3", "col1vb", "col2vb", "col3vb"),
  test("3", "col1vc", "col2vc", "col3vc") )

Code Sinippet :

var whereCond = scala.collection.mutable.ArrayBuffer[Column]()
  val t1 = testmap.filter( p => p.id.equalsIgnoreCase("1") )    //This will call by iteration, we need rule per iteration
  t1.map( rule =>  {
   if ( ! (  rule.col1.equalsIgnoreCase("all") ) )  {
      whereCond.+=(col("col1")<=>rule.col1 + " && ") 
   if ( ! ( rule.col2.equalsIgnoreCase("all") ) )  {
      whereCond.+=(col("col2")<=>rule.col2 + " && ")
    }
   if ( !( rule.col3.equalsIgnoreCase("all") ) ) {
      whereCond.+=(col("col3")<=>rule.col3 + "  || ")
    }
  }
 })
 var finalColumn = col("")
 whereCond.toArray[Column].map(c => { finalColumn.+=(c) } )
 finalColumn    

But not getting expected results

And also, I tried below code snippet too

  var columnData =  col("")
  val df = testmap.toDF.where($"id"<=>"3").distinct
  val col1List = df.select("col1").rdd.map(r=>       r.getString(0)).collect().toList
  val col2List = df.select("col2").rdd.map(r=> r.getString(0)).collect().toList
  val col3List = df.select("col3").rdd.map(r=> r.getString(0)).collect().toList
  for( i <- 0 to col1List.size - 1 )
    if ( columnData  == col("")) 
        columnData  =  col("col1")<=>col1List(i)  && col("col2")<=>col2List(i) &&  col("col3") <=>col3List(i) 
      else
        columnData  = columnData  || (col("col1")<=>col1List(i)  && col("col2")<=>col2List(i) &&  col("col3") <=>col3List(i)  )
  columnData

Whenever we do && or || operation on the col scala automatically creating brackets for both of them

For above code I am getting output as below

    (((((col1 <=> col1vc) AND (col2 <=> col2vc)) AND (col3 <=> col3vc)) 
    OR (((col1 <=> col1va) AND (col2 <=> col2va)) AND (col3 <=> col3va))) 
    OR (((col1 <=> col1vb) AND (col2 <=> col2vb)) AND (col3 <=> col3vb))) 

But I am expecting output as

    col1 <=> col1vc AND col2 <=> col2vc AND col3 <=> col3vc 
    OR (col1 <=> col1va AND col2 <=> col2va AND col3 <=> col3va )
    OR (col1 <=> col1vb AND col2 <=> col2vb AND col3 <=> col3vb )


Get this bounty!!!

#StackBounty: #performance #sql #sql-server #t-sql #powershell Inserting data into SQL-Server table dynamically

Bounty: 50

I know I can do all this via SSMA quite quickly, but I wanted to see if I could do it efficiently in PowerShell myself as a little project.

Basically, the entire script takes tables from an Access database and creates a database in SQL Server with the same data. Everything from creating the table structure to cleansing the data is running quick. The slow part is inserting the data, which was inevitable but I want to see if I can make it a bit faster.

Here is the code:

<# Insert data into tables #>
function Insert_Data {
    param ( $data, $tableName )
    $columns = ($data.PSObject.Properties | where {$_.name -eq "Columns"}).value.columnName | Sort-Object

    $Insert = "INSERT INTO $tableName
    VALUES "

    $i = 0
    $x = 0

    foreach ($item in $data) {
        $Insert += "
        ("
        foreach ($item in $columns) {
            $Insert += "'" + $data.rows[$x].$item + "',"
        } 
        $Insert = $Insert.Substring(0,$Insert.Length-1)
        $Insert += "),"
        if ($i -eq 900) {
            $Insert = $Insert.Substring(0,$Insert.Length-1)
            $Insert += ";"
            Invoke-SQLCMD -Query $Insert -ServerInstance "." -database tmpAccessData -erroraction "Stop"
            $Insert = "INSERT INTO $tableName
                              VALUES "


            $i = 0
        }
        $i++
        $x++
    }

    $Insert = $Insert.Substring(0,$Insert.Length-1)
    $Insert += ";"

    Invoke-SQLCMD -Query $Insert -ServerInstance "." -database tmpAccessData -erroraction "Stop"

    Remove-Variable -Name data
}

It generates a large SQL query and inserts that into the specified table once it hits 900 values or the end of the Access table.

The data variable is the full Access table pulled into an Object:

$data_Clients       = Get-AccessData -Query "SELECT * FROM Client"    -Source $($settings.FastrackFiles.access_Fastrack);

And tablename is just the name of the table in the destination SQL database.


Get this bounty!!!

#StackBounty: #performance #sql #recursion #sql-server #t-sql Modeling tabular structure in MSSQL to store data like Excel sheet applyi…

Bounty: 50

I’ve created this database structure to store tabular information (it’s a simplification of my real structure but enough to show the point). In it I have Sheets and each Sheet can define different Columns. The important part is that with this structure I can define different datatypes for each Column.

Those could be divided in two groups: Natives (defined in the Native_Types table) and SheetType from the already created Sheets.

Model structure

Code to get unpivoted data:

CREATE VIEW RowsData
AS
    SELECT r.ID, s.ID SheetID, col.ID ColID, 
            col.Name, ISNULL(clsVal.NativeValue, cls.NativeValue) NativeValue
    FROM Sheets s
        INNER JOIN [Columns] col ON s.ID = col.SheetID
        INNER JOIN [Rows] r ON s.ID = r.SheetID
        INNER JOIN Cells cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID
        LEFT OUTER JOIN [Rows] rType ON cls.RowValueID = rType.ID
        LEFT OUTER JOIN Sheets sType ON rType.SheetID = sType.ID
        LEFT OUTER JOIN Cells clsVal ON rType.ID = clsVal.RowID AND sType.WhenIsTypeShowThisColumnIDValue = clsVal.ColumnID
GO

Sample data:

ID          SheetID     ColID       Name               NativeValue
----------- ----------- ----------- ------------------ ------------
1           1           1           Person Name        Aron
1           1           2           Person Country     Uruguay
2           1           1           Person Name        Sam
3           2           4           Country Name       Uruguay
4           2           4           Country Name       USA
5           2           4           Country Name       Russia
6           3           5           Pet Name           Mickey
6           3           6           Pet Age            83
6           3           7           Pet Person Owner   Aron
7           3           5           Pet Name           Pluto
7           3           6           Pet Age            8
7           3           7           Pet Person Owner   Aron
8           3           5           Pet Name           Zabivaka
8           3           6           Pet Age            4
8           3           7           Pet Person Owner   Sam

From here is all ok and any suggestion if there is a better way or any
improvements are welcome!


Weird stuff begins

Then I’ve implemented some kind of inheritance over Columns and its Cells.
For instance defining new Column for Sheet 3 (Pets) called “Pet Country” (setting it SheetTypeID -> 2, Countries). Now Pets have a Column “Pet Country” but instead of define a Cell value for each one if I define this Column as inherited (and particular structure to support in which way) I could inherit the Country value from “Pet Owner”. Remember that “Pet Owner” is of SheetTypeID 1 (Persons) and this Sheet have a Column of SheetTypeID 2 (Countries).

By simplifying I mean that if I have a Pet that has a Column “Pet Owner” and “Pet Country,” I don’t want to specify manually the “Pet Country” value, I want to inherit it from “Pet Owner” related with each Pet.

To accomplish that I have added two auxiliary fields to Columns table:

  • ColumnSameSheetInheritFromID -> This defines from which Column of the same Sheet this Column must inherit (must be Sheet type Column).

  • ColumnRelatedToFromColumnTypeID -> This defines from which Column of the Sheet type of the above Column must this Campo get the real inherited Cell value then.

Adjusted diagram:

Adjusted diagram


Helper self referenced function to get inherited Cells from Columns
(here is where I presume should be a better way to do things)

CREATE FUNCTION [dbo].[GetInheritedCells](@rowID INT, @colID INT)
RETURNS @result TABLE (ID INT)
AS
BEGIN   

    DECLARE @ColumnSameSheetInheritFromID INT = NULL;
    DECLARE @ColumnRelatedToFromColumnTypeID INT = NULL;

    --GETTING COLUMN FROM AND COLUMN VALUE IDs
    SELECT @ColumnSameSheetInheritFromID = col.ColumnSameSheetInheritFromID,
           @ColumnRelatedToFromColumnTypeID = col.ColumnRelatedToFromColumnTypeID  
    FROM dbo.[Columns] col
    WHERE col.ID = @colID;

    --IF COLUMN IS NOT INHERITED FUNCTION ENDS
    IF @ColumnSameSheetInheritFromID IS NULL OR @ColumnRelatedToFromColumnTypeID IS NULL
    BEGIN
        RETURN;
    END

    DECLARE @cellFromID INT = NULL;
    DECLARE @cellInhValueID INT = NULL;
    DECLARE @colFromAUX_ID INT = NULL;

    DECLARE @cellHaveValue INT = NULL;

    --GETTING REF FROM ROW RELATED TO THE RESPECTIVE DATA OF INHERITED COLUMN FROM AND CURRENT ROW              
    SELECT @cellFromID = cls.ID, 
           @colFromAUX_ID = col.ColumnSameSheetInheritFromID,
           @cellHaveValue = IIF(cls.RowValueID IS NULL, 0, 1)

        FROM dbo.[Rows] r
            LEFT OUTER JOIN dbo.[Columns] col
                ON r.SheetID = col.SheetID
            LEFT OUTER JOIN dbo.[Cells] cls
                ON r.ID = cls.RowID AND col.ID = cls.ColumnID
    WHERE r.ID = @rowID AND col.ID = @ColumnSameSheetInheritFromID

    IF @cellHaveValue = 0 AND @colFromAUX_ID IS NOT NULL
        SET @cellFromID = (SELECT TOP 1 ID FROM dbo.GetInheritedCells(@rowID, @ColumnSameSheetInheritFromID));
    ELSE IF @cellFromID IS NULL AND @ColumnSameSheetInheritFromID IS NULL
        SET @cellFromID = -1;

    IF @cellFromID > 0
    BEGIN
        SELECT @rowID = cls.RowValueID
        FROM dbo.[Cells] cls INNER JOIN dbo.[Columns] col ON cls.ColumnID = col.ID
        WHERE cls.ID = @cellFromID;

        IF @rowID IS NOT NULL
        BEGIN
            --GETTING REF FROM ROW RELATED TO THE RESPECTIVE DATA OF INHERITED COLUMN FROM AND CURRENT ROW              
            SELECT @cellInhValueID = cls.ID, 
                   @colFromAUX_ID = col.ColumnSameSheetInheritFromID,
                   @cellHaveValue = (IIF(cls.RowValueID IS NULL, IIF(cls.NativeValue IS NULL, 0, 1),1))

                FROM dbo.[Rows] r
                    LEFT OUTER JOIN dbo.[Columns] col
                        ON r.SheetID = col.SheetID
                    LEFT OUTER JOIN dbo.[Cells] cls  
                        ON r.ID = cls.RowID AND col.ID = cls.ColumnID
            WHERE r.ID = @rowID AND col.ID = @ColumnRelatedToFromColumnTypeID

            IF @cellHaveValue = 0 AND @colFromAUX_ID IS NOT NULL
            BEGIN
                INSERT INTO @result SELECT ID FROM dbo.GetInheritedCells(@rowID, @ColumnRelatedToFromColumnTypeID);
            END
            ELSE
            BEGIN
                INSERT INTO @result SELECT cls.ID
                FROM dbo.[Rows] r
                    LEFT OUTER JOIN dbo.[Columns] col
                        ON r.SheetID = col.SheetID
                    LEFT OUTER JOIN dbo.[Cells] cls  
                        ON r.ID = cls.RowID AND col.ID = cls.ColumnID
                WHERE r.ID = @rowID AND col.ID = @ColumnRelatedToFromColumnTypeID;
            END
        END
    END 

    RETURN;
END

Updated RowsData view:

ALTER VIEW RowsData
AS
    SELECT r.ID, s.ID SheetID, col.ID ColID, /*Columns Added*/col.ColumnSameSheetInheritFromID, col.ColumnRelatedToFromColumnTypeID,
            col.Name, ISNULL(clsVal.NativeValue, cls.NativeValue) NativeValue
    FROM Sheets s
        INNER JOIN [Columns] col ON s.ID = col.SheetID
        INNER JOIN [Rows] r ON s.ID = r.SheetID
        /*OUTER APPLY added*/
        OUTER APPLY (SELECT ID FROM dbo.GetInheritedCells(r.ID, col.ID)) inhCel
        LEFT JOIN Cells cls ON r.ID = cls.RowID AND col.ID = cls.ColumnID /* This added -> */ OR inhCel.ID = cls.ID
        LEFT OUTER JOIN [Rows] rType ON cls.RowValueID = rType.ID
        LEFT OUTER JOIN Sheets sType ON rType.SheetID = sType.ID
        LEFT OUTER JOIN Cells clsVal ON rType.ID = clsVal.RowID AND sType.WhenIsTypeShowThisColumnIDValue = clsVal.ColumnID
GO

Resultant data example:

ID          SheetID     ColID       ColumnSameSheetInheritFromID ColumnRelatedToFromColumnTypeID Name                                               NativeValue
----------- ----------- ----------- ---------------------------- ------------------------------- -------------------------------------------------- ---------------
1           1           1           NULL                         NULL                            Person Name                                        Aron
1           1           2           NULL                         NULL                            Person Country                                     Uruguay
2           1           1           NULL                         NULL                            Person Name                                        Sam
2           1           2           NULL                         NULL                            Person Country                                     NULL
3           2           4           NULL                         NULL                            Country Name                                       Uruguay
4           2           4           NULL                         NULL                            Country Name                                       USA
5           2           4           NULL                         NULL                            Country Name                                       Russia
6           3           5           NULL                         NULL                            Pet Name                                           Mickey
6           3           6           NULL                         NULL                            Pet Age                                            83
6           3           7           NULL                         NULL                            Pet Person Owner                                   Aron
6           3           8           7                            2                               Pet Country                                        Uruguay
7           3           5           NULL                         NULL                            Pet Name                                           Pluto
7           3           6           NULL                         NULL                            Pet Age                                            8
7           3           7           NULL                         NULL                            Pet Person Owner                                   Aron
7           3           8           7                            2                               Pet Country                                        Uruguay
8           3           5           NULL                         NULL                            Pet Name                                           Zabivaka
8           3           6           NULL                         NULL                            Pet Age                                            4
8           3           7           NULL                         NULL                            Pet Person Owner                                   Sam
8           3           8           7                            2                               Pet Country                                        NULL

Database script with example data to play with here!


Get this bounty!!!

#StackBounty: #sql #postgresql #query-performance Query optimization with multi-column variant matching

Bounty: 100

TL;DR – I’m looking for advice on how to better write the query below.

Below is a pared down version of my table structure with some sample data. I don’t have control over the data
structure at all so recommendations on schema changes unfortunately won’t help me.

Problem

Given a building_level_key and a faction_key I need to return a record from building_levels joined to its
closest match from the building_culture_variants table.

For example, if I used goblin_walls & fact_blue I would expect the goblin_walls record that joins
building_culture_variant_key record 2.

An example structure of tables can be seen below:

Sample data with db structure

  • factions – is a compacted version of the real table as cultures/subculture records are stored in different
    tables but it gets the point across. This table is only really needed in the query so that the appropriate
    culture/subculture can be referenced in relation to a given faction_key.

  • building_levels – acts as a base record for every building in the system. There is only one record per
    building.

  • building_culture_variants – acts as its name implies; there can be more than one record for each building_level_key and each variant record is matched against a building level using the building_level_key and a combination of faction_key, culture_key and subculture_key.

How matching works

Matching starts with finding the given building_level_key in the culture variants table. This is a hard match and is needed to join any two building level and culture variant.

Each building level record will have at least one culture variant. Often there are several culture variants per building level but on average no more that 4. The most common culture variants is a “generic” one which means that the faction_key, culture_key and subculture_key columns are all null so the building will match against any faction. However any combination of the faction columns could have a key so I need to match a given faction against each of the faction columns in the culture variant.

Side note: the culture variant keys are always consistent, meaning I’ll never have a scenario where a faction_key and subculture_key in the culture variants table don’t match a corresponding faction_key and subculture_key from the factions table (and subculture table, which has been omitted for clarity).

What I’ve tried

I have provided a sql fiddle to play around with and included my version of the query below:

SELECT 
  "building_culture_variants"."building_culture_variant_key" AS qualified_key, 
  "building_levels"."building_level_key" AS building_key, 
  "building_levels"."create_time", 
  "building_levels"."create_cost", 
  "building_culture_variants"."name",
  'fact_blue'::text AS faction_key
FROM 
  "building_levels" 
  INNER JOIN "building_culture_variants" ON (
    "building_culture_variants"."building_culture_variant_key" IN (
      SELECT 
        "building_culture_variant_key" 
      FROM 
        (
          SELECT 
            "building_culture_variants"."building_culture_variant_key", 
            (
                CASE WHEN "building_culture_variants"."faction_key" = "building_factions"."faction_key" THEN 1 WHEN "building_culture_variants"."faction_key" IS NULL THEN 0 ELSE NULL END + 
                CASE WHEN "building_culture_variants"."culture_key" = "building_factions"."culture_key" THEN 1 WHEN "building_culture_variants"."culture_key" IS NULL THEN 0 ELSE NULL END + 
                CASE WHEN "building_culture_variants"."subculture_key" = "building_factions"."subculture_key" THEN 1 WHEN "building_culture_variants"."subculture_key" IS NULL THEN 0 ELSE NULL END
            ) AS match_count 
          FROM 
            "building_culture_variants" 
            INNER JOIN (
              -- This is a subquery because here I would join a couple more tables
              -- to collect all of the faction info
              SELECT 
                "factions"."faction_key", 
                "factions"."culture_key", 
                "factions"."subculture_key"
              FROM 
                "factions" 
            ) AS "building_factions" ON ("building_factions"."faction_key" = 'fact_blue')
          WHERE ("building_levels"."building_level_key" = "building_culture_variants"."building_level_key") 
          GROUP BY 
            match_count, 
            building_culture_variant_key 
          ORDER BY 
            match_count DESC NULLS LAST 
          LIMIT 
            1
        ) AS "culture_variant_match"
    )
  ) 
WHERE "building_levels"."building_level_key" = 'goblin_walls'
ORDER BY 
  "building_levels"."building_level_key"

Question

The query I provided above works and gets the job done but I feel like I’m trying to brute force the problem by just nesting a bunch of queries. I get this feeling like I’m not taking advantage of some sql construct that would streamline the performance of, or greatly simplify the query.

So what I’m really asking, is there a better way I could rewrite the query to be more efficient?


Get this bounty!!!

#StackBounty: #java #sql #sql-insert #sql-delete #jsqlparser How to convert insert sql statement to delete sql statement using JSQLParser

Bounty: 50

I want to convert insert sql statement to delete sql statement using JSQLParser, How this can be achieved ?
for example here is sample insert sql

INSERT INTO emp (empno, ename, job, sal, comm, deptno, joinedon) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30, TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));

post conversion to delete sql will be following

DELETE FROM emp WHERE empno=4160 AND ename='STURDEVIN' AND job='SECURITY GUARD' AND sal=2045 AND comm=NULL AND deptno=30 AND joinedon=TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF');

Here is code thus far

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;

import java.util.List;

public class Main {

    public static void main(String[] args) {
        String insertSQL = "INSERT INTO emp (empno, ename, job, sal, comm, deptno, joinedon) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30, TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));";
        final Statement statement;
        try {
            statement = CCJSqlParserUtil.parse(insertSQL);
            if (statement instanceof Insert) {
                final Insert insertStatement = (Insert) statement;
                System.out.println("insertStatement: " + insertStatement);
                List<Column> columns = insertStatement.getColumns();
                for (final Column column : columns) {
                    System.out.println("column: " + column);
                }
                ItemsList itemsList = insertStatement.getItemsList();
                System.out.println("itemsList: " + itemsList);
                final Delete deleteStatement = new Delete();
                deleteStatement.setTable(insertStatement.getTable());
                System.out.println("deleteStatement: " + deleteStatement);
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }
}

I am building this project using maven here is pom dependency.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>oracle.insert.delete.jsqlparser</groupId>
    <artifactId>oracle_from_insert_to_delete_using_JSqlParser</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>


</project>

Part of legacy code, These insert statement are written for setting up test data, I want to clean up test data automatically once test is complete. And not depend on poorly written delete statement in teardown.


Get this bounty!!!

#StackBounty: #sql #r #monetdb #monetdblite Add new column to MonetDBLite table

Bounty: 50

I am trying to use R + MonetDB as a large-data analysis stack and am having trouble creating a new column and populating it with data from my analysis. Here is a toy example:

library(MonetDBLite)
library(DBI)

data(mtcars)
db <- dbConnect(MonetDB.R::MonetDB(), embedded="./test.db")

# load mtcars into the database
dbWriteTable(conn=db, value = mtcars, name = "mtcars", overwrite=TRUE)

## Add a new column
dbSendQuery(db, "ALTER TABLE mtcars ADD v1 DOUBLE;")


## insert data into the new column
v1 <- mtcars["mpg"] * pi
dbSendQuery(db, "INSERT INTO mtcars (v1) VALUES (?)", bind.data=v1)

And the error message:

Error in .local(conn, statement, ...) : 
  Unable to execute statement 'INSERT INTO mtcars (v1) VALUES ('c(65.9734457253857, 65.9734457253857, 71.6283125018473, 67.23008278...'.
Server says 'ERROR: 49.6371639267187, 61.8893752757189, 47.1238898038469, 67.2300827868216)' to type dbl failed.
' [#conversion of string 'c(65.9734457253857, 65.9734457253857, 71.6283125018473, 67.2300827868216, 58.7477826221291, 56.8628270299753, 44.924774946334, 76.6548607475909, 71.6283125018473, 60.318578948924, 55.9203492338983, 51.5221195188726, 54.3495529071034, 47.7522083345649, 32.6725635973338, 32.6725635973338, 46.18141200777, 101.787601976309, 95.5044166691297, 106.499990956694, 67.5442420521806, 48.6946861306418, 47.7522083345649, 41.7831822927443, 60.318578948924, 85.7654794430014, 81.6814089933346, 95.5044166691297, 
].
In addition: Warning message:
In if (is.na(value)) statement <- sub("?", "NULL", statement, fixed = TRUE) else if (valueClass %in%  :
  the condition has length > 1 and only the first element will be used

From this error I surmise that maybe bind.data can’t be used with MonetDBLite?

Question:

How can I add a column(s) to a MonetDBLite table and populate it with data from my R session?


Get this bounty!!!