When trying to use the Execute SQL Query Advance command, I am unable to extract the data using Query. The connection was succeeded but couldn't extract any data.

Hi Abhi,

How are you configuring this advanced command in Studio? Are you using custom query or predefined query? Would you please post a screenshot of the successful connection test here, as well as the SQL query you are executing?

Thanks,

Darren

Hi Darren,

I am using Custom Query. Attached will find you the screenshot and SQL Query. Please advise.

SELECT AL2.BOOK_NUM,

 AL1.SAIL_SHIP_CD,

 AL1.SAIL_SAIL_DTE,

 AL1.SAIL_DURA_DAYS,

 AL2.FINAL_PYMNT_DTE,

 TRUNC(SYSDATE) - AL2.FINAL_PYMNT_DTE AS DaysOverDue,

 AL2.NET_FARE_AMT - ( AL2.UNALLOC_CASH_PMT_AMT + AL2.UNALLOC_UCC_PMT_AMT + AL2.UNALLOC_OTHER_PMT_AMT + AL2.ALLOC_CASH_PMT_AMT + AL2.ALLOC_UCC_PMT_AMT + AL2.ALLOC_OTHER_PMT_AMT + AL2.CABIN_CASH_PMT_AMT + AL2.CABIN_UCC_PMT_AMT + AL2.CABIN_OTHER_PMT_AMT ) AS BalanceDue,

 TRUNC(SYSDATE) AS AsofDate,

 AL3.Grp_Type_Code 

FROM DW.SAIL_DIM AL1,

 DW.GRP_CONTRACT_FACT AL2,

DW.GRP_TYPE_DIM AL3

WHERE (AL1.SYN_SAIL_KEY=AL2.SYN_SAIL_KEY)

AND (( AL2.FINAL_PYMNT_DTE <= (TRUNC(SYSDATE) - 1)

AND AL2.CANCEL_DTE IS NULL

AND TRUNC(AL2.NET_FARE_AMT - ( AL2.UNALLOC_CASH_PMT_AMT + AL2.UNALLOC_UCC_PMT_AMT + AL2.UNALLOC_OTHER_PMT_AMT + AL2.ALLOC_CASH_PMT_AMT + AL2.ALLOC_UCC_PMT_AMT + AL2.ALLOC_OTHER_PMT_AMT + AL2.CABIN_CASH_PMT_AMT + AL2.CABIN_UCC_PMT_AMT + AL2.CABIN_OTHER_PMT_AMT )) >0)

AND AL2.Syn_Grp_Type_Key = AL3.Syn_Grp_Type_Key

AND AL1.SAIL_SAIL_DTE >= TRUNC(SYSDATE) ) 

Best,

Abhishek

This Query was working earlier and was able to extract data. I am not sure why it is not working now. Please advise.

Hi Abhishek,

Does a simple select * from tablename SQL query work with this connection?

Hi Darren,

I tried just using Select * from all tables but still no data extracted.

Hi Abhishek, when you configured your connection, did you enable to Allow Saving password option before clicking ok? If this still doesn't work then we'll need to have a support ticket raised and have someone to get on a call with you to investigate.

Hi Darren, Yes if you look at the screenshot. The option for Allow saving password was enabled. Please let me know how we need to ticket raised to investigate. Thanks!

Hi Abhishek, I may need to have a support account set up for you if you have never raised a ticket before. What is the name of your company so I can look up your account?

Hi Darren, I will raise a ticket from my end. Thanks!