SQL query format

[purplesky]

HI everyone,

I am trying to retrieve my data from Ms Access. So i copy my sql from Access and modify abit and try it in SQL developer, It works. But when i write it on excel vba editor, it doesn't work, let say i write ......
"AND (PS_AWH_ADMIN.AWH_AAP_DATA_YEAR.PARAMETER_NAME) Like '%LOSS' " & _
........., why it can't show out related data? I always have a problem on SQL format when in Excel.

2. Erm, how can i convert some rules to coding? example the western electrical 6rules.

Thank you very much :-)

[mdmackillop]

The usual problem is quotation marks. These don't travel well. Check the string appearance of the code to see if it matches the original SQL

[purplesky]

"AND (PS_AWH_ADMIN.AWH_AAP_DATA_YEAR.PARAMETER_NAME) Like '%LOSS' " & _

It did works on sql developer on tis format, i mean '%LOSS'.. I met this problem b4 tat on the word "OPERATOR". I have a column name called "OPERATOR", bt in excel built-in oso hav this world, end up i muz put""Operator"" and it works. Bt this time Like '%LOSS" it cant works. So can u guys give me a solution to al this string format?.pls suggest some link related to SQL qureries write in Excel vba. Thanks lot.. App:content: reciate..

[purplesky]

TRANSFORM SUM(PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR.PARAMETER_VALUE) AS SumOfPARAMETER_VALUE
SELECT PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT, PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE
FROM PS_DWH_ADMIN.DWH_WIP_DATA_YEAR
JOIN PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR
ON PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.OPERATION = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.OPERATION
AND PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.FACILITY = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.FACILITY
AND PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.LOT
JOIN PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR
ON PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.PARAMETER_SEQUENCE_NUMBER = PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR.PARAMETER_SEQUENCE_NUMBER
WHERE (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.FACILITY)='TDH2MS'
AND(PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT) Like '1E24%'
AND (PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.PARAMETER_NAME) Like '%LOSS'
AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TIME_STAMP)>'29/3/2013 '
AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE)='MVOU'
GROUP BY PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE

Hi, I wrote this on SQL developer ,it show the data without error. But when I wrote this in Excel vba, it doesn't work. anyone know how to solve?


strSQL = "TRANSFORM Sum(PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR.PARAMETER_VALUE) AS SumOfPARAMETER_VALUE" & _
"SELECT PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT, PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE" & _
"FROM PS_DWH_ADMIN.DWH_WIP_DATA_YEAR" & _
"JOIN PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR" & _
"ON PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.OPERATION = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.OPERATION " & _
"AND PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.FACILITY = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.FACILITY " & _
"AND PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.LOT" & _
"JOIN PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR" & _
"ON PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.PARAMETER_SEQUENCE_NUMBER = PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR +
.PARAMETER_SEQUENCE_NUMBER" & "WHERE (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.FACILITY)='TDH2MS'" & _
"AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT) Like '1E24%'" & _
"AND (PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.PARAMETER_NAME) Like '%LOSS' " & _
"AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TIME_STAMP)>'30/3/2013 ' " & _
"AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE)='MVOU'" & _
"GROUP BY PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE"

[sassora]

One definite issue is that you need spaces between each line.

If you use

debug.print strSQL
you can see that the concatenated string isn't quite right.

I've added a few spaces in the code below and >'30/3/2013 ' to >'30/3/2013'.


strSQL = "TRANSFORM Sum(PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR.PARAMETER_VALUE) AS SumOfPARAMETER_VALUE " & _
"SELECT PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT, PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE " & _
"FROM PS_DWH_ADMIN.DWH_WIP_DATA_YEAR " & _
"JOIN PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR " & _
"ON PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.OPERATION = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.OPERATION " & _
"AND PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.FACILITY = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.FACILITY " & _
"AND PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.LOT " & _
"JOIN PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR " & _
"ON PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.PARAMETER_SEQUENCE_NUMBER = PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR.PARAMETER_SEQUENCE_NUMBER " & _
"WHERE (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.FACILITY)='TDH2MS' " & _
"AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT) Like '1E24%' " & _
"AND (PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.PARAMETER_NAME) Like '%LOSS' " & _
"AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TIME_STAMP)>'30/3/2013' " & _
"AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE)='MVOU' " & _
"GROUP BY PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE"

I think the quotation marks may cause a problem too, but it's been a while since I've done any of this. Let's see if this works first.

[purplesky]

Thanks sassora, by removing the space it really works.
Now 1 more question, actually I have 2"JOIN" statement in a SQL. I found that if one JOIN can works, if put 2 JOIN together doesn't work already. It shows error missing keyword.

trSQL = "SELECT PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE ,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TIME_STAMP, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.OWNER,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.OPERATI ON,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.PRODUCT," & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.PRODUCT_TYPE,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR. PROCESS_GROUP, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.PROCESS_CLASS,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR .ROUTE, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.MEAS_STEP,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.BAS IC_TYPE, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.""OPERATOR"",PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.EQUIPMENT, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSACTION_COMMENT,PS_DWH_ADMIN.DWH_WIP_DAT A_YEAR.QTY_IN_1,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.QTY_OUT_1 " & _
"FROM PS_DWH_ADMIN.DWH_WIP_DATA_YEAR " & _
"JOIN PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR " & _
"ON PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.OPERATION = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.OPERATION " & _
"AND PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.FACILITY = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.FACILITY " & _
"AND PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT = PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.LOT " & _
"JOIN PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR" & _
"ON PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.PARAMETER_SEQUENCE_NUMBER = PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR.PARAMETER_SEQUENCE_NUMBER"
strSQL = strSQL & "WHERE(PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TIME_STAMP)>to_date('1/4/2013', 'dd/mm/yyyy') " & _
"AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.FACILITY)='TDH2MS' " & _
"AND (PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE)='MVOU' " & _
"AND (PS_DWH_ADMIN.DWH_WIP_EDC_DATA_YEAR.PARAMETER_NAME)='KELVIN-LOSS' " & _
"GROUP BY PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.LOT,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSCODE ,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TIME_STAMP, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.OWNER,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.OPERATI ON,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.PRODUCT, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.PRODUCT_TYPE,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR. PROCESS_GROUP, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.PROCESS_CLASS,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR .ROUTE, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.MEAS_STEP,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.BAS IC_TYPE, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.""OPERATOR"",PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.EQUIPMENT, " & _
"PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.TRANSACTION_COMMENT,PS_DWH_ADMIN.DWH_WIP_DAT A_YEAR.QTY_IN_1,PS_DWH_ADMIN.DWH_WIP_DATA_YEAR.QTY_OUT_1"

[sassora]

This line needs a space at the end:


"JOIN PS_DWH_ADMIN.DWH_WIP_EDC_VALUES_YEAR" & _


And also the next line.