Run Derived table query

General

Run Derived table query

Hi,

I am using Microstrategy for my reporting purpose.. I usually run the sql genertated in MSTR against Teradata Sql assitant.

When I run the whole query, it runs but when I run the part of it then it throws an error like Select failed (3706).

My query goes like this..

Select x1, x2, x3 from( (Select y1, y2, Y3 from t11) a11 join (select z1, z2, z3 from t21) a12 on join conditions) left outer join ..join condition

I am trying to run 

( (Select y1, y2, Y3 from t11) a11 join (select z1, z2, z3 from t21) a12 on join conditions) in teradata

Thanks,

Manju

3 REPLIES

Re: Run Derived table query

This is my exact query..

select    a13.product_id  product_id,

                a15.ProductName  ProductName,

                pa11.CRTNO  CRTNO,

                pa12.product_id  product_id0,

                pa12.version_id  version_id,

                a13.VersionName  VersionName,

                pa11.DedID  DedID,

                pa11.RevisionNo  RevisionNo,

                pa11.DedID0  DedID0,

                pa11.RevisionNo0  RevisionNo0,

                pa11.DedLineNo  DedLineNo,

                pa11.WJXBFS1  WJXBFS1,

                pa11.WJXBFS2  WJXBFS2,

                pa12.WJXBFS1  WJXBFS3

from      (select  a11.JournalNo  JournalNo,

                                a11.DocumentNo  SalesCode,

                                a11.CRTNO  CRTNO,

                                a12.DedID  DedID,

                                a12.RevisionNo  RevisionNo,

                                sum(a11.SalesInt)  WJXBFS1,

                                sum(a11.SalesLocal)  WJXBFS2

                from      DST_VMBI.Fact1              a11

                                left outer join    DST_VMBI.Dim11            a12

                                  on         (a11.CRTNO = a12.CRTNO and

                                a11.LineNo = a12.LineNo and

                                a11.SectionID = a12.SectionID)

                where   (a12.DedID = 809 and a12.RevisionNo = 17)

                group by              a11.JournalNo  JournalNo,

                                a11.DocumentNo  SalesCode,

                                a11.CRTNO  CRTNO,

                                a12.DedID  DedID,

                                a12.RevisionNo  RevisionNo        )              pa11

                join        (select  a12.DedID  DedID,

                                a12.RevisionNo  RevisionNo,

                                a12.product_id  product_id,

                                a12.version_id  version_id,

                                sum(a11.PriceTotal)  WJXBFS1

                from      DST_VMBI.Facta11          a11

                                left outer join    DST_VMBI.Fct112            a12

                                  on         (a11.RTID = a12.RTID)

                where   ((a12.DedID = 809 and a12.RevisionNo = 17)

                                group by                              a12.DedID  DedID,

                                a12.RevisionNo  RevisionNo,

                                a12.product_id  product_id,

                                a12.version_id  version_id,

                )              pa12

                  on         (pa11.DedID0 = pa12.DedID and

                pa11.DedLineNo = pa12.DedLineNo and

                pa11.RevisionNo0 = pa12.RevisionNo)

                left outer join    DST_VMBI.Dim22            a13

                  on         (pa12.product_id = a13.product_id and

                pa12.version_id = a13.version_id)

                left outer join    DST_VMBI.Dim23            a14

                  on         (pa11.LicenseeID = a14.LicenseeID)

                left outer join    DST_VMBI.Dim24            a15

                  on         (a13.product_id = a15.product_id)

I want run pa11 join pa12 in teradata

Thanks,

Manju

Re: Run Derived table query

    sel    col list 
        from
            (
                Select   x1, x2, x3
                    from (
                                (
                                    Select   y1, y2, Y3
                                        from t11) a11
                                       join
                                  (
                                       select   z1, z2, z3
                                        from t21) a12
                                on
                                join conditions
                               ) a13
                    left outer join
                    a14
                    on a13.xxx=a14.xxx
               )a15

Re: Run Derived table query

I think you mistyped the query as pa12 is not a valid query block, and thus your whole query is not a valid one!

This is what pa12 looks to me from your above query

 where   ((a12.DedID = 809 and a12.RevisionNo = 17)
group by a12.DedID DedID,
a12.RevisionNo RevisionNo,
a12.product_id product_id,
a12.version_id version_id,
) pa12

You can't run pa11 join pa12 in any sql.