Unanswered

SQL statement showing Invalid Syntax

(1) Share

Report

Posted on by SarathNair

I have this Below statement showing Invalid Syntax

SELECT
 warehouse,
 COMPANY,
 CAST(INTERFACED_DATE AS date) IntDate,
 LEFT(ORDER_TYPE, 4) OType,
 COUNT(DISTINCT erp_order) AS erp_order_count,
 SUM(total_qty) AS qty,
 COUNT(internal_shipment_line_num) AS shipment_line_count,
 CASE
 WHEN COMPANY IN ('AMERICAN EAGLE', 'HM', 'HP', 'MONKI', 'BOOTS', 'WEST ELM', 'WILLIAM SONOMA', 'POTTERY BARN', 'POTTERY BARN KIDS', 'OTH')
 AND ORDER_TYPE IN ('ALLOCATION', 'DISTRIBUTION', 'PREALLOCATION', 'REPLENISHMENT') THEN 'BY-RMS-ILS'
 WHEN COMPANY NOT IN ('AMERICAN EAGLE', 'HM', 'HP', 'MONKI', 'BOOTS', 'WEST ELM', 'WILLIAM SONOMA', 'POTTERY BARN', 'POTTERY BARN KIDS', 'OTH')
 AND ORDER_TYPE IN ('ALLOCATION', 'DISTRIBUTION') THEN 'AIMS-ILS' 
 ELSE ORDER_TYPE
 END AS SOURCES
FROM SHIPMENT_DETAIL
WHERE CAST(INTERFACED_DATE AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
 AND company != 'CPT'
GROUP BY warehouse, CAST(INTERFACED_DATE AS date), LEFT(ORDER_TYPE, 4), COMPANY, ORDER_TYPE

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (0)

Sort by

VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @SarathNair ,

To fix the SQL syntax for running it in Power Automate Desktop, you can ensure the query is properly formatted and aligned with standard SQL practices. Here is the revised version of your query:

SELECT warehouse, COMPANY, CAST(INTERFACED_DATE AS DATE) AS IntDate, LEFT(ORDER_TYPE, 4) AS OType, COUNT(DISTINCT erp_order) AS erp_order_count, SUM(total_qty) AS qty, COUNT(internal_shipment_line_num) AS shipment_line_count, CASE WHEN COMPANY IN ('AMERICAN EAGLE', 'HM', 'HP', 'MONKI', 'BOOTS', 'WEST ELM', 'WILLIAM SONOMA', 'POTTERY BARN', 'POTTERY BARN KIDS', 'OTH') AND ORDER_TYPE IN ('ALLOCATION', 'DISTRIBUTION', 'PREALLOCATION', 'REPLENISHMENT') THEN 'BY-RMS-ILS' WHEN COMPANY NOT IN ('AMERICAN EAGLE', 'HM', 'HP', 'MONKI', 'BOOTS', 'WEST ELM', 'WILLIAM SONOMA', 'POTTERY BARN', 'POTTERY BARN KIDS', 'OTH') AND ORDER_TYPE IN ('ALLOCATION', 'DISTRIBUTION') THEN 'AIMS-ILS' ELSE ORDER_TYPE END AS SOURCES FROM SHIPMENT_DETAIL WHERE CAST(INTERFACED_DATE AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AND COMPANY != 'CPT' GROUP BY warehouse, COMPANY, CAST(INTERFACED_DATE AS DATE), LEFT(ORDER_TYPE, 4), ORDER_TYPE;

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No
SarathNair 7 on at

Like
a
(1)

Report
Copy link

Link copied!
HI Vishnu,
Sorry, that was working when i add the below, it gives Invalid Value / Syntax.

WHEN COMPANY NOT IN ('AMERICAN EAGLE', 'HM', 'HP', 'MONKI', 'BOOTS', 'WEST ELM', 'WILLIAM SONOMA', 'POTTERY BARN', 'POTTERY BARN KIDS', 'OTH') AND ORDER_TYPE LIKE ('ECOM%') THEN 'COMS-ILS'

Was this reply helpful? Yes No
NathanAlvares24 1,714 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @SarathNair !

Can you check if this works:

SELECT warehouse, COMPANY, CAST(INTERFACED_DATE AS date) AS IntDate, LEFT(ORDER_TYPE, 4) AS OType, COUNT(DISTINCT erp_order) AS erp_order_count, SUM(total_qty) AS qty, COUNT(internal_shipment_line_num) AS shipment_line_count, CASE WHEN COMPANY IN ('AMERICAN EAGLE', 'HM', 'HP', 'MONKI', 'BOOTS', 'WEST ELM', 'WILLIAM SONOMA', 'POTTERY BARN', 'POTTERY BARN KIDS', 'OTH') AND ORDER_TYPE IN ('ALLOCATION', 'DISTRIBUTION', 'PREALLOCATION', 'REPLENISHMENT') THEN 'BY-RMS-ILS' WHEN COMPANY NOT IN ('AMERICAN EAGLE', 'HM', 'HP', 'MONKI', 'BOOTS', 'WEST ELM', 'WILLIAM SONOMA', 'POTTERY BARN', 'POTTERY BARN KIDS', 'OTH') AND ORDER_TYPE IN ('ALLOCATION', 'DISTRIBUTION') THEN 'AIMS-ILS' WHEN COMPANY NOT IN ('AMERICAN EAGLE', 'HM', 'HP', 'MONKI', 'BOOTS', 'WEST ELM', 'WILLIAM SONOMA', 'POTTERY BARN', 'POTTERY BARN KIDS', 'OTH') AND ORDER_TYPE LIKE 'ECOM%' THEN 'COMS-ILS' ELSE ORDER_TYPE END AS SOURCES FROM SHIPMENT_DETAIL WHERE CAST(INTERFACED_DATE AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AND COMPANY != 'CPT' GROUP BY warehouse, CAST(INTERFACED_DATE AS date), LEFT(ORDER_TYPE, 4), COMPANY, ORDER_TYPE

The LIKE clause is not like this:

('ECOM%')

Its like this without the () as it needs to find a string pattern:

'ECOM%'

Was this reply helpful? Yes No
SarathNair 7 on at

Like
a
(1)

Report
Copy link

Link copied!

Nope @NathanAlvares24 Still showing Invalid Value.

Was this reply helpful? Yes No