MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

ORDER BY clause with LTRIM and RTRIM

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
vkphani
Intermediate


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Tue Oct 25, 2005 1:29 am    Post subject: ORDER BY clause with LTRIM and RTRIM Reply with quote
Hi,

I have a query which has the follwing order by clause:

Quote:
order by ltrim(rtrim(col1)), order by ltrim(rtrim(col2))


this gives me the following error message :

Quote:
SQLCODE: -214, SQLSTATE: 42822, SQLERRMC: 001;ORDER BY;1


But when I do order by using only one column I am able to do it

Quote:
order by ltrim(rtrim(col1))


So how can I combine order by on two columns with ltrim and rtim.
Back to top
View user's profile Send private message Send e-mail
vkphani
Intermediate


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Tue Oct 25, 2005 2:00 am    Post subject: Reply with quote
This is the full SQL query

Quote:

select AM0_ACCOUNT_NUM, AM0_NAME_ADDRESS_1,A.DBAC_ACCT_NUMBER, A.DBAC_ACCT_TYPE, A.DBAC_CURR_CODE,A.DBAC_CURR_CODE from
TLCNL01.LACTM00 LEFT OUTER JOIN(select DBAC_ACCT_NUMBER, DBAC_ACCT_TYPE, DBAC_CURR_CODE,DBAC_CUST_NUMBER from TLCNL01.LCCDBAC where DBAC_CURR_CODE='EUR' and DBAC_ACCT_TYPE = 'CURAC')A on AM0_ACCOUNT_NUM=A.DBAC_CUST_NUMBER where 1=1 and AM0_WRKR_LOCATION like 'NL%' and
ltrim(rtrim(AM0_NAME_ADDRESS_1)) <'4654' select AM0_ACCOUNT_NUM, AM0_NAME_ADDRESS_1,A.DBAC_ACCT_NUMBER,A.DBAC_ACCT_TYPE, A.DBAC_CURR_CODE, A.DBAC_CURR_CODE from TLCNL01.LACTM00 LEFT OUTER JOIN (select DBAC_ACCT_NUMBER, DBAC_ACCT_TYPE, DBAC_CURR_CODE, DBAC_CUST_NUMBER from TLCNL01.LCCDBAC where DBAC_CURR_CODE='EUR' and DBAC_ACCT_TYPE = 'CURAC')A on AM0_ACCOUNT_NUM=A.DBAC_CUST_NUMBER where 1=1 and AM0_WRKR_LOCATION like 'NL%' and
ltrim(rtrim(AM0_NAME_ADDRESS_1)) <'4654' )
order by ltrim(rtrim(AM0_NAME_ADDRESS_1)) DESC,ltrim(rtrim(AM0_ACCOUNT_NUM)) DESC FETCH FIRST 10 ROWS ONLY


Last edited by vkphani on Tue Oct 25, 2005 3:22 am; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
PostPosted: Tue Oct 25, 2005 3:14 am    Post subject: Reply with quote
Quote:

order by ltrim(rtrim(col1)), order by ltrim(rtrim(col2))



That is not a valid syntax. The valid syntax is
Code:

order by ltrim(rtrim(col1))
        ,ltrim(rtrim(col2))


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vkphani
Intermediate


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Tue Oct 25, 2005 3:26 am    Post subject: Reply with quote
Kolusu,

I have rephrased the query but the same error is appearing again.
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose
PostPosted: Tue Oct 25, 2005 3:44 am    Post subject: Reply with quote
vkphani,

Please code your sql in an orderly fashion. You have a SELECT on the where clause which I think is an error.

Your sql should be like this
Code:

SELECT AM0_ACCOUNT_NUM
      ,AM0_NAME_ADDRESS_1
      ,A.DBAC_ACCT_NUMBER
      ,A.DBAC_ACCT_TYPE
      ,A.DBAC_CURR_CODE
      ,A.DBAC_CURR_CODE
  from TLCNL01.LACTM00
   LEFT OUTER JOIN (select DBAC_ACCT_NUMBER
                          ,DBAC_ACCT_TYPE
                          ,DBAC_CURR_CODE
                          ,DBAC_CUST_NUMBER
                      from TLCNL01.LCCDBAC
                     where DBAC_CURR_CODE='EUR'
                       and DBAC_ACCT_TYPE = 'CURAC')A
    on AM0_ACCOUNT_NUM=A.DBAC_CUST_NUMBER
 where 1=1
   and AM0_WRKR_LOCATION like 'NL%'
   and ltrim(rtrim(AM0_NAME_ADDRESS_1)) <'4654'
 order by 2 DESC
         ,1 DESC
  FETCH FIRST 10 ROWS ONLY


I used Order by 2 and 1 which means order by column2 and column1 from your select clause.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vkphani
Intermediate


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Tue Oct 25, 2005 4:10 am    Post subject: Reply with quote
Thanks a lot kolusu.
It worked fine.
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group