Renaming fields

[#1]

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

[#2]

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>

[#3]

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

[#4]

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,

[Or · Accepted solution]

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.

[#6]

or try to renaming field in oracle without as statement :

select "channel_id" "COST CHANNEL_ID"

[#7]

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

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

Thanks:)

[#8]

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.

[#9]

Hi Shoham,

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

It solved the issue.

Thanks a lot.

Khalid

[#10]

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:

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;