Easier Way To Write SQL Statement

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

SQL Server Development (2000)

Easier Way To Write SQL Statement

Author

Topic

jtrapat1
Starting Member

43 Posts

Posted - 2002-11-12 : 14:44:15

I have an sql query that I think can be written easier.
It's a complex join on three tables and I'm getting an sql error message of -305 since this statement has a problem returning NULL values.
---------------------------------------
SELECT MBR_SUB.mbr_id, MBR_SUB.last_name,
MBR_SUB.first_name,MBR_SUB.mi,
DEL_SUB.del_id, DEL_SUB.del_name,
bp73.seq, bp73.priority
FROM nysa.bp73t_req_by bp73

INNER JOIN
(SELECT bp70.budget_yr,bp70.mbr_id,
bp70.last_name,bp70.first_name,bp70.mi
FROM nysa.bp70t_member bp70
WHERE bp70.budget_yr = :bp70.budget_yr)
AS MBR_SUB
ON bp73.budget_yr = MBR_SUB.budget_yr
AND bp73.mbr_id = MBR_SUB.mbr_id

LEFT OUTER JOIN
(SELECT bp71.budget_yr,bp71.del_id,bp71.del_name
FROM nysa.bp71t_delegation bp71
WHERE bp71.budget_yr = :bp71.budget_yr)
AS DEL_SUB
ON bp73.budget_yr = DEL_SUB.budget_yr
AND bp73.del_id = DEL_SUB.del_id

---------------------------------------------------------

Thanks in Advance
John

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-12 : 17:37:35

HI John

You might get a better response if you posted some DDL (Create table statements) and some insert statements for data. I'm sure there a bunch of people that would help, but no one wants to try to guess what is going on there.

Damian

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-12 : 17:55:23

This might be a bug

WHERE bp70.budget_yr = bp70.budget_yr

and again

WHERE bp71.budget_yr = bp71.budget_yr

What are you trying to do?

Sam

jtrapat1
Starting Member

43 Posts

Posted - 2002-11-13 : 09:31:27

Sorry for the lack of detail.
I'll write back with more info soon but if anyone could help in the meantime, here are table descriptions.
I just thought this subquery could be written cleaner.
Here's the insert statement I want to use when I get the results from this query. Most of them are the values from the 73 table:
-----------------------------------------------------
EXEC SQL
INSERT INTO nysa.bp73t_req_by
(budget_yr, adds_id,
mbr_id, del_id,
seq, priority,
edit_userid, edit_tstamp)
VALUES
(:bp73.budget_yr, :bp73.adds_id,
:bp73.mbr_id, :bp73.del_id,
:bp73.seq, :bp73.priority,
:bp73.edit_userid, CURRENT TIMESTAMP);
--------------------------------------------------------------
And, here are the create statements that are giving me the NULL value errors.
---------------------------------------------------
CREATE TABLE "DTSYS "."BP70T_MEMBER" (
"BUDGET_YR" SMALLINT NOT NULL ,
"MBR_ID" SMALLINT NOT NULL ,
"LAST_NAME" CHAR(15) NOT NULL ,
"FIRST_NAME" CHAR(20) NOT NULL ,
"MI" CHAR(1) NOT NULL ,
"PARTY" CHAR(1) NOT NULL ,
"ALLOC" INTEGER NOT NULL ,
"STATUS" CHAR(1) NOT NULL ,
"EDIT_USERID" CHAR(8) NOT NULL ,
"EDIT_TSTAMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP)
IN "LSBPS70" ;
-----------------------------------------------------
CREATE TABLE "DTSYS "."BP71T_DELEGATION" (
"BUDGET_YR" SMALLINT NOT NULL ,
"DEL_ID" SMALLINT NOT NULL ,
"DEL_NAME" CHAR(20) NOT NULL ,
"ALLOC" INTEGER NOT NULL ,
"EDIT_USERID" CHAR(8) NOT NULL ,
"EDIT_TSTAMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP)
IN "LSBPS71" ;
---------------------------------------------------------
CREATE TABLE "DTSYS "."BP73T_REQ_BY" (
"BUDGET_YR" SMALLINT NOT NULL ,
"ADDS_ID" INTEGER NOT NULL ,
"MBR_ID" SMALLINT NOT NULL ,
"DEL_ID" SMALLINT NOT NULL ,
"PRIORITY" CHAR(1) NOT NULL WITH DEFAULT ' ',
"SEQ" SMALLINT NOT NULL WITH DEFAULT 0,
"EDIT_USERID" CHAR(8) NOT NULL ,
"EDIT_TSTAMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP)
IN "LSBPS73" ;
----------------------------------------------------

Thanks
John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources