SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

I noticed that there was no general mega-thread for SQL related questions, and I happen to have a small one, so I figure I may as well create a general thread for stupid/small questions having to deal with any flavor of SQL. Here's mine:

The boss says that there is a bug in the where clause of the correlated subquery at the end having to do with the date, but I'm not seeing it.

code:

select pmg.pin as pin, pmg.tlid as primary_tlid, tt1.TIN as primary_tin, 
	pmgs.tlid as secondary_tlid, tt2.TIN as secondary_tin
from pin_mi_go pmg
inner join tblLocationTIN tlt1 on pmg.tlid = tlt1.tlid
inner join tblTIN tt1 on tlt1.TID = tt1.TID
inner join pin_mi_go_secondary pmgs on pmg.pin = pmgs.pin
inner join tblLocationTIN tlt2 on pmgs.tlid = tlt2.tlid
inner join tblTIN tt2 on tlt2.TID = tt2.TID
where tt1.TIN <> tt2.TIN
and exists (select TLID 
	from tblProvLicSpecLoc tplsl 
	where (tplsl.tlid = pmg.tlid 
	or tplsl.tlid = pmgs.tlid) 
	and (tplsl.DelDate < GETDATE()
	or tplsl.DelDate is null))

Please help.

:siren:

ATTENTION: PLEASE USE THE INSTANT SQL FORMATTER :siren:

this handy tool will format your SQL code into something remotely readable. Just look what it did to the above chunk of garbage:

code:

SELECT pmg.pin   AS pin,
       pmg.tlid  AS primary_tlid,
       tt1.tin   AS primary_tin,
       pmgs.tlid AS secondary_tlid,
       tt2.tin   AS secondary_tin
FROM   pin_mi_go pmg
       INNER JOIN tbllocationtin tlt1
         ON pmg.tlid = tlt1.tlid
       INNER JOIN tbltin tt1
         ON tlt1.tid = tt1.tid
       INNER JOIN pin_mi_go_secondary pmgs
         ON pmg.pin = pmgs.pin
       INNER JOIN tbllocationtin tlt2
         ON pmgs.tlid = tlt2.tlid
       INNER JOIN tbltin tt2
         ON tlt2.tid = tt2.tid
WHERE  tt1.tin <> tt2.tin
       AND EXISTS (SELECT tlid
                   FROM   tblprovlicspecloc tplsl
                   WHERE  (tplsl.tlid = pmg.tlid
                            OR tplsl.tlid = pmgs.tlid)
                          AND (tplsl.deldate < Getdate()
                                OR tplsl.deldate IS NULL))

uh zip zoom fucked around with this message at 01:01 on Nov 2, 2007

# ¿ Nov 1, 2007 19:52

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 17:34

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

uh zip zoom posted:

I noticed that there was no general mega-thread for SQL related questions, and I happen to have a small one, so I figure I may as well create a general thread for stupid/small questions having to deal with any flavor of SQL. Here's mine:

The boss says that there is a bug in the where clause of the correlated subquery at the end having to do with the date, but I'm not seeing it.

Okay, apparently the problem was not so much my query but my lack of understanding of what the databases used for and how and why data is entered into it, so basically I needed to find dates that were greater than today, not dates that were smaller than, so it should have been:

code:

select pmg.pin as pin, pmg.tlid as primary_tlid, tt1.TIN as primary_tin, 
	pmgs.tlid as secondary_tlid, tt2.TIN as secondary_tin
from pin_mi_go pmg
inner join tblLocationTIN tlt1 on pmg.tlid = tlt1.tlid
inner join tblTIN tt1 on tlt1.TID = tt1.TID
inner join pin_mi_go_secondary pmgs on pmg.pin = pmgs.pin
inner join tblLocationTIN tlt2 on pmgs.tlid = tlt2.tlid
inner join tblTIN tt2 on tlt2.TID = tt2.TID
where tt1.TIN <> tt2.TIN
and exists (select TLID 
	from tblProvLicSpecLoc tplsl 
	where (tplsl.tlid = pmg.tlid 
	or tplsl.tlid = pmgs.tlid) 
	and (tplsl.DelDate > GETDATE()
	or tplsl.DelDate is null))

but still, people should throw their SQL questions that don't deserve their own thread in here.

# ¿ Nov 1, 2007 20:36

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

ashgromnies posted:

It's kind of hard to identify the issue with such limited information.

What is the bug, exactly? Like... what goes wrong? The query doesn't work at all, or it returns unintended results? How are the results unintended?

Those are some uuugly column and table names by the way

the query returned unintended results, because I misunderstood the purpose of that column.

I considered using cast to make getdate() a smalldatetime like the other side of the compare, but that really didn't have any effect.

Yes, the naming conventions are very confusing, and it's kind of a mess that I've inherited, but I've only been there a month and the position is entry-level. Sorting all of that out is one of my long long long term goals.

# ¿ Nov 1, 2007 21:07

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

m5 posted:

Personally I think it's risky to perform relational comparisons on fields that might be null. That's what COALESCE and ISNULL (MSSQL) and CASE are for.

SQL does not short-circuit logical expressions, or, at least, MSSQL doesn't.

Also, for your SQL reformatting needs: the online SQL formatter.

thanks for the heads up on that link. I'll bookmark it.

Hopefully other people get from the thread title that this is intended to be a general small questions thread for SQL.

# ¿ Nov 2, 2007 00:56

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

I'm trying to use Microsoft Excel to build a script to insert spreadsheet data (and not the headers) into an MS SQL database, unfortunately I don't know jack about (read: forgot the first time I was told) the syntax involved with creating a string with Excel. Help?

# ¿ Nov 27, 2007 01:29

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

in MS SQL, I'm trying to alter a nullable integer column so that it's not null with a default value of zero.

How could this best be done using a constraint (since I believe that's the only way to go about it, since I don't believe you can add default values when altering a column)?

# ¿ May 27, 2008 22:28

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

Xae posted:

Update the Column, set =0 where col is null
Create a Before Insert Row Trigger
Add Constraint

suppose a user adds a record and doesn't include a value for the column that has the default value, will that field still get said default value?

# ¿ May 27, 2008 23:34

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 17:34

uh zip zoom: May 28, 2003; Sensitive Thugs Need Hugs

Alex007 posted:

This is what you're looking for:

yes, yes it is. Thank you very much.

# ¿ May 28, 2008 00:11

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'