Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Tbonehwd
Starting Member
1 Post |
 Posted - 2006-02-17 : 10:44:23
|
| I am trying to recreate some queries that I originally created in Access using linked SQL tables. I have found that I can't use things like "Last" and "IIF" Below is one of the queries from Access if anyone has a couple of minutes could you please show me how this could be done in a true SQL way. "SELECT PROOLN_M.ITM_NUM, Last(PROOLN_M.DSC_001)As Title1, Last(PROOLN_M.DSC_003) As ISBN1, Last(CDSITM_M.CPR_YER) As Year1 " & _ ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_PUR " & _ ", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_RET " & _ ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_PUR " & _ ", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_RET " & _ ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,PROOLN_M.QTY_SHP*-1)) AS NET_UNITS " & _ ", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,PROOLN_M.ITM_NET*-1)) AS NET_DOLLARS " & _ "FROM ((PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM) INNER JOIN CDSITM_M ON PROOLN_M.ITM_NUM=CDSITM_M.ITM_NUM) INNER JOIN INVSAC_T ON CDSITM_M.ITM_SAC = INVSAC_T.SAC_CDE " & _ "WHERE PROORD_M.ORD_STA IN ('F','B') AND PROORD_M.ORD_TYPE IN ('C','I','P','V') " & _ "AND INVSAC_T.SAC_SUM = 'HSE' & _ "AND PROOLN_M.SHP_CTM = '000000777777' & _ "AND PROOLN_M.ITM_NUM = '2114' & _ "AND CDSITM_M.ITM_SAC = 'FAL04' & _ "AND PROORD_M.ACT_DTE >=#01/31/2004# & _ "AND PROORD_M.ACT_DTE <=#12/31/2005# & _ "GROUP BY PROOLN_M.ITM_NUM ;" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-02-17 : 10:50:00
|
| IFF => CASE....look up the syntax in BOL (or via other examples noted on this site)LAST ... no direct functional equivalent in SQL....you need to ORDER DESC your data and pick the TOP 1 record to get this. |
 |
|
|
|
|
|
|
|
Trying to convert an Access VBA query