I want to create a table loaded with data from the database. I have created the dbxquery as below
|dbxquery query="SELECT wl.d, MAX (start_dt) last_run, count(0) '90_DAY_RUN_COUNT', WF.WF_NAME, WF.WF_DESCRIPTION, PR.PROCESS_NAME FROM A_abc.workflow_log wl left outer join ( SELECT wf_id FROM A_abc.workflow_log WHERE start_dt > TRUNC (SYSDATE - 1, 'dd') GROUP BY wf_id ) d1 on d1.wf_id = WL.WF_ID join A_ABC.WORKFLOW wf on WF.WF_ID = WL.WF_ID join A_ABC.PROCESS pr on PR.PROCESS_ID = WF.PROCESS_ID WHERE wl.start_dt > TRUNC (SYSDATE - 90, 'dd') and d1.wf_id is null and WF.WF_ID not in ( 500 , 501 , 502 , 516 , 517 , 288 , 289 , 360 , 729 , 721 , 719 , 828 , 706 , 704 , 410 , 242 , 241 , 661 , 359 , 845 , 878 , 877 , 625 , 618 , 611 , 588 , 589 , 590 , 591 , 468 , 469 , 470 , 471 , 472 , 473 , 474 , 475 , 476 , 477 , 478 , 479 , 454 , 455 , 456 , 457 , 458 , 327 , 325 , 326 , 700 , 692 , 693 , 694 , 695 , 696 , 697 , 698 , 699 , 701 , 702 , 740 , 741 , 742 , 743 , 744 , 745 , 746 , 760 , 761 , 762 , 763 , 764 , 765 , 766 , 767 , 768 , 769 , 770 , 771 , 772 , 773 , 774 , 775 , 776 , 780 , 781 , 782 , 783 , 784 , 785 , 786 , 787 , 788 , 792 , 793 , 794 , 795 , 797 , 798 , 799 , 802 , 803 , 804 , 805 , 806 , 807 , 808 , 809 , 168 , 189 , 186 , 182 , 178 , 174 , 170 , 193 , 177 , 185 , 173 , 188 , 181 , 167 , 169 , 192 , 312 , 218 , 278 , 514 , 515 , 480 , 748 , 747 , 751 , 749 , 750 , 640 , 558 , 559 , 560 , 561 , 562 , 563 , 564 , 565 , 357 , 554 , 50 , 52 , 53 , 46 , 48 , 27 , 49 , 45 , 54 , 47 , 51 , 725 , 408 , 406 , 397 , 897 , 405 , 407 , 298 , 496 , 494 , 497 , 953 , 359 , 360 ) group by wl.wf_id , d1.wf_id , WF.WF_NAME, WF.WF_DESCRIPTION, PR.PROCESS_NAME order by PR.PROCESS_NAME, count(0) " connection="A_ABC_DAP1"
But it shows the error java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected .
Even this query is working perfectly when I run it in sql developer. Can anyone please help me to solve it. I have also tried converting the special characters and spaces in to HEX, but no use on it.
can you try replacing the count(0) '90_DAY_RUN_COUNT'
with count(0) RUN_COUNT_90_DAY
yes tried it.. Not working still same error