How can I insert CREATEDDATETIME field into Temp table. I created one temp table for reporting purpose and now I am trying to insert records, i need to insert records from WORKFLOWTRACKINGTABLE.CREATEDDATETIME. Kindly help me the coding
*This post is locked for comments
Thank you!!!
You didn't get my point. You tried to use this code
insert_recordset LedgerJournalTmp (PostedDateTime,PostedDateTimeTZID) select createdDateTime from purchRfqTable;
but I never said anything like. Of course that such a thing doesn't even compile.
I explained what happened in database; trying to use the simplified the SQL code as X++ code of course can't succeed. PostedDateTimeTZID field in database, but isn't directly accessible from X++.
Thanks a lot I got the solution from the link you provided and it's working. I used the code as follows
public void processReport()
{
PURCHREQLINE PURCHREQLINE;
PURCHREQTABLE PURCHREQTABLE;
PURCHRFQCASETABLE PURCHRFQCASETABLE;
PURCHRFQTABLE PURCHRFQTABLE;
HCMWORKER HCMWORKER;
DIRPERSON DIRPERSON;
dataContract = this.parmDataContract() as QtnMonTNContract1;
prTransFromDate= dataContract.parmTransFromDate();
prTransToDate= dataContract.parmTransToDate();
ttsBegin;
delete_from QuotationMonthlyTurnOverTemp;
while select PurchReqId from PurchReqTable
join PURCHREQLINE
join VendReplyDate,CreatedDateTime from PURCHRFQTABLE WHERE PURCHRFQTABLE.STATUSLOW==4 && PURCHRFQTABLE.STATUSHIGH==4 && (PURCHRFQTABLE.VendReplyDate >= prTransFromDate) && (PURCHRFQTABLE.VendReplyDate <= prTransToDate)
join PURCHRFQCASETABLE
Where
PURCHREQLINE.PURCHRFQCASEID == PURCHRFQCASETABLE.RFQCASEID
&& PURCHREQLINE.PURCHREQTABLE == PURCHREQTABLE.recid
&& PURCHRFQCASETABLE.RFQCASEID == PURCHRFQTABLE.RFQCASEID
&& PURCHREQTABLE.PURCHREQBUYER== HCMWORKER.RECID
&& HCMWORKER.PERSON==DIRPERSON.RECID
{
QuotationMonthlyTurnOverTemp.PURCHREQID = PurchReqTable.PurchReqId;
QuotationMonthlyTurnOverTemp.VendReplyDate = PURCHRFQTABLE.VendReplyDate;
QuotationMonthlyTurnOverTemp.submitDate = PURCHRFQTABLE.CreatedDateTime;
QuotationMonthlyTurnOverTemp.nameBuyer = DIRPERSON.Name;
QuotationMonthlyTurnOverTemp.insert();
}
ttsCommit;
}
When I use the following query as you explained, I am getting error "The table LedgerJournalTmp does not contain the field PostedDateTimeTZID."
insert_recordset LedgerJournalTmp (PostedDateTime,PostedDateTimeTZID)
select createdDateTime from purchRfqTable;
Aha, you actually have a problem with insert_recordset. First of all, let me to remove everything unrelated from your code, so it's easier to demonstrate the point.
Share
Report
All responses (
Answers (
LedgerJournalTmp tmpTable;
PurchRfqTable purchRfqTable;
insert_recordset tmpTable (PostedDateTime)
select createdDateTime from purchRfqTable;
(I use LedgerJournalTmp instead of your custom table to allow everybody to copy and run the code without extra effort. It will increase your chance to get an answer if people don't have to invest too much time into it.)
This code compiles correctly, but it fails at runtime, complaining about the number of fields. There is also an infolog entry containing the actual SQL code, which is the key for understanding what's going on.
In my case, it's something like this:
INSERT INTO tempdb."DBO".t10841_927139FCA5F9443AA2BEBF3DF6488164 (POSTEDDATETIME,POSTEDDATETIMETZID,RECVERSION,PARTITION) SELECT T1.CREATEDDATETIME AS f1,1 AS RECVERSION,5637144576 AS PARTITION FROM PURCHRFQTABLE T1 WHERE ((PARTITION=5637144576) AND (DATAAREAID=N'usmf'))
If I simplify it, the problem should become obvious:
INSERT INTO MyTempTable (POSTEDDATETIME,POSTEDDATETIMETZID) SELECT CREATEDDATETIME FROM PURCHRFQTABLE
As you see, it tried to insert two fields, but it selects only one. It doesn't have any value for PostDateTimeTzId, which is a special field generated under the hood; it stores information about time zone of the corresponding date/time field.
When you manage to get here, you can check if somebody already ran into the some problem and you'll indeed find an older thread on this forum: Unable to insert into a utcDateTime column due to Time Zone Id.
As you see, your problem isn't specific to reports, temporary tables or CreatedDateTime fields. Hopefully now you have a better idea how to isolate such problems, so you can understand which part of the solution is to blame.
By the way, I hope you've never intended to run your query, because it misses conditions on joins.
I created field name as submitDate in my temp table and EDT as CreatedDateTime, still showing error, I am facing this error only when I use this field. There is no error showing in AOT code, error showing only in report.
insert_recordset QuotationMonthlyTurnOverTemp (PURCHREQID,VendReplyDate,submitDate,nameBuyer)
select PurchReqId from PurchReqTable
join PURCHREQLINE
join PURCHRFQCASETABLE
join VendReplyDate,createdDateTime from PURCHRFQTABLE WHERE PURCHRFQTABLE.STATUSHIGH==4
join HCMWORKER
join Name from DIRPERSON
Error Message...
An exception occurred in the query metadata execution. The exception is Cannot execute a data definition language command on QuotationMonthlyTurnOverTemp (QuotationMonthlyTurnOverTemp).
The SQL database has issued an error.
SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
...................................
Create a new field in your temporary table, using CreatedDateTime data type, or your own EDT extending CreatedDateTime. The field may be called WorkflowCreatedTime or something like that.
When filling the table, use it as any other field.
#1
André Arnaud de Cal...
291,661
Super User 2024 Season 2
#2
Martin Dráb
230,379
Most Valuable Professional
#3
nmaenpaa
101,156
