topic Re: Using Alias Names for Oracle Tables in QlikView

Using Alias Names for Oracle Tables

mbespartochnyy — Mon, 28 Sep 2015 14:30:04 GMT

Hello all,

I'm having trouble assigning alias names to Oracle tables in QlikView script. Here's a script I've written and below it is how I theoretically want it to be written:

CATEGORIZATION_HIERARCHY:

LOAD

ID,

"PARENT_ID",

"INSTANCE_ID",

"LEVEL_NUM",

"ID_NAME",

"PARENT_ID_NAME";

SQL SELECT *

FROM WHS."HIERARCHY"

WHERE INSTANCE_ID = 7000;

What I'm trying to get to is something like this:

CATEGORIZATION_HIERARCHY:

LOAD

H.ID,

"H.PARENT_ID",

"H.INSTANCE_ID",

"H.LEVEL_NUM",

"H.ID_NAME" as SUBCATEGORY,

"H2.ID_NAME" as CATEGORY,

"H.PARENT_ID_NAME";

SQL SELECT

H.ID,

"H.PARENT_ID",

"H.INSTANCE_ID",

"H.LEVEL_NUM",

"H.ID_NAME" as SUBCATEGORY,

"H2.ID_NAME" as CATEGORY,

"H.PARENT_ID_NAME";

FROM WHS."HIERARCHY" H, WHS."HIERARCHY" H2

WHERE INSTANCE_ID = 7000

AND H.PARENT_ID = H2.ID;

When I run revised script, I get the following error:

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "H2.ID": invalid identifier

I've hit the wall with this one. It doesn't seem to understand that H2 is an alias for WHS."HIERARCHY". Anyone has any idea why not?

Thanks,

Mikhail Bespartochnyy

Re: Using Alias Names for Oracle Tables

sunny_talwar — Wed, 30 Sep 2015 21:02:12 GMT

Hey Mikhail,

Not sure if this will work, but please give it a shot:

CATEGORIZATION_HIERARCHY:

LOAD

H.ID,

"H.PARENT_ID",

"H.INSTANCE_ID",

"H.LEVEL_NUM",

"H.ID_NAME" as SUBCATEGORY,

"H2.ID_NAME" as CATEGORY,

"H.PARENT_ID_NAME";

SQL SELECT

H.ID,

"H.PARENT_ID",

"H.INSTANCE_ID",

"H.LEVEL_NUM",

"H.ID_NAME" as SUBCATEGORY,

"H2.ID_NAME" as CATEGORY,

"H.PARENT_ID_NAME";

FROM WHS."HIERARCHY" H

JOIN WHS."HIERARCHY" H2 on H.PARENT_ID = H2.ID

WHERE H.INSTANCE_ID = 7000;

Best,

Sunny

Re: Using Alias Names for Oracle Tables

maxgro — Wed, 30 Sep 2015 22:17:01 GMT

CATEGORIZATION_HIERARCHY:

LOAD

SQL SELECT

H.ID,

"H.PARENT_ID",

"H.INSTANCE_ID",

"H.LEVEL_NUM",

"H.ID_NAME" as SUBCATEGORY,

"H2.ID_NAME" as CATEGORY,

"H.PARENT_ID_NAME";

FROM WHS."HIERARCHY" H, WHS."HIERARCHY" H2

WHERE H.INSTANCE_ID = 7000

AND H.PARENT_ID = H2.ID;

Re: Using Alias Names for Oracle Tables

mbespartochnyy — Mon, 05 Oct 2015 13:27:29 GMT

Thanks, Sunny! This was close, but it didn't work. Turns out that aliasing after SQL statement is done in database where the command is sent, in my case command was sent to Oracle DB and aliasing was done there. As aliasing was complete the SQL SELECT statement generated output table. When Oracle outputted table, it got rid of alias prefix in field names, so when I wrote H.ID or H.PARENT_ID after the LOAD statement, QlikView did not find these fields because in the Oracle DB they were named ID and PARENT_ID.

The solution is either to go with LOAD *; statement following by wither my or your SQL SELECT statement or to rename field names to exclude alias table name from field names.