COGNOiSe.com - The IBM Cognos Community

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

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:
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:

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.

Thanks for reply dougp,

is there any work round for this?

Quote from: dougp on 10 Jul 2019 10:15:54 AM
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.

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

Quote from: krunal on 11 Jul 2019 09:48:26 AM
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.

MFGF to the rescue once again!

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

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]

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]

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

Quote from: dougp on 11 Jul 2019 10:53:12 AM
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.