|
Hi All,
I am writing a query for a daily report generation. It involves data from 4 tables. Am so confused about which joins to use. It also makes use of GROUP BY CLAUSE on Cust_NBR and make use of agrregate function Count.
I have written a query as follows but it's not working.
Plz help me...as it is urgent.
select SH.ARBL_NBR Airbill,S.cust_NBR Account_Number,S.Shpt_Id,S.City_Nam sender_City,
S.St_Prvnc_Cd Sender_State,s.cntry_id Sender_Country,S.Postl_Cd sender_postal,
S.Svc_Lvl_Cd Sender_Service_Area_Code,
R.City_Nam Receiver_City,R.St_Prvnc_Cd Receiver_State,R.cntry_id Reciever_Country,
R.Postl_Cd Receiver_Postal, R.Svc_Lvl_Cd Receiver_Service_Area_Code,
U.Frst_Nam||' ' ||U.last_nam Name,
S.STR_ADDR_1 Sender_Street_Address1, S.Str_Addr_2 Sender_Street_Address2
from shpt SH
Right Outer Join shpt_addr S
on sh.shpt_id = S.shpt_id
Right Outer Join shpt_addr R
ON S.shpt_id = R.shpt_id
Right Outer Join Users U
ON SH.User_Id = U.User_Id
Right Outer Join Cust C
On S.Cust_Nbr = C.Cust_Nbr
where S.ADDR_TYP_CD = 1 and R.Addr_Typ_Cd = 2
and c.creat_dt between trunc(sysdate) and trunc(sysdate-90)
group By S.Cust_Nbr Desc
Having Count(sh.shpt_id >= 25)
|
|
|
|
|
Hi, what kind of Error do u get??
|
|
|
|
|
Error i get is ORA-00933: SQL command not properly ended.
There is some mistake in the query which i am not getting.
Plz help me out.
|
|
|
|
|
Hi, try this please maybe the syntax problem is in the Having count-->
select R.City_Nam Receiver_City,R.St_Prvnc_Cd Receiver_State,R.cntry_id Reciever_Country,
R.Postl_Cd Receiver_Postal, R.Svc_Lvl_Cd Receiver_Service_Area_Code,
U.Frst_Nam||' ' ||U.last_nam Name,
S.STR_ADDR_1 Sender_Street_Address1, S.Str_Addr_2 Sender_Street_Address2
from shpt SH
Right Outer Join shpt_addr S
on sh.shpt_id = S.shpt_id
Right Outer Join shpt_addr R
ON S.shpt_id = R.shpt_id
Right Outer Join Users U
ON SH.User_Id = U.User_Id
Right Outer Join Cust C
On S.Cust_Nbr = C.Cust_Nbr
where S.ADDR_TYP_CD = 1 and R.Addr_Typ_Cd = 2
and c.creat_dt between trunc(sysdate) and trunc(sysdate-90)
group By S.Cust_Nbr Desc
Having Count(sh.shpt_id) >= 25
|
|
|
|
|
Hi,
I didn't work on Access for a very long time.A customer gave me an Access application.He wants me to make a User/Administrator web application depending on that Access file.How could I see the code reading from the tables?It works as an EXE application.
Another problem is Iam trying to migrate it to SQL database.Some tables have more than 20000 records so their migration take a very long time(3 hours at least).Is there any wayy to finish this faster?
I am too late but i will never give up
|
|
|
|
|
TheEagle wrote: Some tables have more than 20000 records so their migration take a very long time(3 hours at least).Is there any wayy to finish this faster?
Is it possible the database is not normalized?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
hi,
i have a table, i query it and i get a row(or more).OK
how can i return a value in the left or right of a particular column(cell)?
for exemple, i have table that has some columns with same kind of data ( race standings: 1st,2nd,3rd,4th...), so i query for Hamilton in any of the columns(standings).
if hamilton is in column 3rd, how can i get the value in the column in the left or right? is it possible?
i am trying to avoid typing larger code here!!! 
(this is an exemple it has nothing to do with F1 ok  )
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
When you query for "Hamilton" you should get the columns ordinal position/index. The simply get the previous/next column
Set the column index to i and:
Dataset.Tables(0).Rows(2)(i + 1)
[edit]
Oops - wrong forum.
Assuming you know the name/index of the column with "hamilton"
You can apply the same logic to a sql construct. Look into the schema information views (system views), this will give you the ordinal position of the column you found and simply increment the ordinal position to get the column name. This will require a lot of dynamic SQL
[/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No oops, you are in the right forum.
No C#!!!
I usually do "everything" in sql and trigger in c# events, i d´ont filter/sort in datagridview (just small things), i just throw info msg´s in c# like OK:row added,deleted,edited ERROR:whatever...
I thought on that idea during a work, didn ´t need it but...maybe i will need on later works.
Thanks
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hamilton will always be in the 1st column, so simply query for the 2nd. 
OK, OK...
It sounds like you need a more flexible database design; rather than
Race, 1stDriver, 2ndDriver, 3rdDriver, ...
Have something more like:
Race, Place, Driver
|
|
|
|
|
the design/strucure is fine, all queries get what i need.
this was an exemple like i said before, sql is finished.
But during the work i thought on that idea, maybe i could/need it on later works. 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
SELECT DATEDIFF(minute, start.Date_TimeWorked, end.Date_TimeWorked) AS ElapsedMinutes FROM TableName start, TableName end WHERE start.ID = end.ID AND start.Status = 'Ready' AND end.Status = 'Completed'
Ready-Complete ok, but I want one more condition added to it, I need time between Date_Timeworked when status is Ready and Rejected?
|
|
|
|
|
|
|
SELECT DATEDIFF(minute, start.Date_TimeWorked, end.Date_TimeWorked) AS ElapsedMinutes FROM TableName start, TableName end WHERE start.ID = end.ID AND start.Status = 'Ready' AND end.Status = 'Completed' and (Date_Timeworked between '2008-09-10' and '2008-09-10' and start.Status = 'Ready' and start.Status = 'Rejected')
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
use getdatetime() to get now time.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi,
Whats the best way for importing a fixed length file to SQL Server 2005. Currently I am using DTS. Are there any better ways to achieve this??
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
It's hard to say what's a better way since you don't specify what is it that you are not satisfied with in DTS.
However, another alternative could be using bcp (Bulk Copy Program) from command line or SqlBulkCopy class from C#.
Mika
|
|
|
|
|
Is it possible to count rows with a multi-selected query? I have no problems counting in one table however, my real goal is to count rows of mutiple tables.
Here's a sample. This counts for one table
SELECT COUNT(*) FROM table_1 WHERE condition
And what I want is to count this multiselected one:
SELECT table_1.column1,table_2.column4,table_3.column7 FROM table_1,table_2,table_3 WHERE condition
Do you know how to count the rows of this one? Is it possible? Thanks!
|
|
|
|
|
SELECT table_1.column1,<br />
(select count(*) from table1 as t1 where t1.col = table_1.col) as CountTable1,<br />
<br />
table_2.column4,<br />
(select count(*) from table2 as t2 where t2.col = table_2.col) as CountTable2,<br />
<br />
table_3.column7<br />
(select count(*) from table3 as t3 where t3.col = table_3.col) as CountTable3<br />
<br />
FROM table_1,table_2,table_3 WHERE condition
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I GOT IT! Thanks! 
|
|
|
|
|
No problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I've spent half of the day trying to install SQL Server 2008, but it wouldn't install. At the very end it shows me a table saying that "Database Engine Services Failed", and "Replication Failed". Evidently, because of this failures, the service in the services (control panel) will not start
|
|
|
|
|
The only thing that comes in mind is that this could be a result if the computer is a domain controller.
|
|
|
|
|
Hi,
Our requirement is to have an automated email sent to particular addresses every night by reading records from a table meeting particular condition , generating report from those records .
How to achieve this in case of sql server 2000?
or is it that , i need to write an service in dot net and let it run on the system at at particular time it will read the table and generate report and send it to particular addresses?
Please give me the proven aproach which I can take?
regards
Vijay
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
