Questions tagged [alias]

Within a query, tables or subqueries may be aliased for readability and so that the same table can be referenced more than once in the query with different join conditions.

Filter by
Sorted by
Tagged with
0
votes
1answer
28 views

SQL Server Alias won't work for my database instance

I have setup a SQL Server 2019 instance on my machine which has a computer name in a format like this: AB-ABC1234ABAB My SQL instance is named 'SQL2019', therefore I connect to my local SQL instance ...
0
votes
1answer
23 views

ORA-00904 invalid identifer error when using alias

I'm trying to return a product of two subqueries, and thought I'd make life easier by aliasing each of the subqueries, then divide the two aliases to get the number I'm after, like this: select ...
0
votes
2answers
504 views

How to select all columns plus one alias column in MySQL

It is a simple question, but i could not find the answer. I want to select all the columns in my table with a SELECT * statement, but i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ...
0
votes
2answers
117 views

Omit table name and dot in SELECT query

When I perform this query: SELECT `tablename`.* FROM `something`.`tablename` I get a table with column names that contain dots: tablename.c1 | tablename.c2 | tablename.c3 ---------------------------...
0
votes
0answers
36 views

Using Alias in Calculations [duplicate]

I know that this Problem was addressed before but my SQL knowledge is not that good to figure it out. Maybe you can help me. select ProductionLine, Product, POId, BatchNumber, ...
0
votes
0answers
28 views

Google BigQuery - Using ARRAY_AGG and Aliasing Fields

I am aggregating various tables into a singular "mega" table to drive an analytics platform. While I have several WITH clauses defined above my provided code snippet, these are simply to mitigate ...
2
votes
1answer
442 views

Querying a SQL Server's CNAME alias instead of the actual instance name

We don't have a DBA here and our infrastructure analyst is trying his best to give us a test instance on a server using a CNAME alias which neither he nor I have done before. The actual database ...
0
votes
1answer
38 views

Multiple vs Single COUNT on same col + Multiple vs Single col index

I have two very simple questions 1. Multiple vs Single COUNT on same col I need to perform CASE on an aggregate column like this: SELECT tbl_students.school, COUNT(tbl_students.id) as students, ( ...
0
votes
2answers
48 views

Is it possible to refer to alias before it is assigned? [duplicate]

Does the query shown below work? (Sorry for this type of question, but I am a novice and it is not possible for me to run it right now). SELECT table.id SUM(10/ta.value) AS amount JOIN tableA ...
0
votes
0answers
25 views

Linked server query fails when using an Alias after a schema change

Recently we added a new column [NewColumn] in a table [OurTable]. After that, a query from linked server referencing this table failed with error: The multi-part identifier "[OurTable].[NewColumn]"...
2
votes
1answer
53 views

MySQL: INSERT INTO SELECT issues

Consider the following tables: => table1 | aid | | --- | | a1 | | a1 | | a3 | | a2 | | ... | => table2 | aid | count | va | vb | | --- | ----- | ----- | ----- | | a1 | c1 | va1 ...
1
vote
0answers
56 views

Postgres - want a database to have two names

One of my Postgres databases is named entirely in upper-case, and a new bit of software needs to see it in lower-case. I've tried renaming it, but unfortunately that breaks the huge multitude of ...
2
votes
0answers
76 views

CNAME alias from named SQL instance to default instance [duplicate]

I know I can use CNAME alis to point from one SQL Server to another for upgrade or such log shipping, but what if one is named instance and other is default instance? Do I need SQL alias? Do i need ...
0
votes
1answer
24 views

Why do I get an error when filtering subSelect?

select "masters".*, (select avg (rating) from "reviews" where "master_id" = "masters"."id") as rating from "masters" where rating > 3 [42703] ERROR: column "rating" does not exist
1
vote
1answer
291 views

SQL Server 2012 - Create Alias from default instance (i.e. '.') to named instance of SQL 2012

Scenario is: I have SQL 2012 running as a named instance 'SQL2012'. I can connect to this via SSMS using '.\SQL2012' Working on a new project, I now want this named instance to be accessible as '.' ...
11
votes
2answers
9k views

Using column alias in a WHERE clause doesn't work

Given a table users with two fields: id and email. select id, email as electronic_mail from ( select id, email from users ) t where electronic_mail = '' Postgres complains that: ...
0
votes
1answer
67 views

Is it possible to tell if an incoming connection is using an SQL Server Alias?

Using SQL Server Configuration Manager to Create a Server Alias for Use by a Client is cool tool used in many cases when it works In my DBA position I do not have access to the application server, to ...
2
votes
3answers
4k views

How to use column alias to calculate other column value

My query is SELECT (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings ,(`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS ...
5
votes
3answers
400 views

Getting SQL server to recognise a date column

I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed. My table looks like this orders ( order_id INT ...
1
vote
1answer
124 views

SQL Server Upgrade - A new Approach for Upgrading

Microsoft's technical guide for SQL Server upgrade references two upgrade paths: in-place and side-by-side. I don't see a third, visibly more viable/convenient option, which is to do an in-place ...
5
votes
1answer
1k views

Same table alias for two different tables (Oracle)

This example has the same table alias used for two different tables. I don't understand why this is allowed by Oracle, and if allowed, how the results make any sense. create table Table_A (x number);...
2
votes
1answer
4k views

Two joins on same table in SQL

I've got 2 SQL tables: NAMES and RELATIONSHIPS Currently NAMES just has 2 columns: name and name_id. The other is a list of relationships between people in the NAMES table. It has 3 columns: ...
1
vote
1answer
6k views

How to create a view from a join with multiple duplicate column names?

I have a few tables with this sort of format myTable: id | Table1_id | Table2_id | Table3_id | value Table1: id | text | ... Table2: id | text | ... Table3: id | text | ... I want to create a ...
1
vote
4answers
515 views

Is there a valid reason to alias a column to the exact same name/case?

I inherited some code like this: SELECT Col_1, Col_2 Col_2, Col_3 ... Does the Col_2 alias actually accomplish anything, or is it completely unneeded?
1
vote
1answer
1k views

Conditional Alias in SQL

Is there any way to give conditional alias to SQL columns?. Because I have two tables for Fields and Category. Meaning of each field changes conditionally. DECLARE @f1 NVARCHAR(10); set @f1= (...
6
votes
3answers
960 views

Display column title as in the query without 'AS' statement

I query data from multiple tables using JOIN statements like this : SELECT u.id,u.name,d.id,d.name FROM user u RIGHT JOIN dog d ON... ; When I get the answer, in psql for example, I've got this kind ...
0
votes
1answer
232 views

Data from Same Column Joined in Two Different Ways

In the following sybase query I would like to pull the vendor column twice: the first time I would like to pull the vendor associated with the ReceiveWorksheet table by associating it with a key ...
0
votes
1answer
130 views

How to easily show very long “canonical” name for each column in all reports?

Related: Does the length of a table name affect performance? For query writing purposes I want to have nice short actual column names. However, from my actual data source, I have an extremely long "...
3
votes
1answer
739 views

Why is the 'AS' table alias keyword required in Visual Studio (Database projects)

Up till now I have been working directly on SQL Server instances, and am now playing around with how best to keep databases in source control. The visual studio database project seems to be exactly ...
-1
votes
2answers
8k views

For a query can you have a two word alias

What I want to do is SELECT COUNT(CustomerID) as CC as 'Customer Count', Country FROM Customers GROUP BY Country HAVING CC > 5 ORDER BY CC DESC; Which is wrong. What is the right way? From: ...
2
votes
1answer
824 views

ORDER BY clause with Alias

I am trying to get the sum of the market values for each given CPTY. Each CPTY can have several market values. In my output list, i want to see the top 5 CPTYs, ordered by the sum of their market ...
3
votes
1answer
2k views

INSERT INTO with UNION Alias ERROR

This question is derived from the following post UNION query not working Fails: INSERT INTO AssetControl SELECT * FROM CSVImport UNION SELECT * FROM AssetTemp; Works: INSERT INTO ...
6
votes
1answer
694 views

When are COLUMN aliases in FROM clauses needed?

FROM provides column_alias, the SQL spec calls these <derived column list> clauses. This is what the postgres docs say about them, A substitute name for the FROM item containing the alias. An ...
0
votes
0answers
402 views

Is there any way to set the alias for command permanently in PostgreSQL?

I'm trying to set the alias for command permanently in PostgreSQL. I've set the alias for a command like given below. Test=> \set x '\\! reset' So, the alias has set for reset the terminal. While ...
5
votes
1answer
2k views

Column alias in Postgres FROM clause?

I'm reading through the Postgres documentation, the page on SELECT statements, and I ran across an aspect of aliases that I have never encountered. In the section on FROM clauses, subheading alias, ...
0
votes
1answer
146 views

SQL Server Change Alias Not Recognized in SSMS

I created an alias named "ProductServer" to point to a SQL Server 2008 R2 named instance on my local machine. I'm trying to upgrade that to point to SQL Server 2014 named instance. I used SQL Server ...
2
votes
3answers
2k views

DB2: Database alias already exists when cataloging

SQL1005N The database alias "XYZ" already exists in either the local database directory or system database directory I am an accidental DB2 LUW on linux administrator; primary background in MSSQL ...
1
vote
1answer
881 views

DB2: Column-Aliases with Line-Breaks

Using DB2 9.7, is it possible to produce a result set where the column-headers contain line-breaks? Something like: select field_name as "Line1<br>Line2" from table_name I want the column-...
2
votes
2answers
3k views

Alias for multiple instances on a SQL Server

I have 2 SQL named instances on a SQL Server, trying to add aliases for both so that application teams can use the alias names in their connection strings. There are 2 IP Addresses assigned for the ...
1
vote
1answer
6k views

Joining two tables with an alias

I have the following query which casues some problems: it complains that ORA-00904: "TLEA"."TLSN_TRD_ID": invalid identifier even though TLSN_TRD_ID is clearly in the TLEA table. It is as if the ...
1
vote
1answer
27 views

I have a query which does not return result with the desired alias names of columns in PostgreSQL

The following is my query which does not return result with the desired alias column names: with d as (select distinct emp.ahrims_id, rank.name as rank from hr_employee as emp, ana_rank as rank, ...
1
vote
2answers
149 views

Referencing a SELECT subquery value in the WHERE clause

Complete query SET @vSelect = ' SELECT T1.BranchShortName AS BranchShortName, ( SELECT TT1.EmployeeName FROM [dbo].[...
5
votes
3answers
2k views

How and when do I use an alias in my SQL statement

I have a query in which I do some calculation and then I give that column an alias. In the next column I would like to use the result of that calculation in an IF statement. MySQL did not recognise ...
3
votes
2answers
919 views

PostgreSQL requires “GROUP BY” when aliasing a table?

Here's something strange: there is a large table named "products" which is partitioned with inheritance such that there are two children: products_active and products_inactive, and the constraint is ...
3
votes
1answer
11k views

how can I use a CASE with an alias defined befor case?

how can I use an alias like pop in a further case like in this example (Oracle database). select (select sum(ccs_pop) from rap4) as pop, case when pop+x=a+b+c then pop+x end as sum1, case ...
0
votes
1answer
572 views

MySQL views performance for alias purposes

So I'm working on a Rails project and I have a legacy database, that is used by other applications, hence its structure cannot be modified. The problem is that table and column names do not follow the ...
0
votes
3answers
117 views

Major performance difference from a small change in query

I have a query written out to search a WordPress database for any post ids that match the keyword search to post content, title, author, or associated company. The company portion of the query is ...
1
vote
1answer
5k views

Aliases in calculations for MySQL

I am in the process to move a really small database from Access to MySQL and I have some queries to translate. I have this really simple query (in Access db): SELECT InvoiceNumber, Field1 + ...
5
votes
3answers
23k views

Use column alias in GROUP BY

I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly? SELECT KOS_VER_ID AS "...
5
votes
2answers
401 views

Why do so many queries have aliases?

I've spent a lot of time searching for, researching and implementing various complex MySQL queries over the last several years. Many of them use lots of aliases for tables, even when none are ...