SQL Syntax Error 1

[lynchg]

I am going bonkers trying to figure out where the error is in this SQL. I am running AQT against a DB2 database on a mainframe. I have successfully run many other SQL statements during the current session. I think I just need another pair of eyes on this. Can someone help me?

Here is the SQL:

CREATE VIEW qHeaderInfo AS SELECT qPMR_Elig.CORP_MBR_ID, MemberSubID || '-' || Right(MemberDepNumber,2) AS MemberNum, qApptFeed.Sex, qApptFeed.DOB, MemLastName || CASE WHEN MemFirstName Is Null THEN '' ELSE ', ' || MemFirstName || CASE WHEN MemMidInit Is Null THEN '' ELSE ' ' || MemMidInit || '.' END END AS DirName, Year(ApptDate - DOB) AS Age, Sex || '[' || Year(ApptDate - DOB) || '] ' || DOB AS AgeData, qApptFeed.ApptDate, qApptFeed.ApptTime, PROV_LAST_NAME || CASE WHEN PROV_FRST_NAME IS Null THEN '' ELSE ', ' END || PROV_FRST_NAME AS PCP, qApptFeed.ApptProvider, qApptFeed.OfficeFaxNum, qPMR_Elig.AZ_Flag, qPMR_Elig.CAD_Flag, qPMR_Elig.DM_Flag, qPMR_Elig.GEN_Flag
FROM (qPMR_Elig INNER JOIN qApptFeed ON qPMR_Elig.CORP_MBR_ID = qApptFeed.CORP_MBR_ID) LEFT JOIN DBA1.PROV_LOOKUP ON qApptFeed.PCP_ID = DBA1.PROV_LOOKUP.PROV_ID
WHERE qApptFeed.ApptDate Is Not Null

Here is the error message:

"The datatype, length, or value of argument '2' of routine '||' is incorrect".

 

[Dagon]

Looks like one of the arguments to the concatenation (||) is of the wrong data type. You would need everything to be of type CHAR or VARCHAR. Try casting e.g.

Right(cast(MemberDepNumber AS CHAR(2)),2)

 

[lynchg]

Thanks a lot! It wasn't the field you used as an example (it is actually a text field), but it was the Year function to get the age. I display the concatenation of Sex, Age, and Birthdate as:

M [58] 06/15/1945

I am relatively new to DB2, and sometimes little syntax issues can get pretty frustrating. Thanks again.