topic Renaming fields in QlikView

Renaming fields

Tue, 01 Jun 2010 08:38:14 GMT

Hi I have a table with the fields given below:

Costs:

SQL SELECT

"CHANNEL_ID",

"PROD_ID",

"PROMO_ID",

"TIME_ID",

"UNIT_COST",

"UNIT_PRICE"

FROM SH.COSTS;

But these fields are creating a syn Table, what is the solution can I use AS to rename them with SQL Select or Load Statemant, can you please rename them for me so i could learn how to do it. any help will be highly appreciated,

Thanks

Khalid

Renaming fields

Tue, 01 Jun 2010 08:56:14 GMT

Hi Khalid

You can do this in either the LOAD statement or the SQL SELECT statement, I prefer to do it in the SQL but that's really just my preference.

SQL SELECT
"CHANNEL_ID" AS NewNameForChannel_ID,
"PROD_ID" AS NewNameForProd_ID,
"PROMO_ID" AS NewNameForPromo_ID,
"TIME_ID" AS NewNameForTime_ID,
"UNIT_COST" AS NewNameForUnitCost,
"UNIT_PRICE" AS NewNameForUnitPrice
FROM SH.COSTS;</body>

Renaming fields

Tue, 01 Jun 2010 09:09:13 GMT

Hi Nigel,

When applying it as you said then it gives this following error during Reload:

SQL Error:[Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected

SQL Scriptline:
SQL State:S1000
Costs:

SQL SELECT "CHANNEL_ID" as [COST CHANNEL_ID],
"PROD_ID" as [COST PROD_ID],
"PROMO_ID" as [COST PROMO_ID],
"TIME_ID" as [COST TIME_ID],
"UNIT_COST" as [COST UNIT_COST],
"UNIT_PRICE" as [COST UNIT_PRICE]
FROM SH.COSTS

why is that so, and i noted one thing that when we use Load statement so AS gets blue color but in SQL Statement AS not gets blue colour, if I use Load what would i have to do, I made a ODBC connection of SH and am using Oracle 10g for fetching out SH Schema. where is the fault lies. please guide me,

Thanks

Khalid

Renaming fields

Tue, 01 Jun 2010 09:37:06 GMT

Hi Khalid

I don't know why you're getting that error, maybe this is something specific to Oracle (I use SQL Server), you could try adding the LOAD statement as below, but I'm not sure it will help:

Costs:
LOAD [COST CHANNEL_ID],
[COST PROD_ID],
[COST PROMO_ID],
[COST TIME_ID],
[COST UNIT_COST],
[COST UNIT_PRICE];
SQL SELECT "CHANNEL_ID" as [COST CHANNEL_ID],
 "PROD_ID" as [COST PROD_ID],
 "PROMO_ID" as [COST PROMO_ID],
 "TIME_ID" as [COST TIME_ID],
 "UNIT_COST" as [COST UNIT_COST],
 "UNIT_PRICE" as [COST UNIT_PRICE]
FROM SH.COSTS

Do you have a final semi-colon after the FROM statement?

Cheers,

Renaming fields

Or — Tue, 01 Jun 2010 09:40:39 GMT

If I'm not mistaken, Oracle required renames be in double-quotes rather than square-brackets:

SQL SELECT "CHANNEL_ID" as "COST CHANNEL_ID",
"PROD_ID" as "COST PROD_ID",
"PROMO_ID" as "COST PROMO_ID",
"TIME_ID" as "COST TIME_ID",
"UNIT_COST" as "COST UNIT_COST",
"UNIT_PRICE" as "COST UNIT_PRICE"
FROM SH.COSTS

Personally, I prefer to do my renaming in the load script, as this allows more flexibility - for example, Oracle will return all field names in UPPERCASE, whereas I often want mine in lowercase or Mixed Case.

Re. :Re: Renaming fields

Tue, 01 Jun 2010 10:06:48 GMT

or try to renaming field in oracle without as statement :

select "channel_id" "COST CHANNEL_ID"

Renaming fields

Tue, 01 Jun 2010 10:12:59 GMT

Yes there is a semi colon aftre from sh.cost;

well I am trying Load now, hopefully it resolves the problem..

Thanks:)

Renaming fields

Tue, 01 Jun 2010 10:16:05 GMT

Hi Khalid

I think the other answers you have are more pertinent to Oracle, you probably need to look at one or both of those.

Renaming fields

Tue, 01 Jun 2010 10:17:39 GMT

Hi Shoham,

Many thanks dear yes you were right Oracle requires renames be in Double-quotes.

It solved the issue.

Thanks a lot.

Khalid

Renaming fields

Tue, 01 Jun 2010 10:20:22 GMT

Hi Nigel,

this resolved my problem, this was replied by some one else, its following:

By Or Shoham in QlikView Desktop (Developer/Professional/Analyzer)

If I'm not mistaken, Oracle required renames be in double-quotes rather than square-brackets:

Renaming fields

Tue, 01 Jun 2010 10:30:17 GMT

Hi Shoham,

this perfectly works for me but if I want to convert it into Load so how would it look like, can you give any hint please.

Thanks

Renaming fields

Or — Tue, 01 Jun 2010 10:56:56 GMT

Load "CHANNEL_ID" as [COST CHANNEL_ID],
 "PROD_ID" as [COST PROD_ID],
 "PROMO_ID" as [COST PROMO_ID],
 "TIME_ID" as [COST TIME_ID],
 "UNIT_COST" as [COST UNIT_COST],
 "UNIT_PRICE" as [COST UNIT_PRICE];

SQL SELECT

"CHANNEL_ID",

"PROD_ID",

"PROMO_ID",

"TIME_ID",

"UNIT_COST",

"UNIT_PRICE"

FROM SH.COSTS;

Please note a couple of things:

1) Oracle defaults to returning all column names in UPPERCASE, so make sure you type them in UPPERCASE in your load script. You can then rename them to lowercase- for example, "PROD_ID" as [Cost_Prod_ID] will work in the Load script.

2) Square brackets are not necessary unless your names contain spaces or other problematic characters, but it's a good habit to use them anyway, so I've included them in my example. "PROD_ID" as COST PROD_ID, would have worked just fine if you'd prefer not to use brackets.

Renaming fields

Tue, 01 Jun 2010 11:14:21 GMT

Hi,

I have another issue arising now, which is I want to concatenate First Name and Last Name AS Name only, so I do it as like below:

THIS IS THE SCRIPT:

Customers:

SQL

SELECT

"COUNTRY_ID",

"CUST_FIRST_NAME"

& ' ' & "CUST_LAST_NAME" as

"CUST_NAME",

"CUST_CITY",

"CUST_CITY_ID",

"CUST_CREDIT_LIMIT",

"CUST_EFF_FROM",

"CUST_EFF_TO",

"CUST_EMAIL",

"CUST_FIRST_NAME",

"CUST_GENDER",

"CUST_ID",

"CUST_INCOME_LEVEL",

"CUST_LAST_NAME",

"CUST_MAIN_PHONE_NUMBER",

"CUST_MARITAL_STATUS",

"CUST_POSTAL_CODE",

"CUST_SRC_ID",

"CUST_STATE_PROVINCE",

"CUST_STATE_PROVINCE_ID",

"CUST_STREET_ADDRESS",

"CUST_TOTAL",

"CUST_TOTAL_ID",

"CUST_VALID",

"CUST_YEAR_OF_BIRTH"

FROM

SH.CUSTOMERS;

AND this is the error received when applied.

SQL Error:[Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected

SQL Scriptline:
SQL State:S1000
Customers:

SQL SELECT "COUNTRY_ID",
"CUST_FIRST_NAME" & ' ' & "CUST_LAST_NAME" as "CUST_NAME",
"CUST_CITY",
"CUST_CITY_ID",
"CUST_CREDIT_LIMIT",
"CUST_EFF_FROM",
"CUST_EFF_TO",
"CUST_EMAIL",
"CUST_FIRST_NAME",
"CUST_GENDER",
"CUST_ID",
"CUST_INCOME_LEVEL",
"CUST_LAST_NAME",
"CUST_MAIN_PHONE_NUMBER",
"CUST_MARITAL_STATUS",
"CUST_POSTAL_CODE",
"CUST_SRC_ID",
"CUST_STATE_PROVINCE",
"CUST_STATE_PROVINCE_ID",
"CUST_STREET_ADDRESS",
"CUST_TOTAL",
"CUST_TOTAL_ID",
"CUST_VALID",
"CUST_YEAR_OF_BIRTH"

FROM SH.CUSTOMERS;

Renaming fields

Tue, 01 Jun 2010 11:15:55 GMT

Hi,

I have another issue arising now, which is I want to concatenate First Name and Last Name AS Name only, so I do it as like below:

THIS IS THE SCRIPT:

Customers:

SQL

SELECT

"COUNTRY_ID",

"CUST_FIRST_NAME"

& ' ' & "CUST_LAST_NAME" as

"CUST_NAME",

"CUST_CITY",

"CUST_CITY_ID",

"CUST_CREDIT_LIMIT",

"CUST_EFF_FROM",

"CUST_EFF_TO",

"CUST_EMAIL",

"CUST_FIRST_NAME",

"CUST_GENDER",

"CUST_ID",

"CUST_INCOME_LEVEL",

"CUST_LAST_NAME",

"CUST_MAIN_PHONE_NUMBER",

"CUST_MARITAL_STATUS",

"CUST_POSTAL_CODE",

"CUST_SRC_ID",

"CUST_STATE_PROVINCE",

"CUST_STATE_PROVINCE_ID",

"CUST_STREET_ADDRESS",

"CUST_TOTAL",

"CUST_TOTAL_ID",

"CUST_VALID",

"CUST_YEAR_OF_BIRTH"

FROM

SH.CUSTOMERS;

AND this is the error received when applied.

SQL Error:[Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected

SQL Scriptline:
SQL State:S1000
Customers:

FROM SH.CUSTOMERS;

Renaming fields

Or — Tue, 01 Jun 2010 11:34:04 GMT

Oracle does not use & for concatenation, it uses || (two pipes).

You should write your queries in an Oracle-friendly environment - for example, PL/SQL Developer - and then copy them to QlikView once you're sure they work. The issues you're asking about have nothing to do with QlikView, they're strictly Oracle SQL. QlikView's editor is not a friendly place to write SQL, as there's no parsing, auto-complete, or any of the other options you'd enjoy in an SQL editor.

Renaming fields

Tue, 01 Jun 2010 11:58:09 GMT

Hi,

It really helped me a lot, thanks it resolved the problem.

Regards,

Khalid