Why this query is not working and says Invalid Column name

Question

SELECT ROUND(K1,2)
FROM (SELECT '5.66666' UNION ALL
      SELECT '5.77777' UNION ALL
      SELECT '5.88888' UNION ALL
      SELECT '5.99999' UNION ALL
      SELECT '6.66666' UNION ALL
      SELECT '7.66666' UNION ALL
      SELECT '8.66666' UNION ALL
      SELECT '9.66666' UNION ALL
      SELECT '9.55555' UNION ALL 
      SELECT '6.88888' AS K1) K

Error says Invalid Column name 'K1'

Mureinik · Accepted Answer · 2017-04-17T10:00:33.003

2

The column alias in a union [all] structure comes from the first query, not the last one. Move your alias there, and you should be OK:

SELECT ROUND(K1,2)
FROM (SELECT 5.66666 AS K1 UNION ALL -- Here!
      SELECT 5.77777 UNION ALL
      SELECT 5.88888 UNION ALL
      SELECT 5.99999 UNION ALL
      SELECT 6.66666 UNION ALL
      SELECT 7.66666 UNION ALL
      SELECT 8.66666 UNION ALL
      SELECT 9.66666 UNION ALL
      SELECT 9.55555 UNION ALL 
      SELECT 6.88888) K

edited Apr 17 '17 at 10:00

answered Apr 17 '17 at 09:47

Mureinik

297,002
52
306
350

Now it is saying that Function `Round is invoked with wrong no of arguments.` – Ankit Bajpai Apr 17 '17 at 09:59
1

@AnkitBajpai The **number** of arguments seems right, but your query was using string literals instead of numerical literals. See my edited answer. – Mureinik Apr 17 '17 at 10:01
@Mureinik,Perfect answer. – Mansoor Apr 17 '17 at 10:03

score 0 · Answer 2 · answered Apr 17 '17 at 09:47

0

Give column name for the first record, then it will take the same name for rest for records.

SELECT ROUND(K1,2)
FROM (SELECT '5.66666' AS K1 UNION ALL
      SELECT '5.77777' UNION ALL
      SELECT '5.88888' UNION ALL
      SELECT '5.99999' UNION ALL
      SELECT '6.66666' UNION ALL
      SELECT '7.66666' UNION ALL
      SELECT '8.66666' UNION ALL
      SELECT '9.66666' UNION ALL
      SELECT '9.55555' UNION ALL 
      SELECT '6.88888') K

answered Apr 17 '17 at 09:47

Jibin Balachandran

3,381
1
24
38

Now it is saying that Function `Round is invoked with wrong no of arguments.` – Ankit Bajpai Apr 17 '17 at 09:59

score 0 · Answer 3 · answered Apr 17 '17 at 09:48

0

Give alias name for first row of first column or give alias name for all rows of first column :

   SELECT ROUND(K1,2)
   FROM 
   (
      SELECT '5.66666' AS K1 UNION ALL
      SELECT '5.77777' AS K1 UNION ALL
      SELECT '5.88888' AS K1 UNION ALL
      SELECT '5.99999' AS K1 UNION ALL
      SELECT '6.66666' AS K1 UNION ALL
      SELECT '7.66666' AS K1 UNION ALL
      SELECT '8.66666' AS K1 UNION ALL
      SELECT '9.66666' AS K1 UNION ALL
      SELECT '9.55555' AS K1 UNION ALL 
      SELECT '6.88888' AS K1
    ) K

answered Apr 17 '17 at 09:48

Mansoor

4,061
1
17
27

Now it is saying that Function `Round is invoked with wrong no of arguments.` – Ankit Bajpai Apr 17 '17 at 09:58
@AnkitBajpai,Check your query.Above query is working fine.Post your last tried query here. – Mansoor Apr 17 '17 at 10:00

Why this query is not working and says Invalid Column name

3 Answers3