Defining a variable using a CASE Statement

marayebonner · May 23, 2024, 9:36pm

I am trying to define a variable at the beginning of my script which will be used several places within the script. If it is Monday I need the date of the Friday before and otherwise I need yesterday's date. The date needs to be in the format YYYYMMDD. I am doing the select &prevDateS just to see that the variable is correct. It is failing with the errors below. Can you help me to get this correct please. I am new to TOAD and trying to generate working scripts.

DEFINE prevDateS = TO_CHAR(SYSDATE-(CASE RTRIM(TO_CHAR(SYSDATE,'day')) when 'monday' THEN 3 ELSE 1 END ), 'yyyymmdd') ;
select &prevDateS from DUAL;

--- ERROR---
old: select &prevDateS from DUAL
new: select TO_CHAR(SYSDATE-(CASE from DUAL

select TO_CHAR(SYSDATE-(CASE from DUAL

Error at line 2
ORA-00907: missing right parenthesis

russell.cannon · May 28, 2024, 1:48pm

Here you go:

DEFINE prevDateS
column newday new_value prevDateS noprint
select TO_CHAR(SYSDATE-(CASE RTRIM(TO_CHAR(SYSDATE,'day')) when 'monday' THEN 3 ELSE 1 END ), 'yyyymmdd') newday from dual;
select &&prevDateS from DUAL;

Cheers,
Russ

marayebonner · May 28, 2024, 4:17pm

Thanks Russ

When I put in your solution, I get the screen below. I do not want to enter the dates at each step as this is an automated process

russell.cannon · May 28, 2024, 5:46pm

You need to use '&&' instead of just '&'.

russell.cannon · May 28, 2024, 5:56pm

Here is what I've got:

set verify off
set feed off
set heading off
def prevdates = 'placeholder'
column prevday new_value prevdates noprint
select TO_CHAR(SYSDATE-(CASE RTRIM(TO_CHAR(SYSDATE,'day')) when 'monday' THEN 3 ELSE 1 END ), 'yyyymmdd') prevday from dual;
select &&prevdates from DUAL;

This isolates the resulting value in the output. You must be sure to use double-ampersand and not just single.

Cheers,
Russ

marayebonner · May 28, 2024, 6:46pm

Hi Russ

Still no data. The entire query is below

set verify off

set heading off

set pagesize 0

set linesize 1200

set echo off

set termout off

set trimspool on

set blanklines on

set feedback off

set trimout off

set embedded off

SPOOL C:/users/bonnerm1/Charges_Prod_1.txt

DEFINE nextDate = TO_CHAR(sysDate,'yyyymmdd') ;

select &&nextDate from DUAL;

DEFINE prevDateS = prevdates = 'placeholder'

column prevday new_value prevdates noprint

select TO_CHAR(SYSDATE-(CASE RTRIM(TO_CHAR(SYSDATE,'day')) when 'monday' THEN 3 ELSE 1 END ), 'yyyymmdd') prevday from dual;

select &&prevdates from DUAL;

PROMPT TRAN_ID,SYSTEM_REF,BEARER,FEE_TYPE,CURRENCY,FEE,TERRITORY

SELECT TRAN_ID||';'||SYSTEM_REF||';'||BEARER||';'||FEE_TYPE||';'||CURRENCY||';'||FEE||';'||TERRITORY

FROM

(

SELECT ----------1------History-----

T.GPTRANID as TRAN_ID, R.REFERENCETEXT as SYSTEM_REF, C.BEARER,

C.PROMPT AS FEE_TYPE, C.CURRENCY, SUM(to_char(C.AMOUNT/100, 999999999999999.99)) as FEE,

'FCIBBBBB' as TERRITORY

FROM FCIBBBBB_HIST.GPTRAN T, FCIBBBBB_HIST.GPTRAN_CHARGE C, FCIBBBBB_HIST.GPTRAN_REFERENCE R, FCIBBBBB_HIST.GPTRAN_AMOUNT A, FCIBBBBB_HIST.gptran_parsedmessage pmsg

WHERE T.GPTRANID = C.GPTRANID AND T.GPTRANID = R.GPTRANID AND T.GPTRANID = A.GPTRANID AND T.gptranid = pmsg.gptranid

AND T.status IN ('Completed','Canceled')

AND R.referenceTag = 'SYSREF'

AND T.messageType in ('103','202' )

and A.Linkelmtags like '%TRANAMT%'

and pmsg.creater is null

and pmsg.tag = 'HISTMOVEDATE'

and pmsg.tagValue >= &&prevDateS

GROUP BY T.GPTRANID, R.REFERENCETEXT, C.BEARER, C.PROMPT, C.CURRENCY

)

UNION ALL

(

SELECT -------1---------SendTo Swift-----

T.GPTRANID as TRAN_ID, R.REFERENCETEXT as SYSTEM_REF, C.BEARER,

C.PROMPT AS FEE_TYPE, C.CURRENCY, SUM(to_char(C.AMOUNT/100, 999999999999999.99)) as FEE,

'FCIBBBBB' as TERRITORY

FROM FCIBBBBB.GPTRAN T, FCIBBBBB.GPTRAN_CHARGE C, FCIBBBBB.GPTRAN_REFERENCE R, FCIBBBBB.GPTRAN_AMOUNT A

WHERE T.GPTRANID = C.GPTRANID AND T.GPTRANID = R.GPTRANID AND T.GPTRANID = A.GPTRANID

AND T.status IN ('SentTo SWIFT')

AND A.amountid = (select am.amountid from FCIBBBBB.gptran_amount am, FCIBBBBB.gptran_parsedmessage pmsg , FCIBBBBB.gptran g

where g.gptranid = pmsg.gptranid and pmsg.tag ='TRANAMT' and am.amountid = pmsg.amountid

and g.gptranid = T.gptranid

and pmsg.creater is null

)

AND R.referenceTag = 'SYSREF'

AND T.messageType in ('103','202' )

AND A.valueDate >= &&prevDateS

AND A.valueDate < &&nextDate

GROUP BY T.GPTRANID, R.REFERENCETEXT, C.BEARER, C.PROMPT, C.CURRENCY

)

UNION ALL

(

SELECT ----------2------History-----

T.GPTRANID as TRAN_ID, R.REFERENCETEXT as SYSTEM_REF, C.BEARER,

C.PROMPT AS FEE_TYPE, C.CURRENCY, SUM(to_char(C.AMOUNT/100, 999999999999999.99)) as FEE,

'FCIBJMKN' as TERRITORY

FROM FCIBJMKN_HIST.GPTRAN T, FCIBJMKN_HIST.GPTRAN_CHARGE C, FCIBJMKN_HIST.GPTRAN_REFERENCE R, FCIBJMKN_HIST.GPTRAN_AMOUNT A, FCIBJMKN_HIST.gptran_parsedmessage pmsg

WHERE T.GPTRANID = C.GPTRANID AND T.GPTRANID = R.GPTRANID AND T.GPTRANID = A.GPTRANID AND T.gptranid = pmsg.gptranid

AND T.status IN ('Completed','Canceled')

AND R.referenceTag = 'SYSREF'

AND T.messageType in ('103','202' )

and A.Linkelmtags like '%TRANAMT%'

and pmsg.creater is null

and pmsg.tag = 'HISTMOVEDATE'

and pmsg.tagValue >= &&prevDateS

GROUP BY T.GPTRANID, R.REFERENCETEXT, C.BEARER, C.PROMPT, C.CURRENCY

)

UNION ALL

(

SELECT ----------2------SendTo Swift-----

T.GPTRANID as TRAN_ID, R.REFERENCETEXT as SYSTEM_REF, C.BEARER,

C.PROMPT AS FEE_TYPE, C.CURRENCY, SUM(to_char(C.AMOUNT/100, 999999999999999.99)) as FEE,

'FCIBJMKN' as TERRITORY

FROM FCIBJMKN.GPTRAN T, FCIBJMKN.GPTRAN_CHARGE C, FCIBJMKN.GPTRAN_REFERENCE R, FCIBJMKN.GPTRAN_AMOUNT A

WHERE T.GPTRANID = C.GPTRANID AND T.GPTRANID = R.GPTRANID AND T.GPTRANID = A.GPTRANID

AND T.status IN ('SentTo SWIFT')

AND A.amountid = (select am.amountid from FCIBJMKN.gptran_amount am, FCIBJMKN.gptran_parsedmessage pmsg , FCIBJMKN.gptran g

where g.gptranid = pmsg.gptranid and pmsg.tag ='TRANAMT' and am.amountid = pmsg.amountid

and g.gptranid = T.gptranid

and pmsg.creater is null

)

AND R.referenceTag = 'SYSREF'

AND T.messageType in ('103','202' )

AND A.valueDate >= &&prevDateS

AND A.valueDate < &&nextDate

GROUP BY T.GPTRANID, R.REFERENCETEXT, C.BEARER, C.PROMPT, C.CURRENCY

)

);

/

SPOOL OFF;

russell.cannon · May 28, 2024, 8:32pm

Well, unfortunately your code is unreadable because it is not in a code block, so I took the trouble to copy/paste it into an editor and formatted it up to give some vertical alignment.

I prefer ANSI join syntax. Instead of such things as "where tab1.col = tab2.col", I would use "from tab1 join tab2 on tab1.col = tab2.col", but there is no reason that what you have shouldn't work as long as the terms of the query match up with your data.

In any event, since I don't have your data or tables, I cannot try anything with the code. But, pulling out the bit from my suggestion does what it should, though you should take out the extra middle bit from "DEFINE prevDateS = prevdates = 'placeholder'". Take out the second "prevdates =" which I suppose was just a copy/paste glitch. It still works.

What I would suggest is for you to break it down and see where the trouble is. Start with a single query of the set of UNION ALL queries and build up from there.

Cheers,
Russ

marayebonner · May 29, 2024, 11:44pm

Thanks Russ. Much appreciated. I got it working

marayebonner · June 6, 2024, 5:39pm

Hi Russ
The query when automated is returning the data twice. However when run in TOAD or SQL developer I only get the data once. Is there something in the SET comands or something I am doing that would casue the results to duplicate? (e.g. it returns result lines 1,2,3 and then lines 1,2,3 again

russell.cannon · June 6, 2024, 5:55pm

If the code returns just one data set when run in an editor window or in SQL Developer, then the code is good. Perhaps there is something in the way it is being automated. Could you tell us something about how that is being done--maybe post some screenshots?

Cheers,
Russ

marayebonner · June 6, 2024, 6:20pm

Hi Russ
The scripts are run via autosys using the commands below. I have attached the ksh files

/apps/oracle/dba/scripts/autosys/fcib_main.ksh

/apps/oracle/dba/scripts/autosys/checksum_source.ksh

/apps/oracle/dba/scripts/autosys/gptran_charges.ksh

(Attachment ksh_files.zip is missing)

russell.cannon · June 6, 2024, 6:21pm

I laud you for using Korn Shell in your scripts. I think maybe they did not get posted.

Cheers,
Russ

marayebonner · June 6, 2024, 6:24pm

Russ

Attaching the ksh files as txt files

fcib_main.txt (577 Bytes)

checksum_source.txt (595 Bytes)

gptran_charges.txt (592 Bytes)

russell.cannon · June 6, 2024, 6:26pm

OK Great! How about posting the SQL scripts that are being called?

russell.cannon · June 6, 2024, 6:30pm

Also, those are KSH88 scripts. Do you not have KSH93+ installed?

marayebonner · June 6, 2024, 6:40pm

CHECKSUM_SOURCE.sql (34.4 KB)
FCIB_MAIN.sql (290.5 KB)
GPTRAN_CHARGES.sql (26.9 KB)

marayebonner · June 6, 2024, 6:42pm

I have asked the team responsible what ksh is being used. I just write the scipts and test them. I then send them to the team that does the automation

russell.cannon · June 6, 2024, 6:42pm

Yep, I thought so. You need to get rid of the forward slash right before "spool off". You MUST use BOTH a semicolon AND a forward slash with PL/SQL. You MUST NOT use BOTH for just SQL.

Cheers,
Russ

russell.cannon · June 6, 2024, 6:43pm

If you have KSH as your user shell, you can hit ESC and then CTRL-V to get the version. All recent versions of KSH use that key sequence.

Cheers,
Russ