Author Topic: SQL Query help  (Read 10873 times)

Offline AsherO

  • Global Moderator
  • Dansdeals Lifetime 30K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 30533
  • Total likes: 7831
  • DansDeals.com Hat Tips 79
    • View Profile
  • Location: NYC
Re: SQL Query help
« Reply #40 on: May 14, 2015, 03:03:33 PM »
In T-SQL (MS SQL Server) you can just say isnull([ColumnName], 'replacenullwiththistext')
100% of Likes will be donated to support our brothers and sisters in Eretz Yisrael

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: SQL Query help
« Reply #41 on: May 15, 2015, 10:49:59 AM »
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: SQL Query help
« Reply #42 on: July 02, 2015, 12:53:38 PM »
How can we do an exact match search and replace with PHPMyadmin?

I did
Code: [Select]
SET `payment_amount` = replace(payment_amount, '10', '10.00')But it will replace records like 100, 1000 etc.
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline JoeyShmoe

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1276
  • Total likes: 254
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Lakewood
Re: SQL Query help
« Reply #43 on: July 02, 2015, 01:15:59 PM »
How can we do an exact match search and replace with PHPMyadmin?

I did
Code: [Select]
SET `payment_amount` = replace(payment_amount, '10', '10.00')But it will replace records like 100, 1000 etc.

Try
Code: [Select]
SET payment_amount = IF(payment_amount = '10', '10.00', payment_amount)
OR you can do
Code: [Select]
SET `payment_amount` = replace(payment_amount, '10', '10.00') WHERE payment_amount = '10'
Both should work
DDF A-Z Link Extension
Chrome
Firefox
Info

Offline AsherO

  • Global Moderator
  • Dansdeals Lifetime 30K Presidential Platinum Elite
  • **********
  • Join Date: May 2008
  • Posts: 30533
  • Total likes: 7831
  • DansDeals.com Hat Tips 79
    • View Profile
  • Location: NYC
Re: SQL Query help
« Reply #44 on: July 02, 2015, 01:21:58 PM »
OR you can do
Code: [Select]
SET `payment_amount` = replace(payment_amount, '10', '10.00') WHERE payment_amount = '10'

This one should be much more efficient.
100% of Likes will be donated to support our brothers and sisters in Eretz Yisrael

Offline Alexsei

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
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).
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline JoeyShmoe

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1276
  • Total likes: 254
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
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?
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline JoeyShmoe

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1276
  • Total likes: 254
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
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
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline itsyehuda

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Feb 2013
  • Posts: 1637
  • Total likes: 6
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
Re: SQL Query help
« Reply #51 on: July 02, 2015, 01:43:53 PM »
CAST the value as an int?
This is the value
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ

Offline JoeyShmoe

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Nov 2014
  • Posts: 1276
  • Total likes: 254
  • 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

Offline itsyehuda

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Feb 2013
  • Posts: 1637
  • Total likes: 6
  • 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

  • DansDeals Copper Elite
  • *
  • Join Date: Mar 2014
  • Posts: 11
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
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 »

Offline Shevs177

  • DansDeals Copper Elite
  • *
  • Join Date: Mar 2014
  • Posts: 11
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
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

Offline ponash123

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jun 2015
  • Posts: 473
  • Total likes: 264
  • 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 Presidential Platinum Elite
  • *********
  • Join Date: Sep 2011
  • Posts: 5789
  • Total likes: 1421
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
  • Programs: COVID-23
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
Jews ≠ Zionists
Palestinians ≠ Hamas
Satmar ≠ SatmarHQ