SQL HELP SELECT A.TICKET_NB, CHAR(\' |\') ,A.JOB_N, CHAR(\' |\') ,A.JOB_ID, CHAR

Question

SQL HELP

SELECT A.TICKET_NB, CHAR(' |')                                      

   ,A.JOB_N, CHAR(' |')                                               

   ,A.JOB_ID, CHAR(' |')                                              

   ,A.CLASS_C, CHAR('    |')                                          

   ,A.CREATE_DTM, CHAR(' |')                                          

   ,A.TICKET_STAT_C                                                   

FROM &:NODE..PMF_TICKET_PTC A JOIN &:NODE..PMF_CLASS_PCL B ON       

    B.CLASS_C = A.CLASS_C                                             

WHERE A.TICKET_STAT_C IN ('A','S','Q')                              

   AND A.CREATE_DTM <= (CURRENT TIMESTAMP - 30 MINUTES)               

   AND B.AUTO_SUB_F IN ('Y') AND B.STATUS_C IN ('A')                  

   AND A.JOB_N IN ('PRDLOAD', 'ETSWGERT')                             

WITH UR                                                             

FOR FETCH ONLY                                                       

          ;   

__________

Thats my SQL so far

but this line   AND A.JOB_N IN ('PRDLOAD', 'ETSWGERT')   

What im trying to do is pull all lines from PMF_TICKET_PTC joined to PMF_CLASS_PCL via a like field CLASS where the tickets are less than 30 min old

but i want to exclude jobs PRDLOAD and ETSWGERT, I just dont know how to do it. Any suggestions or help would be greatly appreciated ! thanks!

Explanation / Answer

make the following changes in the query

1. Replace IN with NOT IN

to exclude jobs PRDLOAD and ETSWGERT we have to use

A.JOB_N NOT IN ('PRDLOAD', 'ETSWGERT')

2.Syntax for current timestamp is worng you are missing underscore

it should be CURRENT_TIMESTAMP

also for 30 min older records use dateadd function

DATEADD(minute,-30,CURRENT_TIMESTAMP)

so the query should be

SELECT A.TICKET_NB, CHAR(' |')                                      

   ,A.JOB_N, CHAR(' |')                                               

   ,A.JOB_ID, CHAR(' |')                                              

   ,A.CLASS_C, CHAR('    |')                                          

   ,A.CREATE_DTM, CHAR(' |')                                          

   ,A.TICKET_STAT_C                                                   

FROM &:NODE..PMF_TICKET_PTC A JOIN &:NODE..PMF_CLASS_PCL B ON       

    B.CLASS_C = A.CLASS_C                                             

WHERE A.TICKET_STAT_C IN ('A','S','Q')                              

   AND A.CREATE_DTM <=
DATEADD(minute,-30,CURRENT_TIMESTAMP)               

   AND B.AUTO_SUB_F IN ('Y') AND B.STATUS_C IN ('A')                  

   AND A.JOB_N NOT IN ('PRDLOAD', 'ETSWGERT')                             

WITH UR                                                             

FOR FETCH ONLY                                                       

          ;   

_______

I dont understand why you used char() function actually what it does is

it takes a number code and returns the character for that number code

its syntax is char(number_code)

for example char(116) is t

if your intention is to seperate columns by | symbol you just use '|' thats it

or the ascii code for |(pipe symbol) is 124

so to print ' | ' use char(124)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.