Database

Re: Whats wrong with SQL statement? [modified]

2-Mar-10 19:47

Table 1 (UNITS)
ScanID ScanCount OrderID SerialNo ScanStatus ProductionNo
1981 1294 2 147639NZ301774 SHIPPED 10007627289
There are 1300 SerialNo's for ProductionNo That will end with status 'SHIPPED'

Table 2 (Unit_Logs)
LogID SerialNo ProductionNo Old_Status New_Status
1 147639NZ301774 10007627289 PACKED SHIPPED
There will be 1300 records with status changes from PACKED to SHIPPED

Needed RESULT:
ProductionNo PACKED SHIPPED
10007627289 1300 1300

This is what I have tried:
SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED,
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status
HAVING (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')

I added 1 log entry to the table, but the query returns nothing.
Think might need sub query, but not sure where to go from here.

Jörgen Andersson2-Mar-10 21:48

Jörgen Andersson

2-Mar-10 21:48

HAVING is only meant to be used with aggregate functions.
Try something similar to this:

SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED, 
COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS JOIN UNIT_Logs
ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo, UNITS.ScanStatus, UNIT_Logs.Old_Status

<edit>fixed layout</edit>

"When did ignorance become a point of view" - Dilbert

modified on Wednesday, March 3, 2010 3:54 AM

i.j.russell2-Mar-10 22:04

i.j.russell

2-Mar-10 22:04

SELECT UNITS.ProductionNo, 
   COUNT(UNITS.SerialNo) AS SHIPPED,
   COUNT(UNIT_Logs.SerialNo) AS PACKED
FROM UNITS 
   JOIN UNIT_Logs
      ON UNITS.ProductionNo = UNIT_Logs.ProductionNo
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')
GROUP BY UNITS.ProductionNo

gengel2-Mar-10 22:46

Re: Whats wrong with SQL statement? [modified]

2-Mar-10 22:46

Hi,

Thanks for the replies.

This query only seems to make a 1 to 1 join.

So if there is only one log entry it returns both SHIPPED and PACKED as 1 instead of 1294.

ProductionNo SHIPPED PACKED
10007627289 1 1

if i remove:
WHERE (UNITS.ScanStatus = 'SHIPPED') AND (UNIT_Logs.Old_Status = 'PACKED')

ProductionNo SHIPPED PACKED
10007627289 1294 1294

I tried this mess. Will try a few more options with this.
SELECT UNITS.ProductionNo, COUNT(UNITS.SerialNo) AS SHIPPED
FROM UNITS
WHERE UNITS.Scanstatus='SHIPPED' AND
(SELECT COUNT(UNIT_Logs.SerialNo) AS PACKED FROM UNIT_Logs WHERE UNIT_Logs.Old_Status='PACKED')
GROUP BY UNITS.ProductionNo

i.j.russell2-Mar-10 23:35

i.j.russell

2-Mar-10 23:35

Try this one.

SELECT u.ProductionNo,
   u.SHIPPED,
   l.PACKED
FROM 
(
   SELECT ProductionNo, 
      COUNT(SerialNo) AS SHIPPED
   FROM UNITS 
   WHERE ScanStatus = 'SHIPPED'
   GROUP BY ProductionNo
) u
LEFT OUTER JOIN 
(
   SELECT ProductionNo, 
      COUNT(SerialNo) AS PACKED
   FROM UNIT_Logs 
   WHERE Old_Status = 'PACKED'
   GROUP BY ProductionNo  
) l
ON l.ProductionNo = u.ProductionNo

[Fixed typo]

modified on Wednesday, March 3, 2010 7:56 AM

gengel3-Mar-10 1:14

3-Mar-10 1:14

Wow. did not even know you could do this.

I get this error though:
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'l'.

I am going to break it down into components to make sure the parts work and then try and connect it back together again.

Thanks

gengel3-Mar-10 1:17

3-Mar-10 1:17

Sorry found the problem. , before the FROM in the first bracket.

It works perfectly. Thanks for the help.

i.j.russell3-Mar-10 2:00

i.j.russell

3-Mar-10 2:00

You're welcome.

Permission sp_rename

Clas Andersson2-Mar-10 7:23

Clas Andersson

2-Mar-10 7:23

I am creating a dynamic pivot-table with web - sql-server 2008
When the pivot-table is created i want to rename columnname.
I am execute a stored procedure, sp_rename..... from the web-page.

Web-page - select variablesnames
Web-page - variablesname --> short columnname
SQl-server - create pivot table (dynamic sql, short columnnames - 4000 char limit)
Web-page/Sql-server - rename short columnnames to variablesnames

The problem is the permission to execute sp_rename .

How/where can I adjust permission for the the "webuser" in sql-server ?

Thanks in advance
Clas

Re: Permission sp_rename

Mycroft Holmes2-Mar-10 10:52

Mycroft Holmes

2-Mar-10 10:52

I am assuming that you need this table only for display on your form, in which case why don't you rename the columns in the UI!

Never underestimate the power of human stupidity
RAH

Sql issue..

<<Tash18>>1-Mar-10 21:05

<<Tash18>>

1-Mar-10 21:05

I have 2 tables in my data base..
1. User_Group table
2. Function_Group table
Example:
Function_Group table :

Groupid      Applicationid    Functionid

LCUser1	 LCDocMgmt	  LC01
LCUser2	 LCDocMgmt	  LC02
LCUser1	 LCDocMgmt	  LC03
LCUser3	 LCDocMgmt	  LC04
LCUser3	 LCDocMgmt	  LC05
LCUser2	 LCDocMgmt	  LC06
LCUser2	 LCDocMgmt	  LC08
LCUser1	 LCDocMgmt	  LC09

User_Group table:

Userid  Groupid

900410	 LCUser1
900846	 LCUser1
900411	 LCUser3

Now i use the following query to retrieve the Groupid for a particular Userid :

Select ug.groupid from User_Group ug, Function_Group fg where fg.ApplicationID='LCDocMgmt' and fg.GroupID = ug.GroupID and ug.UserID='900846'

The result that i get on executing the above query is:

groupid

LCUser1
LCUser1
LCUser1

Actually i Dont require repetition of records. If there is 2 distinct records i just want the 2 distinct records without repetition.. can any one plz help me..

Thanx in advance....
Regards,
Tash

Re: Sql issue..

J4amieC1-Mar-10 21:15

J4amieC

1-Mar-10 21:15

<<tash18>> wrote:
distinct records

You answered your own question.

Select DISTINCT ug.groupid from User_Group ug, Function_Group fg where fg.ApplicationID='LCDocMgmt' and fg.GroupID = ug.GroupID and ug.UserID='900846'

Re: Sql issue..

<<Tash18>>1-Mar-10 22:44

<<Tash18>>

1-Mar-10 22:44

Thanx alot... That was really a Dumb question from my side i knew that... Anyways a million thanx..

Regards,
Tash

Re: Sql issue..

J4amieC1-Mar-10 23:05

J4amieC

1-Mar-10 23:05

No problem. I also don't think it was particularly dumb compared to the general par.

Should I index this?

Mike6543211-Mar-10 9:22

Mike654321

1-Mar-10 9:22

If I do something like:

Update Field1=something1, Field2=something2
Where Field3=123;

I always index Field3, but I'm not sure if I need to index Field1 and Field2 too?

Thanks!

Luc Pattyn1-Mar-10 9:34

Luc Pattyn

1-Mar-10 9:34

AFAIK you're query is not selecting based on field1 or field2, so it would not benefit from indexing those fields. Future queries obviously may or may not benefit from indexing.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

Mike6543211-Mar-10 9:42

Mike654321

1-Mar-10 9:42

Thank you. Just double checking.

David Mujica1-Mar-10 10:20

David Mujica

1-Mar-10 10:20

The other argument is that if you indexed field1 & field2, you would require more data writes. Both the main table data would be updated and the index data would be updated. Even worse, because you update data that is indexed you could cause all kinds of index reorganization when the value of field1 changes from A to Z.

My 2 cents.
Smile | :)