asked on

SQL - Syntax Query v3

Hi EE,

For the following code

SELECT        T1.PROJLINKEDTOORDER, T1.ITEMID, T1.NAME, T1.PRODGROUPID, T1.PRODSTATUS, T1.PRODPRIO, T1.PRODLOCKED, T1.PRODID,
                         T1.PRODPOSTINGTYPE, T1.PRODTYPE, T1.SCHEDSTATUS, T1.DLVDATE, T1.STUPDATE, T1.FINISHEDDATE, T1.SCHEDSTART, T1.SCHEDEND,
                         T1.SCHEDDATE, T1.PROJSALESCURRENCYID, T1.DATAAREAID, 1010 AS RECID, T2.DATAAREAID AS DATAAREAID#2, T2.INVENTLOCATIONID,
                         T3.DATAAREAID AS DATAAREAID#3, T4.DATAAREAID AS DATAAREAID#4
FROM            dbo.PRODTABLE AS T1 INNER JOIN
                         dbo.INVENTDIM AS T2 LEFT OUTER JOIN
                         dbo.PRODROUTETRANS AS T3 ON T3.TRANSREFTYPE = 0 AND T1.PRODID = T3.TRANSREFID AND T1.DATAAREAID = T3.DATAAREAID LEFT OUTER JOIN
                         dbo.PRODROUTE AS T4 ON T1.PRODID = T4.PRODID AND T1.DATAAREAID = T4.DATAAREAID ON T1.INVENTDIMID = T2.INVENTDIMID AND
                         T1.DATAAREAID = T2.DATAAREAID
GROUP BY T1.PROJLINKEDTOORDER, T1.ITEMID, T1.NAME, T1.PRODGROUPID, T1.PRODSTATUS, T1.PRODPRIO, T1.PRODLOCKED, T1.PRODID,
                         T1.PRODPOSTINGTYPE, T1.PRODTYPE, T1.SCHEDSTATUS, T1.DLVDATE, T1.STUPDATE, T1.FINISHEDDATE, T1.SCHEDSTART, T1.SCHEDEND,
                         T1.SCHEDDATE, T1.PROJSALESCURRENCYID, T1.DATAAREAID, T2.DATAAREAID, T2.INVENTLOCATIONID, T3.DATAAREAID, T4.DATAAREAID


                   select prodid, DATAAREAID
                   from PRODTABLE

Open in new window

None of the aliases for T1 are recognized not too sure why.

Any assistance is welcome.

Thank you.

Peter Chan

Hi,
What is the error? T1 should be recognized in your query.

Zack

ASKER

Hi Peter,

The multi-part identifier could not be found.

Thank you.

Peter Chan

Try

SELECT        T1.PROJLINKEDTOORDER, T1.ITEMID, T1.NAME, T1.PRODGROUPID, T1.PRODSTATUS, T1.PRODPRIO, T1.PRODLOCKED, T1.PRODID,
                         T1.PRODPOSTINGTYPE, T1.PRODTYPE, T1.SCHEDSTATUS, T1.DLVDATE, T1.STUPDATE, T1.FINISHEDDATE, T1.SCHEDSTART, T1.SCHEDEND,
                         T1.SCHEDDATE, T1.PROJSALESCURRENCYID, T1.DATAAREAID, 1010 AS RECID, T2.DATAAREAID AS DATAAREAID#2, T2.INVENTLOCATIONID,
                         T3.DATAAREAID AS DATAAREAID#3, T4.DATAAREAID AS DATAAREAID#4
FROM            dbo.PRODTABLE T1 INNER JOIN
                         dbo.INVENTDIM T2 LEFT OUTER JOIN
                         dbo.PRODROUTETRANS T3 ON T3.TRANSREFTYPE = 0 AND T1.PRODID = T3.TRANSREFID AND T1.DATAAREAID = T3.DATAAREAID LEFT OUTER JOIN
                         dbo.PRODROUTE T4 ON T1.PRODID = T4.PRODID AND T1.DATAAREAID = T4.DATAAREAID ON T1.INVENTDIMID = T2.INVENTDIMID AND
                         T1.DATAAREAID = T2.DATAAREAID
GROUP BY T1.PROJLINKEDTOORDER, T1.ITEMID, T1.NAME, T1.PRODGROUPID, T1.PRODSTATUS, T1.PRODPRIO, T1.PRODLOCKED, T1.PRODID,
                         T1.PRODPOSTINGTYPE, T1.PRODTYPE, T1.SCHEDSTATUS, T1.DLVDATE, T1.STUPDATE, T1.FINISHEDDATE, T1.SCHEDSTART, T1.SCHEDEND,
                         T1.SCHEDDATE, T1.PROJSALESCURRENCYID, T1.DATAAREAID, T2.DATAAREAID, T2.INVENTLOCATIONID, T3.DATAAREAID, T4.DATAAREAID

Open in new window

PortletPaul

There are 2 queries, and in the last join of the first query you have used ON twice (which isn't valid syntax)

LEFT OUTER JOIN dbo.PRODROUTE AS T4 
ON T1.PRODID = T4.PRODID 
AND T1.DATAAREAID = T4.DATAAREAID 
ON T1.INVENTDIMID = T2.INVENTDIMID --<< assume this should be AND instead
AND T1.DATAAREAID = T2.DATAAREAID

Open in new window

Aside from that I could not find any alias that hasn't been defined in the from clause.

SELECT
     T1.PROJLINKEDTOORDER
    ,T1.ITEMID
    ,T1.NAME
    ,T1.PRODGROUPID
    ,T1.PRODSTATUS
    ,T1.PRODPRIO
    ,T1.PRODLOCKED
    ,T1.PRODID
    ,T1.PRODPOSTINGTYPE
    ,T1.PRODTYPE
    ,T1.SCHEDSTATUS
    ,T1.DLVDATE
    ,T1.STUPDATE
    ,T1.FINISHEDDATE
    ,T1.SCHEDSTART
    ,T1.SCHEDEND
    ,T1.SCHEDDATE
    ,T1.PROJSALESCURRENCYID
    ,T1.DATAAREAID
    ,1010 AS RECID
    ,T2.DATAAREAID AS DATAAREAID#2
    ,T2.INVENTLOCATIONID
    ,T3.DATAAREAID AS DATAAREAID#3
    ,T4.DATAAREAID AS DATAAREAID#4
FROM dbo.PRODTABLE AS T1
INNER JOIN dbo.INVENTDIM AS T2
LEFT OUTER JOIN dbo.PRODROUTETRANS AS T3 ON T3.TRANSREFTYPE = 0
    AND T1.PRODID = T3.TRANSREFID
    AND T1.DATAAREAID = T3.DATAAREAID
LEFT OUTER JOIN dbo.PRODROUTE AS T4 ON T1.PRODID = T4.PRODID
    AND T1.DATAAREAID = T4.DATAAREAID
    AND T1.INVENTDIMID = T2.INVENTDIMID
    AND T1.DATAAREAID = T2.DATAAREAID
GROUP BY
     T1.PROJLINKEDTOORDER
    ,T1.ITEMID
    ,T1.NAME
    ,T1.PRODGROUPID
    ,T1.PRODSTATUS
    ,T1.PRODPRIO
    ,T1.PRODLOCKED
    ,T1.PRODID
    ,T1.PRODPOSTINGTYPE
    ,T1.PRODTYPE
    ,T1.SCHEDSTATUS
    ,T1.DLVDATE
    ,T1.STUPDATE
    ,T1.FINISHEDDATE
    ,T1.SCHEDSTART
    ,T1.SCHEDEND
    ,T1.SCHEDDATE
    ,T1.PROJSALESCURRENCYID
    ,T1.DATAAREAID
    ,T2.DATAAREAID
    ,T2.INVENTLOCATIONID
    ,T3.DATAAREAID
    ,T4.DATAAREAID
;

Open in new window

and the second query:

SELECT prodid
    ,DATAAREAID
FROM PRODTABLE
;

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zack

ASKER

Hi PortletPaul,

Thank you once again sir for your assistance much appecriated.

PortletPaul

No problem Zack, cheers!