Adding this query to existing query

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding this query to existing query

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2011-03-21 : 15:12:06

I did this query quick to get some data, and then had this snippet sent to me to do something specific I need. Problem is I'm not sure how to add this new code to the existing query...

Existing:

SELECT     bol_number, u.ref_num, pro_number, client_city, customer_name, customer_state, carrier_name, bol_status

FROM         tbl_bol b
INNER JOIN dbo.tbl_ref_nums u on b.bol_id=u.bol_id
WHERE     (client_name LIKE 'sfs%')

Snippet I'm trying to add:

AND ISNULL(tbl_214_datatable.delivered_date, tbl_bol.delivered_date) IS NULL AND force_delivered = 0 AND ((SELECT TOP 1 ISNULL(status_code, appt_status_code) FROM tbl_214_status a LEFT JOIN tbl_214_isa b ON b.tbl_214_id = a.tbl_214_id WHERE bol_id = tbl_bol.bol_id ORDER BY isa_09 DESC, isa_10 DESC, status_date DESC, status_time DESC, a.record_num DESC) <> 'D1' OR (SELECT COUNT(*) FROM tbl_214_status WHERE bol_id = tbl_bol.bol_id) = 0)

That's what was sent to me. The leading AND throws me off, I'm not sure where it would go. Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-21 : 15:51:37

I haven't looked at the entire snippet, but have you tried just add it at the end of your existing query? That appears to be the location the person who sent it to you intended.

Now that snippet of code looks pretty bad, so there's likely a better way to do things. But we'd need much more information to be able to help rewrite it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

meef
Posting Yak Master

113 Posts

Posted - 2011-03-21 : 16:08:29

I did indeed try that, but get a bunch of errors:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tbl_214_datatable.delivered_date" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tbl_bol.delivered_date" could not be bound.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'force_delivered'.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "tbl_bol.bol_id" could not be bound.
Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "tbl_bol.bol_id" could not be bound.

If I put "b." in front of the ones that say they could not be bound, the error goes away, but it still tells me some are invalid column names. Maybe mine isn't set up the same way his is.

EDIT: Actually I just checked again:

"tbl_214_datatable.delivered_date"

The table is tbl_214_datatable and one of the columns in that table is delivered_date

I can see that right here, so it just needs some reworking I guess. If I try putting dbo. in front I get the error that says it can't be bound again.

latch
Yak Posting Veteran

62 Posts

Posted - 2011-03-21 : 16:32:08

i think you have 5 tables:
1. tbl_bol(b)
2. tbl_ref_nums(u)
3. tbl_214_datatable
4. tbl_214_status(a)
5. tbl_214_isa(b)

for table 1 & 5 you are using same identifier 'b' so it is unable to bound to which table it belongs.

next you need to join the table 4 to 1 before where then add the Snippet
make sure to add tablename.force_delivered to avoid invalid column name warning.

Thanks,
latch

meef
Posting Yak Master

113 Posts

Posted - 2011-03-22 : 09:24:25

I don't know, I'm still confused. Can joins be joined on the same column?

SELECT     bol_number, u.ref_num, pro_number, client_city, customer_name, customer_state, carrier_name, bol_status

FROM         tbl_bol t
INNER JOIN dbo.tbl_ref_nums u on t.bol_id=u.bol_id
INNER JOIN dbo.tbl_214_status a on u.bol_id=a.bol_id
WHERE     (client_name LIKE 'sfs%') and bol_id = tbl_bol.bol_id

I added table 4 into the join, but don't know where to go from there. Should I combine the snippet or just leave it as an "add-on" to my existing code? I started combining (just the WHERE statement) but it doesn't look right to me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-22 : 09:33:44

Tara is probably correct, just append that snippet to the end of the existing query. However, you will need to change the table alias a bit.
Beside that, i have not idea where the part in red comes from and how it should be reference


SELECT		bol_number, u.ref_num, pro_number, client_city, customer_name, customer_state, carrier_name, bol_status
FROM         	tbl_bol tb
INNER JOIN 	dbo.tbl_ref_nums u	on 	tb.bol_id = u.bol_id
WHERE     	(client_name LIKE 'sfs%')
AND 		ISNULL(tbl_214_datatable.delivered_date, tb.delivered_date) IS NULL 
AND 		force_delivered = 0 
AND 		(
			(
			SELECT 	TOP 1 
				ISNULL(status_code, appt_status_code) 
			FROM 	tbl_214_status a 
				LEFT JOIN tbl_214_isa b 	ON 	b.tbl_214_id = a.tbl_214_id 
			WHERE 	bol_id = tb.bol_id 
			ORDER BY isa_09 DESC, isa_10 DESC, status_date DESC, status_time DESC, a.record_num DESC
			) <> 'D1' 
		OR 	(
				SELECT 	COUNT(*) 	
				FROM 	tbl_214_status 
				WHERE 	bol_id 	= tb.bol_id
			) = 0
		)

KH
[spoiler]Time is always against us[/spoiler]

meef
Posting Yak Master

113 Posts

Posted - 2011-03-23 : 09:01:47

Thanks for the tips, it did just need some reworking. I got it working with this:

SELECT		bol_number, u.ref_num, pro_number, client_city, customer_name, customer_state, carrier_name, bol_status, p.delivered_date, customer_zip, ship_date, weight
FROM        tbl_bol tb
	INNER JOIN 	dbo.tbl_ref_nums u	on 	tb.bol_id = u.bol_id
	INNER JOIN dbo.tbl_214_datatable p on tb.bol_id = p.bol_id
WHERE     	(client_name LIKE 'sfs%') and ship_date >= @ShippedLo and ship_date <= @ShippedHi
AND 		ISNULL(p.delivered_date, tb.delivery_date) IS NULL 
AND 		force_delivered = 0 
AND ((SELECT TOP 1
	ISNULL(status_code, appt_status_code) 
	FROM 	tbl_214_status a 
	LEFT JOIN tbl_214_isa b 	ON 	b.tbl_214_id = a.tbl_214_id 
	WHERE 	bol_id = tb.bol_id and ship_date >= @ShippedLo and ship_date <= @ShippedHi
	ORDER BY isa_09 DESC, isa_10 DESC, status_date DESC, status_time DESC, a.record_num DESC) <> 'D1' 
		OR 	
    (SELECT COUNT(*) 	
	FROM 	tbl_214_status 
	WHERE 	bol_id 	= tb.bol_id) = 0) and ship_date >= @ShippedLo and ship_date <= @ShippedHi
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources