Author Topic: SQL Query help  (Read 3116 times)

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #45 on: July 02, 2015, 01:24:09 PM »

Code: [Select]
SET `payment_amount` = replace(payment_amount, '10', '10.00') WHERE payment_amount = '10'

Code: [Select]
SET  `payment_amount` = REPLACE( payment_amount,  '10',  '10.00' ) WHERE payment_amount =  '10'# MySQL returned an empty result set (i.e. zero rows).
Слава Україні! Героям слава!

Offline JoeyShmoe

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2014
  • Posts: 905
  • Total likes: 28
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #46 on: July 02, 2015, 01:26:44 PM »
Code: [Select]
# MySQL returned an empty result set (i.e. zero rows).

If you do
Code: [Select]
SELECT...WHERE payment_amount =  '10' do you also get zero rows? If yes, then you don't have anything that matches this.
DDF A-Z Link Extension
Chrome
Firefox
Info

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #47 on: July 02, 2015, 01:30:59 PM »
If you do
Code: [Select]
SELECT...WHERE payment_amount =  '10' do you also get zero rows? If yes, then you don't have anything that matches this.
Ok.... I see what the issue is, it is actually stored in the db as 10.00 somehow when i'm pulling the data with another program it gets it without the decimals, is this a setting a need to change in the database structure?
Слава Україні! Героям слава!

Offline JoeyShmoe

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2014
  • Posts: 905
  • Total likes: 28
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #48 on: July 02, 2015, 01:34:52 PM »
Ok.... I see what the issue is, it is actually stored in the db as 10.00 somehow when i'm pulling the data with another program it gets it without the decimals, is this a setting a need to change in the database structure?

If the database is saving (and returning) it with the decimal points, I would assume the "issue" is with the program. Can the program be edited? If yes, which language is it written in?
DDF A-Z Link Extension
Chrome
Firefox
Info

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #49 on: July 02, 2015, 01:38:00 PM »
If the database is saving (and returning) it with the decimal points, I would assume the "issue" is with the program. Can the program be edited? If yes, which language is it written in?
No have no source code
Слава Україні! Героям слава!

Online itsyehuda

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Feb 2013
  • Posts: 1649
  • Total likes: 3
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Brooklyn. NY
Re: SQL Query help
« Reply #50 on: July 02, 2015, 01:42:40 PM »
Code: [Select]
SET  `payment_amount` = REPLACE( payment_amount,  '10',  '10.00' ) WHERE payment_amount =  '10'# MySQL returned an empty result set (i.e. zero rows).

CAST the value as an int?
I remember when a coke was a nickel, and you could buy VRs in OD.

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #51 on: July 02, 2015, 01:43:53 PM »
CAST the value as an int?
This is the value
Слава Україні! Героям слава!

Offline JoeyShmoe

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2014
  • Posts: 905
  • Total likes: 28
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #52 on: July 02, 2015, 01:45:58 PM »
This is the value


This is definitely the correct way to save a price decimal in the database, the program is probably rounding to a full number
DDF A-Z Link Extension
Chrome
Firefox
Info

Online itsyehuda

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Feb 2013
  • Posts: 1649
  • Total likes: 3
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Brooklyn. NY
Re: SQL Query help
« Reply #53 on: July 02, 2015, 01:47:42 PM »

This is definitely the correct way to save a price decimal in the database, the program is probably rounding to a full number

Probably a type issue in the program.
I remember when a coke was a nickel, and you could buy VRs in OD.

Offline Shevs177

  • Non-Elite Member
  • Join Date: Mar 2014
  • Posts: 10
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
SQL Query help
« Reply #54 on: July 10, 2015, 02:31:02 PM »
Looking for a dynamic query builder interface. In other words, user can run a report within his software that has not been predetermined, with access to all tables and fields.
For example: he may choose to search for customers that live in Boston and like steak a lot. Or he can run report to find people who like steak but didn't complete their purchase.
I found http://devtools.korzh.com/easyquery/javascript/ but it's buggy
Program, written in node is already underway, and has many features built so I'd prefer not to switch to new language
Any leads are appreciated

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3304
  • Total likes: 54
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: SQL Query help
« Reply #55 on: July 10, 2015, 03:09:35 PM »
Looking for a dynamic query builder interface. In other words, user can run a report within his software that has not been predetermined, with access to all tables and fields.
For example: he may choose to search for customers that live in Boston and like steak a lot. Or he can run report to find people who like steak but didn't complete their purchase.
I found http://devtools.korzh.com/easyquery/javascript/ but it's buggy
Program, written in node is already underway, and has many features built so I'd prefer not to switch to new language
Any leads are appreciated
Check out Chartio
« Last Edit: July 10, 2015, 03:25:38 PM by lubaby »
Once the game is over, the king and the pawn go back in the same box.

Offline Shevs177

  • Non-Elite Member
  • Join Date: Mar 2014
  • Posts: 10
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #56 on: July 10, 2015, 05:58:47 PM »
Cool thanks! Looks good. I scheduled a demo to see if meets my needs. Do you have first hand experience with them?

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3304
  • Total likes: 54
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: SQL Query help
« Reply #57 on: July 10, 2015, 07:28:58 PM »
Cool thanks! Looks good. I scheduled a demo to see if meets my needs. Do you have first hand experience with them?
Tried it out for a bit a year ago; was impressed with the fluidity of it all but we decided to go without it. We were looking for something simple to get the sales managers at my old job a way to easily query the various DB's without learning SQL or bothering the DBA.

Should do the job for what you've mentioned and then some. Not sure what their pricing model looks like nowadays.

Or you can teach your users basic SQL use and your DB structure (make sure to only give them READ permissions so they don't mess anything up)  ;D ;D
Once the game is over, the king and the pawn go back in the same box.

Offline ponash123

  • Dansdeals Gold Elite
  • ***
  • Join Date: Jun 2015
  • Posts: 102
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
Re: SQL Query help
« Reply #58 on: September 04, 2015, 01:51:15 PM »
Can anyone help with this:
I want to base my select statement on the value of my parameter. I'm getting this error - An insufficient number of arguments were supplied for the procedure or function dbo.udf_Test.

My statement looks something like this:

IF (@testparam <> 'Test')
BEGIN
SELECT 
test fields
FROM test table
END
ELSE
BEGIN
SELECT   
 test fields
FROM testtable2
END

Offline Alexsei

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Sep 2011
  • Posts: 2000
  • Total likes: 18
  • DansDeals.com Hat Tips 2
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Champlain NY, with 1' in canada
Re: SQL Query help
« Reply #59 on: December 08, 2015, 10:29:01 AM »
My query is set up like this

update orders_products set products_name = replace(products_name, 'Product ABC', 'Supplies:Product ABC');

It seems like every time the cron runs it adds another "supplies:"

it ends up after being run a few times as:

Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Supplies:Product ABC

So i need to rewrite the statement to ignore if it already exists
Слава Україні! Героям слава!