CODEWITHSUNDEEP
sqloracle-database
Lyndey
Lyndey

Reputation: 77

Oracle SQL - Missing Right parenthesis using LPAD and RPAD

This is the header row report for a longer query. 

SELECT
RPAD ('C', 1)
|| RPAD (' ', 24)
|| RPAD ('HED', 3)
|| RPAD (' ', 1)
|| RPAD ('BSC', 3)
|| RPAD (' ', 1)
|| RPAD ((LPAD (c.total_acct), 6, 0), 6)
|| RPAD (SUBSTR (TO_CHAR (SYSDATE, 'YYYYMMDD'), 1, 4), 4)
|| RPAD (SUBSTR (TO_CHAR (SYSDATE, 'YYYYMMDD'), 5, 2), 2)
|| RPAD (SUBSTR (TO_CHAR (SYSDATE, 'YYYYMMDD'), 7, 2), 2)
|| RPAD (' ', 7)
|| RPAD ((LPAD (c.total_amt), 14, '0'), 14)
|| RPAD (' ', 34)
AS HEADER_ROW

It works until I get to the lines with:

|| RPAD ((LPAD (c.total_acct), 6, 0), 6) 
|| RPAD ((LPAD (c.total_amt), 14, '0'), 14)

Upvotes: 0

Views: 135

Answers (2)

wolφi
wolφi

Reputation: 8361

Yes, @David's answer seems to do the trick.

Furthermore, the SYSDATE headers look a bit strange. Firstly, the date is converted to YYYYMMDD, then the first four chars (the year) are displayed. Then the date is again converted, and chars 5 and 6 (the month) are displayed, same for the days.

I believe you can replace 

|| RPAD (SUBSTR (TO_CHAR (SYSDATE, 'YYYYMMDD'), 1, 4), 4)
|| RPAD (SUBSTR (TO_CHAR (SYSDATE, 'YYYYMMDD'), 5, 2), 2)
|| RPAD (SUBSTR (TO_CHAR (SYSDATE, 'YYYYMMDD'), 7, 2), 2)

safely by

|| TO_CHAR (SYSDATE, 'YYYYMMDD')

Upvotes: 1

David Faber
David Faber

Reputation: 12485

If you look at one of these lines that are causing the issue:

RPAD ((LPAD (c.total_acct), 6, 0), 6)

you should see that the call to LPAD() does not have enough parameters. Maybe you mean this?

RPAD( LPAD( c.total_acct, 6, 0 ), 6 )

The same is the case with the other line causing the issue. The error message is a bit confusing - the problem is actually too many parentheses.

Hope this helps.

Upvotes: 4

Related Questions