Place Results of Query in Text File
-
I need to take the results of the following query and place them in a .CSV file and then e-mail that file to someone else. Is this possible using DTS or do I need to do all this in a .NET application?:
--build the query
select 'L' as RECTYPE, identity(int, 1, 1)as JELINENO,
--Get the current JENO from the issuesreturns table
(select max(JENO) + 1 from issuesreturns) as JENO,
'AJE' as JECODE,
FY = case
when datepart(month, GetDate()) = '10' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '11' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '12' then datepart(year, GetDate()) + 1
else datepart(year, GetDate())
end,
-- The PD starts in April and increases by 1 each month
PD = case
when datepart(month, GetDate()) = '1' then '10'
when datepart(month, GetDate()) = '2' then '11'
when datepart(month, GetDate()) = '3' then '12'
when datepart(month, GetDate()) = '4' then '1'
when datepart(month, GetDate()) = '5' then '2'
when datepart(month, GetDate()) = '6' then '3'
when datepart(month, GetDate()) = '7' then '4'
when datepart(month, GetDate()) = '8' then '5'
when datepart(month, GetDate()) = '9' then '6'
when datepart(month, GetDate()) = '10' then '7'
when datepart(month, GetDate()) = '11' then '8'
when datepart(month, GetDate()) = '12' then '9'
else 'error'
end,
'1' as SUBPD, 'N' as REVFLAG, 'Journal Entry from MAXIMO' as HEADDESC,
--If the issuetype is a return the AMOUNT is a negative value, multiply by -1
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
else m.linecost
end,
--The ACCOUNT is the first six characters of the gldebitacct or glcreditacct field of the matusetrans table
--Limit the results to only those ACCOUNTS that begin with 700048 or 011195
ACCOUNT = case
when left(m.gldebitacct, 6) = '700048' then '40700-497-00'
when left(m.gldebitacct, 6) = '011195' then '60700-470-05'
when left(m.glcreditacct, 6) = '700048' then '40700-497-00'
when left(m.glcreditacct, 6) = '011195' then '60700-470-05'
else 'error'
end,
--The org number is 195.011.0001.002 plus the 16th, 17th and 18th characters of the gldebitacct field
ORG = case
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.402'
then '195.011.0001.002.401'
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.403'
then '195.011.0001.002.401'
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.441'
then '195.011.0001.002.440'
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.442'
then '195.011.0001.002.440'
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.451'
then '195.011.0001.002.450'
when '195.011.0001.002.' + substring (m.gldebitacct, 16, 3) = '195.011.0001.002.461'
then '195.011.0001.002.460'
else '195.011.0001.002.' + substring (m.gldebitacct, 16, 3)
end,
'MAXIMO - MATUSETRANSID = ' + convert(varchar(5), m.matusetransid)as TRANSDESC,
--The project is a 30 character field constructed from the gldebitacct field and two fields from the workorder table
substring (m.gldebitacct, 1, 6)+ '.' + w.wo2 + '.' + w.wo5 + '.' + substring (m.gldebitacct, 8, 7) + '.' +
w.wonum as PROJ,
--more default column values
' ' as REF1, ' ' as REF2, ' ' as RECYCLE, ' ' as ORGABBR,
' ' as PROJABBR, ' ' as PAGABBR, 'Y' as BILLABLEFLAG,
'From LOSC issues and returns to CostPoint to charge WOs' as NOTES
--place results in a temporary table
into #temp
--tables used in the query
from matusetrans m, workorder w, issuesreturns i
--conditions
where (m.it1 <> 'Y' or m.it1 is null)
and m.issuetype in ('ISSUE', 'RETURN')
and m.storeloc in ('B631OT', 'B642GB', 'B641GB', 'B681AS', 'B681BS', 'B681CA', 'B681SP')
and (w.wonum = m.refwo)
and (substring (m.gldebitacct, 1, 6)+ '.' + w.wo2 + '.' + w.wo5 + '.' +
substring (m.gldebitacct, 8, 7) + '.' + w.wonum IS NOT NULL)
and (substring(m.gldebitacct, 1, 6) <> '700051')
--perform the transaction
go
--update the it1 field of the matusetrans table to prevent further pulls of this information
UPDATE matusetrans
SET IT1 = 'Y'
from matusetrans, workorder
WHERE matusetrans.refwo = workorder.wonum
and (substring(matusetrans.gldebitacct, 1, 6) = '700048'
OR substring(matusetrans.gldebitacct, 1, 6) = '011195'
OR substring(matusetrans.glcreditacct, 1, 6) = '700048'
OR substring(matusetrans.glcreditacct, 1, 6) = '011195')
and matusetrans.issuetype in ('ISSUE', 'RETURN')
and matusetrans.storeloc in ('B631OT', 'B642GB', 'B641GB', 'B681AS', 'B681BS', 'B681CA', 'B681SP')
and substring (matusetrans.gldebitacct, 1, 6)+ '.' + workorder.wo2 + '.' + workorder.wo5 + '.' +
substring (matusetrans.gldebitacct, 8, 7) + '.' + workorder.wonum IS NOT NULL
and substring(matusetrans.gldebitacct, 1, 6) <> '700051'
--build the summary row
set identity_insert #temp on
insert into #temp(rectype,
jelineno,
jeno,
jecode,
fy,
pd,
subpd,
revflag,
headdesc,
amount,
account,
org,
transdesc,
proj,
ref1,
ref2,
recycle,
orgabbr,
projabbr,
pagabbr,
billableflag,
notes)
select max(rectype),
max(jelineno)+ 1,
max(jeno),
max(jecode),
max(fy),
max(pd),
max(subpd),
max(revflag),
max(headdesc),
sum(amount) * -1,
'20100-006-00',
'195.011',
'VENDOR OWNED STOCK USED',
'',
max(ref1),
max(ref2),
max(recycle),
max(orgabbr),
max(projabbr),
max(pagabbr),
max(billableflag),
max(notes)
from #temp
--display the results
select * from #temp
order by JELINENO
insert into issuesreturns
select * from #temp
--drop the temporary table
drop table #temp
-
"I need to take the results of the following query and place them in a .CSV file and then e-mail that file to someone else. Is this possible using DTS or do I need to do all this in a .NET application?:"
Should be easy in DTS -- just mirror what you would do to load a CSV file into a table. That is, set up a "transform data task" that goes FROM your SQL query and TO a destination text file.
Wish they were all that simple...
JR
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply