![]() ![]() Looking at the first few songs, you can see they all have the same GenreId and we know that 1 corresponded to the “Rock” genre.Case checks if a value matches a list of conditions, and returns some output based on the first condition that’s met. Now you could not JOIN in Genre Names, How could we replace the GenreId in the Track table?ĬASE WHEN allows you to assign the genres yourself. Let’s say you lost the “Genre” table (indicated with the red X through it). However, the “GenreId” column is a reference to another table that links each id number to a genre of music. The “TrackId” in the table below is unique for each entry. The “Track” table in the Chinook database is a large, informational table on many different songs by many different artists. This example uses the Chinook database with PostgreSQL 11. To determine what data type is used in a column you can use the “\d (Table name)” command and look for the “Type” column: \d "Track" Remember you can cast any of your values in order to make the data types match. The Name field is a VARCHAR and cannot be compared to an INTEGER. Name = 40 ) THEN 'Rock' END AS Genre FROM Track ORDER BY Track. If you try to compare incompatible types SQL will return an “Invalid Input Syntax” error. ![]() Make sure that you are comparing the correct data types in your queries. More on optimizing queries from Data School here. If queries are taking a long time to finish, consider finding ways to optimize your query. This can dramatically increase the query time. CASE WHEN queries can become very slow because the query has to check each condition for every row until it finds a case where it satisfies the condition. There is no defined maximum for the number of WHEN conditions you can have within a CASE WHEN statement. Time to complete a query can be a problem. Note: you do not have to show all of the columns, or any columns besides the CASE column in the output. SELECT ( optional : any desired columns ), CASE WHEN ( condition ) THEN ( desired output ) WHEN ( other condition ) THEN ( desired output ) ELSE ( desired output ) END AS ( descriptive header for the output column ) FROM ( appropriate table ) In the example we see this done with ELSE City which puts LA unchanged in the new column Syntax ![]() NOTE: If you put the column name after THEN or ELSE it will put the value from the original column into the newly created column.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |