Home » SQL & PL/SQL » SQL & PL/SQL » select count(*)
select count(*) [message #360287] Thu, 20 November 2008 05:04 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hello,

Table T1(step,name,table_name,details,number_month)
Table T2(step,date,state_T1_table,unique_id)

step will have 1-10
month - default value 13
table_name has 10 tables
unique_id - If any processing is done on T1 then for the corresponding step unique id will be generated and stored in T2 with the corresponding step and state as well.

I need to fetch the number of records of table_name from T1 table with condition that for all the records ,date field from T2 table are 13 months older than sysdate.

T1 and T2 are connected , as for all the table_name in T1 , details are stored in T2 like state ,date and step.

I am not able to write the sql query for the same.

Please suggest me.
Re: select count(*) [message #360290 is a reply to message #360287] Thu, 20 November 2008 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It has already been said to you.

Please read OraFAQ Forum Guide
Read "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: select count(*) [message #360291 is a reply to message #360287] Thu, 20 November 2008 05:31 Go to previous messageGo to next message
yasar
Messages: 1
Registered: November 2008
Junior Member
hi,

I would request you to give some sample data for the table 1 & 2, also mention the output out of these tables. this will help for writing the query easily..

Thanks,
Yasar.
Re: select count(*) [message #360293 is a reply to message #360287] Thu, 20 November 2008 05:44 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hi Michel,

I read the forum guide.

But my humble request is that if the problem is easier to resolve directly then is that so important to go for the procedure.

I know you will have tons of knowledge and are always willing to help the junior members, but preparing a test case and using tools for a select query will take lot more time.

Please dont take me wrong but i am just trying to say if somebodies problem can be resolved without the compulsion of testcase and exact parameters that could help lots and lots of people to explain and give their queries frequently.
Re: select count(*) [message #360296 is a reply to message #360287] Thu, 20 November 2008 05:49 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I cannot help myself, but this is a nice example, how SQL is very close to structured English.

> I need to fetch the number of records of table_name
SELECT COUNT(* or (distinct) table_name) - not sure what exactly you want to count
maybe you want to restrict the result set to given <table_name> - it would belong to WHERE clause

> from T1 table
FROM t1

> with condition that for all the records ,date field from T2 table are 13 months older than sysdate.
WHERE NOT EXISTS (SELECT *
FROM t2
WHERE <join conditions> and <date_field is 13 months younger than sysdate>)

Of course, specifying all constraints (primary and foreign keys, unique and not-null constraints) - CREATE TABLE statements are most welcome here; and providing sample data with expected result would help everybody here to understand what exactly you want (as you can see from my comments, it is not clear to me; and I doubt anybody is interested in guessing it).
Re: select count(*) [message #360298 is a reply to message #360291] Thu, 20 November 2008 06:04 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hi yasar,

Table T1

STEP - Number(3) from 1 to 10
name - contains the stored procedure names to run for the corresponding step and table
table_name varchar2(20) - t1,t2,t3 to t10
number_month Number(3)- default value is 13.

Table T2

STEP Number(3)- numbers from 1 to 10
Unique id varchar2(20) is the sequence number which will be generated for the step in table T1 for the corresponding step and table_name in T1 table.

T2 is mainly the table , which contains the details for the data of table T1.
Like for the step 1 in table T1 , the details entered in table T2 will be date, sequence id generated and the state of the table_name in T1.

So here i need to fetch the number of records from table_name of T1 (Since table names are mentioned in the field table_name , so for the corresponding step, i need to fetch the count of number of record of the table)

Hope i am clear this time.

Please tell me if i should provide more input.
Re: select count(*) [message #360303 is a reply to message #360287] Thu, 20 November 2008 06:12 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Table T1

Trying to make this more clear.

STEP - Number(3) from 1 to 10 not null and primary key
name - contains the stored procedure names to run for the corresponding step and table - Not null
table_name varchar2(20) - t1,t2,t3 to t10 - not null
number_month Number(3)- default value is 13. - not null

Table T2

STEP Number(3)- numbers from 1 to 10 - not null , primary key
Unique_id varchar2(20) is the sequence number which will be generated for the step in table T1 for the corresponding step and table_name in T1 table. varchar2(20) , not null
State varchar2(1) - default value 'e' , 'p' etc

ALSO one point to add here , table_name field of table T2 has names of table and from these table i need to fetch record count.

[Updated on: Thu, 20 November 2008 06:33]

Report message to a moderator

Re: select count(*) [message #360314 is a reply to message #360287] Thu, 20 November 2008 06:43 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Actually the whole concept in real time is too dificult , so i am not able to put all the things clearly.

Atleast if somebody can tell me that .

How to fetch record count from a table which are older then 13 months (from sysdate) and the date comparison to be done on the basis of date field stored in another table ?
Re: select count(*) [message #360315 is a reply to message #360298] Thu, 20 November 2008 06:55 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@shaksing,

shaksing wrote on Thu, 20 November 2008 17:34

So here i need to fetch the number of records from table_name of T1 (Since table names are mentioned in the field table_name , so for the corresponding step, i need to fetch the count of number of record of the table)



If I undersatnd your requirement correctly, you need to use PL/SQL here along with Dynamic SQL. Normal SQL Query won't help you here.
I hope you will find Performing SQL Operations with Native Dynamic SQL helpful.

I strongly advice you to follow the guidelines if you want proper responses. A quote from this forum is "You need to help us to help you".

Regards,
Jo http://img2.mysmiley.net/imgs/smile/innocent/innocent0002.gif
Re: select count(*) [message #360321 is a reply to message #360287] Thu, 20 November 2008 07:14 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
THANKS JOHN,

THATS WAS REALLY HELPFUL , I AM TRYING TO DO IT BY EATING MY OWN HEAD NOW.
Re: select count(*) [message #360332 is a reply to message #360321] Thu, 20 November 2008 07:45 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I'm pretty sure that eating your own head will not be of any help Wink

And...
Quote:
but preparing a test case and using tools for a select query will take lot more time.


Ok, so you want US to spend all the time to solve your problem but YOU don't want to spend the time to help us help you. See how far you get in life with an attitude like that, lazy.
Re: select count(*) [message #360380 is a reply to message #360314] Thu, 20 November 2008 14:47 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
shaksing wrote on Thu, 20 November 2008 13:43
Actually the whole concept in real time is too dificult , so i am not able to put all the things clearly.

Good reason for creating a test real case and research possible solutions.
shaksing wrote on Thu, 20 November 2008 12:44
but preparing a test case and using tools for a select query will take lot more time.

Proper describing of the problem in forums takes time too, and I doubt that it will be less. What is so time consuming in making script for creating two tables with sample data?
shaksing wrote on Thu, 20 November 2008 13:43
How to fetch record count from a table which are older then 13 months (from sysdate) and the date comparison to be done on the basis of date field stored in another table ?

If the table relationship is 1:1 or N:1, a simple join will sufice (as the another table rows does not multiply the row count).
If the table relationship is 1:N, the check may be done using (NOT) EXISTS, as stated in my previous post.
As for the date comparison, the latest version of table description does not contain any date column; only you know the column meaning, so it is guessing from crystal ball for anyone else. Really, there is a chance that this comparison is not possible as the necessary information is not stored.
Re: select count(*) [message #360416 is a reply to message #360332] Thu, 20 November 2008 22:13 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Joy

Don't misunderstand me but frankly saying i have lots of other work and in the meanwhile i was trying to get some sql knowledge. Its not that i am lazy but have lot of other commitments too.This is the truth and people thought that i am lazy.And i am beginner so not aware of all the requisites before going to execute.

Flyboy ,

Thanks for your answer, that was helpful and i am able to find the output.


First time i joined any forum and what i found is people will answer the queries for sure , but for a beginner like me there will be lot more of comments which are out of the scope of the queries asked.

Regards,
Shaksing

Re: select count(*) [message #360460 is a reply to message #360287] Fri, 21 November 2008 02:06 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
but for a beginner like me there will be lot more of comments which are out of the scope of the queries asked.

I would not call asking for clarification of your requirements "out of the scope". I am afraid they will repeat, as you are still unable to formulate it clearly. And yes, showing sample tables, data and expected results would make it more clear than this puzzled bunch of statements you post.
Just one comment to: http://www.orafaq.com/forum/t/128025/0/
Again, it is totally not clear what you ask. Maybe joicejohn's reply in this thread (the one you shouted at) could guide you (as I had to repeat my suggestion twice to satisfy you; although I am still not sure what you are trying to achieve).
Good luck.
Re: select count(*) [message #360461 is a reply to message #360287] Fri, 21 November 2008 02:11 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
hi flyboy

I have used the output given by you and joicejohn and now got the output i needed.

Asking about the requirements is perfectly fine , and i agree with that also that i was not able to give proper clarification what i require , but there were some more things which i was pointing to .

Thanks a lot for your help.

Will you be able to guide me , a post which i recently sent here.

[Updated on: Fri, 21 November 2008 02:12]

Report message to a moderator

Re: select count(*) [message #360464 is a reply to message #360293] Fri, 21 November 2008 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Please dont take me wrong but i am just trying to say if somebodies problem can be resolved without the compulsion of testcase and exact parameters that could help lots and lots of people to explain and give their queries frequently.


The reason why we're big on tet cases here is that English is not a first language for many of the posters, and their questions are frequently hard to understand.
A test case with sample data and required output removes a great deal of ambiguity and confusion.

Additionally, if you prepare a test case then that's work that we don't have to do.
As you're the person asking for help, many of us feel that it's reasonable to ask you to do some of the work.

[Updated on: Fri, 21 November 2008 02:17]

Report message to a moderator

Re: select count(*) [message #360466 is a reply to message #360287] Fri, 21 November 2008 02:16 Go to previous message
shaksing
Messages: 115
Registered: November 2008
Senior Member
I got your point JROW Smile ... Thanks fir replying
Previous Topic: Using If statment in Bulk Collect & ForAll Problem
Next Topic: constraints vs triggers
Goto Forum:
  


Current Time: Thu Dec 08 16:25:41 CST 2016

Total time taken to generate the page: 0.05925 seconds