Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Reference a value in a table to use in another SQL power Query

Hi All   I have a value held in another PQ table. 1 row 1 column containing an invoice number. - Is it possible to use this particular invoice number and use in a SQL query without having to go int...
Need Help
Tips and Tricks
  • KNP's avatar
    KNP
    4 years ago

    Double quote missing before the closing square bracket I think. 

    I notice you're missing a table alias for your SHIP_DATE_RETURN, not sure if that will cause you any abiguity issues.

    This is just the query portion, make sure it goes inside the correct ().

    [Query="
SELECT rd.SERIAL_NUMBER
     , rd.REF_NUM
     , rd.MODEL_ASC
     , pm.MODEL
     , rd.MOTO_PART_NUMBER
     , rd.ASC_SITE_ID
     , ras.ASC_SITE_NAME
     , rd.REPAIR_TYPE
     , acm.ASC_CUSTOMER_NAME
     , rd.WARR_CODE
     , rd.RMA_CREATION_DATE
     , rd.EXPIRY_DATE
     , rd.RECV_DATE
     , rd.SHIP_DATE_RETURN
     , rd.UNIT_STATUS
     , rd.PROJECT_NAME
     , rd.FINAL_UNIT_DETERMINATION
     , rd.INVOICE_NUMBER
     , rd.INVOICE_DATE
     , ROUND((SHIP_DATE_RETURN - rd.RECV_DATE)) AS AGE
     , (rd.RECV_DATE + 30)                      AS TAT
     , rd.LABCOST
     , rd.FREIGHT_COST
FROM rdwadmin.rdw_repair_details rd
   , rdwadmin.rdw_part_master pm
   , rdwadmin.rdw_asc_customer_master acm
   , rdwadmin.rdw_asc_site ras
WHERE rd.moto_part_number = pm.moto_part_number
      AND rd.ASC_CUSTOMER_NUMBER = acm.ASC_CUSTOMER_NUMBER
      AND rd.ASC_SITE_ID = ras.ASC_SITE_ID
      AND rd.INVOICE_NUMBER = '"& #"Current-Invoice"[CurrentInvoice]{0} &"'

"]

     

KNP's avatar
KNP
Super User
4 years ago

This looks like your issue. Helps now that I know what the table is called. ğŸ˜‰

 

Change the above to...

'"& #"Current-Invoice[CurrentInvoice]{0} &"'

 

If you renamed your query you wouldn't need to reference the table with the #"".

Pay close attention to the quotes. Opens with single, double. Closes with double, single.

Make sure you don't remove the last ]) that closes off the "Query".

 

I think this will get you there.

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

So i amended the name, but it didnt like the extra ' at the end as you can see...

 

 

I removed the extra ' from the end the, PQ then started loading then came up with the below:

 

 

Though the name is spelled correctly?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi KNP - Do you have any further suggestions on this, I think we are so close on this.

    • KNP's avatar
      KNP
      Super User
      4 years ago

      Anonymous - sorry, I think this is my typo that has confused things.

      I think it should be...

      '"& #"Current-Invoice"[CurrentInvoice]{0} &"'

      It was missing the double quote to close off the table reference.

      Give that a try and let me know how you go.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        KNP Thanks for the reply. 

         

        I have added the change: It says there is an invalid literal

         

        Here is the query section, not sure if this makes it easier?

         

        [Query="select rd.SERIAL_NUMBER, rd.REF_NUM, rd.MODEL_ASC, pm.MODEL, rd.MOTO_PART_NUMBER, rd.ASC_SITE_ID, ras.ASC_SITE_NAME, rd.REPAIR_TYPE, acm.ASC_CUSTOMER_NAME, rd.WARR_CODE, rd.RMA_CREATION_DATE, rd.EXPIRY_DATE, rd.RECV_DATE, rd.SHIP_DATE_RETURN, rd.UNIT_STATUS, rd.PROJECT_NAME, rd.FINAL_UNIT_DETERMINATION, rd.INVOICE_NUMBER, rd.INVOICE_DATE, round((SHIP_DATE_RETURN - rd.RECV_DATE)) as AGE, (rd.RECV_DATE+30) AS TAT, rd.LABCOST, rd.FREIGHT_COST #(lf)from rdwadmin.rdw_repair_details rd, rdwadmin.rdw_part_master pm, rdwadmin.rdw_asc_customer_master acm, rdwadmin.rdw_asc_site ras#(lf)where rd.moto_part_number = pm.moto_part_number#(lf)AND rd.ASC_CUSTOMER_NUMBER = acm.ASC_CUSTOMER_NUMBER #(lf)AND rd.ASC_SITE_ID = ras.ASC_SITE_ID #(lf)AND rd.INVOICE_NUMBER = '"& #"Current-Invoice"[CurrentInvoice]{0} &"'])

         

        Thanks, really apprecite your help on this ğŸ™‚