Problem Note 69271: A SQL procedure query that uses Boolean logic as a condition in an ON clause might generate an error
 |  |  |  |
A SQL procedure query that uses Boolean logic as a condition in an ON clause might generate an error.
Here are some examples of Boolean logic:
- including a variable without it being compared to anything (the value is not equal to 0):
ON T1.ISACTIVE
- a CASE expression where the resulting value is not compared to anything (the value is not equal to 0). In this example, if ISACTIVE=1, then if B.ID=C.ID, then the row is kept and if ISACTIVE is not equal to 1, then if B.ID=D.ID, then the row is kept:
ON CASE WHEN T1.ISACTIVE = 1 THEN B.ID=C.ID ELSE B.ID=D.ID END
On Microsoft Windows systems, the error might look similar to the following:
ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
Please contact technical support and provide them with the following traceback information:
The SAS task name is [SQL]
ERROR: Read Access Violation SQL
Exception occurred at (3355B058)
Task Traceback
Address Frame (DBGHELP API Version 4.0 rev 5)
000000003355B058 000000003579D6A0 sassqlu:\sqltxt2.c\say_special 5238
000000003355558E 000000003579D990 sassqlu:\sqltxt2.c\say_slct 13037 +(0x1D)
0000000033552732 000000003579DC20 sassqlu:\sqltxt2.c\say_ssel 12633 +(0x8E)
000000003355FF40 000000003579E3F0 sassqlu:\sqltxt2.c\say_special 2767 +(0x26)
000000003356DE02 000000003579E4D0 sassqlu:\sqltxt2.c\say_expr 6803
0000000033540958 000000003579EA40 sassqlu:\sqltxt2.c\sqltxt2 938 +(0xD1)
000000002BD0380C 000000003579EA48 sassqlp:tkvercn1+0x427CC
000000002BD022A7 000000003579F3C0 sassqlp:tkvercn1+0x41267
000000002BCEE5A9 000000003579F6E0 sassqlp:tkvercn1+0x2D569
000000002BD21E26 000000003579F7D0 sassqlp:tkvercn1+0x60DE6
000000000F3F5503 000000003579F7D8 sassqx:tkvercn1+0x344C3
000000000F3F4C5E 000000003579FA70 sassqx:tkvercn1+0x33C1E
000000000097147C 000000003579FA78 sassql:tkvercn1+0x43C
00000000035BA366 000000003579FB28 sashost:\vvtentr.c\vvtentr 311
00000000035C0574 000000003579FF20 sashost:\WX6htthread.c\htthread 126 +(0x9)
00007FFAED2F7034 000000003579FF28 KERNEL32:BaseThreadInitThunk+0x14
00007FFAEEE62651 000000003579FF58 ntdll:RtlUserThreadStart+0x21
ERROR: Read Access Violation SQL
Exception occurred at (3355B058)
Task Traceback
Address Frame (DBGHELP API Version 4.0 rev 5)
000000003355B058 000000003579D6A0 sassqlu:\sqltxt2.c\say_special 5238
000000003355558E 000000003579D990 sassqlu:\sqltxt2.c\say_slct 13037 +(0x1D)
0000000033552732 000000003579DC20 sassqlu:\sqltxt2.c\say_ssel 12633 +(0x8E)
000000003355FF40 000000003579E3F0 sassqlu:\sqltxt2.c\say_special 2767 +(0x26)
000000003356DE02 000000003579E4D0 sassqlu:\sqltxt2.c\say_expr 6803
0000000033540958 000000003579EA40 sassqlu:\sqltxt2.c\sqltxt2 938 +(0xD1)
000000002BD0380C 000000003579EA48 sassqlp:tkvercn1+0x427CC
000000002BD022A7 000000003579F3C0 sassqlp:tkvercn1+0x41267
000000002BCEE5A9 000000003579F6E0 sassqlp:tkvercn1+0x2D569
000000002BD21E26 000000003579F7D0 sassqlp:tkvercn1+0x60DE6
000000000F3F5503 000000003579F7D8 sassqx:tkvercn1+0x344C3
000000000F3F4C5E 000000003579FA70 sassqx:tkvercn1+0x33C1E
000000000097147C 000000003579FA78 sassql:tkvercn1+0x43C
00000000035BA366 000000003579FB28 sashost:\vvtentr.c\vvtentr 311
00000000035C0574 000000003579FF20 sashost:\WX6htthread.c\htthread 126 +(0x9)
00007FFAED2F7034 000000003579FF28 KERNEL32:BaseThreadInitThunk+0x14
00007FFAEEE62651 000000003579FF58 ntdll:RtlUserThreadStart+0x21
On UNIX systems, the error might look similar to the following:
ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
The SAS task name is [SQL (2) ]
Segmentation Violation
Traceback of the Exception:
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sas(+0x17fc6e) [0x560408172c6e]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sas(+0x503be) [0x5604080433be]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x14b) [0x2b8666b952db]
/lib64/libpthread.so.0(+0xf630) [0x2b866549a630]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x2e703) [0x2b86c071d703]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x27a63) [0x2b86c0716a63]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x2440c) [0x2b86c071340c]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x2edf3) [0x2b86c071ddf3]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x3ad29) [0x2b86c0729d29]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(sqltxt2+0x484) [0x2b86c0700694]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlp(+0x53599) [0x2b86ae381599]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlp(sqlpsel+0xdb8) [0x2b86ae380158]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlp(sqlplan+0x219) [0x2b86ae36c309]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlp(sqlprep+0x3ee) [0x2b86ae39ecee]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqx(+0x44282) [0x2b86ad766282]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqx(sqlloop+0x33c) [0x2b86ad76569c]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassql(sassql+0x36d) [0x2b86c045e97d]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sas(vvtentr+0x17f) [0x560408042f7f]
/lib64/libpthread.so.0(+0x7ea5) [0x2b8665492ea5]
/lib64/libc.so.6(clone+0x6d) [0x2b8665ee28cd]
Please contact technical support and provide them with the following traceback information:
The SAS task name is [SQL (2) ]
Segmentation Violation
Traceback of the Exception:
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sas(+0x17fc6e) [0x560408172c6e]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sas(+0x503be) [0x5604080433be]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x14b) [0x2b8666b952db]
/lib64/libpthread.so.0(+0xf630) [0x2b866549a630]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x2e703) [0x2b86c071d703]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x27a63) [0x2b86c0716a63]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x2440c) [0x2b86c071340c]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x2edf3) [0x2b86c071ddf3]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(+0x3ad29) [0x2b86c0729d29]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlu(sqltxt2+0x484) [0x2b86c0700694]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlp(+0x53599) [0x2b86ae381599]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlp(sqlpsel+0xdb8) [0x2b86ae380158]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlp(sqlplan+0x219) [0x2b86ae36c309]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqlp(sqlprep+0x3ee) [0x2b86ae39ecee]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqx(+0x44282) [0x2b86ad766282]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassqx(sqlloop+0x33c) [0x2b86ad76569c]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sassql(sassql+0x36d) [0x2b86c045e97d]
/sasbin/SAS94M7/SASHomeM7/SASFoundation/9.4/sasexe/sas(vvtentr+0x17f) [0x560408042f7f]
/lib64/libpthread.so.0(+0x7ea5) [0x2b8665492ea5]
/lib64/libc.so.6(clone+0x6d) [0x2b8665ee28cd]
On mainframe systems, the error might look similar to the following:
ERROR: System abend 0C4 occurred in module SASSQLU function say_special at
offset 00C290.
ERROR: Traceback will be attempted...
NOTE: Traceback called
NOTE: Module Function Offset Line
NOTE: -------- -------- ------ ----
NOTE: SASSQLU SAY_SLCT 001092 0
NOTE: SASSQLU GEN_SLCT 0000E6 0
NOTE: SASSQLU SAY_SSEL 000C9A 0
NOTE: SASSQLU @@720644 00495E 0 say_special
NOTE: SASSQLU SAY_EXPR 001ABC 0
NOTE: SASSQLU SQLTXT2 0009B2 0
NOTE: SASSQLP SQLPSEL 002424 0
NOTE: SASSQLP SQLPLAN 00008C 0
NOTE: SASSQLP SQLPREP 0006D2 0
NOTE: SASSQX SQLLOOP 000A22 0
NOTE: SASSQL SASSQL 000312 0
NOTE: SASHOST UNKNOWN 002E2C 0
ERROR: Delete current task.
offset 00C290.
ERROR: Traceback will be attempted...
NOTE: Traceback called
NOTE: Module Function Offset Line
NOTE: -------- -------- ------ ----
NOTE: SASSQLU SAY_SLCT 001092 0
NOTE: SASSQLU GEN_SLCT 0000E6 0
NOTE: SASSQLU SAY_SSEL 000C9A 0
NOTE: SASSQLU @@720644 00495E 0 say_special
NOTE: SASSQLU SAY_EXPR 001ABC 0
NOTE: SASSQLU SQLTXT2 0009B2 0
NOTE: SASSQLP SQLPSEL 002424 0
NOTE: SASSQLP SQLPLAN 00008C 0
NOTE: SASSQLP SQLPREP 0006D2 0
NOTE: SASSQX SQLLOOP 000A22 0
NOTE: SASSQL SASSQL 000312 0
NOTE: SASHOST UNKNOWN 002E2C 0
ERROR: Delete current task.
The workaround is to include the comparison rather than using Boolean true/false logic.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 |
| z/OS 64-bit | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft® Windows® for x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows 8 Enterprise x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows 8 Pro x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows 8.1 Enterprise x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows 8.1 Pro 32-bit | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows 8.1 Pro x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows 10 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows Server 2008 R2 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows Server 2008 for x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows Server 2012 Datacenter | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows Server 2012 R2 Std | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Microsoft Windows Server 2012 Std | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Windows 7 Enterprise x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Windows 7 Professional x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| 64-bit Enabled AIX | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| 64-bit Enabled Solaris | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| HP-UX IPF | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Linux for x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Solaris for x64 | 9.4 | 9.4 | 9.4 TS1M0 | 9.4 TS1M8 | ||
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2025-04-18 11:30:19 |
| Date Created: | 2022-06-07 08:53:27 |