How to use Temp table in Congos Report Studio.

[krunal]

Hi all

i am designing the report from SQL query which has temp table in that.
which is giving me an error while validating the query.

Following kind of query i am using in SQL

Select * into TempEMP from DimEmp

select * from TempEmp

Thanks
Krunal

[dougp]

This has always annoyed me.  Why does Cognos think it needs to parse a query string that I have chosen to run as Native or Pass-Thru?

This succeeds:

Code Select Expand

SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country
FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN

But this fails to parse:

Code Select Expand

SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country
into #a
FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN

select city, county
from #a

It's a perfectly valid query that will produce exactly the same results, but Cognos refuses to even send it to the database server.

[krunal]

Thanks for reply dougp,

is there any work round for this?

[MFGF]

This has always annoyed me.  Why does Cognos think it needs to parse a query string that I have chosen to run as Native or Pass-Thru?

This succeeds:

Code Select Expand

SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country
FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN

But this fails to parse:

Code Select Expand

SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country
into #a
FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN

select city, county
from #a

It's a perfectly valid query that will produce exactly the same results, but Cognos refuses to even send it to the database server.

Interesting! It's been a while since I tried using SQL directly in a report, and I'm surprised the Pass-Thru setting still rejects the second query. I always assumed it would just get handed off to the database without validation. Just out of interest, have you tried the query encased in double-braces? This can be an alternative to selecting the Pass-Thru setting. I'd be interested to know if this works. I don't have access to an instance that permits user-defined SQL at the moment to try it myself...

Just a random thought :)

MF.

[krunal]

Tried with Braces and got following error code ;)

The metadata could not be determined because statement 'select * from [#TempEMP]' uses a temp table.  Metadata discovery only supports temp tables when analyzing a single-statement batch.

Thanks

[MFGF]

Tried with Braces and got following error code ;)

The metadata could not be determined because statement 'select * from [#TempEMP]' uses a temp table.  Metadata discovery only supports temp tables when analyzing a single-statement batch.

Thanks

Did you use single braces (Native) or double braces (Pass-Thru)?

Thanks for trying it - much appreciated! :)

MF.

[dougp]

MFGF to the rescue once again!

The following SQL queries work in pass-thru, but not native:

Code Select Expand

SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country
into [#a]
FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN

select city, country
from [#a]

drop table [#a]

Code Select Expand

create table [#a] (
City varchar(128),
Country varchar(128)
)

insert [#a]

SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country

FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN

select city, country
from [#a]

drop table [#a]

Code Select Expand

with a (
City,
Country
)
as (
SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country

FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN
)

select city, country
from a

[MFGF]

MFGF to the rescue once again!

The following SQL queries work in pass-thru, but not native:

Code Select Expand

SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country
into [#a]
FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN

select city, country
from [#a]

drop table [#a]

Code Select Expand

create table [#a] (
City varchar(128),
Country varchar(128)
)

insert [#a]

SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country

FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN

select city, country
from [#a]

drop table [#a]

Code Select Expand

with a (
City,
Country
)
as (
SELECT
    BRANCH.CITY AS City,
    COUNTRY.COUNTRY_EN AS Country

FROM
    GOSALES.COUNTRY COUNTRY
        INNER JOIN GOSALES.BRANCH BRANCH
        ON COUNTRY.COUNTRY_CODE = BRANCH.COUNTRY_CODE
GROUP BY
    BRANCH.CITY,
    COUNTRY.COUNTRY_EN
)

select city, country
from a

Hurrah! It's always good to figure out ways to thwart roadblocks like this. Thanks for trying it and for reporting back - hope it helps others in future too :)

Cheers!

MF.