on 03-05-2009 3:54 PM
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
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jeff,
Can you provide the full definition of your derived table?
Thanks
Didier
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.