September 22, 2008 at 4:04 am
Good day,
I am having some trouble retrieving a value from my select statement the below is an extract from a job that I am updating so that it provides the DIRFEE value as well as the ASKFEE. I am sure it is probably something simple that I am doing wrong but if anyone could help I would be very grateful.
Neil
SELECT CL1.CLIENT_UNO, CL1.NAME_UNO, MA1.MATTER_UNO, MA1.CLNT_MATT_CODE, CL1.CLIENT_NAME, NA1.NAME,
PE2.LOGIN, PE2.EMPLOYEE_CODE, PE1._VP_CODE,
PE1.EMPLOYEE_CODE, MA2._PRE_PRORATE_AMT,
REPLACE(RE1.NAME,'''',''),
CASE
WHEN VP1.UCODE='FCHATTERJE' THEN 'FCHATTERJEE'
WHEN VP1.UCODE='DPERINCHIE' THEN 'DPERINCHIEF'
WHEN VP1.UCODE='DHENDRICKS' THEN 'DHENDRICKSON'
WHEN VP1.UCODE='VKAM' THEN 'VKAMBATIKA'
WHEN VP1.UCODE='JRICHARDS' THEN 'JRICHARDSON'
WHEN VP1.UCODE='DSOUZA' THEN 'DPERKINS'
ELSE VP1.UCODE
END AS UCODE, VP1.UNAME,
RE2.CODE, RE2.NAME,
CONVERT(MONEY,UF1.FLDVALUE)
CONVERT(MONEY,UF2.FLDVALUE)
CMSDIRFEE
FROM HBM_MATTER MA1
INNER JOIN TBM_MATTER MA2 ON MA1.MATTER_UNO=MA2.MATTER_UNO
INNER JOIN HBM_CLIENT CL1 ON MA1.CLIENT_UNO=CL1.CLIENT_UNO
INNER JOIN HBM_NAME NA1 ON CL1.NAME_UNO=NA1.NAME_UNO
INNER JOIN HBM_PERSNL PE1 ON MA1.RESP_EMPL_UNO=PE1.EMPL_UNO
INNER JOIN HBM_PERSNL PE2 ON MA1.BILL_EMPL_UNO=PE2.EMPL_UNO
INNER JOIN HBM_PERSNL PE3 ON MA1.ASSIGN_EMPL_UNO=PE3.EMPL_UNO
INNER JOIN FMSSRV.VP4000LIVE.DBO.ENTITY EN1 ON MA1.CLNT_MATT_CODE=LTRIM(RTRIM(EN1.ENTCODE))
INNER JOIN FMSSRV.VP4000LIVE.DBO.VPUSER VP1 ON EN1.ADMINCODE=VP1.UCODE
INNER JOIN FMSSRV.VP4000LIVE.DBO.REFLABELS RE2 ON EN1.USERREFCODE1=RE2.CODE
INNER JOIN FMSSRV.VP4000LIVE.DBO.REFLABELS RE1 ON EN1.ENTCODE=RE1.CODE
LEFT JOIN FMSSRV.VP4000LIVE.DBO.USERFIELDS UF1 ON EN1.ENTCODE=UF1.ENTCODE AND UF1.FLDKEY='ASKFEE'
LEFT JOIN FMSSRV.VP4000LIVE.DBO.USERFIELDS UF2 ON EN1.ENTCODE=UF2.ENTCODE AND UF2.FLDKEY='DIRFEE'
(SELECT TOP1 MA3._PRE_PRORATE_AMT FROM TBM_MATTER MA3 LEFT JOIN HBM_MATTER ON HBM_MATTER.MATTER_UNO=MA3.MATTERUNO WHERE HBM_MATTER.MATT_TYPE_CODE IN ('ID','IDNT','CD','CDNT')) AS CMSDIRFEE
WHERE MA1.STATUS_CODE IN ('OPEN','PEND')
AND MA1.MATT_TYPE_CODE IN ('CA','CANT')
AND MA1.OFFC IN ('SVCS','ATLB')
September 22, 2008 at 5:21 am
Hi Neil
I reckon this is what you want. Note that there were a couple of missing commas in the SELECT list.
[font="Courier New"]SELECT CL1.CLIENT_UNO, CL1.NAME_UNO, MA1.MATTER_UNO, MA1.CLNT_MATT_CODE, CL1.CLIENT_NAME, NA1.[NAME],
PE2.LOGIN, PE2.EMPLOYEE_CODE, PE1._VP_CODE,
PE1.EMPLOYEE_CODE, MA2._PRE_PRORATE_AMT,
REPLACE(RE1.NAME,'''',''),
CASE
WHEN VP1.UCODE='FCHATTERJE' THEN 'FCHATTERJEE'
WHEN VP1.UCODE='DPERINCHIE' THEN 'DPERINCHIEF'
WHEN VP1.UCODE='DHENDRICKS' THEN 'DHENDRICKSON'
WHEN VP1.UCODE='VKAM' THEN 'VKAMBATIKA'
WHEN VP1.UCODE='JRICHARDS' THEN 'JRICHARDSON'
WHEN VP1.UCODE='DSOUZA' THEN 'DPERKINS'
ELSE VP1.UCODE
END AS UCODE, VP1.UNAME,
RE2.CODE, RE2.[NAME],
CONVERT(MONEY,UF1.FLDVALUE), -- <-- missing comma
CONVERT(MONEY,UF2.FLDVALUE), -- <-- missing comma
d.CMSDIRFEE
FROM HBM_MATTER MA1
INNER JOIN TBM_MATTER MA2 ON MA1.MATTER_UNO=MA2.MATTER_UNO
INNER JOIN HBM_CLIENT CL1 ON MA1.CLIENT_UNO=CL1.CLIENT_UNO
INNER JOIN HBM_NAME NA1 ON CL1.NAME_UNO=NA1.NAME_UNO
INNER JOIN HBM_PERSNL PE1 ON MA1.RESP_EMPL_UNO=PE1.EMPL_UNO
INNER JOIN HBM_PERSNL PE2 ON MA1.BILL_EMPL_UNO=PE2.EMPL_UNO
INNER JOIN HBM_PERSNL PE3 ON MA1.ASSIGN_EMPL_UNO=PE3.EMPL_UNO
INNER JOIN FMSSRV.VP4000LIVE.DBO.ENTITY EN1 ON MA1.CLNT_MATT_CODE=LTRIM(RTRIM(EN1.ENTCODE))
INNER JOIN FMSSRV.VP4000LIVE.DBO.VPUSER VP1 ON EN1.ADMINCODE=VP1.UCODE
INNER JOIN FMSSRV.VP4000LIVE.DBO.REFLABELS RE2 ON EN1.USERREFCODE1=RE2.CODE
INNER JOIN FMSSRV.VP4000LIVE.DBO.REFLABELS RE1 ON EN1.ENTCODE=RE1.CODE
LEFT JOIN FMSSRV.VP4000LIVE.DBO.USERFIELDS UF1 ON EN1.ENTCODE=UF1.ENTCODE AND UF1.FLDKEY='ASKFEE'
LEFT JOIN FMSSRV.VP4000LIVE.DBO.USERFIELDS UF2 ON EN1.ENTCODE=UF2.ENTCODE AND UF2.FLDKEY='DIRFEE'
-- take this out
--(SELECT TOP1 MA3._PRE_PRORATE_AMT
--FROM TBM_MATTER MA3
--LEFT JOIN HBM_MATTER ON HBM_MATTER.MATTER_UNO=MA3.MATTERUNO
--WHERE HBM_MATTER.MATT_TYPE_CODE IN ('ID','IDNT','CD','CDNT')) AS CMSDIRFEE
WHERE MA1.STATUS_CODE IN ('OPEN','PEND')
AND MA1.MATT_TYPE_CODE IN ('CA','CANT')
AND MA1.OFFC IN ('SVCS','ATLB')
-- and replace it with this...
LEFT JOIN (SELECT MAX(MA3._PRE_PRORATE_AMT) AS CMSDIRFEE, MA3.MATTERUNO
FROM TBM_MATTER MA3
INNER JOIN HBM_MATTER ON HBM_MATTER.MATTER_UNO=MA3.MATTERUNO -- will geberate the same end result as a left join
WHERE HBM_MATTER.MATT_TYPE_CODE IN ('ID','IDNT','CD','CDNT')
GROUP BY MA3.MATTERUNO) AS d ON d.MATTERUNO = MA2.MATTER_UNO
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 22, 2008 at 5:22 am
Hi Neil,
I can't quite figure out, from what you've posted, what the question/problem is. You'll need to post a bit more information than the query. If you can post the DDL (the t-sql used to create the tables), some sample data and what exactly the statement isn't doing that you expect it to, we should be able to help you.
Thanks,
September 22, 2008 at 5:41 am
Thanks Chris and Adrian managed to get it working I think so starting to test it now!
many many thanks! 🙂
Neil
September 22, 2008 at 5:48 am
Thanks for the feedback Neil, good luck!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply