November 3, 2005 at 7:08 am
I have the following snippet of code:
------------------------------------------------------------------------------------------
--if the following is null, then do not print
substring (m.gldebitacct, 1, 6)+ '.' + w.wo2 + '.' + w.wolo5 + '.' + substring (m.gldebitacct, 8, 7) + '.' +
w.wonum as PROJ,
------------------------------------------------------------------------------------------
As it states, if this is null then I need to ignore this record. What do I need to do to handle this situation?
Thanks,
Dave
November 3, 2005 at 10:34 am
Hi Dave,
I'll get the ball rolling... We need more information before we can help you out. Please give us more of the proc you are using. Also, can you expand a bit on the issue?
It sounds like you need to put a test into the WHERE clause. But without more information this is pure speculation.
Help us help you.
Wayne
November 3, 2005 at 11:17 am
Here is a much larger snippet of the code:
--build query default value columns
select 'L' as RECTYPE, identity(int, 1, 1)as JELINENO, '800002' as JENO, 'AJE' as JECODE,
'2006' as FY,
-- 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 AMOUNTt 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.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'
else '195.011.0001.002.' + substring (m.gldebitacct, 16, 3)
end,
'MAXIMO - MATUSETRANSID = ' + convert(varchar(4), m.matusetransid)as TRANSDESC,
---------------------------------------------------------------------------------------------------------------
--if the following is null, then do not print
--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.wolo5 + '.' + substring (m.gldebitacct, 8, 7) + '.' +
w.wonum as PROJ,
----------------------------------------------------------------------------------------------------------------
--more default column values
' ' as REF1, ' ' as REF2, ' ' as RECCYCLE, ' ' 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
--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)
November 4, 2005 at 8:14 am
If I understood you correctly, then you should put the mentioned condition into WHERE clause:
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.wolo5 + '.' + substring (m.gldebitacct, 8, 7) + '.' + w.wonum IS NOT NULL
Is that what you need? Not sure about "don't print"... but I suppose that you mean to say that if that part is null, such row should not be inserted into the temp table.
November 4, 2005 at 8:35 am
That seemed to do it.
Thanks for your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply