Database

Re: Knowing Table structure in Sybase

17-Jul-07 4:12

The Sybase equivalent is "sp_help <tablename>". At one time, MS SQL-Server and Sybase SQL-Server (or whatever they call it these days) used to have the same code-base and system catalog tables.

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

PIEBALDconsult17-Jul-07 7:54

PIEBALDconsult

17-Jul-07 7:54

To get the list of columns and their types with any SQL database I get an IDataReader for
SELECT * FROM table, then I simply call its GetSchemaTable() method (I don't read the data).

newbie: How can I see the warnings when creating DB from file?

Atara17-Jul-07 1:57

Atara

17-Jul-07 1:57

in the mysql command-line I did:

mysql> create database myDB;
mysql> use myDB;
mysql> source mySource.sql;

and I get lines as -

Query OK, x rows affected, y warnings (z sec)

My question: How\where can I see what were the warnings, so I can fix them?

Thanks,

Atara

Re: newbie: How can I see the warnings when creating DB from file?

Atara17-Jul-07 21:02

Atara

17-Jul-07 21:02

found it:

mysql> SHOW WARNINGS;

Atara

ORA-12705: Cannot access NLS data files or invalid environment specified

qtuan16-Jul-07 22:25

qtuan

16-Jul-07 22:25

Hi all,
In VS2005, I use OracleConnection class of Oracle.DataAccess.Client Namespace so connect to Oracle database throw exception: "ORA-12705: Cannot access NLS data files or invalid environment specified".But i use OracleConnection class of System.Data.OracleClient is fine.
Please help me.Thankx

Problem in Query

Khan.Bangash16-Jul-07 21:42

Khan.Bangash

16-Jul-07 21:42

I have these three Tables,
customers, cust_order, cust_debit
customers primary ID is added to cust_order and cust_order primary key is added to cust_debit.
I want records from customers and cust_order table on a condition which is associated to cust_debit. Tables Structure is as follow
cutomers cust_order cust_debit
cust_id fname, lname, ...... order_id, cust_id, order_date ID, order_id, amount

I need fname, lname, order_id, order_date from customers and cust_order where cust_debit.amount >=2000,

Now I need only one record of a particular customers.I used the following query

SELECT cust_order.order_id as order_id, customers.fname as fname , customers.lname as lname, cust_order.order_date as order_date,cust_order.order_status as order_status from cust_order,customers, cust_debit where customers.customer_id=cust_order.customer_id and cust_order.order_id = cust_debit.order_id and cust_debit.amount >=200

It works fine, But I need only one record of a customers, like when we use DISTINCT.
How can I use that.
Pls Help me
Thanks in Advance.

andyharman16-Jul-07 22:17

http://www.codeproject.com/script/comments/forums.asp?forumid=1725&df=2&noise=2&tid=2132707&select=2132856#xx2132856xx[^]

16-Jul-07 22:17

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Khan.Bangash16-Jul-07 22:30

Khan.Bangash

16-Jul-07 22:30

I have vote u 5.
Ya the link u have given I have already seen that, but I cant understand that. Pls
Help me and read my post carefully and then set query and then post if u dont mind it.
Thanks in Advance.

andyharman17-Jul-07 1:23

17-Jul-07 1:23

I'm not really sure what you are trying to acheive with the "cust_debit" table. It looks like it contains the amount outstanding for each order.

If it is the amount outstanding then the following change would give you the latest order for any customer who has a total of more than 2000 outstanding:

....
   from (
      select customer_id, max(order_id)
      from cust_order
      inner join cust_debit
        on cust_debit.order_id = cust_order.order_id
      group by customerId
      having sum(cust_debit.amount) >= 2000
   ) as lastest_order_id
....

The following uses a correlated-subquery to give you customers where any one single order has more than 2000 outstanding:

....
   from (
      select customer_id, max(order_id)
      from cust_order
      where 2000 <= (
         select sum(cust_debit.amount) from cust_debit
         where cust_debit.order_id = cust_order_id)
      group by customerId
   ) as lastest_order_id
....

Both examples give the details of the most-recent order. If you want the oldest order then use min(order_id). Hopefully you should now be able to solve your problem using one of these examples.

Regards
Andy

Khan.Bangash17-Jul-07 1:55

Khan.Bangash

17-Jul-07 1:55

Dear there is a problem in ur query its not working correctly, infact system hangs when I execute ur query.
Pls help me. I m tied of this query.

andyharman17-Jul-07 3:18

Rights needed to create tablespace in oracle?

17-Jul-07 3:18

Not half as tired as I am! I don't mind helping people, but it doesn't seem like you are trying very hard.

How about you putting some effort into understanding what the queries are trying to do. The SQL that I provided is basically three separate select-statements layered inside each other. I would suggest that you get the inner-most query working first:

select customer_id, max(order_id)
from cust_order
inner join cust_debit
  on cust_debit.order_id = cust_order.order_id
group by customerId
having sum(cust_debit.amount) >= 2000

and the build up gradually to your final solution. If your machine is hanging then that may indicate that your database is not indexed properly.

Mushtaque Nizamani16-Jul-07 20:25

Mushtaque Nizamani

16-Jul-07 20:25

Hi,
What sort of rigts needed to create tablespace in oracle?

Best Regards,
Mushq

Mushtaque Ahmed Nizamani
Software Engineer
Ultimus Pakistan

"English is my second language, so please don't mind if i do some grammatical or spelling mistakes in my messages."

Re: Rights needed to create tablespace in oracle? [modified]

andyharman16-Jul-07 20:31

Re: Doubt in query........., [modified]

16-Jul-07 20:31

GRANT create tablespace TO #user id#;

-- modified at 4:22 Wednesday 18th July, 2007

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Doubt in query.........,

Member 387988116-Jul-07 19:12

Member 3879881

16-Jul-07 19:12

Hi i have table like this format:
Id sMonth sYear value1
608 6 2007 100
608 7 2007 200
608 8 2007 150
608 6 2008 652
608 7 2008 120
608 8 2008 234
Suppose i need the records between the 6th month 2007 to 6th month 2008

O/P:

Id sMonth sYear value1
608 6 2007 100
608 7 2007 200
608 8 2007 150
608 6 2008 652

How to write the query???,

If its in date format we can use between condition...,
But in this case how u can compare the months and years ???,

Regards,
Magi

andyharman16-Jul-07 20:24

16-Jul-07 20:24

How about

select * from MyTable
   where (sYear > 2007 or (sYear = 2007 and sMonth >= 6))
   and (sYear < 2008 or (sYear = 2008 and sMonth <= 6))

select * from MyTable
   where convert(varchar(6), (Year*100)+sMonth) between '200706' and '200806'

Regards
Andy

-- modified at 14:19 Wednesday 18th July, 2007

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Member 387988116-Jul-07 20:36

Member 3879881

16-Jul-07 20:36

Hi andy,
Thank u for ur reply...,
Ya i getting o/p for the first query...,
But second query is not giving proper o/p...,
Anyway one solution is enough...,
Thank's lot...,
keep in touch,

Regards,
Magi

andyharman16-Jul-07 21:17

16-Jul-07 21:17

You're welcome.

The second query should have used (sYear*100). I hadn't woken-up properly Smile | :)

RepliCrux16-Jul-07 20:37

RepliCrux

16-Jul-07 20:37

5 votes to you man. I was about to say bad database design Poke tongue | ;-P

RepliCrux16-Jul-07 20:53

RepliCrux

16-Jul-07 20:53

Sorry Magi,

Did not wanted to hurt your feelings about the design, But it is a bad practice, you should always use DateTime format. If you want to look at month and year only, you can still do that using datetime column. As you proceed further you will find data Access more and more difficult. That is why they say that make sure your design is right. Consult your senior developers about the design.

And finally, as you said, it might be right in your case, so thats good Smile | :)

Cheers

Member 387988116-Jul-07 21:01

Member 3879881

16-Jul-07 21:01

S repli,
I know what ur trying to say...,
But i cant get date from my end user...,
Becoz a month full records i am storing in one row itself...,
So how i can tell the date in this?...,
So in this we can use table deisgn like that only...,
Sorry yar i think my words also make u to feel..,
Once again sorry for that,
Keep in touch,

Regards,
Magi

ChandraRam17-Jul-07 4:01

ChandraRam

17-Jul-07 4:01

andyharman wrote:
select * from MyTable where convert(varchar(6), (Year*12)+sMonth) between '200706' and '200806'

Actually, (Year*12)+sMonth wont get you those values at all Wink | ;)