SQL question

[SPIDERMAN]

Hi gurus,

I have 3 tables and their fields as follows:
1) Buyers: BuyerID
2) Sellers: SellerID
3) BuyersSellers: BuyerID, SellerID, GoldorSilver

Where GoldorSilver contains either 'G' for Gold or 'S' for Silver.

The following query gives me all the buyers, which is what I want. But it
also give me duplicate rows if a buyer buy both Gold AND Silver. If I want
to reduce the number of rows (so that there aren't rows with similar
BuyerID) but add extra columns (Gold column and Silver column), how do I
write this query in Oracle SQL or ANSI99 SQL? Thank you in advance


select BuyerID, SellerID, GoldorSilver from Buyers, Sellers, BuyersSellers
where Buyers.BuyerID=BuyersSellers.BuyerID(+) and
Sellers.SellerID(+)=BuyersSellers.SellerID

[Uma]

Hi ..

a simple SQL question..

Table EMP:


EmpId EmpName LocId
1 Ramu 1
2 Vishnu 1
3 Uma 1
4 Babu 2
5 Shilpa 2


tABLE Dept :

DeptId DeptName Dep_Loc Loc_num
1 Hyd IA 10
2 Sec'Bad NJ 20
3 Guntur NY 30

There is no direct relation between Emp & dept .

Emp table LocId stores the value of DeptId from Dept table.

Now I wan update the Emp Table , loc id values with corresponding
Loc_num in dept table..

I wan do it in single query ??


any Ideas..


Thanks
Uma

[Jonathan Leffler]

Uma wrote:

> a simple SQL question..
>
> Table EMP:
>
>
> EmpId EmpName LocId
> 1 Ramu 1
> 2 Vishnu 1
> 3 Uma 1
> 4 Babu 2
> 5 Shilpa 2
>
>
> tABLE Dept :
>
> DeptId DeptName Dep_Loc Loc_num
> 1 Hyd IA 10
> 2 Sec'Bad NJ 20
> 3 Guntur NY 30
>
> There is no direct relation between Emp & dept .
>
> Emp table LocId stores the value of DeptId from Dept table.
>
> Now I wan update the Emp Table , loc id values with corresponding
> Loc_num in dept table..
>
> I wan do it in single query ??

UPDATE EMP SET LocId = (SELECT Loc_Num FROM Dept WHERE DeptId = LocId)
WHERE LocID IN (SELECT DeptId FRO Dept);

The where clause on the UPDATE - as opposed to the correlated
sub-select - is to ensure that if there is a LocId value with no
matching DeptId in the Dept table, then the row is untouched. OTOH,
it might be better to ensure that there are none before doing the
update - or maybe the default behaviour of setting LocId to NULL would
be acceptable. Your call.

I've not verified the SQL - in Informix, you might have to double up
the parentheses around the sub-select - yes, there's an obscure
reason, but it is arcane and irksome.


--
Jonathan Leffler #include <disclaimer.h>
Email: [email]jleffler@earthlink.net[/email], [email]jleffler@us.ibm.com[/email]
Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]

[Serge Rielau]

or in V8 FP2:

MERGE INTO EMP USING DEPT ON DeptId = LocId
WHEN MATCHED UPDATE SET LocID = Loc_Num;

Cheers
Serge


--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
[url]http://www7b.software.ibm.com/dmdd/[/url]

[Dave]

Hi

We have a UDB database and all the tables & other database objects are in a
schema (user) called 'abc' which
has got DBADM privilege. The application uses a different UserId to
connect to the database
(from an app server) which has got SELECT / INSERT / UPDATE / DELETE
privileges on all the tables
in 'abc' schema. What is the best way of avoiding pre-fixing the table
names with 'abc.' for the
other Users to access the tables in 'abc' schema ? Is 'SET SCHEMA' the way
to go, or are there any
better alternatives ?

(similar to public synonyms concept in Oracle OR objects owned by
'dbo' in Sybase / MS Sql Server)

Thanks

[Knut Stolze]

Dave <adavi@comcast.net> wrote:

> Hi
>
> We have a UDB database and all the tables & other database objects are in
> a
> schema (user) called 'abc' which
> has got DBADM privilege. The application uses a different UserId to
> connect to the database
> (from an app server) which has got SELECT / INSERT / UPDATE / DELETE
> privileges on all the tables
> in 'abc' schema. What is the best way of avoiding pre-fixing the table
> names with 'abc.' for the
> other Users to access the tables in 'abc' schema ? Is 'SET SCHEMA' the
> way to go, or are there any
> better alternatives ?

Why do you want to avoid using fully qualified names? I think that's the
best way to ensure that your application behaves as it should.

If you connect via CLI, you can set the schema in the db2cli.ini file.
Otherwise, SET SCHEMA is available to you as you already mentioned.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[John K]

can anyone tell me what's wrong with this?

searchSQL = "Select * FROM Events WHERE Date >=#" & DateBegin & "# AND Date
<=#" & DateEnd & "# ORDER BY Date " & sort & ";"

I have a form sending the correct data but I can't get this to recognize the
'Date<=' For example, when I do a search and a date lands on the last day
of the month, it doesn't turn up. It reports the 'greater than or equal to'
but not the later. What am I doing wrong?

I'm not a SQL pro so don't kill me...

Thanks!!!
John

[John K]

can anyone tell me what's wrong with this?

searchSQL = "Select * FROM Events WHERE Date >=#" & DateBegin & "# AND Date
<=#" & DateEnd & "# ORDER BY Date " & sort & ";"

I have a form sending the correct data but I can't get this to recognize the
'Date<=' For example, when I do a search and a date lands on the last day
of the month, it doesn't turn up. It reports the 'greater than or equal to'
but not the later. What am I doing wrong?

I'm not a SQL pro so don't kill me...

Thanks!!!
John

[Curt_C [MVP]]

do a response.write on the SQL once and see what it shows..
Also...is this MS Access?


--
----------------------------------------------------------
Curt Christianson (Software_AT_Darkfalz.Com)
Owner/Lead Designer, DF-Software
[url]http://www.Darkfalz.com[/url]
---------------------------------------------------------
...Offering free scripts & code snippits for everyone...
---------------------------------------------------------


"John K" <noway@amIgivingitouthere.com> wrote in message
news:hMx0b.211465$EQ5.198283@twister.nyroc.rr.com. ..

> can anyone tell me what's wrong with this?
>
> searchSQL = "Select * FROM Events WHERE Date >=#" & DateBegin & "# AND

Date

> <=#" & DateEnd & "# ORDER BY Date " & sort & ";"
>
> I have a form sending the correct data but I can't get this to recognize

the

> 'Date<=' For example, when I do a search and a date lands on the last day
> of the month, it doesn't turn up. It reports the 'greater than or equal

to'

> but not the later. What am I doing wrong?
>
> I'm not a SQL pro so don't kill me...
>
> Thanks!!!
> John
>
>

[grw]

searchSQL = "Select * FROM Events WHERE [Date]BETWEEN #" & DateBegin & "#
AND #" & DateEnd & "# ORDER BY Date " & sort & ";"

Note "date" is a reserved word


"John K" <noway@amIgivingitouthere.com> wrote in message
news:4Nx0b.211472$EQ5.202762@twister.nyroc.rr.com. ..

> can anyone tell me what's wrong with this?
>
> searchSQL = "Select * FROM Events WHERE Date >=#" & DateBegin & "# AND

Date

> <=#" & DateEnd & "# ORDER BY Date " & sort & ";"
>
> I have a form sending the correct data but I can't get this to recognize

the

> 'Date<=' For example, when I do a search and a date lands on the last day
> of the month, it doesn't turn up. It reports the 'greater than or equal

to'

> but not the later. What am I doing wrong?
>
> I'm not a SQL pro so don't kill me...
>
> Thanks!!!
> John
>
>

[grw]

Also posted in asp.db

"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in message
news:u1eY9dqZDHA.3436@tk2msftngp13.phx.gbl...

> do a response.write on the SQL once and see what it shows..
> Also...is this MS Access?
>
>
> --
> ----------------------------------------------------------
> Curt Christianson (Software_AT_Darkfalz.Com)
> Owner/Lead Designer, DF-Software
> [url]http://www.Darkfalz.com[/url]
> ---------------------------------------------------------
> ..Offering free scripts & code snippits for everyone...
> ---------------------------------------------------------
>
>
> "John K" <noway@amIgivingitouthere.com> wrote in message
> news:hMx0b.211465$EQ5.198283@twister.nyroc.rr.com. ..

> > can anyone tell me what's wrong with this?
> >
> > searchSQL = "Select * FROM Events WHERE Date >=#" & DateBegin & "# AND

> Date

> > <=#" & DateEnd & "# ORDER BY Date " & sort & ";"
> >
> > I have a form sending the correct data but I can't get this to recognize

> the

> > 'Date<=' For example, when I do a search and a date lands on the last

day

> > of the month, it doesn't turn up. It reports the 'greater than or equal

> to'

> > but not the later. What am I doing wrong?
> >
> > I'm not a SQL pro so don't kill me...
> >
> > Thanks!!!
> > John
> >
> >

>
>

[Alan]

Two things, watch your use of reserved keywords for column names (Date) -
enclose in brackets [...] as required.

The problem is probably the time part of the date. A date of '31 Dec 2003'
for example will also have a time part, e.g. 11:45:27.990. A date without a
specified time component will assume 0:00:000.000. In your query below
you're probably finding that the 'Date' param has a time part and the
DateEnd does not, meaning a comparison of (for example) 4/05/2003
13:30:31.123 <= 4/05/2003 00:00:00.000 resolves to false and your end date
is excluded.

If it's the time part that's pushing your date outside the range of your
WHERE clause, one solution is to use DateAdd to add one day to DateEnd so
that 'Date' now falls inside the range with it's time part included - note
though that your end date now also includes some hours/minutes/seconds from
the next period. Another option is to use a series of Casts/Converts to
remove the time part from DateEnd and append 23:59.59.999 to give you the
latest possible *time* for the last date in your range, or remove the time
portion completely from all dates if they're not needed. If you stick with a
inclusive range, <= and >= then you can also use the BETWEEN operator, Date
BETWEEN DateBegin and DateEnd, - I think it's available in Access.

I've written this with SQL Server in mind but the concepts still hold for
Access AFAIK.

Hope this gives you something to go on.

Alan

"John K" <noway@amIgivingitouthere.com> wrote in message
news:4Nx0b.211472$EQ5.202762@twister.nyroc.rr.com. ..

> can anyone tell me what's wrong with this?
>
> searchSQL = "Select * FROM Events WHERE Date >=#" & DateBegin & "# AND

Date

> <=#" & DateEnd & "# ORDER BY Date " & sort & ";"
>
> I have a form sending the correct data but I can't get this to recognize

the

> 'Date<=' For example, when I do a search and a date lands on the last day
> of the month, it doesn't turn up. It reports the 'greater than or equal

to'

> but not the later. What am I doing wrong?
>
> I'm not a SQL pro so don't kill me...
>
> Thanks!!!
> John
>
>

[John K]

I know, I should have caught that sooner. This was actually a 3rd party app
I found at aspin. Used it to save time, didn't have enough to write one
from scratch. I've been tweakin it and problems like this keep coming up,
arg. It also came with the Date field including the Time which is a pain
because I'll have to go back and create a new field for time and rewrite all
the sql calls because it screws up searches, e.g. BETWEEN #DATE# AND #DATE#
if the time is greater than midnight....pain in the kee. So much for saving
time....


"grw" <none@none.com> wrote in message
news:OB3EbtqZDHA.1740@TK2MSFTNGP10.phx.gbl...

> searchSQL = "Select * FROM Events WHERE [Date]BETWEEN #" & DateBegin & "#
> AND #" & DateEnd & "# ORDER BY Date " & sort & ";"
>
> Note "date" is a reserved word
>
>
> "John K" <noway@amIgivingitouthere.com> wrote in message
> news:4Nx0b.211472$EQ5.202762@twister.nyroc.rr.com. ..

> > can anyone tell me what's wrong with this?
> >
> > searchSQL = "Select * FROM Events WHERE Date >=#" & DateBegin & "# AND

> Date

> > <=#" & DateEnd & "# ORDER BY Date " & sort & ";"
> >
> > I have a form sending the correct data but I can't get this to recognize

> the

> > 'Date<=' For example, when I do a search and a date lands on the last

day

> > of the month, it doesn't turn up. It reports the 'greater than or equal

> to'

> > but not the later. What am I doing wrong?
> >
> > I'm not a SQL pro so don't kill me...
> >
> > Thanks!!!
> > John
> >
> >

>
>

[John K]

I know, I should have caught that sooner. This was actually a 3rd party app
I found at aspin. Used it to save time, didn't have enough to write one
from scratch. I've been tweakin it and problems like this keep coming up,
arg. It also came with the Date field including the Time which is a pain
because I'll have to go back and create a new field for time and rewrite all
the sql calls because it screws up searches, e.g. BETWEEN #DATE# AND #DATE#
if the time is greater than midnight....pain in the kee. So much for saving
time....


"Phill. W" <P.A.Ward@open.ac.uk> wrote in message
news:bhvqlb$grb$1@yarrow.open.ac.uk...

> "John K" <noway@amIgivingitouthere.com> wrote in message
> news:hMx0b.211465$EQ5.198283@twister.nyroc.rr.com. ..

> > can anyone tell me what's wrong with this?
> >
> > searchSQL = "Select * FROM Events WHERE Date >=#" & DateBegin & "# AND

> Date

> > <=#" & DateEnd & "# ORDER BY Date " & sort & ";"

>
> John,
>
> 1). Don't use "Select *", it's downright inefficient,
> 2). Don't use "Date" as a column name; it's a reserved word
> 3). Investigate the SQL keyword "Between",
> 4). Make sure the dates you supply "make sense" to your database,
> by which I mean /format/ your dates before adding them into the
> SQL. Wherever I can, I use this format - "yyyy-mm-dd hh:nn:ss"
> - just about everything can understand it without any ambiguity.
>
> HTH,
> Phill W.
>
>

[Kiavik]

Hello All

I have a problem with an SQL query. The query is quite simple cause i need
to get all the rows of a table which have a field set to null.

For exaple consider this table:

CUSTOMERS
----------------------------------------
NAME | SECOND_NAME | LAST_NAME | ADDRESS
----------------------------------------

I need to geet all the customers which have SECOND_NAME set to NULL.

I tried something like this:

SELECT * FROM CUSTOMERS
WHERE SECOND_NAME=NULL;

But the DB2 gives me an error: "NULL is invalind in the context it is used"

How can I select all the rows which have second name null?

Thanks

--
Nel tempo dell'inganno universale
dire la verità è un atto rivoluzionario
(George Orwell)


ICQ:61810110
YAHOO!:Kiavick

[Anton Versteeg]

try:

WHERE SECOND_NAME is NULL

Kiavik wrote:

>Hello All
>
>I have a problem with an SQL query. The query is quite simple cause i need
>to get all the rows of a table which have a field set to null.
>
>For exaple consider this table:
>
>CUSTOMERS
>----------------------------------------
>NAME | SECOND_NAME | LAST_NAME | ADDRESS
>----------------------------------------
>
>I need to geet all the customers which have SECOND_NAME set to NULL.
>
>I tried something like this:
>
>SELECT * FROM CUSTOMERS
>WHERE SECOND_NAME=NULL;
>
>But the DB2 gives me an error: "NULL is invalind in the context it is used"
>
>How can I select all the rows which have second name null?
>
>Thanks
>
>
>

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

[Rajesh Kapur]

I have a table where the hierarchy is built by storing the parent_id in the
same table....

id,
parent_id
..... other fields

Can someone suggest an SQL statement that will list the entire hierarchy as
follows

id1
id1.1
id1.2
id1.2.1
id1.2.2
id1.2.3
id1.2.3.1
id1.3
id1.4
id2
.....

Thanks!

[Paul Watson]

I'd use SPL and a bit of recursion, or if you are feeling brave
you could use the node datablade

Rajesh Kapur wrote:

>
> I have a table where the hierarchy is built by storing the parent_id in the
> same table....
>
> id,
> parent_id
> .... other fields
>
> Can someone suggest an SQL statement that will list the entire hierarchy as
> follows
>
> id1
> id1.1
> id1.2
> id1.2.1
> id1.2.2
> id1.2.3
> id1.2.3.1
> id1.3
> id1.4
> id2
> ....
>
> Thanks!

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
[url]www.oninit.com[/url] #

[Art S. Kagel]

On Wed, 05 Nov 2003 13:07:23 -0500, Rajesh Kapur wrote:

SQL is REALLY bad at recursive data structures, as attractive as they are to
programmers. Recognising this Oracle (yes Mark I'll even give credit to the big
'O' when it's appropriate, I just don't get the opportunity often ;-} ) years ago
added extensions to their SQL to permit processing these babies in a single SQL.
Unfortunately no other SQL implementation has added that feature so you'll have
to implement it in code in you app or in an SPL. Another approach would be to
code a UDF in 'C' or Java which might be more efficient, assuming you have 9.xx.

Art S. Kagel

> I have a table where the hierarchy is built by storing the parent_id in the
> same table....
>
> id,
> parent_id
> .... other fields
>
> Can someone suggest an SQL statement that will list the entire hierarchy as
> follows
>
> id1
> id1.1
> id1.2
> id1.2.1
> id1.2.2
> id1.2.3
> id1.2.3.1
> id1.3
> id1.4
> id2
> ....
>
> Thanks!

[Mark Townsend]

> Unfortunately no other SQL implementation has added that feature so you'll have
> to implement it in code in you app or in an SPL.

Hmm - well, IBM DB2 HAS implemented recursive common table expressions
(which IS in the standard and IS potentially a better implementation
that what we implemented in Oracle many,many years ago). Of course, I
don't believe that Informix has implemented this yet.

How's that for truth in advertising ?

Recursive CTE's are actually pretty cool - see
[url]http://www7b.software.ibm.com/dmdd/library/techarticle/0307steinbach/0307steinbach.html#section1[/url]
for a discussion of them, and how they relate to Oracle's CONNECT BY
capabilities.