Thanks Jose! This works perfectly, you're a lifesaver.
I know you're not supposed to do this, but with UD tables I wasn't sure if it mattered. Basically, refresh a UD table with a query result on a schedule. We use this table for reporting purposes only.
However, when I run the query, it doesn't seem to let me insert rows. I don't know if there is something in the E9 tables preventing me from doing this. I get the "
SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=" message on the update, but if I comment out the update, the select statement works fine.
Otherwise, if I cannot get this to work, I will just end up exporting the results to .csv and script the DMT to pick it up and upload, since it seems like the BPM route requries too much set up to get a .p file to run on a schedule.
However, when I run the query, it doesn't seem to let me insert rows. I don't know if there is something in the E9 tables preventing me from doing this. I get the "
SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=" message on the update, but if I comment out the update, the select statement works fine.
Otherwise, if I cannot get this to work, I will just end up exporting the results to .csv and script the DMT to pick it up and upload, since it seems like the BPM route requries too much set up to get a .p file to run on a schedule.
I realize I forgot to ask a questions.
Essentially, while unadvised, is tehre something wrong with the insert query or am I better off going the DMT or BPM routeÉ
Are you running Straight SQL? Or this within Epicor? If its within Epicor how are you doing the insert? If its Straight SQL it should let you insert all day long. Though I frown upon this hehe
Jose C Gomez
Software Engineer
T: 904.469.1524 mobile
Quis custodiet ipsos custodes?
On Mon, Apr 13, 2015 at 4:48 PM, jockthemotie@... [vantage] <vantage@yahoogroups.com> wrote:Â<div> <p>I realize I forgot to ask a questions.</p><div><br></div><div>Essentially, while unadvised, is tehre something wrong with the insert query or am I better off going the DMT or BPM routeÉ</div><p></p> </div><span class="ygrps-yiv-1814241199"> <div style="color:#fff;min-height:0;"></div>
If you are using Straight SQL, please provide your source code I think you may be returning multiple results in a SubSelect within your insert and its causing issues.
Jose C Gomez
Software Engineer
T: 904.469.1524 mobile
Quis custodiet ipsos custodes?
On Mon, Apr 13, 2015 at 4:53 PM, Jose Gomez <jose@...> wrote:Are you running Straight SQL? Or this within Epicor? If its within Epicor how are you doing the insert? If its Straight SQL it should let you insert all day long. Though I frown upon this heheOn Mon, Apr 13, 2015 at 4:48 PM, jockthemotie@... [vantage] <vantage@yahoogroups.com> wrote:Â<div> <p>I realize I forgot to ask a questions.</p><div><br></div><div>Essentially, while unadvised, is tehre something wrong with the insert query or am I better off going the DMT or BPM routeÉ</div><p></p> </div><span> <div style="color:#fff;min-height:0;"></div>
When doing inserts to any table within Epicor, it's expecting 1 row at a time, so you need to do some sort of loop to only insert 1 row at a time.
For some reason none of the Epicor tables allow mass inserts.
Ha, I know that you are definitely on record as recommending against this! I also wouldn't do it if it weren't just the UD tables, whenever our President asks for a SQL script to change something I give the danger speech.
Code is below, super simple:
insert into UD04
(Company,
Key1,
Key2,
Number01,
Number02,
Number03,
ShortChar01,
ShortChar02)
select jobhead.company,
jobhead.JobNum,
a.FirstOp,
a.ProdHours,
a.DaysOut,
a.SetUp,
jo.OpCode,
op.OpDesc
--into #ud04
from jobhead
join
(select
jh.jobnum,
MIN(jo.oprseq) as FirstOp,
SUM(jo.estprodhours) as ProdHours,
SUM(jo.DaysOut)as DaysOut,
SUM(jo.estsethours)as SetUp
from jobhead jh
join joboper jo on jh.Company=jo.Company and jh.JobNum=jo.JobNum
where jh.JobFirm=1 and jh.JobClosed=0 and jo.OpComplete=0 and jh.Company='VT'
group by jh.JobNum)a on a.jobnum=jobhead.JobNum
join JobOper jo on jo.Company=jobhead.Company and jo.JobNum=a.JobNum and jo.OprSeq=a.FirstOp
join OpMaster op on op.Company=jo.Company and op.OpCode=jo.OpCode
where jobhead.JobFirm=1 and jobhead.JobClosed=0 and jo.OpComplete=0 and jobhead.Company='VT'
order by jobhead.JobNum
Is there a way to temporarily lift that restriction, of the single row insert?
Is because its a trigger that generates they sysrowid.
Open a cursor on your select and do the insert within. They should solve it
On Apr 13, 2015 6:50 PM, "jockthemotie@... [vantage]" <vantage@yahoogroups.com> wrote:Â<div> <p>Is there a way to temporarily lift that restriction, of the single row insert?</p> </div> <div style="color:#fff;min-height:0;"></div>
I'm not familiar with triggers/cursors, could you expand a bit?
ok One of the issues might be that you are getting duplicate data in your select. I ran your select (changing the company and got a few duplicate records (generates Key1, Key2 that are the same) But outside of that here is the code to use as a cursor. A cursor basically allows you to loop through a result set of a query and do something with it.
In this case I am looping through the results of your select and inserting them into UD04 one at a time. (Note I've limited the select to 50 entries for time sake so you'll have to remove the TOP(50) when you run it.Â
Hope this helps
On Mon, Apr 13, 2015 at 7:44 PM, jockthemotie@... [vantage] <vantage@yahoogroups.com> wrote:Â<div> <p>I'm not familiar with triggers/cursors, could you expand a bit?</p> </div><span> <div style="color:#fff;min-height:0;"></div>