SQL Syntax error

[rathy_moorthy2]

Hi,

Please help resolve the syntax error.

I want to do a self join to do a calculation, but am getting the following syntax error.

MDXRuntime: Internal deployment of object failed: Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near ".": line 2 col 55

Please help me correct it.

Followign is the code i am writing-

BEGIN

   var_out = select t1.sl_no from t1 as "_SYS_BIC"."spack/TEST"

      join t2 as "_SYS_BIC"."spack/TEST"

      on t1.sl_no = t2.sl_no;

Thanks in Advance!

Regards,

Rathy

[Former Member · Accepted solution]

Hi,

Think you have got the table (view) name and their aliases the other way round. Shouldn't it be like this instead?

  var_out = select t1.sl_no from "_SYS_BIC"."spack/TEST" as t1

      join "_SYS_BIC"."spack/TEST" as t2

      on t1.sl_no = t2.sl_no;

Thanks,

Anooj

[rathy_moorthy2 · Accepted solution]

Thanks that is correct,

now i have an issue in when i do a join and put a where clause

var_out = select t1.sl_no,t1.depth from "_SYS_BIC"."spack/TEST" as t1
      left outer join "_SYS_BIC"."spack/TEST" as t2
      on t1.sl_no = t2.sl_no
      where t1.depth >= t2.depth;

without the where condition it is bringing records, but when i use the where condition it gets 0 records, any idea what could be wrong?

Regards,

Rathy

[Former Member · Accepted solution]

Not sure, difficult to say without knowing your data & requirement(s) in more detail.

[patrickbachmann · Accepted solution]

I know the "AS" syntax should work but try without.  That's how I have gotten mine to work.  Try like this;

var_out = select t1.sl_no,t1.depth from "_SYS_BIC"."spack/TEST" t1

      left outer join "_SYS_BIC"."spack/TEST" t2

      on t1.sl_no = t2.sl_no

      where t1.depth >= t2.depth;

[henrique_pinto · Accepted solution]

Do your tables really have data that meets that WHERE clauses statement?

Try the complementary one, i.e. with "WHERE t1.depth < t2.depth".

If the search with no where clause brings data and the >= filter brings nothing, this one should bring everything. If it doesn't, then it's really a bug in the system or some other underlying issue, not a problem with your query per se.

Best regards,

Henrique.

[henrique_pinto · Accepted solution]

Oh I just noticed it's a join between two identical views...

Then it's really tricky...