SQL Statement

Hi,

I have the SQL statement below that’s works but puts ‘ ’ around the results of the column micros.mi_def.name_1 , how can I remove the ‘ ’ ?


Results:
1001011,'Fishcakes',21,3
1005009,'Angus Burger',11,1
1005019,'Bacon Burger',24,2
1006004,'Four Cheese',18,2

SQL:
SELECT item = micros.mi_def.obj_num, name = micros.mi_def.name_1, Sales = SUM(CONVERT(INT, (price_1_sls_ttl+price_2_sls_ttl+price_3_sls_ttl+price_4_sls_ttl))), Quantity = sum(price_1_sls_cnt+price_2_sls_cnt+price_3_sls_cnt+price_4_sls_cnt)
FROM micros.rest_status, micros.rvc_def, micros.dly_rvc_mi_ttl
right outer join micros.mi_def
where rvc_def.rvc_seq = dly_rvc_mi_ttl.rvc_seq
and mi_def.maj_grp_seq in (1,2,3)
and dly_rvc_mi_ttl.business_date = rest_status.business_date -1
GROUP BY micros.mi_def.maj_grp_seq, micros.mi_def.obj_num, micros.mi_def.name_1
ORDER BY item;
output to 'c:\export\test.001'
hellblazerukAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
Try changing the output line to

OUTPUT TO 'c:\export\test.001' FORMAT ASCII DELIMITED BY ',' QUOTE '';

mlmcc
0
 
mlmccCommented:
What database are you running against?

I suspect it is a setting in your database configuration to export text fields with quotes around them.

mlmcc
0
 
vastoCommented:
This is normal. The type of micros.mi_def.name_1 is varchar, string, text ( whatever is the type for string in your database) and the export will add ' to distinct the text from the other data. Actually this formatting is done by the command "output" and this is not a SQLServer command ( at leas not SQL92). Search for options for this command , may be there is a parameter , which will allow you to export the data without formatting
0
 
PortletPaulfreelancerCommented:
What database are you using please?

Your query has syntax issues, and while you state "it works" it may not be reliable.
1. There are 4 tables in the FROM clause, but only 2 join conditions in the WHERE clause (so there is a missing join condition)
2. you mix non-ANSI and ANSI join syntax, that is never good. Use only ANSI syntax.

FROM micros.rest_status
   , micros.rvc_def
   , micros.dly_rvc_mi_ttl
     RIGHT OUTER JOIN micros.mi_def --<< missing ???

WHERE rvc_def.rvc_seq = dly_rvc_mi_ttl.rvc_seq
      AND dly_rvc_mi_ttl.business_date = rest_status.business_date - 1

      AND mi_def.maj_grp_seq IN (1, 2, 3)

Open in new window

0
 
hellblazerukAuthor Commented:
Hi,

the database is SQL anywhere 11.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.