The Long and Winding Code - 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)

The Long and Winding Code

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 13:16:17

"The Long and Winding Code led me to your door......." (to paraphrase Sir Paul)

I have a rather large piece of code that is supposed to handle this problem: a resultant table with everyone who has a valid diagnosis. The problems is I can't figure out why it doesn't work. The result should list EVERYONE except: MARS, SATURN & PLUTO.

Here's the testing input and code:

DROP TABLE [Fowler].[dbo].[Agents]
DROP TABLE [Fowler].[dbo].[Diags]
DROP TABLE [Fowler].[dbo].[Results]

CREATE TABLE [Fowler].[dbo].[Agents]
(Name varchar(20) NULL,Agt_Code varchar (4) NULL)
GO
INSERT Agents VALUES('Fred MERCURY ','MERC')
INSERT Agents VALUES('Venus DE MILO ','DEMI')
INSERT Agents VALUES('Earth WINDANDFIRE ','WIND')
INSERT Agents VALUES('Veronica MARS ','MARS')
INSERT Agents VALUES('Elmo P. JUPITER ','JUPI')
INSERT Agents VALUES('Gladys SATURN ','SATU')
INSERT Agents VALUES('Billy Bob URANUS,III','URAN')
INSERT Agents VALUES('Fowler J. NEPTUNE ','NEPT')
INSERT Agents VALUES('PLUTO Earnhardt ','PLUT')
GO
CREATE TABLE [Fowler].[dbo].[Diags]
(Agt_Code nvarchar (4) NULL,
Diag_Code nvarchar (8) NULL,
Diag1 nvarchar (8) NULL,
Diag2 nvarchar (8) NULL,
Diag3 nvarchar (8) NULL,
Diag4 nvarchar (8) NULL,
Diag5 nvarchar (8) NULL)
GO
INSERT Diags VALUES('MERC','340. ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('DEMI','340. ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('WIND','340. ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('MARS',' ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('JUPI','340. ','491.8 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('SATU',' ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('URAN','340. ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('NEPT','340. ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('PLUT',' ','414.05 ','999-1111',' ',' ',' ')
GO

SELECT a.[Name], a.[Agt_Code] as a_Agt_Code, d.[Agt_Code], d.[Diag_Code],
d.[Diag1], d.[Diag2], d.[Diag3], d.[Diag4], d.[Diag5]
INTO [Fowler].[dbo].[Results_Temp]
FROM [Fowler].[dbo].[Agents] a,
[Fowler].[dbo].[Diags] d
WHERE a.Agt_Code = d.Agt_Code
GO

SELECT [Name], [a_Agt_Code], [Diag_Code], [Diag1], [Diag2], [Diag3], [Diag4], [Diag5]
INTO [Fowler].[dbo].[Results_Final]
FROM [Fowler].[dbo].[Results_Temp]
WHERE (
(DIAG_CODE in
( '250.0','250.1','250.2','250.3','250.4','250.5','250.6','250.7',
'250.8','250.9','250.00', --Diabetes (all ages)
'279', --Disorders involving immune mechanism
'282.7', --Hemoglobinopathies
'332.0','332.1', -- Parkinsons
'340', --Multiple sclerosis
'343.0','343.1','343.2','343.3','343.4','343.8','343.8',--Infantile cerebral palsy
'345.0','345.1','345.2','345.3','345.4','345.5','345.6','345.7','345.8','345.9',--Epilepsy
'414.0','414.00','414.01','414.02','414.03','414.04','414.05',--Chronic ischemic heart disease
'414.1','414.10','414.11','414.19',--Chronic ischemic heart disease
'414.8',--Chronic ischemic heart disease
'414.9',--Chronic ischemic heart disease
'424.0','424.1','424.2','424.3','424.9','424.90','424.91','424.99',--Valve disorders
'425', --Cardiomyopathy
'428.0','428.1','428.9', --CHF
'436', --Stroke
'491.0','491.1','491.2','491.20','491.21','491.8','491.9',--Chronic Bronchitis
'492.0', --Emphysema
'493.0','493.1','493.2','493.9',--Asthma (all ages)
'496', --Chronic Obstructive Pulmonary Disease
'515', --Pulmonary fibrosis
'582', --Chronic glomerulonephritis
'585', --Chronic renal failure
'v42.0', --Kidney transplants
'v42.1', --Heart transplants
'v42.6', --Lung transplants
'v42.7' --Liver transplants
) )
and (DIAG1 is NULL or
DIAG1 in ( '250.0','250.1','250.2','250.3','250.4','250.5','250.6','250.7',
'250.8','250.9','250.00', --Diabetes (all ages)
'279', --Disorders involving immune mechanism
'282.7', --Hemoglobinopathies
'332.0','332.1', -- Parkinsons
'340', --Multiple sclerosis
'343.0','343.1','343.2','343.3','343.4','343.8','343.8',--Infantile cerebral palsy
'345.0','345.1','345.2','345.3','345.4','345.5','345.6','345.7','345.8','345.9',--Epilepsy
'414.0','414.00','414.01','414.02','414.03','414.04','414.05',--Chronic ischemic heart disease
'414.1','414.10','414.11','414.19',--Chronic ischemic heart disease
'414.8',--Chronic ischemic heart disease
'414.9',--Chronic ischemic heart disease
'424.0','424.1','424.2','424.3','424.9','424.90','424.91','424.99',--Valve disorders
'425', --Cardiomyopathy
'428.0','428.1','428.9', --CHF
'436', --Stroke
'491.0','491.1','491.2','491.20','491.21','491.8','491.9',--Chronic Bronchitis
'492.0', --Emphysema
'493.0','493.1','493.2','493.9',--Asthma (all ages)
'496', --Chronic Obstructive Pulmonary Disease
'515', --Pulmonary fibrosis
'582', --Chronic glomerulonephritis
'585', --Chronic renal failure
'v42.0', --Kidney transplants
'v42.1', --Heart transplants
'v42.6', --Lung transplants
'v42.7' --Liver transplants
) )
and (DIAG2 is NULL or
DIAG2 in ( '250.0','250.1','250.2','250.3','250.4','250.5','250.6','250.7',
'250.8','250.9','250.00', --Diabetes (all ages)
'279', --Disorders involving immune mechanism
'282.7', --Hemoglobinopathies
'332.0','332.1', -- Parkinsons
'340', --Multiple sclerosis
'343.0','343.1','343.2','343.3','343.4','343.8','343.8',--Infantile cerebral palsy
'345.0','345.1','345.2','345.3','345.4','345.5','345.6','345.7','345.8','345.9',--Epilepsy
'414.0','414.00','414.01','414.02','414.03','414.04','414.05',--Chronic ischemic heart disease
'414.1','414.10','414.11','414.19',--Chronic ischemic heart disease
'414.8',--Chronic ischemic heart disease
'414.9',--Chronic ischemic heart disease
'424.0','424.1','424.2','424.3','424.9','424.90','424.91','424.99',--Valve disorders
'425', --Cardiomyopathy
'428.0','428.1','428.9', --CHF
'436', --Stroke
'491.0','491.1','491.2','491.20','491.21','491.8','491.9',--Chronic Bronchitis
'492.0', --Emphysema
'493.0','493.1','493.2','493.9',--Asthma (all ages)
'496', --Chronic Obstructive Pulmonary Disease
'515', --Pulmonary fibrosis
'582', --Chronic glomerulonephritis
'585', --Chronic renal failure
'v42.0', --Kidney transplants
'v42.1', --Heart transplants
'v42.6', --Lung transplants
'v42.7' --Liver transplants
) )
and (DIAG3 is NULL or
DIAG3 in ( '250.0','250.1','250.2','250.3','250.4','250.5','250.6','250.7',
'250.8','250.9','250.00', --Diabetes (all ages)
'279', --Disorders involving immune mechanism
'282.7', --Hemoglobinopathies
'332.0','332.1', -- Parkinsons
'340', --Multiple sclerosis
'343.0','343.1','343.2','343.3','343.4','343.8','343.8',--Infantile cerebral palsy
'345.0','345.1','345.2','345.3','345.4','345.5','345.6','345.7','345.8','345.9',--Epilepsy
'414.0','414.00','414.01','414.02','414.03','414.04','414.05',--Chronic ischemic heart disease
'414.1','414.10','414.11','414.19',--Chronic ischemic heart disease
'414.8',--Chronic ischemic heart disease
'414.9',--Chronic ischemic heart disease
'424.0','424.1','424.2','424.3','424.9','424.90','424.91','424.99',--Valve disorders
'425', --Cardiomyopathy
'428.0','428.1','428.9', --CHF
'436', --Stroke
'491.0','491.1','491.2','491.20','491.21','491.8','491.9',--Chronic Bronchitis
'492.0', --Emphysema
'493.0','493.1','493.2','493.9',--Asthma (all ages)
'496', --Chronic Obstructive Pulmonary Disease
'515', --Pulmonary fibrosis
'582', --Chronic glomerulonephritis
'585', --Chronic renal failure
'v42.0', --Kidney transplants
'v42.1', --Heart transplants
'v42.6', --Lung transplants
'v42.7' --Liver transplants
) )
and (DIAG4 is NULL or
DIAG4 in ( '250.0','250.1','250.2','250.3','250.4','250.5','250.6','250.7',
'250.8','250.9','250.00', --Diabetes (all ages)
'279', --Disorders involving immune mechanism
'282.7', --Hemoglobinopathies
'332.0','332.1', -- Parkinsons
'340', --Multiple sclerosis
'343.0','343.1','343.2','343.3','343.4','343.8','343.8',--Infantile cerebral palsy
'345.0','345.1','345.2','345.3','345.4','345.5','345.6','345.7','345.8','345.9',--Epilepsy
'414.0','414.00','414.01','414.02','414.03','414.04','414.05',--Chronic ischemic heart disease
'414.1','414.10','414.11','414.19',--Chronic ischemic heart disease
'414.8',--Chronic ischemic heart disease
'414.9',--Chronic ischemic heart disease
'424.0','424.1','424.2','424.3','424.9','424.90','424.91','424.99',--Valve disorders
'425', --Cardiomyopathy
'428.0','428.1','428.9', --CHF
'436', --Stroke
'491.0','491.1','491.2','491.20','491.21','491.8','491.9',--Chronic Bronchitis
'492.0', --Emphysema
'493.0','493.1','493.2','493.9',--Asthma (all ages)
'496', --Chronic Obstructive Pulmonary Disease
'515', --Pulmonary fibrosis
'582', --Chronic glomerulonephritis
'585', --Chronic renal failure
'v42.0', --Kidney transplants
'v42.1', --Heart transplants
'v42.6', --Lung transplants
'v42.7' --Liver transplants
) )
and (DIAG5 is NULL or
DIAG5 in ( '250.0','250.1','250.2','250.3','250.4','250.5','250.6','250.7',
'250.8','250.9','250.00', --Diabetes (all ages)
'279', --Disorders involving immune mechanism
'282.7', --Hemoglobinopathies
'332.0','332.1', -- Parkinsons
'340', --Multiple sclerosis
'343.0','343.1','343.2','343.3','343.4','343.8','343.8',--Infantile cerebral palsy
'345.0','345.1','345.2','345.3','345.4','345.5','345.6','345.7','345.8','345.9',--Epilepsy
'414.0','414.00','414.01','414.02','414.03','414.04','414.05',--Chronic ischemic heart disease
'414.1','414.10','414.11','414.19',--Chronic ischemic heart disease
'414.8',--Chronic ischemic heart disease
'414.9',--Chronic ischemic heart disease
'424.0','424.1','424.2','424.3','424.9','424.90','424.91','424.99',--Valve disorders
'425', --Cardiomyopathy
'428.0','428.1','428.9', --CHF
'436', --Stroke
'491.0','491.1','491.2','491.20','491.21','491.8','491.9',--Chronic Bronchitis
'492.0', --Emphysema
'493.0','493.1','493.2','493.9',--Asthma (all ages)
'496', --Chronic Obstructive Pulmonary Disease
'515', --Pulmonary fibrosis
'582', --Chronic glomerulonephritis
'585', --Chronic renal failure
'v42.0', --Kidney transplants
'v42.1', --Heart transplants
'v42.6', --Lung transplants
'v42.7' --Liver transplants
) )
)
GO

SELECT * FROM [Fowler].[dbo].[Results_Temp] GO
SELECT * FROM [Fowler].[dbo].[Results_Final] GO

Please help!!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 13:51:43

your dial_code data all contains:
'340. '

but your diag_code criteria is:
'340'

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 13:59:46

quote:
Originally posted by TG

your dial_code data all contains:
'340. '

but your diag_code criteria is:
'340'

Be One with the Optimizer
TG

I fixed that but I'm getting 0 for my output results....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 13:59:48

and you diag2 data is: '999-1111'

but your criteria is like the other data ie: '340.05'

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 14:00:46

Try each AND section seperately to see where the non-selectivity is coming from.

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 14:00:52

Yeah, anything else could be in those other columns.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 14:03:51

quote:
Originally posted by TG

Try each AND section seperately to see where the non-selectivity is coming from.

Be One with the Optimizer
TG

Is there a way to check each Diag without having to list all the codes each time?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

cbot
Starting Member

3 Posts

Posted - 2005-08-19 : 14:04:17

Hi Xerxes!

Just taking a quick look at it (if that's even possible!

), you are inserting the value '340. ' into the DIAG_CODE field in the dbo.Diags table, yet you are looking for the value '340' in the WHERE clause. If you switch those values to match (or use another datatype that could be converted properly? I'm not sure what format the codes come in), I think you'll have better luck.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 14:04:50

Your long and winding code has left a pool of tears...I've seen that code before.
But your subject lines always lead me back...leads me to your code.

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 14:38:45

quote:
Originally posted by cbot

Hi Xerxes!

Just taking a quick look at it (if that's even possible! ), you are inserting the value '340. ' into the DIAG_CODE field in the dbo.Diags table, yet you are looking for the value '340' in the WHERE clause. If you switch those values to match (or use another datatype that could be converted properly? I'm not sure what format the codes come in), I think you'll have better luck.

Thanks.....but I'm stuck with this....

BTW....you're not a virus, are you? I've seen SDBot (and I don't think it stood for Steely Dan Bot neither!) enough times this week that I dreamed the Trend Micro logo last night!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 14:40:33

quote:
Originally posted by TG

Your long and winding code has left a pool of tears...I've seen that code before.
But your subject lines always lead me back...leads me to your code.

Be One with the Optimizer
TG

.....OK....."Why leave me standing here
Let me know the way.............."

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 14:54:35

Oh...were still waiting? Sorry.

- change your data ('340. ' to '340')
- change your data (' ' to null)
- change your criteria for diag2 (add '999-1111')

That should do it. But if that doesn't do it, Why don't we do it in the road?

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 14:54:51

I made changes and wanted to ensure that even if the Diag1 is null that it would take it. It does not. I'm really frustrated here. Here's a new copy of the input and code (shortened).

DROP TABLE [Fowler].[dbo].[Agents]
DROP TABLE [Fowler].[dbo].[Diags]
DROP TABLE [Fowler].[dbo].[Results_Temp]
DROP TABLE [Fowler].[dbo].[Results_Final]

CREATE TABLE [Fowler].[dbo].[Agents]
(Name varchar(20) NULL,Agt_Code varchar (4) NULL)
GO
INSERT Agents VALUES('Fred MERCURY ','MERC')
INSERT Agents VALUES('Venus DE MILO ','DEMI')
INSERT Agents VALUES('Earth WINDANDFIRE ','WIND')
INSERT Agents VALUES('Veronica MARS ','MARS')
INSERT Agents VALUES('Elmo P. JUPITER ','JUPI')
INSERT Agents VALUES('Gladys SATURN ','SATU')
INSERT Agents VALUES('Billy Bob URANUS,III','URAN')
INSERT Agents VALUES('Fowler J. NEPTUNE ','NEPT')
INSERT Agents VALUES('PLUTO Earnhardt ','PLUT')
GO
CREATE TABLE [Fowler].[dbo].[Diags]
(Agt_Code nvarchar (4) NULL,
Diag_Code nvarchar (8) NULL,
Diag1 nvarchar (8) NULL,
Diag2 nvarchar (8) NULL,
Diag3 nvarchar (8) NULL,
Diag4 nvarchar (8) NULL,
Diag5 nvarchar (8) NULL)
GO
INSERT Diags VALUES('MERC','340. ',' ','999-1111',' ',' ',' ')
INSERT Diags VALUES('DEMI','340. ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('WIND','340. ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('MARS',' ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('JUPI','340. ','491.8 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('SATU',' ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('URAN','340. ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('NEPT','340. ','414.05 ','999-1111',' ',' ',' ')
INSERT Diags VALUES('PLUT',' ','414.05 ','999-1111',' ',' ',' ')
GO

SELECT a.[Name], a.[Agt_Code] as a_Agt_Code, d.[Agt_Code], d.[Diag_Code],
d.[Diag1], d.[Diag2], d.[Diag3], d.[Diag4], d.[Diag5]
INTO [Fowler].[dbo].[Results_Temp]
FROM [Fowler].[dbo].[Agents] a,
[Fowler].[dbo].[Diags] d
WHERE a.Agt_Code = d.Agt_Code
GO

SELECT [Name], [a_Agt_Code], [Diag_Code], [Diag1], [Diag2], [Diag3], [Diag4], [Diag5]
INTO [Fowler].[dbo].[Results_Final]
FROM [Fowler].[dbo].[Results_Temp]
WHERE (
(DIAG_CODE in
( '250.0','250.1','250.2','250.3','250.4','250.5','250.6','250.7',
'250.8','250.9','250.00', --Diabetes (all ages)
'279.', --Disorders involving immune mechanism
'282.7', --Hemoglobinopathies
'332.0','332.1', -- Parkinsons
'340.', --Multiple sclerosis
'343.0','343.1','343.2','343.3','343.4','343.8','343.8',--Infantile cerebral palsy
'345.0','345.1','345.2','345.3','345.4','345.5','345.6','345.7','345.8','345.9',--Epilepsy
'414.0','414.00','414.01','414.02','414.03','414.04','414.05',--Chronic ischemic heart disease
'414.1','414.10','414.11','414.19',--Chronic ischemic heart disease
'414.8',--Chronic ischemic heart disease
'414.9',--Chronic ischemic heart disease
'424.0','424.1','424.2','424.3','424.9','424.90','424.91','424.99',--Valve disorders
'425.', --Cardiomyopathy
'428.0','428.1','428.9', --CHF
'436.', --Stroke
'491.0','491.1','491.2','491.20','491.21','491.8','491.9',--Chronic Bronchitis
'492.0', --Emphysema
'493.0','493.1','493.2','493.9',--Asthma (all ages)
'496.', --Chronic Obstructive Pulmonary Disease
'515.', --Pulmonary fibrosis
'582.', --Chronic glomerulonephritis
'585.', --Chronic renal failure
'v42.0', --Kidney transplants
'v42.1', --Heart transplants
'v42.6', --Lung transplants
'v42.7' --Liver transplants
) )
and (DIAG1 is NULL or
DIAG1 in ( '250.0','250.1','250.2','250.3','250.4','250.5','250.6','250.7',
'250.8','250.9','250.00', --Diabetes (all ages)
'279.', --Disorders involving immune mechanism
'282.7', --Hemoglobinopathies
'332.0','332.1', -- Parkinsons
'340.', --Multiple sclerosis
'343.0','343.1','343.2','343.3','343.4','343.8','343.8',--Infantile cerebral palsy
'345.0','345.1','345.2','345.3','345.4','345.5','345.6','345.7','345.8','345.9',--Epilepsy
'414.0','414.00','414.01','414.02','414.03','414.04','414.05',--Chronic ischemic heart disease
'414.1','414.10','414.11','414.19',--Chronic ischemic heart disease
'414.8',--Chronic ischemic heart disease
'414.9',--Chronic ischemic heart disease
'424.0','424.1','424.2','424.3','424.9','424.90','424.91','424.99',--Valve disorders
'425.', --Cardiomyopathy
'428.0','428.1','428.9', --CHF
'436', --Stroke
'491.0','491.1','491.2','491.20','491.21','491.8','491.9',--Chronic Bronchitis
'492.0', --Emphysema
'493.0','493.1','493.2','493.9',--Asthma (all ages)
'496.', --Chronic Obstructive Pulmonary Disease
'515.', --Pulmonary fibrosis
'582.', --Chronic glomerulonephritis
'585.', --Chronic renal failure
'v42.0', --Kidney transplants
'v42.1', --Heart transplants
'v42.6', --Lung transplants
'v42.7' --Liver transplants
) )

)
GO

SELECT * FROM [Fowler].[dbo].[Results_Temp] GO
SELECT * FROM [Fowler].[dbo].[Results_Final] GO

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 14:56:44

quote:
Originally posted by TG

Oh...were still waiting? Sorry.

- change your data ('340. ' to '340')
- change your data (' ' to null)
- change your criteria for diag2 (add '999-1111')

That should do it. But if that doesn't do it, Why don't we do it in the road? [Uh, because I'm not your type and that sort of thing is a felony in my state]

Be One with the Optimizer
TG

You said "Why don't we do it in the road?"
My reply [Uh, because I'm not your type and that sort of thing is a felony in my state]

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 15:04:12

Hey Jude, Your problem is covered by the 2nd item on my list. Merc will show up if you change your (nearly) empty strings to nulls or change your criteria from NULL to ' '.

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 15:09:26

TG

"I am the walrus, I am walrus, I am the eggHEAD -- koo-koo-kachoo!"

Thanks, that seems to resolve the problem....how'd you like my last posting? I laughed so hard I think I busted my spleen!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 15:14:53

>>"I am the walrus, I am walrus, I am the eggHEAD -- koo-koo-kachoo!"
Gazoontite

when I said "do it" of course I mean troubleshoot t-sql code. They lock you up for that where you're from?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-19 : 15:21:59

While re-living the joy of this post I noticed your question:
>>Is there a way to check each Diag without having to list all the codes each time?

Yes, if they were in their own table, you could just inner join to that table.

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 15:32:19

quote:
Originally posted by TG

>>"I am the walrus, I am walrus, I am the eggHEAD -- koo-koo-kachoo!"
Gazoontite

when I said "do it" of course I mean troubleshoot t-sql code. They lock you up for that where you're from?

Be One with the Optimizer
TG

I was just being funny......thanks for the Geshundeit!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 15:33:19

quote:
Originally posted by TG

While re-living the joy of this post I noticed your question:
>>Is there a way to check each Diag without having to list all the codes each time?

Yes, if they were in their own table, you could just inner join to that table.

Be One with the Optimizer
TG

Well, I meant aside from putting them ion a table is there any other way to arrange the query?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-19 : 16:24:02

Seriously, does anyone know of a better way to write this query?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources