SQL Select within a select

[patrickbachmann]

Hi experts,

I'm trying to run the following simple test in SQL editor to see if I can successfully use a SELECT within a SELECT;

Select location,

Temperature_low,

(select top 1 LOCATION as NEWLOCATION from "MYSCHEMA"."LOCAL_CLIMATE_COPY"),

Temperature_high

from "MYSCHEMA"."LOCAL_CLIMATE"

It is working and returning the desired data records however the FIELD NAMES are not what I'm looking for.  For my second nested select instead of returning output field named NEWLOCATION it is returning this output;

Location,

Temperature_low,

(SELECTTOP1LOCATIONASNEWLOCATIONFROM"MYSCHEMA"."LOCAL_CLIMATE_COPY"),

Temperature_high

So when I attempt to paste this SQL into an SQLScript calculation view I'm having a problem matching the output to my output parameters.  I'm getting the message that attribute (SELECTTOP1LOCATIONASNEWLOCATIONFROM"MYSCHEMA"."LOCAL_CLIMATE_COPY") is not equal to output NEWLOCATION.

Has anybody done this or can you recommend a better way to write this so I can correctly name my new field?

Thanks,

-Patrick

[Former Member · Accepted solution]

Hi Patrick,

You could change your select statement to:

Select location,

Temperature_low,

(select top 1 LOCATION from "MYSCHEMA"."LOCAL_CLIMATE_COPY") as NEWLOCATION,

Temperature_high

from "MYSCHEMA"."LOCAL_CLIMATE"

Thanks,

Anooj

[patrickbachmann · Accepted solution]

Anooj you are my hero!  It worked.  So simple and staring me right in the face!  Thank you very much.

[Former Member · Accepted solution]

You're welcome Patrick

[Former Member · Accepted solution]

I agree, is a rock star, isn't he? \m/

[patrickbachmann · Accepted solution]

Indeed he is.  A good friend to have. 

[patrickbachmann]

Example;

[lbreddemann]

Hello Patrick,

just out of curiosity: what exactly is the requirement for your statement?

I assume you have a table with temperature measurements for different locations and want to run a query that provides the MIN/MAX temperature for each location. Is that correct?

If so, why not run something like

SELECT location, in(temperature) as low_temp, max(temperature) as high_temp
FROM "MYSCHEMA"."LOCAL_CLIMATE_COPY"
GROUP BY location;

?

Looks much simpler to me (given I understood your requirement correctly).

cheers,

Lars

[patrickbachmann]

Hi Lars, if my example does not make sense it's because it's just a test. I'm basically using a copy of the same table just to see if it worked.  I have another much bigger requirement where the secondary select is looking up values in a completely different table.  I broke that requirement down into much smaller chunks and had to get the syntax correct for a nested select so that's all this is;  a test.