IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Left join.
ie. return nulls if there's no matching record.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
Expand Edited by admin Jan. 31, 2004, 10:27:02 PM EST
New Oy
The PGSQL syntax for this feature isn't nearly as nice to use.

I managed to make the one example above work based on an example in the docs. I ended up with

FROM BILLINGINVOICE LEFT JOIN (TaxInfo LEFT JOIN PaymentInfo ON (TaxInfo.INVOICEID = PaymentInfo.INVOICEID)) ON (BillingInvoice.INVOICEID = TaxInfo.INVOICEID);

as equivalent syntax to

FROM BILLINGINVOICE, TAXINFO, PAYMENTINFO
WHERE BILLINGINVOICE.INVOICEID = TAXINFO.INVOICEID(+)
AND BILLINGINVOICE.INVOICEID = PAYMENTINFO.INVOICEID(+)

Which looks kinda convoluted to my mind. Worse, I've just run across:

FROM EMPLOYER , BILLINGUPCOMINGINVMANUALADJ, EMPLOYERMISCINFO, BILLINGCONTACT ,
ROLE, BROKER, SETOFEMPLOYERS,
(SELECT EMPLOYERID, COUNT(*) AS ACTIVEEENO FROM EMPLOYMENT WHERE EMPLOYMENT.STATUS <> 1
GROUP BY EMPLOYERID HAVING COUNT(*) > 0) AS ACTIVEEES
WHERE EMPLOYER.FEDERALTAXID = EMPLOYERMISCINFO.EMPLOYERID
AND EMPLOYER.FEDERALTAXID = SETOFEMPLOYERS.EMPLOYERTAXID
AND BROKER.BROKERID = SETOFEMPLOYERS.BROKERID
AND EMPLOYERMISCINFO.PAYER = BILLINGCONTACT.CONTACTPAYER(+)
AND ROLE.ID = EMPLOYER.FEDERALTAXID
AND ROLE.FLAG = 1 AND ROLE.ROLE = 8
AND EMPLOYERMISCINFO.EMPLOYERID = BILLINGCONTACT.CONTACTID(+)
AND EMPLOYER.FEDERALTAXID = BILLINGUPCOMINGINVMANUALADJ.EMPLOYERID(+)
AND EMPLOYER.FEDERALTAXID = ACTIVEEES.EMPLOYERID(+)
AND EMPLOYER.FEDERALTAXID IN (SELECT EMPLOYERID FROM PineyCreek.EMPLOYERSTOBEBILLED)
ORDER BY EMPLOYER.FEDERALTAXID

I have no idea where to start with such a complex join. (Other people's) SQL is hard.



.....To the future or to the past, to a time when thought is free, when men are different from one another and do not live alone -- to a time when truth exists and what is done cannot be undone:
.....From the age of uniformity, from the age of solitude, from the age of Big Brother, from the age of doublethink -- greetings!

     -- George Orwell, 1984
New Ick.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New Way
There are about 5 levels of subqueries above that. FWIW, this stuff was written in India.




.....To the future or to the past, to a time when thought is free, when men are different from one another and do not live alone -- to a time when truth exists and what is done cannot be undone:
.....From the age of uniformity, from the age of solitude, from the age of Big Brother, from the age of doublethink -- greetings!

     -- George Orwell, 1984
New Looks to me like it was generated from icky tools.
Blanchard's Law at work again. ;)
New Naah; we manually write shit like that all the time. :-(
New Yeah - a tool would use consistent capitalization



.....To the future or to the past, to a time when thought is free, when men are different from one another and do not live alone -- to a time when truth exists and what is done cannot be undone:
.....From the age of uniformity, from the age of solitude, from the age of Big Brother, from the age of doublethink -- greetings!

     -- George Orwell, 1984
New Just start breaking it down.
Temp tables, whatever. That kind of thing is way too complex to live.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New That's the plan
The thing actually defines a view (shudder).



.....To the future or to the past, to a time when thought is free, when men are different from one another and do not live alone -- to a time when truth exists and what is done cannot be undone:
.....From the age of uniformity, from the age of solitude, from the age of Big Brother, from the age of doublethink -- greetings!

     -- George Orwell, 1984
New Yeah, 'zackly, we use views like that all the time. :-(
New Relax; you'll get the hang of it pretty quickly.
Todd Blanch-es with fear:
Worse, I've just run across:

FROM EMPLOYER , BILLINGUPCOMINGINVMANUALADJ, EMPLOYERMISCINFO, BILLINGCONTACT ,
ROLE, BROKER, SETOFEMPLOYERS,
(SELECT EMPLOYERID, COUNT(*) AS ACTIVEEENO FROM EMPLOYMENT WHERE EMPLOYMENT.STATUS <> 1
GROUP BY EMPLOYERID HAVING COUNT(*) > 0) AS ACTIVEEES
WHERE EMPLOYER.FEDERALTAXID = EMPLOYERMISCINFO.EMPLOYERID
AND EMPLOYER.FEDERALTAXID = SETOFEMPLOYERS.EMPLOYERTAXID
AND BROKER.BROKERID = SETOFEMPLOYERS.BROKERID
AND EMPLOYERMISCINFO.PAYER = BILLINGCONTACT.CONTACTPAYER(+)
AND ROLE.ID = EMPLOYER.FEDERALTAXID
AND ROLE.FLAG = 1 AND ROLE.ROLE = 8
AND EMPLOYERMISCINFO.EMPLOYERID = BILLINGCONTACT.CONTACTID(+)
AND EMPLOYER.FEDERALTAXID = BILLINGUPCOMINGINVMANUALADJ.EMPLOYERID(+)
AND EMPLOYER.FEDERALTAXID = ACTIVEEES.EMPLOYERID(+)
AND EMPLOYER.FEDERALTAXID IN (SELECT EMPLOYERID FROM PineyCreek.EMPLOYERSTOBEBILLED)
ORDER BY EMPLOYER.FEDERALTAXID

I have no idea where to start with such a complex join. (Other people's) SQL is hard.
Looks kind of like a star schema(1) to me, with the EMPLOYER table at the center of everything. OK, sligthly "snowflakey"(1) in a couple of places, where BROKER is linked in via SETOFEMPLOYERS(2) and BILLINGCONTACT via EMPLOYERMISCINFO(2). Like this:
                +------------------+\n                | BILLINGUPCOMING  |\n                |  INVMANUALADJ    |\n                +------------------+\n                         |\n+---------+   +-----------------------+   +---------+\n|  ROLE   |---|        EMPLOYER       |---|SELECT...|\n+---------+   +-----------------------+   +---------+\n                   |             |\n              +---------+   +---------+\n              | SETOF   |   |EMPLOYER |\n              |EMPLOYERS|   |MISCINFO |\n              +---------+   +---------+\n                   |             |\n              +---------+   +---------+\n              | BROKER  |   | BILLING |\n              +---------+   | CONTACT |\n                            +---------+
(Disclaimer: AFAICS, at a quick glance.)

In my experience, the trick (well, not much of a "trick" really, but still...) is, just look at which table name appears most often in the joining-subclauses of the WHERE clause, and unravel it from there; next thing you look for is, which subclauses *doesn't* that table name appear in, then you know one of the tables in that subclause is indirectly joined to the "central-master" table.

HTH!



(1): For those who haven't seen this before, "star" and "snowflake" are data warehousing terminology.

(2): Nice of them to have one of those one way around, and the other the other, BTW. Hey, we don't want to make this too easy to read, so just any schmuck could understand it, now do we? ;-)


   [link|mailto:MyUserId@MyISP.CountryCode|Christian R. Conrad]
(I live in Finland, and my e-mail in-box is at the Saunalahti company.)
You know you're doing good work when you get flamed by an idiot. -- [link|http://www.theregister.co.uk/content/35/34218.html|Andrew Wittbrodt]
New I'm saving those for dessert
There are still a bunch of functions to port and some simpler views. I'm going to save the ball busters like that one for last (which might be this evening but hey...).

Thanks for all the insight.



.....To the future or to the past, to a time when thought is free, when men are different from one another and do not live alone -- to a time when truth exists and what is done cannot be undone:
.....From the age of uniformity, from the age of solitude, from the age of Big Brother, from the age of doublethink -- greetings!

     -- George Orwell, 1984
New Dumb question on the second statement
Isnt that statement a pile of mollases to run? Guarrantied to choke the DB to death? Nowadays I avoid SQL like the plague but back when I was programming the idea was striped queries. It always seemed faster to get a single result from an indexed table and use that as a param to get the next result. As compared to joins as noted below a 486 would just choke with all cpu and memory full. Did all that change with faster hardware? I still think it would be faster to scan 10 individual tables than scan 10 joined tables.
thanx,
bill
same old crap, con artists ripping off fools. Ah, hell, Catholic Church it start off that way. They All do. Jesus probably had three walnut shells one pea, then he's dead and can't be questioned,
Gabriel Dupre

questions, help? [link|mailto:pappas@catholic.org|email pappas at catholic.org]
New Then, in the Bronze Age, the Query Optimizer was invented...
New Which typically chokes with too many joins.
Regards,

-scott anderson

"Welcome to Rivendell, Mr. Anderson..."
New Yeah, well; whaddaya expect, from Bronze-Age technology? :-)
New Depends on the optimizer...
run a show command and you'll see what it's up to.

Joins aren't usually the problem - table scans are. Joins are simple (particularly when the indexes are properly used), because with a good optimizer, the indexes are joined before the entire row is gotten.
New Left join in the new SQL standard syntax
The (+) is the older syntax. But with Oracle 9, they started using the LEFT JOIN/RIGHT JOIN stuff as well.
     New Question - what does this (oracle) syntax mean? - (tuberculosis) - (18)
         Left join. - (admin) - (17)
             Oy - (tuberculosis) - (16)
                 Ick. -NT - (admin) - (7)
                     Way - (tuberculosis) - (6)
                         Looks to me like it was generated from icky tools. - (FuManChu) - (2)
                             Naah; we manually write shit like that all the time. :-( -NT - (CRConrad) - (1)
                                 Yeah - a tool would use consistent capitalization -NT - (tuberculosis)
                         Just start breaking it down. - (admin) - (2)
                             That's the plan - (tuberculosis) - (1)
                                 Yeah, 'zackly, we use views like that all the time. :-( -NT - (CRConrad)
                 Relax; you'll get the hang of it pretty quickly. - (CRConrad) - (1)
                     I'm saving those for dessert - (tuberculosis)
                 Dumb question on the second statement - (boxley) - (4)
                     Then, in the Bronze Age, the Query Optimizer was invented... -NT - (CRConrad) - (2)
                         Which typically chokes with too many joins. -NT - (admin) - (1)
                             Yeah, well; whaddaya expect, from Bronze-Age technology? :-) -NT - (CRConrad)
                     Depends on the optimizer... - (Simon_Jester)
                 Left join in the new SQL standard syntax - (Simon_Jester)

How very 1996.
156 ms