Visit Our UNIX and Linux User Community


calling mysql gurus : need help with my view


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting calling mysql gurus : need help with my view
# 1  
Old 09-02-2009
calling mysql gurus : need help with my view

hi there

I have a view which is working fine, but i have been told that i need to make sure the resulting output excludes all rows with nic_status equal to the string "removed".

Does anybody know in which part of the code below i would place the conditional ...

Code:
WHERE nic_status != 'removed'

view code below

Code:
CREATE VIEW `view2` AS 
select 
`t2`.`serial` AS `serial`,
`t2`.`nic_name` AS `nic_name`,
`t2`.`nic_ip` AS `nic_ip`,
`t2`.`nic_duplex` AS `nic_duplex`,
`t2`.`nic_speed` AS `nic_speed`,
`t2`.`nic_status` AS `nic_status`,
`t2`.`datetimestamp` AS `datetimestamp` 
from `network` `t2` 
where (`t2`.`serial`,`t2`.`nic_name`,`t2`.`datetimestamp`) in
(select `network`.`serial` AS `serial`,`network`.`nic_name` AS `nic_name`,max(`network`.`datetimestamp`) AS `MAX(datetimestamp)` 
from `network` where ((`network`.`serial` = `t2`.`serial`) and (`network`.`nic_name` = `t2`.`nic_name`)) group by `network`.`serial`,`network`.`nic_name`) 
order by `t2`.`serial`,`t2`.`nic_name`;

any help on this would be great

Last edited by hcclnoodles; 09-02-2009 at 07:35 PM..
# 2  
Old 09-02-2009
as you're refering to a previous discussion, could you, at least, link to it.
And so, we would all be able to know exactly what it's about...

else help is here
# 3  
Old 09-02-2009
Quote:
Originally Posted by hcclnoodles
...
I have a view which is working fine, but i have been told that i need to make sure the resulting output excludes all rows with nic_status equal to the string "removed".
...
The answer is: "It depends".
You are doing two things in your view -
(a) determining the maximum datetimestamp for each tuple (serial, nic_name) of the network table.

(b) Fetching a few more columns for the tuple (serial, nic_name, max_datetimestamp), nic_status included.

Now, do you want to consider status = "removed" while finding out the max of datetimestamp ?

If no, then the predicate simply gets added to the subquery.

If yes, then the predicate goes in the outer query.

Again, in the latter case, what do you want to do if the max of datetimestamp is for a record that has nic_status = "removed" ? The inner query would fetch that, but it would be discarded by the outer query. Do you want no rows returned for that tuple, or do you want to check for the max datetimestamp of records with nic_status != "removed" in the first place itself ?

HTH,
tyler_durden
# 4  
Old 09-03-2009
ignore post

Last edited by rethink; 09-03-2009 at 08:25 AM..
# 5  
Old 09-03-2009
thank you for explaining that Tyler. Im pretty new to this so its quite hard for me to get my head around Smilie

I guess end goal is to have a view which will show the "latest" record for that tuple unless that record has has a nic_status of "removed", in which case i dont want to it to return a value for that nic_name at all

For example .... If I have 4 'nic_names' bge0, bge1, bge2 and bge3, and the "latest" time stamped record for 'nic_name' bge1 has nic_status of "removed" , then I would want NO record for bge1 to appear on the view at all ! same goes for any other interfaces

so my view for example will only have latest records for

bge0
bge2
bge3

With this in mind, I guess the answer to your question would be that the predicate would go in the outer query as effectively i would want to

a) get all the latest records for each unique interface
b) once this is all in, return only those that dont have nic_status of "removed"

apologies for the lack of knowledge on how views are organised but would you be able to advise whereabouts in the view, i would place my WHERE statement.. I am having trouble working out which is the inner query and which is the outer ...would I put an "AND" after the current WHERE statement or do I need a whole new WHERE statement in there

Thanks again for helping me with this

Previous Thread | Next Thread
Test Your Knowledge in Computers #539
Difficulty: Medium
In C, int x; creates an integer variable associated with the identifier x initialized to one .
True or False?

9 More Discussions You Might Find Interesting

1. What is on Your Mind?

Moving from Desktop View to Mobile View

See attached video for a demo on how to move back and forth from the desktop view to the mobile view. Currently this only works for the home page, but I will work on some new PHP code in the future to make this work with the page we are currently on. Edit: The issue with making every page ... (2 Replies)
Discussion started by: Neo
2 Replies

2. Shell Programming and Scripting

Question to gurus with expect

Hi., I need to ask question for expect script. I have prompt like # and very long script (orachk). I added to expect script line set prompt "(%|#|\\\$) $" and insert into it also piece of code ---- expect { timeout { puts "Running..." exp_continue } ... (0 Replies)
Discussion started by: beckss
0 Replies

3. Shell Programming and Scripting

Question to gurus about sed.

Hi Folks. I need change something into file and after all manipulation I need delete only last COMMA into this piece of code -> GROUP 1 ( '/oralog1/ORAPRD/log01a.dbf', '/oralog2/ORAPRD/log01b.dbf' ) SIZE 512M, GROUP 2 ( '/oralog1/ORAPRD/log02a.dbf', ... (12 Replies)
Discussion started by: beckss
12 Replies

4. UNIX for Dummies Questions & Answers

Calling all the awk gurus out there.

Hi all, I just signed up to the forums, although, I have lurked on here for awhile. Anyways, my issue is I am trying to get awk to spit out something I can use without having to spend hours in excel hell haha. So, I used sed to replace the spaces with semicolons and redirected that to a file. ... (6 Replies)
Discussion started by: savigabi
6 Replies

5. Shell Programming and Scripting

Hey Perl Gurus

Hey guys im trying to get this if statement to work and i dont know whats wrong. can anybody help? if($author=~/\A+\Z/i)&&(length($author!=0)) { print " $author validation correct" } elsif($author!=~/\A+\Z/i)&&(length($author=0)) { $author='BLANK'; } else { ... (1 Reply)
Discussion started by: neil1983
1 Replies

6. Shell Programming and Scripting

Calling MYSQL Stored Procedure?

Hi, Can anyone help me with the correct syntax to call a MYSQL stored procedure from a shell script. I have tried the following, (no input params): /usr/bin/mysql -uadmin -ppassword call TL_CLENSEDATA(); resulting in error: /home/hosting/data/scripts/dbclense.sh: line 12: syntax error... (3 Replies)
Discussion started by: kshelluser
3 Replies

7. Shell Programming and Scripting

SED GURUS - Help!

I wish to substituite a string on each line but ONLY if it appears within double-quotes: this_string="abc#def#geh" # Comment here I wish to change the "#" characters within the double quoted string to "_": this_string="abc_def_geh" # Comment here ... but as you see, the "comment" hash... (2 Replies)
Discussion started by: Simerian
2 Replies

8. Shell Programming and Scripting

Help with shell script - Unix Gurus calling

Unix Gurus, I have been breaking my head to get this done..seems simple.. I need to read a flat file and based on a key word in a line, i need to skip the previous 3 lines. eg : Line1 Line2 Line3 Line4 Line5 Line6 Error Line7 Line8 Line9 Error Line10 (4 Replies)
Discussion started by: ravred
4 Replies

9. UNIX for Advanced & Expert Users

Any RF unix gurus out there?

I am having a problem here. We are having several problems in regards to hung process's on unix (HPUX box), caused by my RF equipment (Mobile data capture units). these contact the host via a simply telnet session and locks the system? Is it a timeout problem as the timeout is disabled on the host. (5 Replies)
Discussion started by: Subrosa
5 Replies

Featured Tech Videos