0

So I'm new to SQL. I have to ensure this file works on both Oracle and SQL Server. For this I use SQL Tools (Oracle) and sQL Server Management Studio.

I'm begining to learn the different between PL/SQL and TSQL (I think), which is basically just to allow them to work together as there are slight differences.

Well, I have this code, and it works for one (SQL Tools), and not for the other. And I dont know why. They both need to work and be the same code.

    SELECT DISTINCT * FROM
(
SELECT
somestuff
WHERE mu.id = {my_users_id}
)
WHERE (1=1)
{{parent:ParentUsers:mu_ID}}

Now, I dont know why that doesnt work. It says there is a problem with the WHERE clause. If I comment out the line WHERE mu.id = {my_users_id} and remove {{parent:ParentUsers:mu_ID}} then it works in SQL Tools. but not in Management Studio.

Why? And what is going on here?

I thought the point in commenting out code, any code, was so that it doesnt get read, yet I seem to be getting the impression sometimes in some situations they do get read?? Am I wrong here? Thanks

Edit: I realise the lack of code isnt helping, so I've tried to change some things here to represent the real code as much as possible. 

    SELECT DISTINCT * FROM
(
SELECT
eb2.id AS EB_ID,
ebmystuff_pkg.user_entry_count(EB2.user_code, EB2.address_code, EB2.other_column) ENTRIES,
eb.other_column AS OTHER_COLUMN
FROM MY_TABLE mt
WHERE mu.id = {my_users_id}
)
WHERE (1=1)
{{parent:ParentUsers:mu_ID}}
Boneist
  • 22,910
  • 1
  • 25
  • 40
user2903379
  • 375
  • 3
  • 22
  • 3
    `psql` is an interactive tool used with PostgreSQL. PL/SQL is a programming language used with Oracle. Which one are you using? – Bob Jarvis - Слава Україні Mar 09 '16 at 12:17
  • 4
    It may depend on what kind of commenting-out you are doing, and how the tools interpret that before sending it to the DB. You haven't shown any errors so it's hard to guess what's happening. But curly braces are not part of SQL or PL/SQL syntax, so the tools are probably substituting other strings in those places - and may be doing it very differently, and I guess one doesn't use/expand them at all.. Don't confuse client/IDE/tool functionality with SQL or database functionality. – Alex Poole Mar 09 '16 at 12:18
  • @BobJarvisin it's PL/SQL. And AlexPoole the error I get is: "Incorrect syntax near the keyword WHERE" Message 156, Level 15, State 1 – user2903379 Mar 09 '16 at 12:42
  • That's coming from SQL Server I believe. You haven't shown a `from` clause, but 'somestuff' may really include that. Anyway, it looks like SQL Tools is replacing the curly braces with proper syntax, and Management Studio isn't - and SQL Server doesn't understand the braces it receives. Or vice versa, it isn't entirely clear. Are they supposed to be ODBC escape sequences maybe? – Alex Poole Mar 09 '16 at 12:49
  • @AlexPoole I've added an edit there to help, im sorry im not explaining very well. – user2903379 Mar 09 '16 at 13:05

2 Answers2

0

The first think I see on your code that is wrong is a missing from:

SELECT DISTINCT * FROM
(
  SELECT somestuff
  **FROM** sometable mu
  WHERE mu.id = {my_users_id}
)
WHERE (1=1)
{{parent:ParentUsers:mu_ID}}

Now, I know that in SQL Server, the {} does not mean anything... to add a variable there you should use @my_users_id.

Walter_Ritzel
  • 1,387
  • 1
  • 12
  • 16
0

I found that the answer was to add an alias to it.

I did

   SELECT DISTINCT * FROM
(
  SELECT somestuff
  **FROM** sometable mu
 -- WHERE mu.id = {my_users_id}
)m
WHERE (1=1)
user2903379
  • 375
  • 3
  • 22