use select parameter in subquery as column value

Question

I'm trying to set the columnName, databaseName, schemaName etc. dynamically based on a temporary table but cant seem to make it work. i've tried below?

Create Table #test(databaseName varchar(128), schemaName varchar(128), columnName varchar(128), datatypeName varchar(128));

INSERT INTO #test ('testDatabase', 'testSchema', 'testTable', 'priceColumn');

SELECT
Case
    WHEN DataType = 'int'
    THEN SELECT MAX(ColumnName) FROM Concat(databaseName, '.', schemaName, '.', tableName)
    ELSE 0
end
FROM #test;

DROP TABLE #test;

the expected result is that this below subquery take each line in the row in #test table and then query based on these values so it return the maxPrice from that table

SELECT MAX(ColumnName) FROM Concat(databaseName, '.', schemaName, '.', tableName)

Am I missing something? 1. You create the temp table. 2. You Select from the temp table. 3. Drop the temp table. But you never insert any rows into it. — SS_DBA
– SS_DBA, Commented Apr 27, 2017 at 17:51
i do that in another select statement which is not included due to the size and complexity of it. i could create test insert though. — Peter Pik
– Peter Pik, Commented Apr 27, 2017 at 17:52
It is hard to tell. It appears the goal is to find the a.b.c nomenclature name of the last field of a given data type. We need clarification. — Ross Bush
– Ross Bush, Commented Apr 27, 2017 at 17:52
Understood. Can you show some sample data and expected results? — SS_DBA
– SS_DBA, Commented Apr 27, 2017 at 17:52
...Just a side note, If you use @test instead of #test you don't have to drop the temp table as it lives in memory and is automatically de-allocated when the statement loses scope. — Ross Bush
– Ross Bush, Commented Apr 27, 2017 at 17:59

Ross Bush · Accepted Answer · 2017-04-27 18:52:45Z

1

Are you trying to aggregate on a calculated field while preserving the query flow?

You can try something like this.

SELECT 
    MAX(FullName)
FROM
(
    SELECT
         FullName = databaseName+ '.' + schemaName + '.' + columnName,
         *
    FROM
        @test
) AS A
WHERE
    DataTypeName='int'

Another example

SELECT 
    MAX(FullNameInt),
    MAX(FullNameOther)
FROM
(
    SELECT
        FullNameInt   = CASE WHEN DataTypeName='int' THEN  databaseName+ '.' + schemaName + '.' + columnName ELSE NULL END,
        FullNameOther = CASE WHEN DataTypeName<>'int' THEN databaseName+ '.' + schemaName + '.' + columnName ELSE NULL END,
         *
    FROM
        @test
) AS A

edited Apr 27, 2017 at 18:52

answered Apr 27, 2017 at 18:05

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Pik Over a year ago

the problem is here this does not enable to add more dataTypeNames like in a case statement

Ross Bush Over a year ago

I updated my answer, however, I no longer think I understand what it is you are attempting.

Collectives™ on Stack Overflow

use select parameter in subquery as column value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related