ATTENTION

This FlexSim Community Forum is read-only. Please post any new questions, ideas, or discussions to our new community (we call it Answers) at https://answers.flexsim.com/. Our new Question & Answer site brings a modern, mobile-friendly interface and more focus on getting answers quickly. There are a few differences between how our new Q&A community works vs. a classic, threaded-conversation-style forum like the one below, so be sure to read our Answers Best Practices.


flexsim.com

Go Back   FlexSim Community Forum > FlexSim Software > Q&A
Downloads

Q&A Using Flexsim and building models

  #1  
Old 02-16-2016
Axel Kohonen
Guest
 
Posts: n/a
Downloads: 22
Uploads: 0
Default Is it possible to do an SQL query with WHERE X > Y where the referenced table contains string data?

Hi,

I have my data in string tables and I am wondering if it is possible to do an SQL query on the table to look for the rows WHERE the value in a certain column is above or below a certain value, i.e.
Code:
 SELECT * FROM table WHERE bestBeforeTime < currentTime
The bestBeforeTime is in a string column and I would like to keep it that way as other code counts on all columns being string data.

I know it is possible to do a compare like this for numerical values, but is it possible to do with a string value? It does not seems to work when I change the current time into a string an then compare the values.

Thanks!

Kind regards,
Axel
  #2  
Old 02-16-2016
sagar bolisetti's Avatar
sagar bolisetti sagar bolisetti is offline
Flexsim User
 
Join Date: Aug 2013
Location: Hyderabad,India
Posts: 160
Downloads: 145
Uploads: 0
Thanks: 124
Thanked 99 Times in 63 Posts
Rep Power: 150
sagar bolisetti is a jewel in the roughsagar bolisetti is a jewel in the roughsagar bolisetti is a jewel in the roughsagar bolisetti is a jewel in the rough
Default

Hi Axel,

Please try the following code


Code:
query(concat("SELECT * FROM table\
WHERE bestBeforeTime<",numtostring(currentTime,0,0)));
You should use numtostring() command which return a string contains a number value
The Following User Says Thank You to sagar bolisetti For This Useful Post:
Axel Kohonen (02-19-2016)
  #3  
Old 02-18-2016
Axel Kohonen
Guest
 
Posts: n/a
Downloads: 22
Uploads: 0
Default

Hi Sagar,

Thank you, but I cannot get it to work correctly. If I do as you outlined above the query does not find anything. Only if I change the table column into numercial format. If I put the timestring into '-signs then the query finds something, but the results are wrong as the code seems to perform a string comparison for the "greater" of the strings. Which gives weird results.

I use a for loop to solve the problem currently which works, but the query could be faster of course.

Any clue on the issue?

Axel
  #4  
Old 02-19-2016
sagar bolisetti's Avatar
sagar bolisetti sagar bolisetti is offline
Flexsim User
 
Join Date: Aug 2013
Location: Hyderabad,India
Posts: 160
Downloads: 145
Uploads: 0
Thanks: 124
Thanked 99 Times in 63 Posts
Rep Power: 150
sagar bolisetti is a jewel in the roughsagar bolisetti is a jewel in the roughsagar bolisetti is a jewel in the roughsagar bolisetti is a jewel in the rough
Default

Hi Axel,

What is bestBeforetime?What values you want to fetch based on this query?
  #5  
Old 02-19-2016
Axel Kohonen
Guest
 
Posts: n/a
Downloads: 22
Uploads: 0
Default

Hi Sagar,

The bestBeforeTime is the time in model seconds when the product gets too old. The time is stored as a string in the table and this seems to be the problem. If I change the same time into a number then the SQL query works as it should. But if the value is stored as a string then the query does not work.

Guess I will need to change the column in th etalbe into number format.

Thanks!
Axel
  #6  
Old 02-19-2016
Jörg Vogel's Avatar
Jörg Vogel Jörg Vogel is offline
Flexsim User
 
Join Date: Sep 2007
Location: Hannover, Germany
Posts: 643
Downloads: 35
Uploads: 0
Thanks: 802
Thanked 665 Times in 410 Posts
Rep Power: 642
Jörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond reputeJörg Vogel has a reputation beyond repute
Default

Hello Axel,

I haven't got a solution since you have found one. But in my search for an answer I have got to the arithmetic of boolean algebra. First of all you use the boolean algebra. In excel there exists the statement strings are greater than numerical values. That is maybe the problem you get when you compare strings with numerical values.

Jörg
The Following User Says Thank You to Jörg Vogel For This Useful Post:
Axel Kohonen (02-19-2016)
  #7  
Old 02-19-2016
Anthony Johnson's Avatar
Anthony Johnson Anthony Johnson is offline
Manager of Product Development
 
Join Date: Jul 2007
Posts: 440
Downloads: 86
Uploads: 4
Thanks: 171
Thanked 899 Times in 288 Posts
Rep Power: 735
Anthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond reputeAnthony Johnson has a reputation beyond repute
Default

When comparing strings, SQL will compare them like using a standard string compare, i.e. it will compare their alphabetical order.

You can always explicitly convert them to numbers as part of the query:
Code:
SELECT * FROM table WHERE stringtonum(bestBeforeTime) < stringtonum(currentTime)
As of 7.7 you can use any FlexSim command as part of the query.
The Following 5 Users Say Thank You to Anthony Johnson For This Useful Post:
Sebastian Hemmann (02-21-2016)
  #8  
Old 02-22-2016
Axel Kohonen
Guest
 
Posts: n/a
Downloads: 22
Uploads: 0
Default

Hi Anthony,

Great, thank you! Did not realize that I can turn both strings into numbers to get the correct comparison. Comparing them as strings obviously does not give the desired result.

Axel

Tags
sql


Thread Thread Starter Forum Replies Last Post
Global Table string value Congshi Wang Q&A 1 05-24-2010 07:38 AM
model related query mohanboyapati Q&A 0 10-11-2009 07:32 PM
Query regarding operator shivrash Q&A 1 07-21-2009 04:53 AM
IF with a String Gavin Douglas Q&A 1 07-14-2008 12:36 PM
Error: Cannot find the input table or query 'Objects'. AlanZhang Q&A 8 05-04-2008 07:06 PM


All times are GMT -6.
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Copyright 1993-2018 FlexSim Software Products, Inc.