SQL

(why do INSERT and UPDATE have to be so different to each other, anyway?)

CREATE PROCEDURE St_R334b
@Region varchar(12),
@fYear char(4),
@fMonthStart char(2),
@fMonthEnd char(2)
AS
BEGIN

DECLARE @Months int
DECLARE @TotalRev float

SELECT @Months = convert(int,@fMonthEnd) - convert(int,@fMonthStart) + 1

SELECT @TotalRev = Sum(t.MON_REV_C)
FROM tdrs_data AS t
WHERE t.fYear=@fYear AND (t.fMonth BETWEEN @fMonthStart AND @fMonthEnd)

SELECT c.CorporateID, c.CorporateName, c.Region,
Sum(t.MON_REV_C) AS mon_rev_c_sum,
Sum(t.MON_REV_C)/@TotalRev AS mon_rev_pct_of_ttl,
Sum(t.MON_REV_P) AS mon_rev_p_sum,
Sum(t.MON_PAX_C) AS mon_pax_c_sum,
Sum(t.MON_PAX_P) AS mon_pax_p_sum,
rev_class =
CASE
WHEN ((Sum(t.MON_REV_C)/@Months) * 12) > 3000000 THEN 1
WHEN ((Sum(t.MON_REV_C)/@Months) * 12) > 1000000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 500000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 100000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 3000000 THEN 'Over $3 MM'
WHEN ((Sum(t.MON_REV_C)/@Months) * 12) > 1000000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 500000 AND ((Sum(t.MON_REV_C)/@Months) * 12) 100000 AND ((Sum(t.MON_REV_C)/@Months) * 12)

What's the reason for using a sub-DOS CLI in 2004? Not that I mind using a command line--but why can't it act more like BASH? My problems are really more with Oracle than with SQL.

http://www.cygwin.com/

What is it good for
selecting stuff from places where things are like %other things.
-- dave225 (right.knewi...), November 5th, 2004 2:10 PM. (Dave225) (later) (link)

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

bother things? mother things? what what what?
-- Mr Noodles (infinitecow...), November 5th, 2004 2:11 PM. (Mr Noodles) (later) (link)

xpost

*RUNS AWAY*

SQL joins are one of the things that are a pain in the ass to learn to master, but once you can do them and understand why you'd want an inner join vs an outer join, you will be happy. And a huge geek.

fairly good joins 101: http://www.onjava.com/pub/a/onjava/2004/01/07/SQLJoins.html

Thank goodness I don't have to worry about this stuff any more.

Programmers make things different and sometimes just a little bit too complicated on purpose for job security.

1. The user puts a P against a record for which they want an invoice to be printed.
2. A Unix shell script runs (ending by calling itself to run 30 seconds later), which invokes...
3. A database procedure, which looks through all the records, searching for a P. When it finds one, it spools...
4. Another Unix shell script.
5. The script from step 2 moves the one from step 4, changes its permissions, and tidies it up with...
6. A small sed script.
7. The step 2 script then runs that from step 4, which calls...
8. One of 8 or 9 (I can't remember) Report Writer reports, which generates...
9. An output file.
10. Much as 5 - the step 2 script moves this, changes its permissions, and runs...
11. Another small sed script.
12. Finally, the resultant file is sent to the printer, and
13. The user sees an invoice appear on their printer.
14. Well, one more step: all the intermediate files are deleted by the one in step 2. This obviously made tracking problems and errors incredibly difficult.

This developer was frantic with worry: he couldn't imagine how he was going to convert this. I told him that we shouldn't try to do anything of the sort: that the only steps that mattered were 1 and 13. I developed a single report with flexible enough formatting to handle the differences between the 8 or 9 previous ones, and when the user committed that P in step 1, that triggered a call to this report, the output for which went directly to the printer. It took me less time to develop this than it had taken him to even understand the old method. The users commented on two differences: the new invoice looked slicker and more attractive, and it appeared much more quickly. The developer was delighted that he had something orders of magnitude easier to support. That's the kind of bad design systems sometimes end up with, until someone who likes simplicity and clarity gets at them.

The hump from v7 to v8 looked like a big one and I certainly seemed to miss my opportunity to train on the job with companies who were migrating from one version to the other by not bailing out of the pharma sector when I had the chance in summer/autumn 2000.

By the time I was in the job market a year later I was out of luck (general lack of interest in programming as a discipline meant I never kept abreast of new developments or tried to improve my skills, so if I didn't encounter something in a wage-earning environment or on a course I'd been sent on I wouldn't know it).

I think the difference is that in an OO program, persisting your information in a relational database requires that you write code to make it write to tables in the database (either that or use something like Hibernate or TopLink to do it for you). This isn't theoretically required, since the classes contain enough information to know how to persist themselves without any additional code; this is how OO databases work.

This sounds simple, but then you have to be very careful how you use the objects, since you may wind up accidentally firing off thousands of unexpected round-trips across the network to a database. I've never understood how taking a "pure" OO approach can work in a large production system without *considerable* additional complexity, i.e. complicated caching mechanisms and mechanisms to handle lost updates etc. This is not to say I'm not in favour of OO; I am; no better way to go about writing programs, but when it comes to persistence, you have to compromise in order that things perform.

At work recently, my boss asked me to test out the latest version of an application we're using. "It's supposed to be more efficient when working across a wide-area network like we're doing," he said.

When I asked what was so bad about the current version that could be improved - well. This app shows tables of data on-screen quite a lot. It turns out that for each view it displays, version 1 uses an individual SELECT statement for every single cell in the table on-screen. The new version is much more efficient, because they've cut it down to one SELECT per row.

A stored procedure to get all the rows sounds like a better solution, unless of course the query to one row depends on the results from the previous query.

You can get around these problems, but you have to program lots of smarts into the classes and guess about how the client program is going to use to objects, i.e. load tons of objects up at once if you think someone's going to loop across them. Then you've got the problem of making sure if someone's going to update something that someone else hasn't updated it in the background.

In addition to this, when you guess about how people will use your program, typically you get it wrong.

...i can piss about in access quite well, but that's about it, our student database runs on "advantage database" which uses some sort of, um, delphi thingummy...

Yesterday, I realised that in order to make all my lovely pretty new reports in Crystal Reports floating over Pivotal, I would have to figure out how the old reports were done. So I cracked the lid and stared into the Heart Of Darkness that is our current Oracle database.

I've read Oracle 8 for Dummies, but it hasn't really told me much about anything I didn't really know already (don't ever use an Intelligent Primary Key, like, duh) about database in general rather than Oracle in specific.

I've figured out what TOAD is. It's my schema browser and SQL compiler thingeygummy. And I'm wrestling with Crystal - both its reports bit and its clumbsy SQL generator.

A View is not what I thought it was - it's more like a permanent query. And Procedures and Packages are a bit like massive Macros. And inner/outer left/right Joins are like all the different one to many/one to on Relationships. But what's a Synonym?

And when am I ever going to get my own DBA login? And my own email? And that laptop they promised me...

I'm sure that MSSQL also has all that inner/outer and left/right join gubbins.

In my world, a View is more like a GUI thing - whether you're looking at the front end (the GUI input form) or the back end (data sheet/design mode). So I guess an Oracle View behaves like a table, but is really built like a Query. I don't even *know* how to get at the GUI bits of this database. (Well, OK, I do, through a DOS promt, that's how old and shite it is.) Because all I'm doing is the report managing.

We have someone here who's a fan of ACT - assuming the ACT I'm thinking of is the same one you are - but we're trying to wean him away from it.

(this where a Sheepfux0r pops up and says "I didn't know Kate was working in Canberra, ho ho ho")

I hate ACT. I hate it so much. It's not a database, it's an old rusty bucket. Yuck.

I'm just confused as heck by these Views. So, basically a View is a permanent query that you can do stuff with, which is two or more tables bashed together (by joins). Except one of the Joins has gone horribly wrong - it was supposed to be one to many, but it's become many to many, meaning I have double records in my report! Wrong, wrong, wrong! I thought the Crystal SQL was doing it, but it's actually in TOAD as well.

there is power in a UNION

CREATE OR REPLACE PACKAGE BODY Production_Report_Pkg IS

I told my boss that one of my web geek friends solved one of the worst ongoing problems and now no one seems to care if I'm on the interweb. (Thanks, Noodles!)

Where's the (+) love?

INSERT INTO love.train LT
SELECT people
FROM allover.world ALW
  WHERE ALW.hands *= LT.hand

(I'm sure I didn't do that right, where is my debugger to give me annoying messages.)

ach, whatever.

k/l... Indeed... ISAM, VSAM, GSAM, QSAM etc. People often use relational databases when it's not fit for purpose.

That's my geek tip for the day! You're welcome.

I never knew there was something more heavy on the acronyms than computing, but banking would be it.

Does anyone want a mortgage? I'm setting up new clients in the test database. ;-)

If you're not going to be storing any new data, but are only trying to analyse data already stored in other tables, then a View is probably what you are looking for.

However, I am not an Oracle oracle.

(xpost)

Whoops, sorry, programming all caps on there.

I've got the info from one table in, filtered by date - now I have to join the other two tables. This is going to be hard with those crazy Oracle joins!

I'm very proud. It's my first Oracle thing from scratch.

Sigh. No one cares. Even the bloke who asked for the report hasn't commented on it.

CREATE OR REPLACE VIEW V_JUNE05_PRODUCTS
( STATUS,
BSOC,
BSOC_SUB,
INT_DATE,
CE_NARR,
CE_NARREXT,
CASE_NUM,
CN_NARR,
CN_NARREXT )
AS SELECT
C.STATUS,
C.BSOC,
C.BSOC_SUB,
C.INT_DATE,
CE.NARR,
CE.NARR_EXTENDED,
C.CASE_NUM,
CN.CHQ_NARR,
CN.EXTRA_NARR
FROM CASES C, CODES_EXTENDED CE, CODES_NARR CN
WHERE C.INT_DATE >= '01-JUN-05'
AND C.BSOC = CE.MAIN_CD (+)
AND C.BSOC_SUB = CE.SUB_CD (+)
AND C.BSOC = CN.MAIN_CD (+)
AND C.BSOC_SUB = CN.SUB_CD (+)
AND CE.CLASS (+) = '1'

(I said I didn't know much about Oracle)

It's an Oracle foible that it took me ages to figure out.

I think I get my head around that.

aw, it has your nose!

Today we have a Proper Oracle Person to explain what I've been doing wrong. Hurrah.

http://www.bbc.co.uk/cgi-perl/whatson/prog_parse.cgi?FILENAME=20050629/20050629_1100_49700_1492_30

Ed: I heard a trailer for that during Today this morning - it sounds rather interesting.

Heh. My tech support guy is a dead ringer for Captain Picard. It's quite scary, actually!

man, I am *so* putting Oracle on my resume after this...

I'm having a lot of trouble getting my COUNT function to work.

See, what I want to do is not count the total number of records, or the discrete values - what I want to do is count the number of a *specific* value.

I have a Boolean field - I want a subtotal COUNT that counts the number of "FALSE" returns within a group.

How the heck do I do this? there's nothing either in my SQL dictionary or my Dummies book which explains it.

I'm using the useless GUI cause they won't give me access to anything else. :-(

How the heck do I do this?

hmm... i would've assumed CR could do more. that's pretty ridiculous.
m.

The only things it will let me do are these:

Count (fld, condFld, cond)

where fld is the field, condFld is what you group it by, and cond another thing you group it by. (This is making no sense to me - it's like subtotal in Excel, telling the thing where to put the subtotals in.)

Crystal Reports

I think I'm going to have to create a whole new query and a whole new subreport. Which seems insane for such a minor thing.

use HAVING after the group statement.

e.g. HAVING COUNT(1) > 4

Stupid Microsoft Query.

I wish you could manifest Inner Joins within a left outer. Instead you have to create a view and link to that!

In my oracle database no one else ever goes in there, so I can create all the views I want.

In a nutshell, we've got data for our organisation's contacts and such like hosted remotely on an SQL database. We've made significant changes to it recently, and wanted to upload new tables. To cut a long story short, our data hosts say that their insurance indemnity prevents them from assisting us, which means we can't upload the data we need to upload.

It appears we have two choices, neither of which I'm familiar with how to do:

1) Our database is a SQL Server one, so we need to create a new database in that, then either send our host company the raw datafiles (detached database) or a backup (.bak file) of the contents.

2) Setup a DTS job to import the records directly onto the SQL server from our local server

I'm afraid that I'm utterly stumped on both of these, and need some help. Can anyone advise, or even better, be able to help directly?

Yours hopefully.

I would give more explicit steps but I'm kind of going off dim memories here and I'm late for work.

The big problem *I* had was that the wizard didn't know how to take account of foreign key relationships. So, the package it created wouldn't run, because it would try to import data into the new tables in the wrong order. The only way to get around it was to use the wizard's output as a starting point, make notes on where it failed, then use the DTS package editor to add dependancy data to the package - "do not copy B until A is complete" and so on. The DTS editor is graphical, so this is a matter of dragging arrows around on the screen.

xpost: as Dan says, detaching and transferring the data file is probably easier for what you're doing.

- We had an access database
- That became an SQL one
- I copied the tables into Excel, did some clean up on them and want to put them back into the SQL databse.

The hosting company said nope, they couldn't write over the old tables with the new ones as their insurance didn't indemnify them for this. This means I have 4 tables in Excel which I want to put back into our SQL database / create a brand new database with. This is what I have no idea how to do - any tips? Better still, would anyone on here be able to do it for me? Happy to pay that special someone...

Just create an Excel connection to the table, and Data Transformation Task to the SQL Server database you want to copy it to, bob's your uncle.

Can I just create a new set of tables on Enterprise Manager and import the data from excel tables and then point the Access database at those new tables instead, and the leave the old tables there to slowly degrade, accuracy wise?

who set up the original tables in the first place?

To Ken's - it is lost in the mists of time

Alternatively, do something like what Ken suggested: make new tables with backup copies of the old data, then delete the actual tables' contents and replace with the new data.

OK so I usually use SQL Server 2000 so am not used to dealing with MS Query (in Excel)

How do I use date functions in Query??

I want to do something like this:

SELECT * FROM [TABLE]
WHERE order_date = getdate()

But Query won't accept getdate, now, date, or anything else I can think of. Is it just too fucking shit to deal with date functions? There's nothing in Help about it.

I have a workaround using a parameter, i.e.

SELECT * FROM [TABLE]
WHERE order_date = ?

and then put =today() in a cell on teh Excel sheet and use that as the parameter, but that can't be the best way to do it, surely??

?

-- Jaq (js...), February 23rd, 2006 3:09 PM. (Jaq) (link)

I'm now attempting to use it and it's fucking horrible. I didn't have too much trouble using DTS apart from a couple of bugs here and there, but this SUCKS.

It's not even stable. I was copying 700,000 rows from one server to another, and it ended up inserting the data from 500,000 of them into one row. I ended up with a varchar field of length 512,000,000. I've since worked out this happens when the server is running low on memory, so I can't run anything else when an SSIS package is running. Great.

Today I found that the SQL Server Destination object is basically useless, as trying to copy a datetime field from OLE DB Source (also SQL Server) to SQL Server Destination will fail with a type conversion error, even though the source & destination columns are both datetime. You have to use an OLE Destination DB object for it to work.

And apparently the migration tool to upgrade DTS to SSIS is so unreliable even Microsoft have advised not to use it.

ARGH. I know it's a Microsoft product, so I shouldn't be surprised, but this is SO BAD it defies all expectation. I'd rather just write everything in stored procedures than use this, but I don't think my manager will agree unfortunately, so I'm stuck with it :'(

declare @cmd nvarchar(4000)
set @cmd = 'bcp "SELECT TOP 1 Photo FROM dbo.ImageWarehouse" queryout "c:datatest2.jpg" -T -n'

exec xp_cmdshell @cmd

hey so like.. i'm running a website on drupal and somehow ran out of memory on my virtual machine - bringing it to its knees! are there any simple diagnostic tools i can try to see if particular queries are being extra hoggy?

never used drupal but it's a php framework, right? what DB are you running under it? mysql?

i spent WAY too much time today setting up the MySQL-python-1.2.2 lib. I love open source- build, error. google error. fix file. build. get new error. google. fix new error. build. reads config wrong. fix config. build. repeat 3 more times. finally install. 25 minutes down the drain. on the other hand, it's a nice lib once it's running.

getting ruby on rails to work with mysql on Leopard was the delightful way I spent my entire day.

My best is that it's not MySQL going badzooks, Tracer. Use top or a similar tool to track down who's hogging memory at the process level. That'll at least allow you to rule out MySQL.

I was trying to re-install mysql because I'd forgotten the root password, and was trying to follow the instructions <a href="http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html";>here</a> except that at the step where I'm supposed to enter "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld.exe", something funky happened. Now, every time I boot up my computer, mysqld.exe crashes. I can at least bring up the mysql console, though I can't do anything because I don't have the password. A regular uninstall/reinstall doesn't fix things. Is there a way to unfubar things, or at least start over with a clean uninstall?

Nuke the database directory. Also, you don't need to reinstall to reset the root password on MySQL.

Database directory being where Mysql is installed?

Not necessarily. The directory where all the database files are kept. I don't know where this is on Windows.

running Mysql on windows is just wrong. but yeah, one of your endless config files should tell you where you have that directory.