April 13, 2015 at 8:02 pm
Hi all
Some help would be appreciated. 🙂 I am in the process of finding and preparing to extract data out to spreadsheets from an old tracking system (SQL 2005) and have run up against the limit of my SQL knowledge and examples I can understand online. 🙁
I can successfully use this query to get some of the data.
declare @file_num varchar(20)
set @file_num = '001/001/000/00002'
select
CAST(cntfl.file_no AS VARCHAR) [File No:]
,CAST(cntfl.file_part_no AS VARCHAR) [Part No:]
,CAST(cntfl.file_internal_no AS VARCHAR) [Internal No:]
,CAST(stdbxct.bxct_box_no AS VARCHAR) [Box:]
,stdaud.aud_ao_no [At:]
,stdaud.aud_date [Since:]
,CAST(cntfl.file_barcode_no AS VARCHAR) [Barcode:]
,cntfl.file_name01 [Title 1:]
,cntfl.file_name02 [Title 2:]
,cntfl.file_name03 [Title 3:]
,cntfl.file_name04 [Title 4:]
,cntfl.file_create_d [Date Created:]
,cntfl.file_closed_d [Date Closed:]
,cntfl.file_active_until [Active Until:]
from cnt_file as cntfl
INNER JOIN std_bxct as stdbxct on stdbxct.bxct_file_no = cntfl.file_no
INNER JOIN std_aud as stdaud on stdbxct.bxct_box_no = stdaud.aud_file_no
where file_no = @file_num
However I need to get any data even if there are Nulls.
So I tried to do the following with the joins.
INNER JOIN std_bxct as stdbxct
on isnull(stdbxct.bxct_file_no, '(novalue)') = isnull(cntfl.file_no, '(novalue)')
INNER JOIN std_aud as stdaud
on isnull(stdbxct.bxct_box_no, '(novalue)') = isnull(stdaud.aud_file_no, '(novalue)')
This gave me the error.
" Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query."
For the life of me I can't seem to make any sense of how to change the query to use CONVERT. If you can help point me in the right direction with an example or a useful link it would be appreciated.
I trust the above makes sense and is enough to explain the issue?
Thanks L
April 13, 2015 at 9:49 pm
L30 (4/13/2015)
I can successfully use this query to get some of the data.
.
.
.
INNER JOIN std_bxct as stdbxct on stdbxct.bxct_file_no = cntfl.file_no
INNER JOIN std_aud as stdaud on stdbxct.bxct_box_no = stdaud.aud_file_no
where file_no = @file_num
However I need to get any data even if there are Nulls.
Without data and DDL, this is only a guess. Replace your INNER JOIN's with LEFT JOIN's like so:
LEFT JOIN std_bxct as stdbxct on stdbxct.bxct_file_no = cntfl.file_no
LEFT JOIN std_aud as stdaud on stdbxct.bxct_box_no = stdaud.aud_file_no
If the LEFT JOIN condition is not satisfied, then any column in your SELECT statement that begins with stdbxct or stdaud will be shown as NULL. An INNER JOIN would filter those out.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 13, 2015 at 10:13 pm
Thanks LinksUp, and congratulation on your excellent mind reading skills. That looks like it will work just fine. Appreciate you taking the time to respond. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply