PDA

View Full Version : If argument in SQL query



jazznaura
08-30-2011, 11:20 AM
Hi All,
I have the following query written in Excel (VBA), but isthere a way to insert an if/then /else statement into it?


SQL = "SELECT #tmpFR10.MySelect, #tmpFR10.Tpnd,#tmpFR10.Descrip, #tmpBSQB.Act_BOH, #tmpBSQB.PALLET_QTY, #tmpBSQB.sTOCK_I,#tmpBSQB.Multi_p, "
SQL = SQL & "#tmpBSQB.s_dock, #tmpBSQB.non_s_dock,#tmpFR10.CUBE, #tmpFR10.Weight, LEFT(whs.dw_prodbpr.SubGroup,4),LEFT(whs.dw_prodbpr.SubGroup,3) FROM #tmpFR10 " _
& " LEFT JOIN whs.Focus_Prodtut ON #tmpFR10.tpnd =whs.Focus_Prodtut.Tpnd " _
& " LEFT JOIN whs.dw_prodbpr ONwhs.Focus_Prodtut.Tpnb = whs.dw_prodbpr.Tpnb " _
& " LEFT JOIN #tmpBSQB ON #tmpFR10.TPND =#tmpBSQB.TPND"





In addition to the above I wish to say

If #tmpBSQB.Act_BOH > (#tmpBSQB.s_dock + #tmpBSQB.non_s_dock)) THEN #tmpBSQB.Act_BOH ELSE (#tmpBSQB.s_dock +#tmpBSQB.non_s_dock)
The results being output to a new column. i’m ok with VBA but only just starting with SQL.
Any help would be appreciated. Thanks,

Bob Phillips
09-06-2011, 04:31 AM
SQL = "SELECT #tmpFR10.MySelect, " & _
" #tmpFR10.Tpnd, " & _
" #tmpFR10.Descrip, " & _
" #tmpBSQB.Act_BOH, " & _
" #tmpBSQB.PALLET_QTY, " & _
" #tmpBSQB.sTOCK_I, " & _
" #tmpBSQB.Multi_p, " & _
" #tmpBSQB.s_dock, " & _
" #tmpBSQB.non_s_dock, " & _
" #tmpFR10.CUBE, " & _
" #tmpFR10.Weight, " & _
" LEFT(whs.dw_prodbpr.SubGroup,4),LEFT(whs.dw_prodbpr.SubGroup,3), " & _
" IIF(#tmpBSQB.Act_BOH > (#tmpBSQB.s_dock + #tmpBSQB.non_s_dock), #tmpBSQB.Act_BOH, (#tmpBSQB.s_dock + #tmpBSQB.non_s_dock)) AS 'Calc' " & _
"FROM #tmpFR10 " & _
"LEFT JOIN whs.Focus_Prodtut ON #tmpFR10.tpnd =whs.Focus_Prodtut.Tpnd " _
"LEFT JOIN whs.dw_prodbpr ONwhs.Focus_Prodtut.Tpnb = whs.dw_prodbpr.Tpnb " _
"LEFT JOIN #tmpBSQB ON #tmpFR10.TPND =#tmpBSQB.TPND"

jazznaura
09-07-2011, 07:33 AM
if with 2 ii :banghead: , thats great just the pointer i needed.
also came across case statement so will try both.

thanks again xld.