SQL Statement

Posted on 2014-09-09
Last Modified: 2014-09-10

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 ‘ ’ ?

1005009,'Angus Burger',11,1
1005019,'Bacon Burger',24,2
1006004,'Four Cheese',18,2

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'
Question by:hellblazeruk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 100

Expert Comment

ID: 40312406
What database are you running against?

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

LVL 18

Expert Comment

ID: 40312652
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
LVL 48

Expert Comment

ID: 40313239
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


Author Comment

ID: 40314646

the database is SQL anywhere 11.
LVL 100

Accepted Solution

mlmcc earned 500 total points
ID: 40314788
Try changing the output line to

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


Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Powershell finalizing the end of an array. 4 33
Help with Progress 4gl Rounding Function 6 60
calculate days away 11 61
Estimating my database size 7 51
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question