All Apps and Add-ons

dbxquery returns error

karthi25
Path Finder

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.

0 Karma

cmerriman
Super Champion

can you try replacing the count(0) '90_DAY_RUN_COUNT' with count(0) RUN_COUNT_90_DAY

0 Karma

karthi25
Path Finder

yes tried it.. Not working still same error

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...