SQL Joining a Table to Itself is not working!

Hamza_DBM · 2018-11-21 22:26:56

Hi folks,

I am using a Reportserver Enterprise Edition, I tried to create a report using an SQL query that contains a self-join to compare rows within the same table, unfortunately, this is not working! It gives me the error "column ambiguously defined"!

The same query is working fine when using Oracle SQL Developer.

Please guys, do you have any idea to solve this ?

Is this not supported yet ?

Thank you in advance!

Last edited by Hamza_DBM (2018-11-21 22:29:07)

eduardo · 2018-11-22 07:45:38

Hi Hamza_DBM,

this error comes from your oracle. As stated here: https://kb.tableau.com/articles/issue/e … lang=en-en
you should give (unique) aliases to all your columns. When this works, delete the aliases one by one until you find the column causing the problem.

Regards,
Eduardo

eduardo · 2018-11-22 12:43:53

Hi Hamza_DBM,

I am seeing the problem, though. I raised ticket RS-3165 to analyze.

Regards,
Eduardo

Hamza_DBM · 2018-11-22 16:07:10

Hi Eduardo,

Thank you for the swift reply, I have tried by changing the aliases and it works for SQL query to create a dynamic list. However, The issue is that in our database we are using the same naming for some key columns in all tables,
for example; the primary key ID is named "ID" everywhere and as I am trying to use my reports which created by "crystal report" which doesn't generate or support aliases I can't make it work.
My question is what to do in this case ? In my thinking Reportserver in this case will just need parameters to be the same as my crystal reports and has nothing to do with my crystal report's SQL query!?

Please check my crystal report SQL query with the same ID name in two tables:

 SELECT "AKA"."ARCUSTO_ID", 
"AKA_BREAKS"."EFFECT_DATE",
 "AKA_BREAKS"."QPRICE", 
"AKA"."CURRENCY_ID",
 "ARINVT"."ID", 
"ARINVT"."DESCRIP2",
"ARINVT"."ITEMNO", 
"ARINVT"."CLASS", 
"STANDARD"."MFGNO", 
"STANDARD"."ID", 
"V_COMB_PROD_CRW1"."FG_LOTNO",
"ARINVT"."EPLANT_ID", 
"V_COMB_PROD_CRW1"."PROD_DATE"
 FROM   ((("IQMS"."ARINVT" "ARINVT" LEFT OUTER JOIN "IQMS"."V_COMB_PROD_CRW1" "V_COMB_PROD_CRW1" ON

Thank You,
Hamza.

Last edited by Hamza_DBM (2018-11-22 16:09:05)

jalbrecht · 2018-11-22 17:45:26

Dear Hamza,

pls. give us the complete query. On a first glance treat ID as ARINVT_ID like ARCUSTO_ID. With Crystal pls. be a little more precise. From what we know it is definitely possible to use aliases for attribute clauses in Queries that do provide data to Crystal reports .... . Even Crystal has its problems and strategies with non unique column names. Rename the columns in the query to achieve unique column names.

wbr jan

Hamza_DBM · 2018-11-26 19:29:51

Hi Jalbercht,

Here is my complete code:

SELECT
 MASTER_LABEL.SERIAL, 
 MASTER_LABEL.ITEMNO, 
 MASTER_LABEL_parent.ITEMNO,
 MASTER_LABEL_parent."SERIAL, 
 MASTER_LABEL.BADGENO,
 MASTER_LABEL_parent.BADGENO
 FROM   
 (IQMS.MASTER_LABEL MASTER_LABEL_parent LEFT OUTER JOIN IQMS.TRANSLOG_MASTER_LABEL TRANSLOG_MASTER_LABEL_parent ON MASTER_LABEL_parent.ID=TRANSLOG_MASTER_LABEL_parent.MASTER_LABEL_ID)
 LEFT OUTER JOIN (((IQMS".MASTER_LABEL MASTER_LABEL LEFT OUTER JOIN IQMS.TRANSLOG_MASTER_LABEL TRANSLOG_MASTER_LABEL ON MASTER_LABEL.ID=TRANSLOG_MASTER_LABEL.MASTER_LABEL_ID)
 LEFT OUTER JOIN IQMS.TRANSLOG TRANSLOG ON TRANSLOG_MASTER_LABEL.TRANSLOG_ID=TRANSLOG.ID) FULL OUTER JOIN IQMS.TRANSLOG TRANSLOG_parent ON TRANSLOG.SUB_BATCH"="TRANSLOG_parent.SUB_BATCH) 
 ON TRANSLOG_MASTER_LABEL_parent.TRANSLOG_ID=TRANSLOG_parent.ID
 WHERE  MASTER_LABEL.SERIAL='000315393'
 AND MASTER_LABEL_parent.ITEMNO<>MASTER_LABEL.ITEMNO

Thank you

Last edited by Hamza_DBM (2018-11-26 19:31:20)

eduardo · 2018-11-27 09:20:05

Hi Hamza_DBM,

you can give the duplicate columns an alias, e.g. "select MASTER_LABEL.ITEMNO AS MASTER_ITEMNO.

In order to use aliases in crystal reports you can use a command (SQL select statement) e.g.
Refer also to this:
https://archive.sap.com/discussions/thread/3673550

Regards,
Eduardo

Hamza_DBM · 2018-11-27 18:19:34

Hi Eduardo,

Thank your for your reply, actually, I know that I can create my crystal reports using SQL select statement. However ,the problem is that I have already many crystal reports
that already exist which they are a very complex and most of the parameters and filters are controlled by Crystal it self and not the SQL query.
This will make the task to recreate them using SQL query very heavy or maybe not possible at all.

My question is this (Be able to use crystal reports without a need to add aliases to the duplicate columns in its Sql query) something that you guys are working on to be supported in your next future releases ?

Thank you.
Hamza

eduardo · 2018-11-28 08:23:19

Hi Hamza_DBM,

currently, what we do is we replace the crystal datasource with the reportserver datasource. We don't modify/directly use the sql query in the crystal report, so if crystal handles this correctly it should work also in reportserver.
So to make sure: you are *not* using dynamic lists here, but crystal reports directly. You upload the crystal file and execute the crystal report in reportserver directly after setting a reportserver datasource, right?

Regards,
Eduardo

eduardo · 2018-11-28 08:28:12

Hi Hamza_DBM,

please also post your exact reportserver version (rsversion.properties).

Regards,
Eduardo

Hamza_DBM · 2019-01-02 20:39:41

Hi eduardo,

Verson: RS3.0.5-6005 (2018-10-30-10-32-54)

Thank you

ChrisGreenwalty · 2019-01-03 19:02:24

Looks like that you may need to change the sequence of SQL queries. I hope that after that, your problem will be fixed.

eduardo · 2019-01-04 09:15:23

Hi Hamza_DBM,

please answer this question:
"So to make sure: you are *not* using dynamic lists here, but crystal reports directly. You upload the crystal file and execute the crystal report in reportserver directly after setting a reportserver datasource, right?"

Regards,
Eduardo

ReportServer Forum

#1 2018-11-21 22:26:56

SQL Joining a Table to Itself is not working!

#2 2018-11-22 07:45:38

Re: SQL Joining a Table to Itself is not working!

#3 2018-11-22 12:43:53

Re: SQL Joining a Table to Itself is not working!

#4 2018-11-22 16:07:10

Re: SQL Joining a Table to Itself is not working!

#5 2018-11-22 17:45:26

Re: SQL Joining a Table to Itself is not working!

#6 2018-11-26 19:29:51

Re: SQL Joining a Table to Itself is not working!

#7 2018-11-27 09:20:05

Re: SQL Joining a Table to Itself is not working!

#8 2018-11-27 18:19:34

Re: SQL Joining a Table to Itself is not working!

#9 2018-11-28 08:23:19

Re: SQL Joining a Table to Itself is not working!

#10 2018-11-28 08:28:12

Re: SQL Joining a Table to Itself is not working!

#11 2019-01-02 20:39:41

Re: SQL Joining a Table to Itself is not working!

#12 2019-01-03 19:02:24

Re: SQL Joining a Table to Itself is not working!

#13 2019-01-04 09:15:23

Re: SQL Joining a Table to Itself is not working!

Board footer