Escaping Sequence problem.

[Harish Chandra Durgapal]

Hi All,
Last time I really stuck with a simple String initilization problem..
I have initilise a String variable with a large sql query as follow.....

String query="select rpad('WS'||substr(h.wdcode,1,7),15) wdcode,
rpad('S'||substr(d.brandcode,1,4)||decode(b.unitsperpack,5000,5,7000,1,10000,2,12000,3,6000,4,0),15) brandcode,
rpad(to_char(h.secsaledate,'"WEEK1M"MMYYYY'),15)docdt,
rpad('CL12',15) trntype,lpad(to_char(sum(d.retailer),'999999999'),15) qty
from basis.dp_secsaleheader h, basis.dp_secsaldetail d, basis.brandmstr b
where h.secsaleheadercode = d.secsaleheadercode and d.secsalecategorycode=1 and b.company='GPI' and d.brandcode = b.brandcode and to_number(to_char(h.secsaledate,'DD')) <=7 and h.secsaledate >= to_date'01092006','DDMMYYYY') and h.secsaledate <= to_date('30092006','DDMMYYYY')and d.retailer>0 and substr(h.wdcode,1,2) ='02' group by rpad('WS'||subst(h.wdcode,1,7),15),rpad('S'||substr(d.brandcode,1,4)||decode (b.unitsperpack,5000,5,7000,1,10000,2,12000,3,6000,4,0),15),
rpad(to_char(h.secsaledate,'"WEEK1M"MMYYYY'),15),
rpad('CL12',15)"
This gives me a error String literal is not properly closed by double quote.
Can any one guide me how to initilise this query to a String variable.
Thanks
harish.

[Ernest Friedman-Hill]

Is this all on one line, or are those line breaks really in your code? You have to have it all on one line in the code. I also can't tell if you have actual double quotes inside the String or not; if you do, then you need to put a "\" backslash before each one.

[Joanne Neal]

If I were you I'd build the statement using a StringBuffer/StringBuilder. It will make it a lot easier to maintain.
e.g.

StringBuffer sbQuery= new StringBuffer("select");
sbQuery.append("rpad('WS'||substr(h.wdcode,1,7),15) wdcode,");
sbQuery.append("rpad('S'||substr(d.brandcode,1,4||decode(b.unitsperpack,5000,5,7000,1,10000,2,12000,3,6000,4,0),15) brandcode,");
sbQuery.append("rpad(to_char(h.secsaledate,'"WEEK1M"MMYYYY'),15)docdt,");
etc.
String query = sbQuery.toString();

As Ernest said, there's probably some double quotes in there which you will need to escape.

[ October 04, 2006: Message edited by: Joanne Neal ]
[ October 04, 2006: Message edited by: Joanne Neal ]

[Stefan Wagner]

And I would put linebreaks and tabs into the sql, to make debugging more convenient:

String query= "select rpad ('WS' || substr (h.wdcode, 1, 7), 15) wdcode,"
+ "\n\t rpad ('S' || substr (d.brandcode, 1, 4) || decode (b.unitsperpack, 5000, 5, 7000, 1, 10000, 2, 12000, 3, 6000, 4, 0), 15) brandcode,"
+ "\n\t rpad (to_char (h.secsaledate, '\"WEEK1M\"MMYYYY'), 15) docdt,"
+ "\n\t rpad ('CL12', 15) trntype,"
+ "\n\t lpad (to_char (sum (d.retailer), '999999999'), 15) qty"
+ "\n\t from "
+ "\n\t\t basis.dp_secsaleheader h, "
+ "\n\t\t basis.dp_secsaldetail d, "
+ "\n\t\t basis.brandmstr b"
+ "\n\t where h.secsaleheadercode = d.secsaleheadercode "
+ "\n\t and d.secsalecategorycode = 1 "
+ "\n\t and b.company = 'GPI' "
+ "\n\t and d.brandcode = b.brandcode "
+ "\n\t and to_number (to_char (h.secsaledate, 'DD')) <= 7 "
+ "\n\t and h.secsaledate >= to_date ('01092006', 'DDMMYYYY') "
+ "\n\t and h.secsaledate <= to_date ('30092006', 'DDMMYYYY')"
+ "\n\t and d.retailer > 0 "
+ "\n\t and substr (h.wdcode, 1, 2) ='02' "
+ "\n\t group by rpad ('WS' || subst (h.wdcode, 1, 7), 15),"
+ "\n\t rpad ('S' || substr (d.brandcode, 1, 4) || decode (b.unitsperpack, 5000, 5, 7000, 1, 10000, 2, 12000, 3, 6000, 4, 0), 15),"
+ "\n\t rpad (to_char (h.secsaledate, '\"WEEK1M\"MMYYYY'), 15),"
+ "\n\t rpad ('CL12', 15)";
System.out.println (query);
 

How readable is that?
[ October 04, 2006: Message edited by: Stefan Wagner ]

[Harish Chandra Durgapal]

Hi Ernest,Joanne,Stefan

Thanks a lot .It solved my problem.