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 |
|
Rawb_One
Starting Member
3 Posts |
 Posted - 2009-01-28 : 17:24:56
|
| Greetings,I'm having a problem with the Coalesce function. I'm using a combo boxes in visual studio to the variables. I want to have the query execute with the parameters entered will the nulls returning anything in the database. Very similiar to http://www.sqlteam.com/article/implementing-a-dynamic-where-clause.Button CodePrivate Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim strRmCode, strFormula, strOperator, strBatchId, strVessel, intDestination As String strRmCode = RM_CODEComboBox.Text strFormula = FORMULAComboBox.Text strVessel = VESSELComboBox.Text strOperator = OPERATORComboBox.Text strBatchId = BATCH_IDComboBox.Text PLCHST_CacheTableAdapter.FillMultiQUERY(MainDataSet.PLCHST_Cache, strBatchId, strFormula, strRmCode, strVessel, strOperator) End SubSQL StatementSELECT BATCH_START_TIME, BATCH_END_TIME, BATCH_ID, FORMULA, RM_CODE, DENSITY, TANK_LEVEL_GAL, TANK_LEVEL_LBS, PRESET_LBS, DELIVERED_LBS, VESSEL, DESTINATION, OPERATOR, TANK_NUMBER, H2O_TYPE, BATCH_STATUS, SEQUENCE_RES, SEQUENCE_ALL, Pri_Key FROM dbo.PLCHST_CacheWHERE BATCH_ID =COALESCE(@strBatch_ID, BATCH_ID) AND FORMULA = COALESCE(@strFormula, FORMULA) AND RM_CODE = COALESCE(@strRmCode, RM_CODE) AND VESSEL = COALESCE(@strVessel, VESSEL) AND OPERATOR = COALESCE(@strOperator, OPERATOR) The problem is that it will return nothing if one or more of the fields is blank. The only way it works is if all of the variables match something in the database. I was under the impression that if I put the Column name in the second part of coalesce it would return the entire column.Thanks for any help.Bob |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-01-28 : 17:48:08
|
Seems like your application is sending empty fields like an empty string '' and that is not NULL.COALESCE works fine with NULL but not with ''.Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Rawb_One
Starting Member
3 Posts |
Posted - 2009-01-28 : 17:55:05
|
| I just realized that, I made a stored procedure in Management and ran it with the null checkbox checked and it worked. How do i make the empty string a NULL? Sorry, kinda new at this. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-01-28 : 18:00:48
|
Try something like this:... WHERE BATCH_ID =COALESCE(NULLIF(@strBatch_ID,''), BATCH_ID) AND ... Don't know if there is a better way...Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Rawb_One
Starting Member
3 Posts |
Posted - 2009-01-28 : 18:27:24
|
| Excellent!Thank you Very much. |
|
|
|
|
|
|
|
|
Coalesce