• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Stored procedure gives me nulls

 
Ranch Hand
Posts: 90
Java ME MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey, guys, I am running this to create a stored procedure but it gives me nulls if I do:



But if I run it as a single Select statement it gives me the values that I am looking for:

 
Bartender
Posts: 1275
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're telling us very little about your environment. What database are you using? How do you call your sp? Can you post an excerpt of your caller code?
In the second SP code you posted an END seems to be missing. Is it just a typo?
 
Daniel Stallard
Ranch Hand
Posts: 90
Java ME MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes sorry the second one is suposed to be just the select statement. and im using php admin so all im doing is going to the routines and executing the stored proc for now
 
Daniel Stallard
Ranch Hand
Posts: 90
Java ME MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That second statment should look like this sorry about that.



my tables are these that im pulling from:

 
Marshal
Posts: 25812
69
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You say "nulls" -- does that mean you get one row of nulls, or what?

And maybe there's something about your SQL dialect I'm missing, but don't you need a GROUP BY clause to control the SUM function?
 
Claude Moore
Bartender
Posts: 1275
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moreover, what happens if you run your SP directly on your database - for example, from a CLI interface or something else more sophisticated ?
 
Master Rancher
Posts: 4663
49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why does your FROM clause have the Flight table in there?
It's not used anywhere, or JOINed or anything.

In addition, the second query (the one you reposted) is using a different flight id (TG453 rather than UH989).

ETA:  And as Paul says, assuming this is MySQL (well, any reasonable RDBMS), a SUM is going to need a GROUP BY, so are you sure these queries are working as displayed?
 
Daniel Stallard
Ranch Hand
Posts: 90
Java ME MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes they work without a group by withount being in a stored proc ill try the group by in the stored. I tried distinct but it didnt work. An as far as the flight id changinging cause i have many flight ids i need to know the total that they brought in.Based on a given flight id. so my outtput shoud be the given flith id an its total Revenue brought in. But when i put it in a stored proc i get null in both columns.
 
Dave Tolls
Master Rancher
Posts: 4663
49
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Quick read of the docs, and it does appear that MySQL treats an aggregation function without a GROUP BY as grouping by all columns in the result.
Learn something new everyday!


Still not sure why Flight is in the FROM clause, though.

Does the result from the stored procedure work correctly if you hard code a flight number in it?
 
Daniel Stallard
Ranch Hand
Posts: 90
Java ME MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i havent tried that ill try it an let you know. An i meant to take flight out just didnt before i posted it
 
Dave Tolls
Master Rancher
Posts: 4663
49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you got a Stored Procedure with a SELECT statement in it that does work?

Just trying to collect together some data points and examples.
 
Daniel Stallard
Ranch Hand
Posts: 90
Java ME MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey guys thanks for all the help and advice.But my error was me when I was executing the stored procedure in php admin I was making a boo boo. I was putting my flight ID in  As 'GH334' instead of GH334 which was why it was giving me a null but I did add the Group by to make it look better.Again thanks a lot
 
Consider Paul's rocket mass heater.
    Bookmark Topic Watch Topic
  • New Topic