John Wilson
01/24/2023, 9:37 PMJohn Wilson
01/24/2023, 9:40 PMJohn Wilson
01/24/2023, 9:43 PMbdw429s
01/24/2023, 9:44 PMJohn Wilson
01/24/2023, 9:44 PMJohn Wilson
01/24/2023, 9:44 PMJohn Wilson
01/24/2023, 9:46 PMbcp veritiadmin.tmp.sedol_axioma format nul -T -n -t"|" -x -f axiomaSedolBCP.fmt -S vdbs.veriti.local
and runs like this
bcp veritiadmin.tmp.sedol_axioma in "C:\Axioma\Downloader\output\extractedFiles\flatfile\AXWW4-MH.20221227.idm" -f "C:\Axioma\Downloader\output\axiomaSedolBCP.fmt" -F 7 -S vdbs.veriti.local -T -N
The real data file has 52K rows and needs to be imported every morning.Patrick
01/24/2023, 9:49 PMJohn Wilson
01/24/2023, 9:49 PMJohn Wilson
01/24/2023, 9:50 PMJohn Wilson
01/24/2023, 9:50 PMPatrick
01/24/2023, 9:53 PMgavinbaumanis
01/24/2023, 10:18 PMormExecuteQuery("SELECT .....
Because if you had
ormExecuteQuery(
"SELECT...
It would fail.gavinbaumanis
01/24/2023, 10:22 PM"this is a 'String' "
Are dates strings? or objects?
What does BCP expect?
I tend to always make sure that the date is in YYYY-MM-DD format - regardless of your regions conventi0on. DB's always "just" get the monthh and date the right way around - if you use YYY-MM-DD format.
Do you have commas "," in strings?
In that is a COMMA a delimiter - and thus "mucking up" count of expected values?Michael Gillespie
01/24/2023, 10:27 PMJohn Wilson
01/24/2023, 10:33 PMJohn Wilson
01/24/2023, 10:34 PMiknowkungfoo
01/28/2023, 6:01 AMx
and y
.
• x.cpt_code
is nvarchar(255)
• y.cpt_code_id
is an int.
• x.cpt_code
can have a comma delimited list of numbers
• I need to join the two tables on the numeric value of the int
.
Figured that if I cast the string to an int
, then the list would wash out. Not the case.
x on cast(x.cpt_code as unsigned) = y.cpt_code_id
matches a single number a
to a list of numbers where the first element is a
.
x on x.cpt_code = y.cpt_code_id
does the exact same thing.
However, if I cast the int
to nchar
, the string matches only when string is the exact numeric value of the int
.
on x.cpt_code = cast(y.cpt_code_id as nchar)
For example:
select CAST('76830,76856' as UNSIGNED) as id;
Returns 76830
iknowkungfoo
01/28/2023, 6:09 AMint
.iknowkungfoo
01/28/2023, 6:10 AMiknowkungfoo
01/28/2023, 6:11 AMactive
column to table x
and turning off the records with a list.chris-schmitz
01/28/2023, 9:25 AMcast( value as UNSIGNED)
stops processing value
as soon as it encounters something that does not represent a number, iirc. So here it would stop at the comma.
What you could try is to build the join on a LIKE clause:
ON x.opt_code LIKE concat( '%', y.opt_code_id, '%')
Definitely not the fastest join, but, it should workchris_hopkins
01/29/2023, 12:02 AMAdam Cameron
02/06/2023, 10:14 AMI am going to ask a contextless question because any context will influence the point of the question, and accordingly the answer.
THIS IS IMPORTANT: Any answers given should be from existing knowledge, and not from googling. I'm not testing ppl's ability to google stuff. I am trying to establish what "common knowledge" on the topic is.
MySQL "CREATE TABLE ... SELECT Statements". Any observations?Cheers. Sorry it's vague.
John Wilson
02/07/2023, 10:35 PMselects
only?gsr
03/11/2023, 3:38 PMselect individual_Ratings.*,count(rating) as tRating from individual_Ratings where productID = 99 group by rating
and my query is giving me an error on this
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shopzone1.individual_Ratings.individualid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
gsr
03/11/2023, 3:38 PMgsr
03/11/2023, 3:40 PMmysql> ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 49
Current database: *** NONE ***
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
mysql> ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))' at line 1
mysql> ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));ET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
websolete
03/11/2023, 4:01 PMwebsolete
03/11/2023, 4:02 PMwebsolete
03/11/2023, 4:02 PMAdam Cameron
03/11/2023, 6:28 PM