SQL generated with column__1 alias

[Former Member]

hi..

i running a report in webi.

SELECT

PUB.unidonem.yil AS Column__1,

SUM(PUB.unibutcealim.adet),

Sum(PUB.unibutcealim.tutar)

....

GROUP BY

Column__1

but i dont want column__1...and i dont know why şs generated??

any suggestions?

thanks.

( i know the custum sql and its ok.. but not enough for me)

[Henry_Banks]

Just don't use the AS clause, as this assigns the column a name

regards,

H

[Former Member]

i dont use AS clause.. it was generated by sql automatically(i look it in the webi)

.

Edited by: werwerhudhe on Oct 5, 2011 1:55 PM

Edited by: werwerhudhe on Oct 5, 2011 1:56 PM

[Former Member]

and an important point

column__1 just only generated when adding a sum operator in select statement.

[Henry_Banks]

Ok, so this SQL isn't a "freehand SQL dataprovider"! my mistake.

- Please paste the entire SQL expression. I don't think you are telling 'the whole story'

- What DB type are you targetting?

- Is more than 1 database schema being called by your refresh?

- What Filter Conditions are you using in the WebI Query panel?

- Is this a Subquery, or a Unionized query?

- What problem does this AS clause cause you? (Are we investigating a defect here, or is this just an enquiry?)

regards,

Henry

[Former Member]

just simple query..it contains 1 dimension and 2 measure.

SELECT

PUB.unidonem.yil AS Column__1,

SUM(PUB.unibutcealim.adet),

Sum(PUB.unibutcealim.tutar)

FROM

PUB.unibutcealim INNER JOIN PUB.unidonem ON (PUB.unidonem.yil=PUB.unibutcealim.yil and PUB.unidonem.ay=PUB.unibutcealim.ay and PUB.unidonem.segment=PUB.unibutcealim.segment and PUB.unidonem.marka=PUB.unibutcealim.marka)

GROUP BY

Column__1

it is a progress database.

and if is no sum in select statement

SELECT

PUB.unidonem.yil,

PUB.unibutceger.adet,

PUB.unibutceger.tutar

FROM

PUB.unibutceger INNER JOIN PUB.unidonem ON (PUB.unibutceger.yil=PUB.unidonem.yil and PUB.unibutceger.ay=PUB.unidonem.ay and PUB.unibutceger.segment=PUB.unidonem.segment and PUB.unibutceger.marka=PUB.unidonem.marka)

so..there is no "column__1"

[Former Member]

not just a enquiry

in sql column__1

i got the error message,"column ambiquesly specified"

[Henry_Banks]

Do you mean "column ambiguously defined" ?

How are you inserting SUM ? (by changing the measure delegation projection in the universe?)

Thx,

H

[Former Member]

if "as column__1" generated by sql automatically i cant run web i report...(error:column abmiquesly...)

but there is no sum in sql select statement there is no "as column__1) and everything is ok.

[Former Member]

i defined an object

"SUM(PUB.unibutcealim.adet)"

[Henry_Banks]

Hi,

Ok, so now i understand you are creating a new Measure in the universe, and testing it in webi,

Here's some background info on your error: http://databases.aspfaq.com/database/what-does-ambiguous-column-name-mean.html

This typically happens when the Select statement has the same name for more than 1 column. So Alias Names need to be setup to ensure unique column names.

There are so many factors that could be at play here.. Your investigation is a a good condidate for a Support Message. Please log an OSS via SMP.

1. Given that this is a Progress DB (generic odbc), you may need some SQL Parameters, or .sbo / .prm file modifications in the DataAccess layer, or some ANSI indetifiers need to be set .. (Relevant guide is here: http://help.sap.com/businessobject/product_guides/boexir31SP3/en/xi31_sp3_data_acs_en.pdf )

2. You might also be interested in Note 1265192 - Web Intelligence Objects Causing Split Queries (missing context)

3. You might also want to check that there aren't Row Level restrictions ontop of your user groups, which may affect SQL generation.

Apologies I cannot do more at this stage.

Regards,

H