Building a query - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Building a query

Author

Topic

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 08:07:00

I have two tables that I need to make a report from.
*Ordertbl*
Name
SSN
UIC
FwdTo
*Addresstbl*
UIC
ParentUIC
ShipToUIC

I need to create a query that will produce the following
Name SSN ParentUIC UIC ShipToUIC

Heres where it gets tricky for me. The ShipToUIC will be the ShipToUIC for the UIC in ordertbl as long as FwdTo is not blank, if FwdTo is not blank the ShipToUIC will be the ShipToUIC for the FwdTo UIC.

Hope that makes sense. My first thought is to make a UDF that takes in two parameters uic, fwdto. if fwdto is not blank pass back the shipto for that fwdto else pass back the shipto for uic. I can do
Select SSN, ParentUIC, o.uic, getshippinguic(o.uic,fwdto)
from ordertbl o, addresstbl a
where o.uic = a.uic;

This works, however it can get really slow when its parsing through 70,000 records. Is there a more efficient way of doing something like this?

Thanks

Ben

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-29 : 08:19:13

i didn't quite understand you, but i think you're looking for something like this:

Select case when FwdTo is not null then FwdTo
when FwdTo is null Ordertbl.ShipToUic
end as ShipToUic

or you could do: select isnull(FwdTo, Ordertbl.ShipToUic) as ShipToUic

Go with the flow & have fun! Else fight the flow :)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-29 : 09:21:16

Does "blank" mean NULL? No need for a separate function, between CASE..WHEN, ISNULL(), or COALESCE() your solution is there. Additionally there are ways to deal with "blank" data that is not null like empty string '' or space ' '.

spirit1's query can work.

Look up COALESCE() it is more ANSI compliant apparently.

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 10:18:52

blank doesnt mean null. I dont allow null values on tables.

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 11:15:39

I cant think of a good way to make a case query for this.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-29 : 11:22:35

what is blank in your case?

Go with the flow & have fun! Else fight the flow :)

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 11:32:29

Blank is '' thats the default for the column

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-29 : 11:50:05

select COALESCE(NULLIF(FwdTo, ''), NULLIF(Ordertbl.ShipToUic, ''), '') as ShipToUic...

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 12:30:09

Hmm, im not sure. I think this is very close though. If fwdto is not blank, I cant take just the fwdto value, I have to get its respective shipto value in addresstbl. If fwdto is blank I need to get the ordertbl uic's respective shipto value in addresstbl

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-29 : 12:45:45

quote:
Originally posted by BenSwitzer

Hmm, im not sure. I think this is very close though. If fwdto is not blank, I cant take just the fwdto value, I have to get its respective shipto value in addresstbl. If fwdto is blank I need to get the ordertbl uic's respective shipto value in addresstbl

I should have asked this from the start.

Need some DDL (CREATE TABLE...), DML(INSERT INTO...) with sample data, and expected results. It seems you're trying to work with a conditional hierarchy.

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 13:17:41

[code]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Addresses]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Addresses]
GO

CREATE TABLE [dbo].[tbl_Addresses] (
[UIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressLine1] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressLine2] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PLAD] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Parent_UIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Decommissioned] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TCONum] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DMS_Address] [varchar] (69) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Reserve_Admin] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Reserve_Ship_To] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Active_Ship_To] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Ship_Exams] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Mail_Results] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Is_ROB] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_Addresses] WITH NOCHECK ADD
CONSTRAINT [DF_tbl_Addresses_TCONum] DEFAULT ('') FOR [TCONum],
CONSTRAINT [DF_tbl_Addresses_Is_ROB] DEFAULT ('N') FOR [Is_ROB],
CONSTRAINT [PK_tbl_Addresses] PRIMARY KEY CLUSTERED
(
[UIC]
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[scrublist]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[scrublist]
GO

CREATE TABLE [dbo].[scrublist] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Cycle] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Pgrade] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SSN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Prate] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Erate] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OrderFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PGUIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Earlies] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PCSFrom] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FWDTo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[chgdate] [numeric](18, 0) NOT NULL ,
[pulldate] [numeric](18, 0) NOT NULL ,
[PCSDate] [numeric](18, 0) NOT NULL ,
[FwdDate] [numeric](18, 0) NOT NULL ,
[ChangedBy] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO[/code]

Sample Scrublist data
SSN UIC FwdTo
111111111 11111
222222222 11111 22222
333333333 11111
Sample Tbl_Address data
UIC Parent_UIC Active_Ship_To
11111 00001 00001
22222 12345 98765

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 13:21:26

Sorry I couldnt send you real sample data due to privacy act.

Expected results
SSN UIC Parent_UIC Ship_To
111111111 11111 00001 00001
222222222 11111 00001 98765
333333333 11111 00001 00001

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 13:31:14

By the way how do you crate a DML?

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-29 : 13:42:11

hey try this: (i'm not sure about performance issues)

Select o.SSN, o.uic, a.Parent_UIC, isnull((select c.Active_ship_to from Tbl_Addresses c where c.uic = o.fwdto),(select c.Active_ship_to from Tbl_Addresses c where c.uic = o.uic)) as fwdto
from Scrublist o, Tbl_Addresses a
where o.uic = a.uic

- RoLY roLLs

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-29 : 13:47:31

I had to modify the code, I am not sure about performance because the JOIN is being manipulated by 3 MS SQL functions, but here goes.

CREATE TABLE [dbo].[tbl_Addresses] (
	[UIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Parent_UIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Active_Ship_To] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_Addresses] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_Addresses] PRIMARY KEY  CLUSTERED 
	(
		[UIC]
	)  ON [PRIMARY] 
GO



CREATE TABLE [dbo].[scrublist] (
	[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[UIC] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SSN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FWDTo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO


Insert Scrublist (SSN, UIC, FwdTo) values
('111111111', '11111', '') 
Insert Scrublist (SSN, UIC, FwdTo) values
('222222222', '11111', '22222')
Insert Scrublist (SSN, UIC, FwdTo) values
('333333333', '11111', '') 
Insert Tbl_Addresses (UIC, Parent_UIC, Active_Ship_To) values
('11111', '00001', '00001')
Insert Tbl_Addresses (UIC, Parent_UIC, Active_Ship_To) values
('22222', '12345', '98765')

select * from scrublist
select * from tbl_addresses

select *
from	dbo.scrublist a JOIN tbl_addresses b
	  ON COALESCE(NULLIF(a.FWDTo, ''), NULLIF(a.UIC, '')) = b.UIC
Results
ID                   UIC   SSN       FWDTo UIC   Parent_UIC Active_Ship_To 
-------------------- ----- --------- ----- ----- ---------- -------------- 
1                    11111 111111111       11111 00001      00001
2                    11111 222222222 22222 22222 12345      98765
3                    11111 333333333       11111 00001      00001

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 13:54:05

well thats really close, but the Parent uic is always the parent uic of the uic from scrublist. Yeah its complicated. So number 2's parent should be 00001

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-29 : 14:03:03

Maybe?

select DISTINCT a.*,b.*, c.Parent_UIC
from	dbo.scrublist a JOIN tbl_addresses b
	  ON COALESCE(NULLIF(a.FWDTo, ''), NULLIF(a.UIC, '')) = b.UIC
	JOIN tbl_Addresses c
	  ON a.UIC = c.UIC

ID                   UIC   SSN       FWDTo UIC   Parent_UIC Active_Ship_To Parent_UIC 
-------------------- ----- --------- ----- ----- ---------- -------------- ---------- 
1                    11111 111111111       11111 00001      00001          00001
2                    11111 222222222 22222 22222 12345      98765          00001
3                    11111 333333333       11111 00001      00001          00001

BenSwitzer
Yak Posting Veteran

72 Posts

Posted - 2004-07-29 : 14:05:30

RoLY your query looks to be working so far, although the performance is just as slow as the UDF. I like the UDF better because its cleaner.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-29 : 18:52:40

Does this give the correct answer? If so, it will probably be a little faster than the function. There should be an index on FwdTo.


SELECT o.SSN, o.uic, a.Parent_UIC, a.Active_ship_to
	FROM Scrublist o, Tbl_Addresses a
	WHERE o.uic = a.uic
	AND o.FwdTo = ''

UNION ALL

SELECT o.SSN, o.uic, b.Parent_UIC, a.Active_ship_to
	FROM Scrublist o, Tbl_Addresses a, Tbl_Addresses b
	WHERE o.FwdTo = a.uic
	AND o.uic = b.uic
	AND o.FwdTo <> ''

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources