asked on

What's wrong with this SQL?

Here's my SELECT:

"select r.ID as `*ID`,   
               p.PROJ_NAME as `Project Name`,                  
                    r.REQID as `Request ID`,
                    r.STATUS as `Status`,
                    r.ALT_TRACKING_ID as `Alternate Tracking ID`,
                    r.INTERNAL as `Internal`,
                    r.NAME as `Request Name`,
               r.PRIORITY as Priority,
               cr.RELEASE as `Target Release`,
               concat(ua.FIRST_NAME,' ',ua.LAST_NAME) as `CR Approver`,
                    concat(ub.FIRST_NAME,' ',ub.LAST_NAME) as BUSINESS_OWNER,

               (select group_concat(`DESCR` SEPARATOR '<br>')
                    from idb_cfg.config
                    where OBJECT = 'PRODUCT_AMS'
                    and find_in_set(VALUE, 'AMS_A,TAX_ACCT,US_PAY')) as `Workstreams`, 
               
               r.TOTALS as `CSG Workstream Total`,
               date_format(rams.CR_APPROVAL_DATE,'%m/%d/%Y') as `CR Approval Date`,  
               r.NOTES as `CR Quick Notes`,
               CONCAT(u.FIRST_NAME, ' ', u.LAST_NAME) AS `Created By`,
                    date_format(r.CREATED_DT,'%d/%m/%Y %h:%i %P') AS `Created On`,
                    CONCAT(ud.FIRST_NAME, ' ', ud.LAST_NAME) AS `Last Modified By`,
                    date_format(r.MODIFIED_DT,'%d/%m/%Y %h:%i %P') AS `Last Modified On`,";

Open in new window

Here's how it looks in PhpStorm:

Notice the way in which it's registering an error at the end of the statatement?

If I remove:

(select group_concat(`DESCR` SEPARATOR '<br>')
               from idb_cfg.config
               where OBJECT = 'PRODUCT_AMS'
               and find_in_set(VALUE, 'AMS_A,TAX_ACCT,US_PAY')) as `Workstreams`,

Open in new window

Now, I get this:

Notice how there's no error now...

What is it about:

(select group_concat(`DESCR` SEPARATOR '<br>')
               from idb_cfg.config
               where OBJECT = 'PRODUCT_AMS'
               and find_in_set(VALUE, 'AMS_A,TAX_ACCT,US_PAY')) as `Workstreams`,

Open in new window

...that's triggering the error?

HainKurt

looks like you have some screenshots and EE has issues with them!
nobody can see the images...
so, save images and attach instead of copy paste (not working!)

Bruce Gust

ASKER

Good to go, HainKurt!

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

your sql is not correct, remove "," at the end!
just before "

Line 24

... `Last Modified On`,";

Chris Stanyon

@HainKurt - already covered the comma issue !!

I'm guessing that comma is in there because the Query hasn't been completed ... more code later on maybe (there's no FROM statements specifying the tables for example - it looks like a partial SQL statement). PHPStorm is indicating a PHP error (probably erroneous).

HainKurt

I am not sure...
we need to see the whole query, just before running...

what happens if you use

SEPARATOR '<br>'
>>>
SEPARATOR '|'

do you get error?

HainKurt

also, what about this syntax

$Sql_Get = <<<SQL
Select ...
...
SQL;

Open in new window

and if you use something like $Param1 inside, it will automatically replaced with value...

Strings - nowdoc syntax
https://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.nowdoc

azup

Is is okay to not use single quotes on the alias?

concat(ub.FIRST_NAME,' ',ub.LAST_NAME) as BUSINESS_OWNER,

Open in new window

All the other aliases are using single quotes.

HainKurt

dont use quote on such thing if it does not have any space or special characters, "_" is ok...
and you should not use space in column names (my opinion! never used such thing unless it is absolutely necessary)...
You should leave that part to UI/APP

r.STATUS as `Status`,
>>>
r.STATUS as Status,

slightwv (䄆 Netminder)

>>All the other aliases are using single quotes.

What you posted aren't single quotes. They are back-quotes or smart-quotes.

This is a single quote: '
This isn't: `

azup

Thank you slightwv for pointing that out.

Bruce Gust

ASKER

Gentlemen, I was concerned about what appeared to be a situation where PhpStorm was registering an error. Fact is, it was a warning and not an error.

I had a co-worker point out that if there's a legitimate problem, PhpStorm will display the title of the file with a red line underneath it. Otherwise, it's a warning and not a dealbreaker.

Thanks for your input!