I think that is where the confusion is coming. If it is null then use the vendor.vend_num and match it to vendaddr.vend_num to get the vendaddr.name. So in summary, if the vendor.vend_remit is not null then match that to vendaddr.vend_num to get the vendaddr.name. Since the vend_remit is not null then it must pull the vendaddr.name where vendaddr.vendnum = vendor.vend_remit. The vendaddr.name BOX USA would be the result if I was matching it up to the vendor.vend_num of BOX0001.
#SQL SERVER IF STATEMENT CODE#
On isNull(vendor.vend_num, vendor.vend_remit) = vendaddr.vend_numĬheck Ref Amount Date Code Name vend_num vend_remitĨ5772BOX0001-4935.28000000 00:00:00.000BK1BOX USA BOX0001 INT0010īased on the above results, the name should be based on the vendor.vend_remit, which means that the vendaddr.name is not Box USA, but International Paper.
![sql server if statement sql server if statement](https://community.qlik.com/legacyfs/online/108189_1.jpg)
The original code modified using everyone's input: However, the correct name is not pulling up. I hope that this helps.īoth to ChrisM and Drew.
![sql server if statement sql server if statement](https://i.ytimg.com/vi/HKfaojG7tI0/maxresdefault.jpg)
#SQL SERVER IF STATEMENT HOW TO#
So my dilemma is that I am not sure how to write a case statement or an if else statment among the joins. So basically if vendor.ven_num is not null then vendor.name = vendor.name where vendor.vend_num = vendor.vend_num else vendor.name = vendor.name where vendor.vend_remit = vendor.vend_remit. If it is null, then the name will be based on the vendor number. If the remit number is not null then I pull the vendor name from the vendor table. One of the joined tables houses both the vendor number and remit number. In the vendor table there is only one name field and then the vendor number. If it is, then the vendor name will be pulled from vendor where it equals the vendor.vend_remit. Now, I want to pull the correct vendor name based on whether the vendor.vend_num is null. ON aptrxp_all.vend_num=vendaddr.vend_num) I have done Internationalisation with a similar schema and I decided to first try to find the wanted language and only if no record is found in that language query a default.ON aptrxp_all.check_num=glbank.check_num) INDEX ON UniqueKey TAG xFirstLanguage UNIQUE LEFT JOIN Table_B ON (Table_A.key = Table_B.key) ĪND (Table_B.language=?m.ForeignLang OR Table_B.language="English") SELECT Table_A.Key as UniqueKey, Table_A.*, Table_B.* FROM Table_A One technically very easy approach would be to query both languages at once and index on the key with a unique index, then the language you found first will stay in the indexed result and english only will stay, if there is no spanish record. Your table design is very good in case you want to support any language and not just two, having all fields twice or more times for further languages would be the worse choice, therefore I'd not change the table schemas. So you'd either query without the language condition and prefer the spanish record or first try to get the spanish result and if you get NULLs requery with the m.ForeignLang filter set to English. You could query SELECT NVL(titlespanish, titleenglish) as title FROM sometable, which then would need to have both titles in one record.īut either you would need to restructure your data or you'd need a complex subquery to pull all languages in one row and then use the NVL logic on that computed record. In general your conditions and field names or expressions in the field list always will be evaluated on You can't then redecide to want another language, that's not possible as a SQL construct. Is not met, no record in Table_B with the same key as in A has the wanted language. If the condition Table_B.language=?m.ForeignLang but the join condition itself will not get NULL, no record fullfilling the condition is found, that doesn't render the condition itself NULL, not even F. Table_B fields, as all the values will be NULL if no record matches. If the join condition Table_B.language=?m.ForeignLang results in no join you can apply NVL() on the
![sql server if statement sql server if statement](https://slidetodoc.com/presentation_image/0cb2bacb2bfdb960d314e31903220f12/image-18.jpg)
SELECT sc, omdate, Ĭurrencies.currency = exchangerates.currency AND Rates (equating to translated text) exist for both the 30 June and 15 March (the latter being the default rate (language) for our purposes here). Here Foreign Currency exchange rates with the exchanges rates bearing a date from when the rate is applicable (to be used). I have tried with the IN clause INLIST() IIF() and ICASE() statements using a similar structure of tables. With only English when the foreign language does not exist. I'm not certain that this can be done within a single SQL statement.įor if you put a condition in the JOIN clause, it will be match for both the TRUE and FALSE condition, depending upon the record being retrieved from the feeding tables and therefore place 2 records into the resultant cursor when both Foreign language and