January 28, 2011 at 5:49 am
I'm a total SQL newbie. I do this stuff kinda for fun to help my work out. I'm a Pharmacist and work at a hospital, so coding is outta my league.
Given that! This code does work, but I would like to do 2 things:
1- Can I have it written more 'pretty'? AKA, the results are like
a(1,2,3)b(1,2,3)c(1,2,3) with null rows all over because there are 9 columns. Is there a way to make it like this:
A(1,2,3)
B(1,2,3)
C(1,2,3) where the similar columns are on top of eachother and there are no redundant rows?
2- Can this code be simplified with some sort of 'combined' OR statement where it is OR (1,2,3,) instead of ((1) OR (2) OR (3))? Seems like that would be nice. Thanks...
select
m.DrugID,m.OrderSetID,m.OrderID,c.DrugID,c.OrderSetID,c.OrderID,
a.DrugID,a.OrderSetID,a.OrderID
from DOeOrderSetPhaPcsIvAdditives a
full join DPhaDrugData d on a.DrugID = d.DrugID
full join DOeOrderSetPhaPcsIvCarriers c on a.DrugID = c.DrugID
full join DOeOrderSetPhaPcsMeds m on a.DrugID = m.DrugID
where
(
c.DrugID in
(select d.DrugID from DPhaDrugData d
where d.UsageType like '%Non%'
and Active = 'Y')
OR
a.DrugID in
(select d.DrugID from DPhaDrugData d
where d.UsageType like '%Non%'
and Active = 'Y')
OR
m.DrugID in
(select d.DrugID from DPhaDrugData d
where d.UsageType like '%Non%'
and Active = 'Y')
)
January 28, 2011 at 6:06 am
I would almost certainly do this using Union All instead of Joins.
select Col1, Col2, Col3
from TableA
union all
select Col1, Col2, Col3
from TableB
union all
... and so on through the tables...
You can add a column that indicates which table any given row comes from.
select Col1, Col2, Col3, 'TableA' as Table
from TableA
union all
select Col1, Col2, Col3, 'TableB' as Table
from TableB
union all
... and so on through the tables...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2011 at 11:41 pm
Thanks for the 'union all' piece. But I don't know how to use it exactly. I can get all the info in 3 columns, so that's nice! But now I don't know how to run a query against it as it's own table. I do not have rights to 'create table' or modify anything, just query. And when I run this:
select x.DrugID,x.OrderSetID,x.OrderID
from
(select
DrugID,OrderSetID,OrderID
from DOeOrderSetPhaPcsMeds
union all
select
DrugID,OrderSetID,OrderID
from DOeOrderSetPhaPcsIvAdditives
union all
select
DrugID,OrderSetID,OrderID
from DOeOrderSetPhaPcsIvCarriers) x
where EXISTS (select DrugID from DPhaDrugData
where UsageType like '%Non%'
and Active = 'Y')
I don't get a difference with the 'where exists' statement. If it's in or out I get the same results. How can I query a 4th table (from the 'where exists' portion) against the 3 tables that are now unioned? Thanks.
January 29, 2011 at 5:39 am
Oops, I got it! Don't use EXISTS I guess. If I end the query with
"where x.DrugID in (etc.)" it works!
Yay, good stuff, nice and pretty now. I won't have to use excel to change a thing... Thanks.
January 29, 2011 at 9:28 am
Here is another way to structure the query using a common table expression:
;With OrderSet (DrugID, OrderSetID, OrderID)
As (
Select DrugID
,OrderSetID
,OrderID
From DOeOrderSetPhaPcsMeds
Union All
Select DrugID
,OrderSetID
,OrderID
From DOeOrderSetPhaPcsIvAdditives
Union All
Select DrugID
,OrderSetID
,OrderID
From DOeOrderSetPhaPcsIvCarriers
)
Select x.DrugID
,x.OrderSetID
,x.OrderID
From OrderSet x
Where x.DrugID In (Select y.DrugID
From DPhaDrugData y
Where y.UsageType Like '%Non%'
And active = 'Y')
You can lookup common table expressions in BOL (SQL Server help system). For me, this makes it a bit clearer and easier to read.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 30, 2011 at 12:30 am
Well, I've never started a query with anything other than 'select'! 🙂
So I don't quite get:
;With OrderSet (DrugID, OrderSetID, OrderID)
As (
but, I will look into it. Also, naming the tables this way seems more confusing to me, but I'm not that familiar with it of course so that probably doesn't help. Another good 'learning opportunity'!
Thanks.
January 31, 2011 at 6:03 am
Common table expressions (CTEs) are a bit of advanced coding. They work wonderfully well for recursive querying and sometimes for normal querying. Definitely play with them, but don't stress if you don't immediately understand them. They can take quite a bit of getting used to.
February 27, 2011 at 5:23 pm
Brandie Tarvin (1/31/2011)They work wonderfully well for recursive querying...
You forgot to mention that recursion is usually hidden RBAR that should usually be avoided. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2011 at 12:21 pm
Jeff Moden (2/27/2011)
Brandie Tarvin (1/31/2011)They work wonderfully well for recursive querying...
You forgot to mention that recursion is usually hidden RBAR that should usually be avoided. 😛
I was thinking of self-referencing tables, which I don't tend to use but are used in Adventure Works (the employees table) and the BOL examples. Or is that exactly what you're talking about when you mention hidden RBAR?
February 28, 2011 at 5:11 pm
The hierarchical examples of Recursive CTE's in BOL aren't that bad and they aren't actually full blown RBAR because they process full levels at a time instead of just one row at a time. So no... those aren't the recursive CTE's that I'm talking about.
I'm talking about the recursive CTE's that people will use to do "counting" things that you'd normally use a Tally Table for. For example, splits, date generators, number generators, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2011 at 8:12 am
PharmD's rule! PharmD's with SQL knowledge DOUBLE RULE!
😀
March 1, 2011 at 8:18 am
P.S. If you would like to "Pretty up" the code more - in other words, use the kinds of punctuation that coders do naturally, I have found this to be a nice lil' tool to learn how to do that (It has helped me out quite a bit in understanding how to format my code for an easier read).
March 1, 2011 at 8:21 am
For instance...
SELECT m.drugid,
m.ordersetid,
m.orderid,
c.drugid,
c.ordersetid,
c.orderid,
a.drugid,
a.ordersetid,
a.orderid
FROM doeordersetphapcsivadditives a
FULL JOIN dphadrugdata d
ON a.drugid = d.drugid
FULL JOIN doeordersetphapcsivcarriers c
ON a.drugid = c.drugid
FULL JOIN doeordersetphapcsmeds m
ON a.drugid = m.drugid
WHERE ( c.drugid IN (SELECT d.drugid
FROM dphadrugdata d
WHERE d.usagetype LIKE '%Non%'
AND active = 'Y')
OR a.drugid IN (SELECT d.drugid
FROM dphadrugdata d
WHERE d.usagetype LIKE '%Non%'
AND active = 'Y')
OR m.drugid IN (SELECT d.drugid
FROM dphadrugdata d
WHERE d.usagetype LIKE '%Non%'
AND active = 'Y') )
March 2, 2011 at 2:18 am
Thanks Rich. How did you know I was a PharmD and not BS-Pharm? I appreciate your comment, it made me smile.
I hope to someday get a good position as an 'informatics pharmacist' doing everything related to computers in healthcare-pharmacy. Time will tell!
March 2, 2011 at 6:06 am
pharmboy4u (3/2/2011)
Thanks Rich. How did you know I was a PharmD and not BS-Pharm? I appreciate your comment, it made me smile.I hope to someday get a good position as an 'informatics pharmacist' doing everything related to computers in healthcare-pharmacy. Time will tell!
Please pardon my ignorance but it seems like an opportunity to learn something new. What's does "PharmD" and "BS-Pharm" each stand for and what are the differences?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply