cancel
Showing results for 
Search instead for 
Did you mean: 

Derived Table How can I give the results of this SQL Line an Explicit Name.

Former Member
0 Kudos

I am trying to create a derived table and I get the error

"each calculated column must have an explicit name"

I added the as shiptype to the end of the line and that did not work. Any thoughts?

shiptype = case when araddr.invno is not null then 'ADDR' else (case when arcadr.custno is not null then 'CADR' else 'CUST' end) end as shiptype,

Thanks

Jeff

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Sure,

Here is the complete query.

This query works fine is sql query analyzer but when I put it in the designer I get the error.

SELECT 'C' as tableId,

'' as StartMast,

armast.invno as invno,

armast.invdte as invdte,

armast.ponum as ponum,

armast.ordate as ordate,

armast.shipvia as shipvia,

armast.fob as fob,

armast.ornum as ornum,

armast.disc as Idisc,

armast.taxrate as Itaxrate,

armast.tax as tax,

armast.invamt as invamt,

armast.disamt as disamt,

armast.paidamt as paidamt,

armast.balance as balance,

armast.dtepaid as dtepaid,

armast.pterms as pterms,

armast.arstat as arstat,

armast.artype as artype,

armast.notes as notes,

armast.salesmn as salesmn ,

'' as StartBill,

arcust.custno,

arcust.company as billComp,

arcust.contact as billaddr1,

arcust.address1 as billaddr2,

arcust.address2 as billaddr3,

arcust.city as billcity,

arcust.addrstate as billstate,

arcust.zip as billzip,

arcust.country as billcountry,

arcust.phone as billphone,

arcust.faxno as billfaxno,

rtrim(cast(arcust.email as varchar(2000))) as billemail,

'' as StartShip,

armast.tosw,

armast.cshipno,

shiptype = case when araddr.invno is not null then 'ADDR' else (case when arcadr.custno is not null then 'CADR' else 'CUST' end) end,

shipcomp = left(case when araddr.invno is not null then araddr.company else (case when arcadr.custno is not null then arcadr.company else arcust.company end) end + space(35),35),

shipaddr1 = left(case when araddr.invno is not null then araddr.address1 else (case when arcadr.custno is not null then arcadr.contact else arcust.contact end) end + space(30),30),

shipaddr2 = left(case when araddr.invno is not null then araddr.address2 else (case when arcadr.custno is not null then arcadr.address1 else arcust.address1 end) end + space(30),30),

shipaddr3 = left(case when araddr.invno is not null then araddr.address3 else (case when arcadr.custno is not null then arcadr.address2 else arcust.address2 end) end + space(30),30),

shipcity = left(case when araddr.invno is not null then araddr.city else (case when arcadr.custno is not null then arcadr.city else arcust.city end) end + space(20),20),

shipstate = left(case when araddr.invno is not null then araddr.addrstate else (case when arcadr.custno is not null then arcadr.addrstate else arcust.addrstate end) end + space(10),10),

shipzip = left(case when araddr.invno is not null then araddr.zip else (case when arcadr.custno is not null then arcadr.zip else arcust.zip end) end + space(10),10),

shipcntry = left(case when araddr.invno is not null then araddr.country else (case when arcadr.custno is not null then arcadr.country else arcust.country end) end + space(15),15),

'' as StartCmnt,

armast.commid as commid,

arcomm.comment as CommCmnt,

arscom.comment as ScomCmnt,

invCmnt = isnull(

case

when armast.commid = 'MISC' then arcomm.comment

when len(rtrim(armast.commid)) > 0 then arscom.comment

else ''

end

,''),

'' as StartTran,

artran.tranlineno,

artran.item,

artran.loctid,

artran.Descrip,

artran.intmemo,

artran.custmemo,

artran.qtyord,

artran.qtyshp,

artran.price,

artran.disc as ldisc,

artran.taxrate as ltaxrate,

artran.extprice,

artran.taxable,

artran.arstat as linestat,

artran.artype as linetype

FROM armast

LEFT JOIN artran ON artran.invno = armast.invno

Left JOIN arcust ON arcust.custno = armast.custno

Left JOIN arcomm ON arcomm.invno = armast.invno

Left JOIN arscom ON arscom.commid = armast.commid

LEFT JOIN araddr ON araddr.invno = armast.invno

LEFT JOIN arcadr ON arcadr.custno = armast.custno AND arcadr.cshipno = armast.cshipno

WHERE (armast.invdte >= getdate() - 180 OR len(rtrim(armast.arstat)) = 0)

AND armast.invno <> '_RECEIPT'

AND len(rtrim(armast.currhist)) = 0

AND armast.custno = 'TJS-01'

Union All

SELECT 'H' as tableId,

'' as StartMast,

arymst.invno as invno,

arymst.invdte as invdte,

arymst.ponum as ponum,

arymst.ordate as ordate,

arymst.shipvia as shipvia,

arymst.fob as fob,

arymst.ornum as ornum,

arymst.disc as Idisc,

arymst.taxrate as Itaxrate,

arymst.tax as tax,

arymst.invamt as invamt,

arymst.disamt as disamt,

arymst.paidamt as paidamt,

arymst.balance as balance,

arymst.dtepaid as dtepaid,

arymst.pterms as pterms,

arymst.arstat as arstat,

arymst.artype as artype,

arymst.notes as notes,

arymst.salesmn as salesmn ,

'' as StartBill,

arcust.custno,

arcust.company as billComp,

arcust.contact as billaddr1,

arcust.address1 as billaddr2,

arcust.address2 as billaddr3,

arcust.city as billcity,

arcust.addrstate as billstate,

arcust.zip as billzip,

arcust.country as billcountry,

arcust.phone as billphone,

arcust.faxno as billfaxno,

rtrim(cast(arcust.email as varchar(2000))) as billemail,

'' as StartShip,

arymst.tosw,

arymst.cshipno,

shiptype = case when aryadr.invno is not null then 'YADR' else (case when arcadr.custno is not null then 'CADR' else 'CUST' end) end,

shipcomp = left(case when aryadr.invno is not null then aryadr.company else (case when arcadr.custno is not null then arcadr.company else arcust.company end) end + space(35),35),

shipaddr1 = left(case when aryadr.invno is not null then aryadr.address1 else (case when arcadr.custno is not null then arcadr.contact else arcust.contact end) end + space(30),30),

shipaddr2 = left(case when aryadr.invno is not null then aryadr.address2 else (case when arcadr.custno is not null then arcadr.address1 else arcust.address1 end) end + space(30),30),

shipaddr3 = left(case when aryadr.invno is not null then aryadr.address3 else (case when arcadr.custno is not null then arcadr.address2 else arcust.address2 end) end + space(30),30),

shipcity = left(case when aryadr.invno is not null then aryadr.city else (case when arcadr.custno is not null then arcadr.city else arcust.city end) end + space(20),20),

shipstate = left(case when aryadr.invno is not null then aryadr.addrstate else (case when arcadr.custno is not null then arcadr.addrstate else arcust.addrstate end) end + space(10),10),

shipzip = left(case when aryadr.invno is not null then aryadr.zip else (case when arcadr.custno is not null then arcadr.zip else arcust.zip end) end + space(10),10),

shipcntry = left(case when aryadr.invno is not null then aryadr.country else (case when arcadr.custno is not null then arcadr.country else arcust.country end) end + space(15),15),

'' as StartCmnt,

arymst.commid as commid,

arcomm.comment as CommCmnt,

arscom.comment as ScomCmnt,

invCmnt = isnull(

case

when arymst.commid = 'MISC' then arcomm.comment

when len(rtrim(arymst.commid)) > 0 then arscom.comment

else ''

end

,''),

'' as StartTran,

arytrn.tranlineno,

arytrn.item,

arytrn.loctid,

arytrn.Descrip,

arytrn.intmemo,

arytrn.custmemo,

arytrn.qtyord,

arytrn.qtyshp,

arytrn.price,

arytrn.disc as ldisc,

arytrn.taxrate as ltaxrate,

arytrn.extprice,

arytrn.taxable,

arytrn.arstat as linestat,

arytrn.artype as linetype

FROM arymst

LEFT JOIN arytrn ON arytrn.invno = arymst.invno

Left JOIN arcust ON arcust.custno = arymst.custno

Left JOIN arcomm ON arcomm.invno = arymst.invno

Left JOIN arscom ON arscom.commid = arymst.commid

LEFT JOIN aryadr ON aryadr.invno = arymst.invno

LEFT JOIN arcadr ON arcadr.custno = arymst.custno AND arcadr.cshipno = arymst.cshipno

WHERE (arymst.invdte >= getdate() - 180 OR (len(rtrim(arymst.arstat)) = 0 AND arymst.invno in (select invno FROM armast where len(rtrim(arstat)) = 0)))

AND arymst.invno <> '_RECEIPT'

AND arymst.custno = 'TJS-01'

Former Member
0 Kudos

Hi Jeff,

Can you provide the full definition of your derived table?

Thanks

Didier