mysql-select ,MYSQL-LEFT mysql-in mysql-if, mysql-case,mysql-like

Deci 2 or 3

if (MODE.equals("P")){documentBody="PRINT_PINV_LINE2";}
String documentLineSqlString="SELECT ITEM, MODEL , DESCRIPTION "+
",if(MOD(SUM(QTY),1)=0,format(SUM(QTY),0),format(SUM(QTY),1)),UNIT"+
",if(if(PRICE=0 or isnull(price),1,PRICE/ROUND(PRICE,2)) <>1,format((PRICE),3),format((PRICE),2))"+
",format(SUM(QTY*PRICE),2) AMOUNT FROM "+documentBody+" WHERE PARENT_RECORDID="+recordID+" GROUP BY DESCRIPTION,PRICE HAVING not (AMOUNT=0 AND DESCRIPTION='Discount') ORDER BY ROW_NO;"; //20150914

from select

SELECT STATUS FROM SALES_ORDER_HEAD WHERE RECORDID=(SELECT PARENT_RECORDID FROM SALES_ORDER_INV_DESC WHERE RECORDID=14211

32228262);




gROUP LONG STRING 

group by 

select sum(crc32(some_column)) from some_table;

The tools by default use the CRC32. Other good choices include MD5 and SHA1. 

NOT IN SELECT


SELECT RECORDID 
FROM PRDTMSTR WHERE NAME LIKE '%ANNUAL%'
AND (CATEGORY= 234356562522  OR CATEGORY= 1390290257098)
AND RECORDID NOT IN (
SELECT PRDTMSTR_RECORDID FROM VENDOR_PRODUCT 
WHERE VENDOR_NO=1412060580598)

;

SELECT RECORDID FROM PRODUCT WHERE PRODUCT.RECORDID  NOT IN (SELECT OLD_RECORDID FROM PRDTMSTR WHERE OLD_RECORDID IS NOT NULL);

max

select doc_no,DESCRIPTION from PRDTMSTR WHERE PRDTMSTR.DOC_NO =(SELECT MAX(DOC_NO) FROM PRDTMSTR);


INTO

SELECT Id, dateCreated FROM products INTO iId, dCreate

Date
 select date(timestamp)>'20150130',TIMESTAMP from DNOTE_HEADER;

2 3	SET @email_list = ""; CALL build_email_list(@email_list); SELECT @email_list;

if

where=where SOHSTA:`A` AND SOqty>poqty  and SUPPLIER like `^'+parent.currentEntity+'^` and SOP_CUR like `^'+parent.currentCurrency+'^` and FIND_IN_SET(TRIM(SODP_RecordID),"'+parent.currentChoose+'"):0 AND CONFIRM:1   and PVIA like `^'+parent.currentVIA+'^` and  IF(`'+parent.currentVIA2+'`:`X`,PVIA<>` `,TRUE)

at
SELECT AC_CODE,NO_2,CONCAT('1188-',NO_2,'-00') 
,CONCAT('1188-',substring(no_2,instr(no_2,'\-')+1),'-00')
FROM CUSTOMER
WHERE (no_2  LIKE '%\-%') and AC_CODE=''; 


substr
SELECT AC_CODE,NO_2,CONCAT('1188-',NO_2,'-00')
,CONCAT('1188-',substring(no_2,instr(no_2,'\-')+1),'-00')
FROM CUSTOMER
WHERE (no_2  LIKE '%\-%') and AC_CODE='';





left
select SecurityCode,MarketCode, Isincode,instrumenttype,spreadtablecode
,left(currencycode,3),left(securitynamegccs,20) from SecurityDefinition;

like

SELECT AC_CODE,NO_2,CONCAT('1188-',NO_2,'-00') FROM CUSTOMER

WHERE no_2  NOT  LIKE '%\-%';

SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;

where=where SOqty>poqty  and SUPPLIER like `^'+parent.currentEntity+'^` and FIND_IN_SET(TRIM(SODP_RecordID),"'+parent.currentChoose+'"):0

in

select DESCRIPTION , RECORDID FROM SETTINGS WHERE DESCRIPTION IN('GOVERNMENT','COMMERCIAL');
SELECT DESCRIPTION,RECORDID FROM ROLE WHERE recordid in(17,18,19,21,22,23,24,25,26,27,28,35,36,37,38);


case (if)

SELECT 
ADDRESS_BOOK.*
,CASE ADDRESS_BOOK.PHONE
WHEN '' THEN VENDOR.PHONE_NO
ELSE ADDRESS_BOOK.PHONE
END AS TEL
FROM ADDRESS_BOOK

LEFT JOIN VENDOR ON VENDOR.RECORDID=ADDRESS_BOOK.PARENT_RECORDID;


Like

Select * from FilteredAccount WHERE name like '%damian'

Select * from FilteredAccount WHERE name like '%damian%'


Group_concat

select GROUP_CONCAT(CODE,",",DESCRIPTION),DATA_GROUP FROM SETTINGS  GROUP BY DATA_GROUP ;

GROUP_CONCAT(SOPQTY.PURCHASE_ORDERS) PURCHASE_ORDERS

SELECT GROUP_CONCAT(DISTINCT categories ORDER BY categories ASC SEPARATOR ' ') FROM table

//GROUP_CONCAT(BAL_INVS SEPARATOR '\n')

COUNT

Select count(*) pk from merged grup by pk having count(*)>1;

FIND_IN_SET

SELECT SUPPLIER FROM SOQTYOS WHERE  ! FIND_IN_SET(TRIM(SUPPLIER),'1394705940696,232') >0;

count distinct

SELECT COUNT(DISTINCT RECORDID) FROM GRN_HEADER_VIEW0;
SELECT PARENT_RECORDID, COUNT(DISTINCT RECORDID) FROM SALES_ORDER_INV_DESC GROUP BY PARENT_RECORDID;


sha 

String s=""; ResultSet rs;
s="select GROUP_CONCAT(db,',',companyname) from COMPANY_LIST "
+" WHERE login_id='"+ loginId +"'"
+"and password=sha('"+password+"') ";
rs=st.executeQuery(s);
if(rs.next()) {bean.setCompanys(rs.getString(1));}

mysql-sha

other
where=where SOqty>poqty  and SUPPLIER like `^'+parent.currentEntity+'^` and FIND_IN_SET(TRIM(SODP_RecordID),"'+parent.currentChoose+'"):0