Posted on

Replacing variable name in excel

Hi all,
Before I begin this question, and before you tell me we dont do homework, I would like to state that I have read a few articles, I have tried a couple of functions (=REPLACE, =SUBSTITUTE) but i'm not able to get the reuslts require, I'm new to Excel
Question: Useappropriate text functions to shorten the variable names to something like Arizona Females Young, Arizona Females Old, and Arizona Females All, also is there a way to do it automatically for all variables in 1 function. The screenshot is attached.
It is kind of urgent (I understand people help within their own time).
Thanks in advance.

excel.png

Posted on

See What’s New in Neo4j 4.0

If you’re not directly plugged in to Neo4j-specific news, you may not have seen the recent splash of all-new features in Neo4j’s latest database version release. Or, perhaps you saw it but it wasn’t clear how it could benefit or help you in the work that you are doing.

This is an exciting release, most of which is based around the evolution of the Neo4j database into a full database management system (DBMS)! There are a variety of new features that go along with this development and assist users and teams in managing multiple graphs.

Posted on

INSERT INTO with LAST_INSERT_ID()

My code has a query that looks like:

INSERT INTO table
VALUES (...)
ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    ...

id is a PRIMARY KEY.

My question is that I can't for anything remember why I have id = LAST_INSERT_ID(id). I wrote it years ago and it's not commented. Can anyone think of a reason why I was doing this?

Posted on

Endless loop in mysql function

Hello all.
I have a funtion in MYSQL to substruct days from a date.
This function seamsto work but some dates it times out.
This is my function

CREATE DEFINER=`root`@`localhost` FUNCTION `Calculater`(intrval int, req date) RETURNS date
    DETERMINISTIC
BEGIN
declare calculated date;
declare added int default  0;

Lbl_Loop: Loop
IF added=intrval then
leave lbl_loop;
End IF;
set calculated = DATE_SUB(req,interval 1 day);
IF weekday(calculated)<5  then
set added= added+1;
End If;
end loop;
RETURN calculated;
END

If I do select Calculater(1,"2020-02-04"); it works, but select Calculater(1,"2020-02-02"); it runs for a few seconds and My SQL times out.

Thanks in advance.

Posted on

What’s the Future of Cloud Databases?

As more organizations look to migrate their databases to the cloud, what does this mean for developers? We at DZone believe there is no one better to ask than you! Tell us about your experiences with cloud databases by taking this 5 minute survey!

Over the next two weeks, we plan to survey hundreds of software developers about their experiences with cloud databases. The key findings from the survey will be found in our Cloud Database Trend Report to be released February 19. It is our hope that we can identify some of the key trends happening in the space to help our community stay ahead of the curve.

Posted on

4 Data Sharding Strategies for Distributed SQL Analyzed

A distributed SQL database needs to automatically partition the data in a table and distribute it across nodes. This is known as data sharding, and it can be achieved through different strategies, each with its own tradeoffs. In this post, we will examine various data sharding strategies for a distributed SQL database, analyze the tradeoffs, explain the rationale for which of these strategies YugabyteDB supports, and what we picked as the default sharding strategy.

What is YugabyteDB? It is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. Yugabyte’s SQL API (YSQL) is PostgreSQL wire compatible.

Posted on

Delete duplicate rows in MySQL

Sometimes you need to delete duplicate rows in a database :)

In the future, set UNIQUE keys when you need them :)

In the example below, we have a table appropriately named TABLE_NAME and there are multiple rows that have the same value for the duplicate_field field. In this example, we want to remove the ones where the id field is set to "remove". In other examples, you can pick any conditional you want when choosing which of the rows to remove or which to keep, including conditionals between them (e.g. foo.id < bar.id will keep the row with the highest id value and delete the others).

Alternatively, if you do want to add that UNIQUE index, you can do the following to brute force dropping duplicate rows:

ALTER IGNORE TABLE TABLE_NAME
ADD UNIQUE INDEX index_name (duplicate_field);
Posted on

SQL, Databases, and Hollywood Movies

Hollywood sign close up

Policemen, doctors, lawyers, scientists, teachers: these are the professions that we often see in Hollywood movies. But are movies also trying to show what the work of data analysts looks like? Here is a list of six movies in which screenwriters, directors, and producers decided to cast SQL and databases in the main roles.

Data analysis is most often associated with business, reporting, and making key decisions for the company. Today it is part of many organizations’ daily life, so it’s no wonder that this topic often appears in movies.

Posted on

SQL query problem with any language except English

Hello guys,
i've the following table in database :

IdCity int
CityName nvarchar(20)

it has the data like this in it :

IdCity CityName
1
2
3
4
5
6
when i try to run the following view

    SELECT       IdCity, CityName
    FROM            dbo.City
    WHERE        (CityName = '')

i get the results null, althought that record in the table
i tried to add a record with cityname= 'abc' and ran the view with this parameter, it worked fine
so it only doesn't get results written in russian
any ideas?

SQL query problem with any language except English

Posted on

Cache query result

I come across this problem every so often and end up having to do work arounds when I would prefer to keep it in the database. I have a query where the data in the query updates frequently so the query won't stay in the cache but I don't need the query to be accurate. I was trying to avoid adding an extra step of storing the data in a temp table and having to update it on a timer.

Does anyone know of a way to get MariaDB or MySQL to Keep the result set for a period of an hour or so before actually running the query again, I have an example of a query below which takes between 4.8-6 seconds to run and the wait is too long and also is needless work for the server, It can just do the query once and get the 4 numbers - then all subsequent requests just return the 4 numbers and every 20 minutes or 1 hour the 4 numbers get updated.

Is there a built in funtion for this or is the only solution to make your own table? I could end up with many of these and I don't want to end up with 100's

SELECT
    SUM(CASE WHEN p.id IS NULL AND f.loc = 1 THEN 1 ELSE 0 END)/250 to_print
    , SUM(CASE WHEN p.id IS NULL AND f.loc NOT IN(1,2,8) THEN 1 ELSE 0 END)/250 to_print_out
    , SUM(CASE WHEN p.id IS NOT NULL AND p.print_date BETWEEN '2020-01-09 14:00:00' AND '2020-01-16 14:00:00' THEN 1 ELSE 0 END)/250 printed_tw
    , SUM(CASE WHEN p.id IS NOT NULL THEN 1 ELSE 0 END)/250 printed
    FROM typist t
    LEFT JOIN printing p ON t.typed_id = p.print_id AND p.type = 4
    LEFT JOIN files f ON t.file_id = f.file_id
    WHERE
    t.type = 1
    AND f.file_id IS NOT NULL
    AND NOT EXISTS(SELECT DISTINCT l.id FROM letters l WHERE l.deleted = 1 AND t.letter_id = l.id)

This is the EXPLAIN for the query

"1" "PRIMARY"   "t" "ref"   "file_id,letter_type,print_stat"    "print_stat"    "2" "const" "134243"    "Using where; Using index"
"1" "PRIMARY"   "f" "eq_ref"    "PRIMARY"   "PRIMARY"   "3" "sys.t.file_id" "1" ""
"1" "PRIMARY"   "p" "ref"   "id,type"   "particle_id"   "8" "sys.t.typed_id"    "1" "Using where"
"2" "MATERIALIZED"  "l" "ref"   "PRIMARY,deleted,deleted_file_id"   "deleted"   "1" "const" "4741"  "Using index"

I have already had numerous variations to try optimising this and the above is the best I have got so far. t has around 200,000 rows, l has around 300,000, f has about 130,000, p has around 300,000 but will bloat well over 1,000,000 in time.

Posted on

mysqli query skipping row 0

I am using bootstrap modals, when i query to populate a dropdown to fill in to my form, my query is show all but the first record and i can't figure out why.

My form looks like this

<div class="form-group">
            <label for="deptcode">Department</label>
                <select class="form-control" id="deptcode" name="deptcode" value="<?php echo $mem['deptcode'];?>"
                    <?php
                    $departments = $mysqli->query("Select * FROM department ORDER BY deptcode");
                     while ($dept = mysqli_fetch_assoc($departments)){

                       $tempdeptname = $dept['deptname'];
                       $tempdeptcode = $dept['deptcode'];
                       //see what the current name is and display it first
                       if ($tempdeptcode == $mem['deptcode'])
                         echo "<option value='$tempdeptcode' selected='selected'>$tempdeptcode ($tempdeptname) </option>\n";
                       else
                       //if blank display first item in dropdown list
                       echo "<option value=\"$tempdeptcode\">$tempdeptcode ($tempdeptname) </option>\n";
                        }
                    }
                    ?>
                    endwhile;
                </select>
        </div>

This works perfectly other than not showing row 0.

Thanks in advance.

Posted on

Codeigniter Transactions

DaniWeb was written in Codeigniter, and when posting to the forums, we use database transactions that look like this:

$this->db->trans_start();

... insert into the posts table ...
... update the member's post count ...
... update the tags table ...
etc

$this->db->trans_complete();

On occassion, the error log will show for a particular query within the transaction: Query error: Deadlock found when trying to get lock; try restarting transaction - Invalid query

When that happens, the entire transaction is rolled back. However, this is not ideal because when someone attempts to make a post, we don't want it to completely fail just because the member's table happened to have been in use at the time.

That being said ... how do I go about restarting the transaction?? e.g. something such as:

if ($this->CI->db->trans_status() === false)
{
    // Code to restart the transaction
}

Is there a better way to be doing it? Should I be manually locking tables?

Posted on

The Benefit of Partial Indexes in Distributed SQL Databases

Partial Indexes


If a partial index is used instead of a regular one, on a nullable column — where only a small fraction of the rows have not null values for this column—then the response time for inserts, updates, and deletes can be shortened significantly. As a bonus, the response times for single row selects shorten a little bit too. This post explains what a partial index is, shows how to create one, describes the canonical use case that calls for a partial index, describes some straightforward performance tests, and shows that the results justify the recommendation to use a partial index when you have the appropriate use case.

Posted on

Thinking in React Hooks: Building an App With Embedded Analytics

Get hooked on React Hooks

You may have noticed that React Hooks, introduced in the React’s 16.8.0 release, have been received by the web dev community very diversely. Some warmly embraced this new way to reuse stateful logic between components, while some strongly criticized it. One thing can be said for sure — React Hooks are an incredibly hot topic now. This is confirmed by the number of articles, tutorials, video courses, and project samples on the subject.

My goal is to briefly introduce to you this powerful concept (if you are not familiar with it yet) and show how it can be applied to building a simple analytical app. Note that we’ll focus more on getting a hands-on experience rather than on debating on the pros and cons of using Hooks.

Posted on

The Northwind PostgreSQL Sample Database Running on a Distributed SQL Database

The Northwind database is a sample database that was originally created by Microsoft and used as the basis for their tutorials in a variety of database products for decades. The Northwind database contains the sales data for a fictitious company called “Northwind Traders,” which imports and exports specialty foods from around the world. The Northwind database is an excellent tutorial schema for a small-business ERP, with customers, orders, inventory, purchasing, suppliers, shipping, employees, and single-entry accounting. The Northwind database has since been ported to a variety of non-Microsoft databases including PostgreSQL.

In this post, we are going to walk you through how to download and install the PostgreSQL-compatible version of Northwind on the YugaByte DB distributed SQL database.

Posted on

Write information to Database

Hi group,

I'm trying to store the information from a various textboxes into my database. However it is not writing the data (I'm getting my planned error message, "New Record FAILED. Please contact your systems administrator." From the code below, do you see where my error may be? What corrections do I need to make to ensure a successful insert?

Dim con As New SqlClient.SqlConnection
        con.ConnectionString = ("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Don\Documents\Visual Studio 2019\SalesForm\DWHRPT.mdf;Integrated Security=True")
        con.Open()
        Dim com As New SqlClient.SqlCommand("", con)
        com.CommandText = "INSERT INTO CUSTREC (CUST_ACCT_NO, CUST_COMPANY_NAME, CUST_LOCATION, CUST_TYPE, CUST_FIRST_NAME, CUST_MIDDLE_INITIAL, 
                                                  CUST_LAST_NAME, CUST_MAIL_ADDRESS1, CUST_MAIL_ADDRESS2, CUST_MAIL_CITY, CUST_MAIL_STATE, 
                                                  CUST_MAIL_ZIP_CODE, CUST_MAIL_ZIP_PLUS4, CUST_CREDIT_LIMIT, CUST_PRIMARY_PHONE, CUST_ALT_PHONE, 
                                                  CUST_TAX_EXEMPT_STATUS, CUST_TAX_EXEMPT_ID, CUST_SHIP_TO_ADDRESS1, CUST_SHIP_TO_ADDRESS2, 
                                                  CUST_SHIP_TO_CITY, CUST_SHIP_TO_STATE, CUST_SHIP_TO_ZIP_CODE, CUST_SHIP_TO_ZIP_PLUS4, CUST_USE_SELL_PRICE,
                                                  CUST_STD_DISC_PCNT, CUST_SHIP_TO_CONTACT_NAME, CUST_SHIP_TO_PHONE_NO, CUST_START_DATE) 
                            VALUES (@AcctNo, @CompName, @Loc, @Type, @FirstName, @MidInt, @LastName, @MailAddr1, @MailAddr2, @MailCity, @MailState, 
                                    @MailZip, @MailZipPlus4, @CreditLimit, @PrimaryPhone, @AltPhone, @TaxStatus, @TaxID, @ShipAddr1, @ShipAddr2,
                                    @ShipCity, @ShipState, @ShipZipCode, @ShipZipPlus4, @SellPrice, @Disc, @ShipContactName, @ShipToPhone, @StartDate)"

        Try
            com.Parameters.Add("@AcctNo", SqlDbType.BigInt).Value = tbxAccountNo.Text
            com.Parameters.Add("@CompName", SqlDbType.VarChar).Value = tbxCompanyName.Text
            com.Parameters.Add("@Loc", SqlDbType.Int).Value = tbxLoc.Text
            com.Parameters.Add("@Type", SqlDbType.VarChar).Value = tbxCustType.Text
            com.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = tbxFirstName.Text
            com.Parameters.Add("@MidInt", SqlDbType.VarChar).Value = tbxMiddleInt.Text
            com.Parameters.Add("@LastName", SqlDbType.VarChar).Value = tbxLastName.Text
            com.Parameters.Add("@MailAddr1", SqlDbType.VarChar).Value = tbxAddress1.Text
            com.Parameters.Add("@MailAddr2", SqlDbType.VarChar).Value = tbxAddress2.Text
            com.Parameters.Add("@MailCity", SqlDbType.VarChar).Value = tbxCity.Text
            com.Parameters.Add("@MailState", SqlDbType.VarChar).Value = tbxState.Text
            com.Parameters.Add("@MailZip", SqlDbType.VarChar).Value = zipCode
            com.Parameters.Add("@MailZipPlus4", SqlDbType.VarChar).Value = zipPlus4
            com.Parameters.Add("@CreditLimit", SqlDbType.Int).Value = tbxCreditLimit.Text
            com.Parameters.Add("@PrimaryPhone", SqlDbType.BigInt).Value = primephone
            com.Parameters.Add("@AltPhone", SqlDbType.BigInt).Value = altphone
            com.Parameters.Add("@TaxStatus", SqlDbType.VarChar).Value = tbxTaxExempt.Text
            com.Parameters.Add("@TaxID", SqlDbType.VarChar).Value = tbxTaxExemptID.Text
            com.Parameters.Add("@ShipAddr1", SqlDbType.VarChar).Value = tbxShipAddress1.Text
            com.Parameters.Add("@ShipAddr2", SqlDbType.VarChar).Value = tbxShipAddress2.Text
            com.Parameters.Add("@ShipCity", SqlDbType.VarChar).Value = tbxShipCity.Text
            com.Parameters.Add("@ShipState", SqlDbType.VarChar).Value = tbxShipState.Text
            com.Parameters.Add("@ShipZipCode", SqlDbType.VarChar).Value = ShipZipCode
            com.Parameters.Add("@ShipZipPlus4", SqlDbType.VarChar).Value = ShipZipPlus4
            com.Parameters.Add("@SellPrice", SqlDbType.Int).Value = Convert.ToInt32(tbxUseSellPriceNo.Text)
            com.Parameters.Add("@Disc", SqlDbType.Int).Value = Convert.ToInt32(tbxStdDiscPcnt.Text)
            com.Parameters.Add("@ShipContactName", SqlDbType.VarChar).Value = tbxShipToContactName.Text
            com.Parameters.Add("@ShipToPhone", SqlDbType.BigInt).Value = ShipPhone
            com.Parameters.Add("@StartDate", SqlDbType.VarChar).Value = tbxAcctStartDate.Text
            com.ExecuteNonQuery()
            MessageBox.Show("New Record Created", "", MessageBoxButtons.OK)
        Catch ex As System.Data.SqlClient.SqlException
            MessageBox.Show("New Record FAILED.  Please contact your systems administrator.", "", MessageBoxButtons.OK)
        End Try
        con.Close()

In advance, thanks for your help.

Don