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.
81
questions
2
votes
1
answer
106
views
(postgre)SQL how to disambiguate a name that could be a column name or a table alias
In PostgreSQL anyway you can pass a table (row) alias into a function and it will pass the entire record in.
But when you have a table alias x and also a column name x, when you specify f(x) it will ...
- 425
2
votes
2
answers
109
views
DROP ALIAS unable to drop existing alias?
We're unable to drop aliases.
So, I found an alias by running:
SELECT
TABLE_NAME
FROM
QSYS2.SYSTABLES
WHERE
BASE_TABLE_NAME IN ('OE0711P','OE0712P')
AND TABLE_TYPE = 'A'
AND ...
- 275
0
votes
1
answer
843
views
How to create alias in SQL Server 2022
As per https://learn.microsoft.com/en-us/sql/relational-databases/native-client/sql-server-native-client?view=sql-server-ver16:
The SQL Server Native Client (often abbreviated SNAC) has been removed ...
- 353
0
votes
1
answer
299
views
Why cannot I reference the second alias from a subquery containing a UNION?
Why cannot I use Qty2 as an alias in my query? I receive this error:
Error Code: 1054. Unknown column 'qty2' in 'field list'
SELECT mat, qty, qty2, SUM(qty), (ifnull(SUM(qty - qty2),0)) as TotalQty
...
0
votes
1
answer
57
views
How to UNPIVOT an aliased query result using PHP and MySql
I would be grateful for your assistance in helping me get solution for my project weather query problem. I have arrived at the query below and is working:
");
$first_row = true;
while ($row = ...
- 13
0
votes
0
answers
39
views
Change query to replace string by values in table of replacement
To store emails list I have two quite simple tables, domain and mailbox, so user@domain.com is stored as:
domain
id
domain_name
1
domain.com
mailbox
id
local_part
domain_id
1
user
1
(local_part ...
- 143
0
votes
2
answers
430
views
SQL Server 2019: Two Aliases point to same instance
We have a SQL Server 2008 server that I successfully migrated to a new computer with SQL Server 2019.
The SQL Server 2008 server name is 123.123.123.123.
It has two instances named: 123.123.123.123 ...
- 15
0
votes
0
answers
30
views
SQL Server --- How to use a database alias for quick coding? [duplicate]
Hopefully I'm using this term correctly.
Okay as most people know if you have long table names like mylongschema.longtablename ...
You can alias the table for quick reference such as mylongschema....
- 1,603
0
votes
2
answers
438
views
mysql - adding alias columns as a new column
I have the following mysql query...
select x.timest,
max(case when x.devloc='outside' then x.value end) as outside,
max(case when x.devloc='hvac_main_return' then x.value end) as ...
- 103
1
vote
2
answers
8k
views
Unknown column in 'where clause' when using an alias for a subquery in MySQL
This simple query:
select exam.id as exam_id,
(select count(*) from dataset where dataset.id = exam.id) as n_dataset
from exam
where n_dataset = 0
returns the following error:
ERROR 1054 (42S22) at ...
- 113
1
vote
1
answer
677
views
Reuse alias that shares an existing column name
In MySQL I know I can reuse an alias in a query like this:
SELECT
value_a + value_b as value_sum,
(SELECT value_sum) + 5 as padded_sum
Is there any way to make this work if padded_sum is also the ...
- 644
0
votes
1
answer
444
views
Using alias in Group by
I am running into a problem where I have to use an alias (Ordernum) in a group by. I have found that this could be done somehow with subquery but I don't have any success with that and also those are ...
- 1
0
votes
1
answer
189
views
Give alias / column definition list to ROWS FROM()
This obviously doesn't work:
SELECT regexp_matches[1], regexp_matches[1]
FROM ROWS FROM (
regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
);
Error:...
- 257
3
votes
3
answers
3k
views
MySQL reuse select aliases
I currently have a query where I'm doing two subqueries to get X, Y data:
SELECT
t.series AS week,
( ... ) X,
( ..., AND ... ) Y,
ROUND(( ... ) * 100) / ( ..., AND ... ), 2) Z
FROM series_tmp ...
- 83
0
votes
1
answer
2k
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 ...
- 121
0
votes
1
answer
893
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 ...
- 101
4
votes
2
answers
15k
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 ...
- 233
0
votes
2
answers
1k
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
---------------------------...
- 101
0
votes
0
answers
37
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,
...
2
votes
1
answer
7k
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 ...
- 21
0
votes
1
answer
48
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, (
...
- 101
0
votes
2
answers
797
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 ...
- 9
0
votes
0
answers
197
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]"...
- 3,500
2
votes
1
answer
336
views
MySQL: INSERT INTO SELECT issues
Consider the following tables:
=> table1
| aid |
| --- |
| a1 |
| a1 |
| a3 |
| a2 |
| ... |
=> table2
| aid | count | va | vb |
| --- | ----- | ----- | ----- |
| a1 | c1 | va1 ...
- 193
1
vote
0
answers
384
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 ...
- 111
2
votes
0
answers
116
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 ...
- 21
0
votes
1
answer
26
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
1
answer
535
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 '.'
...
- 121
39
votes
3
answers
47k
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:
...
- 527
0
votes
1
answer
144
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 ...
- 6,158
3
votes
3
answers
16k
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
3
answers
2k
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 ...
- 89
1
vote
1
answer
164
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 ...
- 711
5
votes
1
answer
2k
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);...
- 150
5
votes
1
answer
9k
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: ...
- 153
2
votes
1
answer
12k
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 ...
- 155
1
vote
4
answers
3k
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?
- 13
1
vote
1
answer
3k
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= (...
- 45
7
votes
3
answers
3k
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 ...
- 179
0
votes
1
answer
299
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
1
answer
427
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 "...
- 578
4
votes
1
answer
1k
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 ...
- 2,278
-1
votes
2
answers
15k
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
1
answer
1k
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
2
answers
4k
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
1
answer
2k
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 ...
- 60.4k
0
votes
0
answers
613
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 ...
- 483
6
votes
1
answer
5k
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, ...
- 578
2
votes
3
answers
5k
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 ...
- 31
1
vote
1
answer
1k
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-...
- 133